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

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

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

15. Let’s go again to the data of GM and Microsoft stocks on page000. A portfolio composed of

90% of GM and 10% Microsoft stock has expected return of 19.1% and standard deviation of

20.78%. Find another portfolio with the same standard deviation and a higher return. (You can

do this by trial and error, but you can also use

Solver.)

16. John and Mary are considering investing in a combination of ABC stock and XYZ stock.

The return on ABC is determined by a coin flip: If the coin is heads, the return is 35% and if the

coin is tails, the return on ABC is 10%. The return on XYZ stock is similarly determined, but by

a separate coin flip.

16.a. Compute the mean, variance and standard deviation of the returns on ABC and

XYZ.

16.b. What is the correlation of the returns? (Nothing to compute here, just think!)

16.c. John has decided to invest in a portfolio composed of 100% XYZ stock. Mary, on

the other hand, is investing in a portfolio composed of 50% ABC and 50% XYZ. Whose

portfolio is better? Why?

17. Elizabeth and Sandra are considering investing in a combination of ABC stock and XYZ

stock. The return on both stocks is determined by a single coin flip: If the coin is heads, the

return on both stocks is 35% and if the coin is tails, the return is 10%.

17.a. Compute the mean, variance and standard deviation of the returns on ABC and

XYZ.

17.b. What is the correlation of the returns? (Nothing to compute here, just think!)

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

17.c. Elizabeth has decided to invest in a portfolio composed of 100% XYZ stock.

Sandra, on the other hand, is investing in a portfolio composed of 50% ABC and 50%

XYZ. Whose portfolio is better?

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

Appendix 1: Deriving the formula for the minimum variance portfolio

Recall the formula for variance of the portfolio:

()

() ( )

(

)

p GM GM MSFT MSFT GM MSFT GM MSFT

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

Substituting in

SFT GM

ww=− , this equation becomes:

()

()( )

(

)

(

)

(

)

121,

p GM GM GM MSFT GM GM GM MSFT

Var r w Var r w Var r w w Cov r r=+− +−

Setting the derivative of this equation equal to zero will give the formula for the minimum

variance portfolio:

()

()( ) ( ) ( )( )

() ( )

() ( ) ( )

221 ,220

GM GM GM MSFT GM MSFT GM

MSFT GM MSFT

GM MSFT GM MSFT

dVar r

wVarr w Varr Covr r w

Var r Cov r r

Varr Varr Covr r

=−− + −=

−

⇒=

+−

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

Appendix 2: Portfolios with three and more assets

In this appendix we look at portfolios and their efficient frontiers when there are more

than 2 assets. The main points we make:

•

In the multi-asset context we can still calculate the efficient frontier, and it still has its

characteristic shape.

•

The more risky assets there are, the more the portfolio variance is influenced by the

covariances between the assets.

We start by considering a 3-asset problem. To describe 3 assets, we need to know the

expected return, the variance, and all the pairs of covariances. This data is described below.

ABCDE

Stock A Stock B Stock C

Mean 10% 12% 15%

Variance 15% 22% 30%

Cov(r

)

0.03

Cov(r

)

-0.01

Cov(r

)

0.02

A 3-ASSET PORTFOLIO PROBLEM

Suppose we form a portfolio of risky assets composed of proportion x

in asset A, x

in asset B,

and x

in asset C. Since the portfolio is fully invested in risky assets, it follows that x

= 1-x

-x

Portfolio return statistics: The expected return of the portfolio is given by:

()

(

)()

pAABBCC

rxErxErxEr=++

The calculation of the portfolio’s variance of return requires both the variances and the

covariances:

()

() ()

(

)

() () ()

222

2,2,2,

pA AB BC C

BABACACBCBC

Var r x Var r x Var r x Var r

x Cov r r x x Cov r r x x Cov r r

=+++

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

Notice that there are 3 variances and 3 covariances. When—at the end of this section—we show

you the formula for a 4-asset problem, there will be 4 variances and 6 covariances. As the

number of assets grows, so does the number of covariances (in fact their number grows much

faster than the number of variances). This is the meaning of the second bullet at the beginning of

this section—for multi-asset portfolio problems, the portfolio variance is increasingly influenced

by the covariances.

Here’s an example of the mean return and variance calculation for our 3-asset portfolio:

The portfolio statistics are calculated in cells B16:B18:

ABCDEFGHIJ

Stock

Stock B Stock C

Mean 10% 12% 15%

Variance 15% 22% 30%

Cov(r

)

0.03

Cov(r

)

-0.01

Cov(r

)

0.02

Portfolio proportions

0.3000

0.5000

0.2000 <-- =1-B12-B11

Market portfolio statistics

Mean 0.1200 <-- =B11*B3+B12*C3+B13*D3

Variance 0.0899 <-- =B11^2*B4+B12^2*C4+B13^2*D4+2*B11*B12*B6+2*B11*B13*B8+2*B12*B13*B7

Sigma 0.2998 <-- =SQRT(B17)

A 3-ASSET PORTFOLIO PROBLEM

