Calculation of Expected Return and Variance

Several software packages can be used to generate the efficient frontier. We will demonstrate the method using Microsoft Excel. Excel is far from the best program for this purpose and is limited in the number of assets it can handle, but working through a simple portfolio optimizer in Excel can illustrate concretely the nature of the calculations used in more sophisticated "black-box" programs. You will find that even in Excel, the computation of the efficient frontier is fairly easy.

We will apply the Markowitz portfolio optimizer to the problem of international diversification. Table 8.4A is taken from Chapter 25, "International Diversification," and shows average returns, standard deviations, and the correlation matrix for the rates of return on the stock indexes of seven countries over the period 1980-1993. Suppose that toward the end of 1979, the analysts of International Capital Management (ICM) had produced an input list that anticipated these results. As portfolio manager of ICM, what set of efficient portfolios would you have considered as investment candidates?

After we input Table 8.4A into our spreadsheet as shown, we create the covariance matrix in Table 8.4B using the relationship Cov(r;, rj) = pijOioj. The table shows both cell formulas (upper panel) and numerical results (lower panel).

Next we prepare the data for the computation of the efficient frontier. To establish a benchmark against which to evaluate our efficient portfolios, we use an equally weighted portfolio, that is, the weights for each of the seven countries is equal to 1/7 = .1429. To compute the equally weighted portfolio's mean and variance, these weights are entered in the border column A53-A59 and border row B52-H52.10 We calculate the variance of this portfolio in cell B77 in Table 8.4C. The entry in this cell equals the sum of all elements in the border-multiplied covariance matrix where each element is first multiplied by the portfolio weights given in both the row and column borders.11 We also include two cells to

10 You should not enter the portfolio weights in these rows and columns independently, since if a weight in the row changes, the weight in the corresponding column must change to the same value for consistency. Thus you should copy each entry from column A to the corresponding element of row 52.

11 We need the sum of each element of the covariance matrix, where each term has first been multiplied by the product of the portfolio weights from its row and column. These values appear in Panel C of Table 8.4. We will first sum these elements for each column and then add up the column sums. Row 60 contains the appropriate column sums. Therefore, the sum of cells B60-H60, which appears in cell B61, is the variance of the portfolio formed using the weights appearing in the borders of the covariance matrix.

230 PART II Portfolio Theory

Table 8.4 Performance of Stock Indexes of Seven Countries

A

B

C

D

E

F

G

H

1

2

A. Annualized Standard Deviation, Average Return,

3

and Correlation Coefficients of International Stocks, 1980-1993

4

5

Std. Dev. (%)

Average Ret. (%)

6

US

21.1

15.7

7

Germany

25.0

21.7

8

UK

23.5

18.3

9

Japan

26.6

17.3

10

Australia

27.6

14.8

11

Canada

23.4

10.5

12

France

26.6

17.2

13

14

Correlation Matrix

15

US

Germany

UK

Japan

Australia

Canada

France

16

US

1.00

0.37

0.53

0.26

0.43

0.73

0.44

17

Germany

0.37

1.00

0.47

0.36

0.29

0.36

0.63

18

UK

0.53

0.47

1.00

0.43

0.50

0.54

0.51

19

Japan

0.26

0.36

0.43

1.00

0.26

0.29

0.42

20

Australia

0.43

0.29

0.50

0.26

1.00

0.56

0.34

21

Canada

0.73

0.36

0.54

0.29

0.56

1.00

0.39

22

France

0.44

0.63

0.51

0.42

0.34

0.39

1.00

A

B

C

D

E

F

G

H

27

B. Covariance Matrix: Cell Formulas

28

29

US

Germany

UK

Japan

Australia

Canada

France

30

US

b6*b6*b16

b7*b6*c16

b8*b6*d16

b9*b6*e16

b10*b6*f16

b11*b6*g16

b12*b6*h16

31

Germany

b6*b7*b17

b7*b7*c17

b8*b7*d17

b9*b7*e17

b10*b7*f17

b11*b7*g17

b12*b7*h17

32

UK

b6*b8*b18

b7*b8*c18

b8*b8*d18

b9*b8*e18

b10*b8*f18

b11*b8*g18

b12*b8*h18

33

Japan

b6*b9*b19

b7*b9*c19

b8*b9*d19

b9*b9*e19

b10*b9*f19

b11*b9*g19

b12*b9*h19

34

Australia

b6*b10*b20

