CI Market Dashboard

We have put together a collection of market indicators and track them to help you gauge the direction of the market.


CI Analysis Worksheets

Interactive analysis templates covering DuPont analysis (return on equity), valuing stocks the Warren Buffett way, and more to come.


CI Blog Feed

NFC will finally be a success, thanks to Apple Pay
posted 2 hours ago by Hareesh Jayanthi

A New Way to Invest; Motif Investing
posted 2 days ago by Jaclyn McClellan

Unlimited Cloud Storage for Office 365 Subscribers
posted 4 days ago by Jaclyn McClellan

MeGooDo LED Mini Projector Review
posted 5 days ago by Hareesh Jayanthi

Bullish & Bearish Sentiment Hit 8-Week Extremes
posted 8 days ago by Wayne Thorp



Computerized Investing > Fourth Quarter 2011

Mean Variance Optimization: Multi-Asset Portfolio

PRINT | | | | COMMENTS (26) | A A   Reset

by Wayne A. Thorp, CFA

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.

Click here to download the Mean Variance Optimization Part II Spreadsheet.


Discussion

Blaine from UT posted over 3 years ago:

How can one download this spreadsheet? I find it much easier to understand when looking at the spreadsheet as oppose to looking at formulas in the article.
If it is not downloadable, why not?


Brent from NY posted over 3 years ago:

=SQRT(MMULT(MMULT(TRANSPOSE(D49:D58),($B$33:$K$42),D49:D58))

This formula has an open parenthesis. There are 5 left parens and only 4 right parens. Anybody know if it's a matter of too many lefts or too few rights? I keep getting an error message in my spreadsheet with this formula. Thanks.


Wayne from IL posted over 3 years ago:

The formula should be:

=SQRT(MMULT(MMULT(TRANSPOSE(D49:D58),$B$33:$K$42),D49:D58))

No open paren before $B$33

Wayne A. Thorp, CFA, editor, Computerized Investing


Ed from CA posted over 3 years ago:

How can a self covariance is not 1?


Daniel from NV posted over 3 years ago:

The covariance of a security's returns with itself equals the variance of that security's returns. It will rarely be exactly 1; the more volatile the security, the higher the variance. Every element on the diagonal of the covariance matrix is actually a security's variance.


Herb from PA posted over 3 years ago:

Since few portfolios are likely to have exactly the same number of stocks as the sample, it would have been helpful to detail the changes needed to accommodate additional stocks, or a different number of years of data. Also, since the number of years is more likely to be limited than the number of stocks, wouldn't the spreadsheet have been more compact (fewer columns) if the stocks were listed in column A than across row 3?


Jerry from AZ posted over 3 years ago:

What differences are there between the correl function and above? Does the covariance offer the same standard or solid answer as the correlation?


Ronald from CO posted over 3 years ago:

I repeat my earlier comment:

There is a simple way to calculate mean and variance without calculating covariances. Simply calculate the total return for your portfolio (of as many weighted items as you want) for each time period. Then use the AVG and VAR functions on the results. It can be shown mathematically that the mean and variance calulated this way are the same as using covariances (and much easier). You can still use SOLVER to optimize weights to maximum mean return with fixed variance or vice versa. I would be happy to send a spreadsheet to anyone interested for noncommercial purposes. nordron@umich.edu


John from ID posted over 3 years ago:

I loved the article but have a hard time getting the historical annual returns.
I have the following question: I used to use the "Risk Grades" web site to judge the risk of my portfolio and to see "what if" changes. The web site is closed now and I am looking for a way to see the "risk" in my portfolio. Will this spread sheet give me the approximate risk values ??
Second question: Where has the "Risk Grades" site moved to??
Looking forward to an answer or reply.
Thanks,
John


Michael from NY posted over 3 years ago:

Thanks for correcting the portfolio Std Dev formula.


Clifford from CA posted over 3 years ago:

I've hit the wall in Cell D61.

I entered "=SQRT(MMULT(MMULT(TRANSPOSE(D49:D58),$B$33:$K$42),D49:D58))"; got "#VALUE"; entered the Solver Parameters including the Constraint that $D$59 = 1 & got the Solver Results "Solver could not find a feasible solution" with all cells in the range D49:D59 populated by 0% in spite of the constraint that $D$59 = 1.

Is my problem because I'm using Excel 2010??


Geoff from TX posted over 3 years ago:

Cliff, did you try this ... copy-paste this value into cell D61
"=SQRT(MMULT(MMULT(TRANSPOSE(D49:D58),($B$33:$K$42)),D49:D58))" (without the quotes) and then press Ctrl+Shift+Enter to enter this formula ... you should be able to copy-paste this formula into all other cells on row 61 .. just drag the formula across, no need to Ctrl+Shift+Enter when copying across cells


Geoffrey from TX posted over 3 years ago:

also, when using any kind of solver, you can try preloading the cells with approximate values


Geoffrey from TX posted over 3 years ago:

My friend works for MSCI, I'll ask her what happened to "Risk Grades" ... maybe they sold it to a hedge fund :)


Clifford from CA posted over 3 years ago:

Thanks for all the help. I got it to work & now I'm trying to migrate this to a portfolio of 31 stocks.


Eric from CA posted over 3 years ago:

When I select the range to fill in the top half of my 1st covar. matrix and then press Ctrl+Shift+Enter it simply puts the calculation to all the cells in my data range. What am I doing wrong.

Thanks,
Eric


Dennis from ID posted over 2 years ago:

This is great stuff. I have followed through your tutorial by creating my own 'proof of concept' Excel-2010 sheet (it runs satisfactorily). Applying this approach to the real world problem (eg., setting up a 30-stock portfolio) appears to be quite labor intensive however. Do you know of any downloadable Excel 2010 templates that are macro-enabled and capable of:
1) importing the historical data for a portfolio of stocks
2) doing the data massaging required in advance of using Solver

