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

Get My Free Ebook