b7*b10*c20

b8*b10*d20

b9*b10*e20

b10*b10*f20

b11*b10*g20

b12*b10*h20

35

Canada

b6*b11*b21

b7*b11*c21

b8*b11*d21

b9*b11*e21

b10*b11*f21

b11*b11*g21

b12*b11*h21

36

France

b6*b12*b22

b7*b12*c22

b8*b12*d22

b9*b12*e22

b10*b12*f22

b11*b12*g22

b12*b12*h22

37

38

Covariance Matrix: Results

39

40

US

Germany

UK

Japan

Australia

Canada

France

41

US

445.21

195.18

262.80

145.93

250.41

360.43

246.95

42

Germany

195.18

625.00

276.13

239.40

200.10

210.60

418.95

43

UK

262.80

276.13

552.25

268.79

324.30

296.95

318.80

44

Japan

145.93

239.40

268.79

707.56

190.88

180.51

297.18

45

Australia

250.41

200.10

324.30

190.88

761.76

361.67

249.61

46

Canada

360.43

210.60

296.95

180.51

361.67

547.56

242.75

47

France

246.95

418.95

318.80

297.18

249.61

242.75

707.56

A

B

C

D

E

F

G

H

49

C. Border-Multiplied Covariance Matrix for the Equally Weighted Portfolio and Portfolio Variance:

50

Cell Formulas

51

US

Germany

UK

Japan

Australia

Canada

France

52

Weights

a53

a54

a55

a56

a57

a58

a59

53

0.1429

a53*b52*b41

a53*c52*c41

a53*d52*d41

a53*e52*e41

a53*f52*f41

a53*g52*g41

a53*h52*h41

54

0.1429

a54*b52*b42

a54*c52*c42

a54*d52*d42

a54*e52*e42

a54*f52*f42

a54*g52*g42

a54*h52*h42

55

0.1429

a55*b52*b43

a55*c52*c43

a55*d52*d43

a55*e52*e43

a55*f52*f43

a55*g52*g43

a55*h52*h43

56

0.1429

a56*b52*b44

a56*c52*c44

a56*d52*d44

a56*e52*e44

a56*f52*f44

a56*g52*g44

a56*h52*h44

57

0.1429

a57*b52*b45

a57*c52*c45

a57*d52*d45

a57*e52*e45

a57*f52*f45

a57*g52*g45

a57*h52*h45

58

0.1429

a58*b52*b46

a58*c52*c46

a58*d52*d46

a58*e52*e46

a58*f52*f46

a58*g52*g46

a58*h52*h46

59

0.1429

a59*b52*b47

a59*c52*c47

a59*d52*d47

a59*e52*e47

a59*f52*f47

a59*g52*g47

a59*h52*h47

60

Sum(a53:a59)

sum(b53:b59)

sum(c53:c59)

sum(d53:d59)

sum(e53:e59)

sum(f53:f59)

sum(g53:g59)

sum(h53:h59)

61

Portfolio variance sum(b60:h60)

62

Portfolio SD

b61A.5

63

Portfolio mean

a53*c6+a54*c7+a55*c8 + a56*c9 4

a57*c10 + a58*c11

+ a59*c12

CHAPTER 8 Optimal Risky Portfolios 231

Table 8.4 (Continued)

A

C

E

H

64

65

C. Border-Multiplied Covariance Matrix for the Equally Weighted Portfolio and Portfolio Variance:

66

Results

67

Portfolio

US

Germany

UK

Japan

Australia

Canada

France

68

weights

0.1429

0.1429

0.1429

0.1429

0.1429

0.1429

0.1429

69

0.1429

9.09

3.98

5.36

2.98

5.11

7.36

5.04

70

0.1429

3.98

12.76

5.64

4.89

4.08

4.30

8.55

71

0.1429

5.36

5.64

11.27

5.49

6.62

6.06

6.51

72

0.1429

2.98

4.89

5.49

14.44

3.90

3.68

6.06

73

0.1429

5.11

4.08

6.62

3.90

15.55

7.38

5.09

74

0.1429

7.36

4.30

6.06

3.68

7.38

11.17

4.95

75

0.1429

5.04

8.55

6.51

6.06

5.09

4.95

14.44

76

1.0000

38.92

44.19

46.94

41.43

47.73

44.91

50.65

77

Portfolio variance 314.77

78

Portfolio SD

17.7

79

Portfolio mean 16.5

A

B

C