Short of citing an elegant rendition of the above is it possible that in the near future you'd be doing an article on importing data from the web to populate the historical-data table that your method uses?

Although I'm working on my Excel competencies I'm not power user yet and this kind of tutorial stuff is much appreciated. :)


James from OH posted over 2 years ago:

Jerry, here's the primary difference between correlation and covariance.

Correlation is a "relative" number. It is usually presented as a percentage and is always a number that ranges from -100% to +100%. Therefore, comparisons are more meaningful. If A and B are 90% correlated and C and D are 70% correlated, then it can be fairly said the A and B are more highly correlated.

Covariance is an "absolute" number and relates to the magnitude of the underlying data series upon which is is calculated. It has no limit. It could be -1 million or + 5 million. Hence, comparisons are not particularly relevant or effective.

I hope you feel this helps.


Jerry from AZ posted about 1 year ago:

Eric,

Here is a website for excel sheets. Look for the multiple stock download sheet.

http://investexcel.net


Ted Nicholas from CA posted about 1 year ago:

Given the continual marketing of AAII's various optional cost offerings, it would be very interesting to see what the mean variance optimization of, say, the stock superstars portfolio is. Always nice to see how well risk is optimized when paying for advice.


Dan from CA posted about 1 year ago:

This is a joke, right? Or a test for your readers? If not, you should crosscheck by some other method before you believe a tool's result! If I start $100,000 in SNDK and leave it there for the eight years, I'll have $490,757. Do the same with ARO, I'll have $773,942. What is your definition of maximum return? If I rebalance yearly to 42.6% ARO, 17.8% GTIV and 39.6% SNDK, I'll end up with $963,634. That's my definition of maximun return.


Wayne Thorp from IL posted about 1 year ago:

@Dan, risk is in the eye of the beholder. You already know the returns so it makes the decision of placing your assets into three stocks very easy.


D Kane from CA posted about 1 year ago:

We're talking about maximizing return when risk is not a consideration. You said in that section of the article, "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)." I could see that this is not the case by looking at ARO. Just how does your optimizer come up with SNDK? When I run solver to maximize the dollar value at the end of 8 years, I get the portfolio I mentioned.


George B from Ohio posted about 1 year ago:

Is this spreadsheet available for download. My motto is why reinvent the wheel if you don't have to. If it is available for download where do I find it.

Thanks

George


Tim M from Colorado posted 7 months ago:

Instead of looking to either minimize risk or maximize return, an investor might wish to find the optimal mix of both. In other words, the most return per unit risk. This can be measured by the Sharpe ratio.

If we enter the formula for the Sharpe ratio and set the Solver to maximize it, then we can find an optimal risk/reward ratio without putting artificial constraints on the weightings.

Using the current 10-year Treasury bond yield (2.72%) as the risk-free input to the Sharpe ratio, I used the Solver to find the long-only portfolio with the highest Sharpe ratio. The result was GTIV=75.21%, PL=10.47%, SNDK=14.31%. This yielded an expected return of 22.59% with an SD of 18.13%. This is a better return of than the 5%-15% portfolio in the example (21.55% return, 33.28% SD) but with a much better SD.


You need to log in as a registered AAII user before commenting.
Create an account

Log In