Principles of Finance with Excel (Основы финансов c Excel)

Подождите немного. Документ загружается.

PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 8

result of the “A” coin, the result of the “B” coin will be the same. In statistical terms this is a

correlation of +1. Will diversification improve your returns in this situation?

ABCD E FGH I JKL

Payof

Return Probability

120 20% 0.5

92 -8% 0.5

Return statistics

Average return 6.00% <-- =SUMPRODUCT(G4:G21,H4:H21)

Variance 0.0196 <-- =VARP(G4:G21)

Standard deviation 14.00% <-- =SQRT(D26)

CASE 3: FLIPPING A COUNTERFEIT COIN--TWO COIN TOSSES WITH CORRELATION +1

$100 invested:

$50 in A

$50 in B

comes up heads

B comes up heads

Probability: 0.5 *1=0.5

Payoff: $50*1.2 (A) + $50*1.2 (B) = $120

comes up tails

B comes up tails

Probability: 0.5*1=0.5

Payoff: $50*0.92 (A) + $50*0.92 (B) = $92

This can't happen: The coins

are completely correlated, so

we can't have a heads in one

and a tails in the second.

As you can see the returns from splitting your investment between two assets are

identical to the return of only investing in one asset (cells D25:D27). Both the average return

and the return standard deviation are the same as in case 1, where we flipped only one coin.

Message: When the asset returns are perfectly positively correlated, diversification will

not reduce your risk.

Case 4: The case of the counterfeit coin—correlation of -1

We’re still on the same example, and our coin is still counterfeit. But this time it’s

counterfeit with a perfectly negative correlation (-1): If coin “A” comes up heads, coin “B” will

come up tails. In statistical terms, the correlation between the two coins is -1. For this case we

can find a portfolio that completely eliminates all risk: By splitting our investment between

assets A and B, we get 6 percent expected return without any standard deviation:

PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 9

ABCD E FGH I JKL

Payof

Return Probability

106 6% 0.5

Return statistics

Average 6.00% <-- =SUMPRODUCT(G5:G20,H5:H20)

Variance 0 <-- =VARP(G5:G20)

Standard deviation 0.00% <-- =SQRT(D26)

CASE 4: FLIPPING A COUNTERFEIT COIN--TWO COIN TOSSES WITH CORRELATION -1

$100 invested:

$50 in A

$50 in B

comes up heads

B comes up tails

Probability: 0.5*1=0.5

Payoff: $50*1.2 (A) + $50*0.92 (B) = $106

comes up tails

B comes up heads

Probability: 0.5*1=0.5

Payoff: $50*0.92 (A) + $50*1.2 (B) = $106

This can't happen: The coins

are completely correlated, so

we can't have a tails in one and

a tails in the second.

This can't happen: The coins

are completely correlated, so

we can't have a heads in one

and a heads in the second.

Message: When the asset returns are perfectly negatively correlated, diversification can

completely eliminate all risk.

Case 5: The partially counterfeit coin (the real world?)

In the real world there’s often a connection between the stock prices of one company and

those of another. In the most general handwaving

way, stock prices reflect two elements:

• How well a particular business is doing: In some industries this element leads to negative

correlation. For example if Procter & Gamble (a major manufacturer of toothpastes,

laundry soaps, and so on) is gaining market share, it is likely to be at the expense of

Unilever (another company in the same industry). This isn’t always true, though: If Intel

(a major manufacturer of computer chips) is doing well, then it may be that the computer

The website http://c2.com defines “handwaving” as: “Handwaving is what people do when they don't want to tell

you the details, either because they don't want to get bogged down, they don't know, nobody knows, or they have

sinister ulterior motives.”

PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 10

industry is expanding and that AMD (another player in the same industry) is also doing

well.

• How well the economy is doing: Stock prices are heavily affected by the performance of

the economy. This factor tends to be an across-the-board factor, leading to positive

correlation: When the stock market as a whole is up, most stock prices tend to be up, and

vice versa. For stock prices, this factor tends to dominate the first: in general stock

prices move together, though their correlation is far from complete.

Notice how careful we’ve been here in our language: We’ve used words like “tend to go

up”—stock prices are only partially, not perfectly, correlated.

In order to model partial correlation with our coin toss example, we’ll assume that the

“A” coin result influences the result of the “B” coin, but not completely. If the “A” coin comes

up heads (this happens with a probability of 0.5), the probability of the “B” coin coming up

heads is 0.7. If the “A” coin comes up tails (probability 0.5), the probability that the “B” coin

also comes up tails is 0.7. Here’s the spreadsheet that summarizes the returns:

Negative correlation in stock returns can also happen: Our General Motors-Microsoft example in Chapter 11—to

which we return in Section 12.2 of this chapter—is an illustration.

PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 11

ABCD E FGH I JK

Payof

Return Probability

120 20% 0.35

106 6% 0.15

92 -8% 0.35

Return statistics

Average 6.00% <-- =SUMPRODUCT(G4:G21,H4:H21)

