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

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

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

First, recall what we concluded in section 12.1:

•

When the two coins have a perfect negative correlation of -1, we can create a risk-free

asset using combinations of the two assets. In this section you’ll see that a similar

conclusion is true for stock portfolios: Perfectly negatively correlated stock returns allow

you to create a risk-free asset.

•

When the two coins have a perfect positive correlation of +1, it’s impossible to diversify

away any risk. You will see that a similar conclusion holds for stock portfolios.

•

When the two coins have correlation between -1 and +1, some of the risk can be

eliminated through diversification. Again this is true for stock portfolios.

In our example we use some of the same numbers used in our GM-MSFT example, but

we’ll allow the correlation between the returns on the two stocks to vary. We start with the

following example, in which the correlation coefficient between GM and MSFT is

GM,MSFT

0.5.

ABCDEFGHIJ

GM MSFT

Average 14.25% 62.72%

Variance 6.38% 14.43%

Sigma 25.25% 37.99%

Correlation coefficient,

GM,MSFT

0.50

Covariance 0.05 <-- =B6*B5*C5

Percentage

in GM Sigma

Expected

return

0.0 37.99% 62.72%

0.1 35.52% 57.87%

0.2 33.20% 53.03%

0.3 31.08% 48.18%

0.4 29.18% 43.33%

0.5 27.57% 38.49%

0.6 26.28% 33.64%

0.7 25.37% 28.79%

0.8 24.89% 23.95%

0.9 24.85% 19.10%

1.0 25.25% 14.25%

THE EFFECT OF CORRELATION COEFFICIENT ON GM-MSFT PORTFOLIOS in this

example, the correlation = 0.50

The Effect of Correlation on the GM-MSFT Efficient

Frontier

In this example, correlation = 0.50

10%

20%

30%

40%

50%

60%

70%

0% 10% 20% 30% 40%

Standard deviation of portfolio return,

Expected portfolio

return, E(r

)

=SQRT(G12^2*$B$4

+(1-G12)^2*$C$4+2*G12*(1-G12)*$B$7)

=G12*$B$3+(1-G12)*$C$3

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

Correlation coefficient = -1—perfect negative correlation

When the correlation coefficient

GM,MSFT

= -1, we can use our portfolio to create a

riskless asset. This was the message in the simple “coin toss” example with which we started

this chapter (Section 12.1), and it is still true here:

Perfect negative correlation between two risky assets allows the creation of a portfolio

that is risk-free.

Here’s our example in Excel:

ABCDEFGHIJ

GM MSFT

Average 14.25% 62.72%

Variance 6.38% 14.43%

Sigma 25.25% 37.99%

Correlation coefficient,

GM,MSFT

-1.00

Covariance -0.10 <-- =B6*B5*C5

Percentage

in GM Sigma

Expected

return

0% 37.99% 62.72%

10% 31.66% 57.87%

20% 25.34% 53.03%

30% 19.01% 48.18%

40% 12.69% 43.33%

50% 6.37% 38.49%

60.066% 0.00% 33.61%

70% 6.28% 28.79%

80% 12.61% 23.95%

90% 18.93% 19.10%

100% 25.25% 14.25%

THE EFFECT OF CORRELATION COEFFICIENT ON GM-MSFT PORTFOLIOS in this

example, the correlation = -1.00

The Effect of Correlation on the GM-MSFT Efficient

Frontier

In this example, correlation = -1.00

10%

20%

30%

40%

50%

60%

70%

-10% 0% 10% 20% 30% 40%

Standard deviation of portfolio return,

Expected portfolio

return, E(r

)

=SQRT(G12^2*$B$4

+(1-G12)^2*$C$4+2*G12*(1-G12)*$B$7)

=G12*$B$3+(1-G12)*$C$3

A little mathematics explains this result. The portfolio variance for this case can be

written:

()

() ( )

() ()

()

22 2 2

22 2

121

p GM GM MSFT MSFT GM MSFT GM MSFT GM MSFT

GM GM MSFT MSFT GM MSFT GM MSFT

GM GM GM MSFT GM GM GM MSFT

GM GM GM MSFT

Var r w Var r w Var r w w

ww ww

ρσσ

σσ σσ

σσ

=+ +

=+ −

=+− −−

=−−

This means that we can—by choosing the appropriate weights w

and w

MSFT

—set the portfolio

variance equal to zero:

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

()

pGMGM GMMSFT

MSFT

MSFT GM

Var r w w

when w

σσ

=−− =

In our case, this means that

62.72%

0.60066

62.72% 14.25%

MSFT

MSFT GM

σσ

== =

This value is given in cell G18.

Correlation coefficient = +1. The case of perfect positive correlation

When the correlation coefficient

GM,MSFT

= +1, diversification does not reduce risk.

Perfect positive correlation between two risky assets means that risk is not reduced in a