Calculating the efficient frontier with 3 assets

We can use Excel to calculate and graph the efficient frontier for this case.

We’ll make

use of Excel’s

Solver.

Step 1: We use Solver to find the minimum sigma portfolio:

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

Here’s the result:

ABCDEFGHIJ

Stock

Stock B Stock C

Mean 10% 12% 15%

Variance 15% 22% 30%

Cov(r

)

0.03

Cov(r

)

-0.01

Cov(r

)

0.02

Portfolio proportions

0.4370

0.3151

0.2479 <-- =1-B12-B11

Market portfolio statistics

Mean 0.1187 <-- =B11*B3+B12*C3+B13*D3

Variance 0.0800 <-- =B11^2*B4+B12^2*C4+B13^2*D4+2*B11*B12*B6+2*B11*B13*B8+2*B12*B13*B7

Sigma 0.2828 <-- =SQRT(B17)

A 3-ASSET PORTFOLIO PROBLEM

Step 2: We now specify sigma and use Solver to find a portfolio with the maximum

return. We do this by first adding a cell (“Target sigma,” cell B20) to the spreadsheet:

The procedure we’re about to explain is somewhat long-winded—for a much shorter and efficient procedure, see

my book Financial Modeling.

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

ABCDEFGHIJ

Stock

Stock B Stock C

Mean 10% 12% 15%

Variance 15% 22% 30%

Cov(r

)

0.03

Cov(r

)

-0.01

Cov(r

)

0.02

Portfolio proportions

0.4370

0.3151

0.2479 <-- =1-B12-B11

Market portfolio statistics

Mean 0.1187 <-- =B11*B3+B12*C3+B13*D3

Variance 0.0800 <-- =B11^2*B4+B12^2*C4+B13^2*D4+2*B11*B12*B6+2*B11*B13*B8+2*B12*B13*B7

Sigma 0.2828 <-- =SQRT(B17)

Target sigma 0.3000

TABLE OF SIGMA VERSUS MEAN

Target sigma Mean

0.2828 0.1187 <-- This is the minimum sigma portfolio

A 3-ASSET PORTFOLIO PROBLEM

Notice that—starting from row 24—we’ve begun to build a table of the results. The first

row of this table is the minimum sigma portfolio. Now we’ll use Solver to add another row to

this table.

We do this by adding a constraint to Solver:

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

The constraint was added by pressing

Add in the lower portion of the Solver dialog box:

Here’s the result:

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

ABCDEFG

Stock A Stock B Stock C

Mean 10% 12% 15%

Variance 15% 22% 30%

Cov(r

)

0.03

Cov(r

)

-0.01

Cov(r

)

0.02

Portfolio proportions

0.2533

0.3544

0.3923 <-- =1-B12-B11

Market portfolio statistics

Mean 0.1267 <-- =B11*B3+B12*C3+B13*D3

Variance 0.0900 <-- =B11^2*B4+B12^2*C4+B13^2*D4+2*B11*B12*B6+2*

Sigma 0.3000 <-- =SQRT(B17)

Target sigma 0.3000

A 3-ASSET PORTFOLIO PROBLEM

If we repeat this calculation many times for many Target sigmas, we get the efficient

frontier:

ABCDEFGHIJK

TABLE OF SIGMA VERSUS MEAN

Target sigma Mean

0.2828 0.1187 <-- This is the minimum sigma portfolio

0.2900 0.1238

0.3000 0.1256

0.3100 0.1288

0.3200 0.1307

0.3300 0.1323

0.3400 0.1338

0.3500 0.1352

0.3600 0.1365

0.3700 0.1378

0.3800 0.1390

0.3900 0.1401

0.4000 0.1413

0.4100 0.1420

0.4200 0.1435

0.4300 0.1446

0.4400 0.1456

0.4500 0.1467

0.4600 0.1477

0.4700 0.1487

Efficient Frontier for Three Assets

0.10

0.11

0.12

0.13

0.14

0.15

0.16

0.20 0.25 0.30 0.35 0.40 0.45 0.50

Portfolio sigma

Portfolio mean return

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

Four assets

This section has gone into depth about how to calculate returns and variances for a 3-

asset portfolio. If we have 4 assets, we can do the same kinds of calculations (we leave this as an

exercise). What you have to know for this case is how to calculate the portfolio return and

variance.

Call the assets A, B, C, D, and denote the portfolio weights by

,,,

BCD

xxx

Portfolio return statistics: The expected return of the portfolio is given by:

()

(

)()

(

)

pAABBCCDD

r xEr xEr xEr xEr=+++

The calculation of the portfolio’s variance of return requires both the variances and the

covariances:

()

() ()

(

)

(

)

() () ()

() ()

()

2222

2,2,2,

2,2,

pA AB BC CD D

AB A B AC A C AD A D

BC B C BD B D

CD C D

Var r x Var r x Var r x Var r x Var r

x x Cov r r x x Cov r r x x Cov r r

xxCovr r xxCovr r

xxCovr r

=+++

Notice that there now there are 4 variances and 6 covariances.