D

E

F

G

H

80

D. Border-Multiplied Covariance Matrix for the Efficient Frontier Portfolio with Mean of 16.5%

81

(after change of weights by solver)

82

83

Portfolio

US

Germany

UK

Japan

Australia

Canada

France

84

weights

0.3467

0.1606

0.0520

0.2083

0.1105

0.1068

0.0150

85

0.3467

53.53

10.87

4.74

10.54

9.59

13.35

1.29

86

0.1606

10.87

16.12

2.31

8.01

3.55

3.61

1.01

87

0.0520

4.74

2.31

1.49

2.91

1.86

1.65

0.25

88

0.2083

10.54

8.01

2.91

30.71

4.39

4.02

0.93

89

0.1105

9.59

3.55

1.86

4.39

9.30

4.27

0.41

90

0.1068

13.35

3.61

1.65

4.02

4.27

6.25

0.39

91

0.0150

1.29

1.01

0.25

0.93

0.41

0.39

0.16

92

1.0000

103.91

45.49

15.21

61.51

33.38

33.53

4.44

93

Portfolio variance 297.46

94

Portfolio SD

17.2

95

Portfolio mean 16.5

A

B

C

D

E

F

G

H

I

J

96

E. The Unrestricted Efficient Frontier and the Restricted Frontier (with no short sales)

97

98

Standard Deviation

Country Weights In Efficient Portfolios

99

Mean

Unrestricted

Restricted

US

Germany

UK

Japan

Australia

Canada

France

100

9.0

24.239

not feasible

-0.0057

-0.2859

- 0.1963

0.2205

0.0645

0.9811

0.2216

101

10.5

22.129

0.0648

-0.1966

- 0.1466

0.2181

0.0737

0.8063

0.1803

102

10.5

23.388

0.0000

0.0000

0.0000

0.0007

0.0000

0.9993

0.0000

103

11.0

21.483

0.0883

-0.1668

- 0.1301

0.2173

0.0768

0.7480

0.1665

104

11.0

22.325

0.0000

0.0000

0.0000

0.0735

0.0000

0.9265

0.0000

105

12.0

20.292

0.1353

-0.1073

- 0.0970

0.2157

0.0829

0.6314

0.1390

106

12.0

20.641

0.0000

0.0000

0.0000

0.1572

0.0325

0.7668

0.0435

107

14.0

18.408

0.2293

0.0118

- 0.0308

0.2124

0.0952

0.3982

0.0839

108

14.0

18.416

0.2183

0.0028

0.0000

0.2068

0.0884

0.4020

0.0816

109

15.0

17.767

17.767

0.2763

0.0713

0.0023

0.2108

0.1013

0.2817

0.0563

110

16.0

17.358

17.358

0.3233

0.1309

0.0355

0.2091

0.1074

0.1651

0.0288

111

17.0

17.200

17.200

0.3702

0.1904

0.0686

0.2075

0.1135

0.0485

0.0012

112

17.5

17.216

0.3937

0.2202

0.0851

0.2067

0.1166

-0.0098

- 0.0125

113

17.5

17.221

0.3777

0.2248

0.0867

0.2021

0.1086

0.0000

0.0000

114

18.0

17.297

0.4172

0.2499

0.1017

0.2059

0.1197

-0.0681

- 0.0263

115

18.0

17.405

0.3285

0.2945

0.1157

0.1869

0.0744

0.0000

0.0000

116

18.5

17.441

0.4407

0.2797

0.1182

0.2051

0.1227

-0.1263

- 0.0401

117

18.5

17.790

0.2792

0.3642

0.1447

0.1716

0.0402

0.0000

0.0000

118

21.0

19.036

0.5582

0.4285

0.2010

0.2010

0.1380

-0.4178

- 0.1090

119

21.0

22.523

0.0000

0.8014

0.1739

0.0247

0.0000

0.0000

0.0000

120

22.0

20.028

not feasible

0.6052

0.4880

0.2341

0.1994

0.1442

-0.5343

- 0.1365

121

26.0

25.390

not feasible

0.7931

0.7262

0.3665

0.1929

0.1687

-1.0006

- 0.2467

232 PART II Portfolio Theory compute the standard deviation and expected return of the equally weighted portfolio (formulas in cells B62, B63) and find that they yield an expected return of 16.5% with a standard deviation of 17.7% (results in cells B78 and B79).