portfolio context.

Here’s our example in Excel:

ABCDEFGHIJ

GM MSFT

Average 14.25% 62.72%

Variance 6.38% 14.43%

Sigma 25.25% 37.99%

Correlation coefficient, ρ

GM,MSFT

1.00

Covariance 0.10 <-- =B6*B5*C5

Percentage

in GM Sigma

Expected

return

0% 37.99% 62.72%

10% 36.71% 57.87%

20% 35.44% 53.03%

30% 34.17% 48.18%

40% 32.89% 43.33%

50% 31.62% 38.49%

60% 30.35% 33.64%

70% 29.07% 28.79%

80% 27.80% 23.95%

90% 26.53% 19.10%

100% 25.25% 14.25%

THE EFFECT OF CORRELATION COEFFICIENT ON GM-MSFT PORTFOLIOS in this

example, the correlation = 1.00

The Effect of Correlation on the GM-MSFT Efficient

Frontier

In this example, correlation = 1.00

10%

20%

30%

40%

50%

60%

70%

0% 10% 20% 30% 40%

Standard deviation of portfolio return,

Expected portfolio

return, E(r

)

=SQRT(G12^2*$B$4

+(1-G12)^2*$C$4+2*G12*(1-G12)*$B$7)

=G12*$B$3+(1-G12)*$C$3

Notice what we mean by “portfolios do not reduce risk”: When the correlation between

the two assets’ returns is +1, the standard deviation of the portfolio return for this case is the

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

weighted average of the asset standard deviations. A little mathematics explains this result. The

portfolio variance for this case can be written:

()

() ( )

()

22 2 2

The correlation coefficient

GM MSFT

p GM GM MSFT MSFT GM MSFT GM MSFT GM MSFT

GM GM MSFT MSFT GM MSFT GM MSFT

GM GM GM MSFT

Var r w Var r w Var r w w

ww ww

ρσσ

σσ σσ

σσ

↑

=+ +

=+−



This means that the standard deviation of the portfolio is the weighted average of the asset

standard deviations:

()

pGMGM GMMSFT

rw w

σσ σ

=+−

Thus there is no real gain from diversification.

Summary

In this chapter we have discussed the importance of diversification for portfolio returns

and risks. We showed how to calculate the mean and variance and standard deviation of a

portfolio’s return. The efficient frontier is the set of those portfolios which offer the highest

expected return for a given standard deviation. We discussed this frontier and how it is affected

by the correlation between the asset returns.

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

Exercises

Note: Data for the problems is on the CD-ROM which accompanies the book.

1. The table below presents the year-end prices for the shares of Ford and PPG from 1989 to

2001:

ABC

Date

Ford

stock price

PPG

stock price

31-Dec-89 11.813 14.024

31-Dec-90 7.210 17.229

31-Dec-91 7.617 19.138

31-Dec-92 11.612 25.721

31-Dec-93 17.469 30.518

31-Dec-94 15.100 30.736

31-Dec-95 15.642 38.980

31-Dec-96 17.472 49.007

31-Dec-97 26.310 51.040

31-Dec-98 31.807 53.172

31-Dec-99 28.895 58.626

31-Dec-00 22.470 44.867

31-Dec-01 15.720 51.720

PRICES FOR FORD

AND PPG STOCK

1.a. Calculate the following statistics for these two shares: average return, variance of

returns, standard deviation of returns, covariance of returns and correlation coefficient.

1.b. If you invested in a portfolio composed of 50% Ford and 50% PPG, what would be

the portfolio expected return? the standard deviation?

1.c. Comment on the following statement: “Ford has lower returns and higher standard

deviation of returns than PPG. Therefore any rational investor would invest in PPG only

and would leave Ford out of her portfolio.”

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

2. You invest $500 in a stock for which the return is determined by a coin flip. If the coin comes

up head the stock returns 10%, and if it comes up tails the investment returns -10%. What is the

average return, the return variance, and the return standard deviation of this investment, if you

flip the coin one time?

3. You have $500 to invest. You decide to split it into two parts. The return on each $250 will

be determined by a coin toss, and the results of the two tosses are not correlated. If the coin

comes up heads, the investment will return 10% and if it comes up tails it will return -10%.

What is the average return, the return variance, and the return standard deviation of this

investment?

4. The previous question assumes that the correlation between the coin flips in 0. Repeat this

question with the following correlations:

4.a. If the first coin flip is heads, then the second coin flip will be heads as well, and vice

versa (correlation of 1).

4.b. If the first coin flip is heads, then second coin flip will be tails, and vice versa

(correlation of -1).

4.c. If the first coin flip is heads, then the second coin flip will be heads with a

probability of 0.8. If the first coin flip is tails, then the second coin flip will be tails with

a probability of 0.6.

4.d. What can you conclude about the connection between the variance of the return