Variance 0.01372 <-- =H4*(G4-$D$25)^2+H11*(G11-D25)^2+H14*(G14-D25)^2+H21*(G21-D25)^2

Standard deviation 11.71% <-- =SQRT(D26)

CASE 5: FLIPPING A PARTIALLY COUNTERFEIT COIN

TWO COIN TOSSES WITH IMPERFECT CORRELATION

$100 invested:

$50 in A

$50 in B

comes up heads

B comes up heads

Probability: 0.5*0.7=0.35

Payoff: $50*1.2 (A) + $50*1.2 (B) = $120

comes up tails

B comes up tails

Probability: 0.5*0.7=0.35

Payoff: $50*0.92 (A) + $50*0.92 (B) = $92

comes up heads

B comes up tails

Probability: 0.5*0.3=0.15

Payoff: $50*1.2 (A) + $50*0.92 (B) = $106

comes up tails

B comes up heads

Probability: 0.5*0.3=0.15

Payoff: $50*0.92(A) + $50*1.2 (B) = $106

Message: When the asset returns are partially correlated, diversification will reduce risk

but not completely eliminate it.

What's the point?

Though the two-asset, two-coin examples are simple and farfetched, the lessons you learn

from these examples also apply in the “real world” cases of asset diversification:

• If the correlation between asset returns is +1 then diversification will not reduce portfolio

risk.

• If the correlation between asset returns is -1 then we can create a risk-free asset—an asset

with no uncertainty about its returns (a bank savings account is an example)—using a

portfolio of the two assets.

PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 12

• In the real world asset returns are almost never fully correlated. When asset returns are

partially, but not completely, correlated (meaning that the correlation is between -1 and

+1), diversification can lower risk, though it cannot completely eliminate it.

12.2. Back to the real world—Microsoft and General Motors

In Chapter 11 we calculated the data for the annual returns on General Motors (GM)

stock and on Microsoft (MSFT) stock for the 10 years between 1990 – 1999. Here are our

calculations:

ABC D

Date

return

MSFT

return

31-Dec-90 -11.54% 72.99%

31-Dec-91 -11.35% 121.76%

31-Dec-92 16.54% 15.11%

31-Dec-93 72.64% -5.56%

30-Dec-94 -21.78% 51.63%

29-Dec-95 28.13% 43.56%

31-Dec-96 8.46% 88.32%

31-Dec-97 19.00% 56.43%

31-Dec-98 21.09% 114.60%

31-Dec-99 21.34% 68.36%

Average 14.25% 62.72% <-- =AVERAGE(C3:C12)

Variance 0.0638 0.1443 <-- =VARP(C3:C12)

Standard deviation 25.25% 37.99% <-- =STDEVP(C3:C12)

Covariance of returns -0.0552 <-- =COVAR(B3:B12,C3:C12)

Correlation of returns -0.5755 <-- =B17/(B16*C16)

GM AND MSFT

RETURN STATISTICS, 1990-1999

You can see that the average return of holding GM stock (14.25% per year) is much

lower than the average return of holding MSFT stock (62.73%). On the other hand, the risk of

holding Microsoft—measured by either the variance or by the standard deviation of the return—

is higher than the risk of General Motors: This is the tradeoff we would expect—GM has lower

PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 13

return and lower risk than MSFT. Note also that GM and Microsoft returns are negatively

correlated (cell B18): On average, an increase in MSFT returns was accompanied by a decrease

in GM returns. If you use Excel to plot GM returns on the x-axis and MSFT returns on the y-

axis, you can detect a slight “northwest to southeast” pattern in the returns.

MSFT versus GM Returns

y = -0.8656x + 0.7506

= 0.3312

-20%

20%

40%

60%

80%

100%

120%

140%

-40% -20% 0% 20% 40% 60% 80%

GM returns

MSFT returns

The trendline (which illustrates the regression of MSFT on GM) shows this trend.

12.3. Graphing portfolio returns

In this section we graph the returns available to the investor from an investment in a

portfolio composed of GM and MSFT stock. We start by showing you several individual

As explained in Chapter 11, the regression R

indicates the percentage MSFT’s return variability explained by the

variability in GM’s returns. R

is the correlation coefficient squared:

()

0.3312 , 0.5755

GM MSFT

R Correlation Return Return



== =−



. While this R

may seem low, it is typical for

the relation between 2 stocks.

PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 14

portfolios, and end the section by graphing the curve representing all the possible portfolio

returns.

Deriving the risk-return of an individual portfolio

Suppose we form a portfolio composed of 50% GM and 50% MSFT stock. Cells E8:E17

in the spreadsheet below show the annual returns of this portfolio:

ABCDEF

Portfolio proportions

Percentage in GM 50%

Percentage in MSFT 50% <-- =1-B3

Date

Portfolio

returns

GM MSFT

Dec-90 -11.54% 72.99% 30.73% <-- =$B$3*B8+$B$4*C8

Dec-91 -11.35% 121.76% 55.21%

Dec-92 16.54% 15.11% 15.82%

Dec-93 72.64% -5.56% 33.54%

Dec-94 -21.78% 51.63% 14.93%