To compute points along the efficient frontier we use the Excel Solver in Table 8.4D (which you can find in the Tools menu).12 Once you bring up Solver, you are asked to enter the cell of the target (objective) function. In our application, the target is the variance of the portfolio, given in cell B93. Solver will minimize this target. You next must input the cell range of the decision variables (in this case, the portfolio weights, contained in cells A85-A91). Finally, you enter all necessary constraints into the Solver. For an unrestricted efficient frontier that allows short sales, there are two constraints: first, that the sum of the weights equals 1.0 (cell A92 = 1), and second, that the portfolio expected return equals a target mean return. We will choose a target return equal to that of the equally weighted portfolio, 16.5%, so our second constraint is that cell B95 = 16.5. Once you have entered the two constraints you ask the Solver to find the optimal portfolio weights.

The Solver beeps when it has found a solution and automatically alters the portfolio weight cells in row 84 and column A to show the makeup of the efficient portfolio. It adjusts the entries in the border-multiplied covariance matrix to reflect the multiplication by these new weights, and it shows the mean and variance of this optimal portfolio—the minimum variance portfolio with mean return of 16.5%. These results are shown in Table 8.4D, cells B93-B95. The table shows that the standard deviation of the efficient portfolio with same mean as the equally weighted portfolio is 17.2%, a reduction of risk of about one-half percentage point. Observe that the weights of the efficient portfolio differ radically from equal weights.

To generate the entire efficient frontier, keep changing the required mean in the constraint (cell B95),13 letting the Solver work for you. If you record a sufficient number of points, you will be able to generate a graph of the quality of Figure 8.13.

The outer frontier in Figure 8.13 is drawn assuming that the investor may maintain negative portfolio weights. If shortselling is not allowed, we may impose the additional constraints that each weight (the elements in column A and row 84) must be nonnegative; we would then obtain the restricted efficient frontier curve in Figure 8.13, which lies inside the frontier obtained allowing short sales. The superiority of the unrestricted efficient frontier reminds us that restrictions imposed on portfolio choice may be costly.

The Solver allows you to add "no short sales" and other constraints easily. Once they are entered, you repeat the variance-minimization exercise until you generate the entire restricted frontier. By using macros in Excel or—even better—with specialized software, the entire routine can be accomplished with one push of a button.

Table 8.4E presents a number of points on the two frontiers. The first column gives the required mean and the next two columns show the resultant variance of efficient portfolios with and without short sales. Note that the restricted frontier cannot obtain a mean return less than 10.5% (which is the mean in Canada, the country index with the lowest mean return) or more than 21.7% (corresponding to Germany, the country with the highest mean return). The last seven columns show the portfolio weights of the seven country stock indexes in the optimal portfolios. You can see that the weights in restricted portfolios are never negative. For mean returns in the range from about 15% —17%, the two frontiers overlap since the optimal weights in the unrestricted frontier turn out to be positive (see also Figure 8.13).

12 If Solver does not show up under the Tools menu, you should select Add-Ins and then select Analysis. This should add Solver to the list of options in the Tools menu.

13 Inside Solver, highlight the constraint, click on Change, and enter the new value for the portfolio's mean return.

CHAPTER 8 Optimal Risky Portfolios 233

Figure 8.13 Efficient frontier with seven countries.

Figure 8.13 Efficient frontier with seven countries.

Notice that despite the fact that German stocks offer the highest mean return and even the highest reward-to-variability ratio, the weight of U.S. stocks is generally higher in both restricted and unrestricted portfolios. This is due to the lower correlation of U.S. stocks with stocks of other countries, and illustrates the importance of diversification attributes when forming efficient portfolios. Figure 8.13 presents points corresponding to means and standard deviations of individual country indexes, as well as the equally weighted portfolio. The figure clearly shows the benefits from diversification.

A spreadsheet model featuring Optimal Portfolios is available on the Online Learning Center at www.mhhe.com/bkm. It contains a template that is similar to the template developed in this section. The model can be used to find optimal mixes of securities for targeted levels of returns for both restricted and unrestricted portfolios. Graphs of the efficient frontier are generated for each set of inputs. Additional practice problems using this spreadsheet are also available.

Stocks and Shares Retirement Rescue

Stocks and Shares Retirement Rescue

Get All The Support And Guidance You Need To Be A Success At Investing In Stocks And Shares. This Book Is One Of The Most Valuable Resources In The World When It Comes To

Get My Free Ebook


Post a comment