## 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

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

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

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

## Post a comment