from the coin flips and the correlation between the flips?

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

5. Calculate the average return and the variance of a portfolio composed of 30% of GM and

70% of MSFT stocks, using the data described from page000.

6. Consider the following statistics for a portfolio composed of shares of Companies A and B:

ABCDEF

Company A

stock

Company B

stock

Average Return 25% 48%

Variance 0.0800 0.1600

Sigma 28.28% 40.00%

Covariance of Returns 0.00350

Correlation of Returns 0.03094

<-- =B6/(B4*C4)

Portfolio

Proportion of A

0.9

Proportion of B

0.1

Portfolio average return

27.30% <-- =B10*B2+C2*B11

Portfolio standard deviation

25.89% <-- =SQRT(B10^2*B3+B11^2*C3+2*B10*B11*B6)

6.a. Suggest a portfolio combination that improves return while maintaining the same

level of risk.

6.b. Calculate the minimum variance portfolio for the portfolio composed of the two

assets described above.

7. Consider the monthly returns for Ford and General Motors stock given below. Were there

advantages to diversifying between these two stocks? Explain.

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

ABC

Date Ford GM

1-Dec-99 5.52% -1.50%

3-Jan-00 -5.70% 10.83%

1-Feb-00 -16.32% -4.99%

1-Mar-00 10.32% 8.89%

3-Apr-00 20.27% 13.05%

1-May-00 -11.30% -24.12%

1-Jun-00 -7.81% -17.79%

3-Jul-00 9.47% -1.94%

1-Aug-00 -9.18% 23.95%

1-Sep-00 5.42% -7.14%

2-Oct-00 3.63% -4.43%

1-Nov-00 -12.89% -19.61%

1-Dec-00 3.02% 2.89%

2-Jan-01 21.56% 5.43%

1-Feb-01 -3.29% 4.56%

Average 0.85% -0.79%

Standard deviation 11.23% 12.58%

Correlation 0.4056

MONTHLY RETURNS FOR

FORD AND GM STOCK

8. The following spreadsheet presents data for stocks A and B.

ABCD

Average return

34.00% 25.00%

ariance

0.12 0.07

Sigma

34.64% 26.46%

covariance of return

0.016

correlation of return

0.175

Return statistics of A and B stock

8.a. What are the return and the standard deviation of a portfolio composed of 30% of

stock A and 70% of stock B?

8.b. What are the return and the standard deviation of an equally weighted portfolio of

stocks A and B?

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

9. Suppose that the return statistics for A and B stock are given below. What is the standard

deviation of the portfolio minimum variance? (The answer requires only one calculation.)

ABC

Average return 25% 15%

Variance 0.1600 0.0484

Standard deviation 40.00% 22.00%

Covariance of return -0.0880

RETURN STATISTICS

OF A AND B STOCK

10. ABC and XYZ are 2 stocks with the following return statistics:

ABC

Expected

return

Standard

deviation of

return

ABC 15% 33%

XYZ 25% 46%

Covariance(ABC,XYZ) 0.0865

Correlation(ABC,XYZ) 0.5698

10.a. Compute the expected return and standard deviation of a portfolio composed of

25% ABC and 75% XYZ.

10.b. Compute the returns of all portfolios that are combinations of ABC and XYZ with

the proportion of ABC being 0%, 10%, ... , 90%, 100%. Graph these returns

10.c. Compute the minimum variance portfolio

11. Melissa Jones wants to invest in a portfolio composed of stocks ABC and XYX (from

question 10), that will yield a return of 19%. What is the weight of each stock in such a

portfolio, and what is the portfolio’s standard deviation? Answer the question both by using

Excel’s

Goal Seek or Solver and by using the mathematical formulas in the chapter (page000).

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

12. Your client asks you to create a two-asset portfolio having an expected return of 15% and

return standard deviation of 12%. The client specifies that the portfolio include 60% of the stock

‘Merlyn’ (named for her beloved mother…) which has expected return is 13% and has a standard

deviation of 10%.

12.a. What should be the return statistics of the second stock you’ll combine in this

portfolio, assuming the stocks have zero correlation?

12.b. What should be the return statistics of the second stock you’ll combine in this

portfolio, assuming the stocks have covariance of 0.01?

13. What will be the weights, the expected return, the variance, and the standard deviation of a

minimum variance portfolio combining the stocks below, using the mathematical way:

ABCD

Avera

e return

21.00% 14.00%

ariance

0.11 0.045

33.17% 21.21%

covariance of return

-0.002

correlation of return

-0.028

Return statistics of X and Y stock

14. This question relates to the data in exercise 13.

14.a. Calculate and graph the efficient frontier of the stock portfolios composed of stocks

X and Y in the exercise 13.

14.b. Calculate and graph the efficient frontier of the stock portfolios composed of stocks

X and Y in the exercise 13, assuming the correlation between the two stocks is -1.