Dec-95 28.13% 43.56% 35.84%

Dec-96 8.46% 88.32% 48.39%

Dec-97 19.00% 56.43% 37.71%

Dec-98 21.09% 114.60% 67.85%

Dec-99 21.34% 68.36% 44.85%

Average 14.25% 62.72% 38.49% <-- =AVERAGE(E8:E17)

Variance 6.38% 14.43% 2.44% <-- =VARP(E8:E17)

Sigma 25.25% 37.99% 15.62% <-- =STDEVP(E8:E17)

Covariance of returns -5.52% <-- =COVAR(B8:B17,C8:C17)

A PORTFOLIO OF GM AND MSFT STOCK

Stock returns

As discussed in Chapter 11, the portfolio return statistics in cells E19:E21 can be derived

using formulas which involve only information about the individual asset returns, their variances,

and the covariance. There’s no need to do the extensive calculation in cells E19:E21:

• The average portfolio return of 38.49% is the weighted average of the GM and the MSFT

return. Write the percentage weight of GM stock by w

and the percentage weight of

PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 15

MSFT stock by w

MSFT

; it follows, of course, that w

MSFT

= 1 – w

, since the portfolio

proportions must sum to 100%. The formula for the average portfolio return is:

(

)

(

)

(

)

()( )( )

p GM GM MSFT MSFT

GM GM GM MSFT

average portfolio return E r w E r w E r

wEr w Er

=+−

•

The variance of the portfolio return, 2.44%, is a more complicated function of the two

variances and the portfolio weights:

()

() ( ) ( )

Each portfolio weight Twice the product of

is squared and multiplied the portfolio weights

times the variance t

p GM GM MSFT MSFT GM MSFT GM MSFT

variance of portfolio return

Varr w Varr w Varr w w Covr r=+ +

↑





imes the covariance

By using these two formulas, you avoid the need for the long calculation of the portfolio

return, variance, and standard deviation in cells E8:E21. In the spreadsheet below we

incorporate these formulas for the portfolio mean, variance and standard deviation in cells

B12:B14:

PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 16

ABC D

GM MSFT

Average 14.25% 62.72%

Variance 6.38% 14.43%

Sigma 25.25% 37.99%

Covariance of returns -5.52% <-- =COVAR(B9:B18,C9:C18)

Portfolio return and risk

Percentage in GM 50%

Percentage in MSFT 50%

Expected portfolio return 38.49% <-- =B9*B3+B10*C3

Portfolio variance 2.44% <-- =B9^2*B4+B10^2*C4+2*B9*B10*B6

Portfolio standard deviation 15.62% <-- =SQRT(B13)

Portfolio

standard

deviation

Expected

portfolio

return

50% GM, 50% MSFT 15.62% 38.49% 50%

PORTFOLIO STATISTICS

FOR A GM-MSF PORTFOLIO

Portfolio Returns: Expected Return E(r

) and

Standard Deviation

10%

15%

20%

25%

30%

35%

40%

45%

0% 5% 10% 15% 20%

Standard deviation σ

Expected return E(r

)

Portfolio standard

deviation (15.62%) and

expected return (38.49%)

from a portfolio invested

50% in GM and 50% in

MSFT.

The point here is that you don’t need to do an extensive calculation of annual portfolio

returns—it’s enough to know the return statistics for each stock, the portfolio proportions, and

the covariance of the stock returns.

Another portfolio—increasing the weight of MSFT, decreasing GM

Now suppose we graph another portfolio—this time a portfolio invested 25% in GM and

75% in Microsoft:

PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 17

ABC D

GM MSFT

Average 14.25% 62.72%

Variance 6.38% 14.43%

Sigma 25.25% 37.99%

Covariance of returns -5.52% <-- =COVAR(B9:B18,C9:C18)

Portfolio return and risk

Percentage in GM 25%

Percentage in MSFT 75%

Expected portfolio return 50.60% <-- =B9*B3+B10*C3

Portfolio variance 6.44% <-- =B9^2*B4+B10^2*C4+2*B9*B10*B6

Portfolio standard deviation 25.39% <-- =SQRT(B13)

Portfolio

standard

deviation

Expected

portfolio

return

50% GM, 50% MSFT 15.62% 38.49%

25% GM, 75% MSFT 25.39% 50.60%

PORTFOLIO STATISTICS

FOR A GM-MSFT PORTFOLIO

Portfolio Returns: Expected Return E(r

) and

Standard Deviation

10%

20%

30%

40%

50%

60%

0% 5% 10% 15% 20% 25% 30%

Standard deviation of return σ

Expected return E(r

)

= 50%,w

MSFT

= 50%,

=15.62%, E(r

)=38.49%

= 25%,w

MSFT

= 75%,

=25.39%, E(r

)=50.60%

Notice that the new portfolio’s performance is to the “northeast” of the first portfolio—it

has both higher returns and higher standard deviation. The new portfolio gives you greater

expected return, but has higher risk. This is what you would expect—higher return is achieved at

the price of higher risk. As you will see in the next subsection, this may not always be the case.