Mean Variance Optimization: Multi-Asset Portfolio

In the Third Quarter 2011 issue of Computerized Investing, I began a discussion of how investors can use a spreadsheet program to maximize the risk-return makeup of a two-stock portfolio. This oversimplified example allowed us to walk through several functions of Excel to calculate the correlation and covariance between investments—the degree to which returns move in the same direction (or not)—and the standard deviation, or volatility, of individual assets and the portfolio as a whole. The article culminated in using the Solver module in Excel to arrive at the weights invested in each stock in order to minimize the overall portfolio risk (minimize the standard deviation).

Obviously, few investors hold only two stocks or securities. For this article, I will expand on the previous article’s concepts to show you how you can use Excel to construct a multi-asset investment portfolio that achieves the goal of either minimizing risk or maximizing return.

Diversifying to Control Portfolio Risk

For a rational investor, the goal of creating a portfolio of assets is to generate the maximum return for the level of risk with which the investor is comfortable.

In the last Spreadsheet Corner article, we discussed the Nobel Prize–winning work of Harry Markowitz, who illustrated that owning more than one risky investment can actually be less risky than merely owning a single investment. This is because the returns of certain asset classes, and the returns of individual securities within those asset classes, move differently from each other.

Covariance

Covariance is one measure of the degree to which the returns of two risky assets move in tandem. A positive covariance means that asset returns move together, while a negative covariance means returns move inversely (in an opposite direction).

Figure 1 shows the annual total returns over the last eight years for 10 stocks passing a stock screening strategy. Based on these annual returns, we calculated the (expected) average annual return for each stock. To calculate the average annual return for the first stock, Aeropostale Inc. (ARO), we entered the following function in cell B12: =Average(B4:B11). Repeating this process for each stock’s respective annual returns will give us the eight-year average annual returns. By using these average annual returns as a basis for the expected future returns for these stocks, we are making the rather large assumption that these stocks will exhibit the same behavior going forward.

Based on this return data, we can use Excel’s covariance tool to set up a covariance matrix for multiple securities to see how the annual returns of these stocks have moved in relation to each other over the last eight years.

To create the covariance matrix for our 10 stocks, we click the Data Analysis button on the Data tab (in Excel 2007) and choose Covariance from the list. This launches the Covariance dialog box shown in Figure 2. We use the input range B3:K11 from Figure 1, grouped by columns, with the labels in the first row.

When you create a covariance matrix in this manner, half of the resulting table is empty. This is because covariance matrices are symmetric, meaning that the cells in the upper diagonal would be a mirror image of those in the lower diagonal. To fill in the entire covariance matrix, we use the matrix multiplication (MMULT) and transpose (TRANSPOSE) functions in conjunction with one another in Excel. We first select the data range for the covariance matrix shown in Figure 3 (B33:K42), this time not including the row with the ticker symbols. After selecting this range, we enter the following formula: =MMULT(TRANSPOSE(B4:K11–B12:K12),(B4:K11–B12:K12))/8. Where:

• B4:K11 is the data range (excluding the data label row) containing the annual returns for the 10 stocks in our portfolio from Figure 1;
• B12:K12 is the data range of the average annual returns for the 10 stocks in our portfolio from Figure 1; and
• Eight (8) is the number of data points being used (eight years of annual return data).

Once we have typed in this formula, we enter it using Shift+Ctrl+Enter; otherwise, we will get a #VALUE error. When entered correctly, you get a completed covariance matrix such as the one shown in Figure 3.

Due to the covariances between these 10 stocks—specifically, the low or negative values—the standard deviation for the portfolio consisting of equal investments in all 10 stocks (cell L13) is lower than the simple average standard deviation of the 10 stocks (cell L14) by almost 19%, down from 54.8% to 44.5% [we entered the formula =STDEVP(L4:L11) in cell L13 to arrive at 44.5%]. The lower risk of the portfolio is the reduction due to diversification.

Calculating the Portfolio Standard Deviation

Using our covariance matrix, we can employ an alternative method of calculating the portfolio standard deviation (Figure 4). We begin by assuming a portfolio consisting of equal weightings in each of the 10 stocks, or 10% invested in each (column B). In cell B59 we enter the formula =SUM(B49:B58) as a check that the weights of the individual stocks add up to 100%. The expected return of the portfolio is calculated with this formula in cell B60: =SUM(TRANSPOSE(B49:B58)*$B$12:$K$12). This, in effect, multiplies the average annual return for each of the 10 stocks (the range $B$12:$K$12 from Figure 1) by the stock’s respective weighting in the portfolio (B49:B58 in Figure 4). However, since the orientation of these ranges differs, we must transpose one of them. Encouragingly, the expected return of 27.75% in cell B60 matches the result in cell L12 from Figure 1, which is a simple average of the average annual return for the 10-stock portfolio.

Using the weightings of the individual stocks in the portfolio and the covariance matrix, we can calculate the portfolio standard deviation with this formula in cell B61: =SQRT(MMULT(MMULT(TRANSPOSE(B49:B58),($B$33:$K$42),B49:B58)), remembering to enter it using Shift+Ctrl+Enter. In effect, this formula multiplies the portfolio weights of one pair of stocks and their respective covariance and adds these products together for all the stock pairs and then takes the square root of the total to arrive at the portfolio standard deviation. Once again, we can confirm the formula’s validity since the portfolio standard deviation of 44.51% matches the result we arrived at in cell L13 from Figure 1.

Scenario Testing

We can now start using the Excel Solver module to test different portfolio scenarios. Figure 4 also shows the tests and their varying results.

Risk-Minimized Portfolios

First, as we did in the previous Spreadsheet Corner article, we attempt to find the stock weightings that will give us the lowest level of portfolio risk (the minimum variance portfolio). Launch the Solver by clicking on the Data tab in Excel 2007 and choosing the Solver button. To create the minimum variance portfolio using our 10 stocks, we need to minimize the standard deviation in cell D61, which is the Target Cell to use in the Solver. We must, again, also enter the standard deviation formula in cell D61 just as we did for our equal-weighted portfolio: =SQRT(MMULT(MMULT(TRANSPOSE(D49:D58),($B$33:$K$42),D49:D58)).

Next, we select the Min radio button to the right of Equal To: in the Solver Parameters box so that the Solver will minimize the function in the Target Cell (in this case, the standard deviation of our minimum variance portfolio). The Solver derives the minimum variance portfolio in our Target Cell $D$61 by adjusting the weightings of the 10 stocks (D49:D58). Therefore, the By Changing Cells in the Solver Parameters box will be the range $D$49:$D$58, which are the weightings for our 10 stocks. Lastly, we need to add a constraint to our function: Specifically, that the sum of the weightings invested in the 10 stocks will equal 1.0 (100%). Therefore, we click the Add button in the Solver Parameters box and add the constraint $D$59=1 and then click OK. Our Solver parameters should look like they do in Figure 5, at which point we can click on the Solve button.

Looking at columns D49:D58, we see the weightings of our 10 stocks that will give us the lowest possible standard deviation, which in this case is 0% (cell D61). This means that we are able to eliminate the risk, as measured by standard deviation, of this 10-stock portfolio. This is not surprising, given that several stocks have negative correlations and covariances with each other. The portfolio also has an expected annual return of 7.5% (cell D60), which is lower than any of the average annual returns among our 10 stocks. This scenario assumes that we are fully invested, since the weightings must equal 100% (cell D59).

Note that some of the weightings for our risk-minimized portfolio are negative, which means those stocks are being shorted. However, some investors are not comfortable shorting securities. Therefore, in the next column to the right (column E), we use the Solver to arrive at a risk-minimized portfolio that is long only (does not use short selling). We once again open the Solver, this time setting the Target Cell to $E$61; selecting the Equal To Min button; and specifying By Changing Cells $E$49:$E$58. Beyond the constraint that $E$59 equal 1 (100%), we have to add 10 additional constraints requiring that each weighting in the range $E49:$E58 is greater than or equal to zero, to prohibit short selling. The solution Excel provides is to invest in only three of the 10 available stocks, giving us an expected annual return of 15.9% (cell E60) and a portfolio standard deviation of 14.1% (cell E61). It is worth noting that nearly 60% of the portfolio is invested in Gentiva Health Services (GTIV). Looking at Figure 3, GTIV has relatively low negative covariance values, meaning it tends to move in the opposite direction of the other stocks. By overweighting the portfolio in a stock with low negative correlations, we are maximizing its diversification benefits. The other two stocks this portfolio is invested in—Arrow Electronics (ARW) and Protective Life Corp. (PL)—also have negative covariances with GTIV.

Some investors may be reluctant to invest only in three stocks, which requires large bets on individual companies. Therefore, our next scenario in column F caps the maximum weighting for an individual stock at 25%, while again prohibiting short sales. This requires two constraints for each weighting in the range $F$49:$F$58: That each cell be greater than or equal to zero (to avoid shorting), and that each cell be less than or equal to 0.25 (25%). This results in a portfolio with one additional stock—MKS Instruments (MKSI) is added—and 25% of the portfolio is invested in each of the four companies. However, this actually lowers the expected return of the portfolio while increasing the overall risk, or standard deviation, which is not what you are looking for when trying to maximize the risk-return trade-off.

In order to invest in more companies, thereby lowering the exposure to an individual company even further, our final risk-minimized scenario in column G requires at least 5% of the portfolio to be invested in each of the 10 stocks, while capping the weight of any one stock at 15%. The constraints for this scenario would be set up in a similar fashion as in the previous example. Not surprisingly, all four companies that made up the portfolio in the previous scenario received 15% weightings, along with Arrow Electronics (ARW). This scenario gives us the highest expected annual return—21.6% (cell G60)—as well as the highest portfolio risk (standard deviation of 33.3% in cell G61).

Return-Maximized Portfolios

Up to this point, we have been attempting to minimize the overall portfolio risk while placing certain constraints on the weightings invested in each stock. However, with the Solver module we can also choose to maximize the expected portfolio return. In these scenarios, we would be maximizing the expected return in cells I60:K60 (target cells), while still changing the weights in rows 49:58. Our three return-maximized portfolios use the same constraints as their risk-minimized counterparts. We focused on a long-only portfolio, as well as long-only portfolios with weightings between 0% and 25% and between 5% and 15%.

Since we are not concerned with risk, it makes sense that our long-only, return-maximizing portfolio in column I would invest solely in the stock with the highest average annual return—SanDisk Corporation (SNDK). As a result, the portfolio expected return and standard deviation in the first scenario are those of SanDisk—60.4% and 104.5%, respectively.

Capping the weight of any individual stock at 25% again led to a portfolio with only four stocks: Aeropostale Inc. (ARO), Corning Inc. (GLW), Kulicke and Soffa Industries (KLIC) and SanDisk Corporation (SNDK). This resulted in a portfolio with an expected annual return of 45.3% (cell J60) and a standard deviation of 78.4% (cell J61). These are the four companies with the highest average annual returns over the last eight years.

Requiring that each stock make up at least 5% of the portfolio but no more than 15% lowered the expected return to 34.0% (cell K60) and the standard deviation to 56.7% (cell K61). Arrow Electronics (ARW), with the fifth-highest average annual return, received a 15% weighting along with the four stocks from the scenario in column J.

Conclusion

We have shown how you can use spreadsheets to calculate important risk- and return-related portfolio statistics as well as to minimize the overall risk or maximize the expected return of a multi-stock portfolio. While these calculations are useful when creating investment portfolios, it is important to realize that they rest on the assumption that historical relationships between individual assets and asset classes will hold in the future. Furthermore, the time period you use for collecting investment returns will impact the results of your analysis. It is important to choose a period that you feel is representative of a “typical” market cycle. Otherwise, you could be capturing only part of a cycle or using data that is too old and no longer relevant. Knowing the limitations of this kind of portfolio analysis is just as important as what it might tell you.