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.

Computerized Investing > Third Quarter 2011

Mean Variance Optimization

PRINT | | | | COMMENTS (8) | A A   Reset

by Wayne A. Thorp

Modern portfolio theory (MPT) dates back to the 1950s and Harry Markowitz’s portfolio selection technique. It was the first time investors had a formal model quantifying the impact of portfolio diversification. Markowitz’s work was so revolutionary that he eventually was awarded the Nobel Prize in economics. Markowitz’s work showed that you could lower the risk of an investment portfolio by adding risky assets to the mix. Just as important, however, was that you could lower the total risk of a portfolio without sacrificing its expected return.

Prior to Markowitz’s research, investment strategies focused on individual asset selection using anecdotal observations of past performance of companies and their industries. As a result, you could very well end up with a portfolio that looked conservative, yet was not really diversified. Markowitz placed portfolio return and risk on equal footing and illustrated that, through an understanding of how individual securities are correlated, you could lower the risk of the overall portfolio without having an adverse impact on return. The key to building a portfolio is to look beyond the risk-reward characteristics of the individual securities and examine how the securities interact together.

In this installment of Spreadsheet Corner, we discuss the concept of portfolio diversification and derive a spreadsheet model in order to maximize the risk-return makeup of a two-stock portfolio. In future articles we will expand on this premise to aid you in the construction of a multi-asset investment portfolio.

Portfolio Diversification

Investing carries with it risk. We buy assets in the hopes that their value will increase, but there is no guarantee that this will happen. If there were no uncertainty as to investment returns, we would simply buy the investment offering the highest return over our desired holding period. Faced with certain uncertainty, however, prudent investors choose to invest across several assets—to diversify—in the hopes that the gains in some of the assets will offset the losses in others.

Harry Markowitz illustrated how owning more than one risky investment can actually be less risky than merely owning a single investment. To help describe the theory behind this diversification effect, we look at a two-stock portfolio consisting of AutoZone Inc. (AZO) and Duke Energy (DUK). Table 1 shows the annual returns for both stocks for each of the last five years, ending December 31, 2010. This data is available for free from

Looking at the returns for both stocks, we see that they have fluctuated over the last five years. To measure the extent of these fluctuations, we begin by calculating the five-year simple average returns for each stock. Using Excel, this is an easy process. In cell B9, we enter the following function: =AVERAGE(B4:B8). This will return the simple average of the values in the range of cells from B4 through B8, which are the annual returns for Duke Energy. We repeat the process in cell C9 to calculate the average returns for AutoZone. We see that Duke has an average total return of 8.65% over the last five years, while AutoZone has averaged an annual total return of 26.36% over the same period. For our analysis, we assume that these average historical returns are the expected average returns going forward. Therefore, AutoZone would be our preferred investment.

At this point, it may be worthwhile to explain the difference between an arithmetic and a geometric average. An arithmetic or simple average is the sum of a series of numbers divided by the number of data points in the series. This is in contrast to a geometric average, which is typically used to measure the performance of investments. To calculate geometric mean, one is added to each number in the series and then all the numbers are multiplied together. Lastly, this product is raised to the power of one divided by the number of data points in the series, and then one is subtracted from the result.

However, return is only one part of the investment selection process. The riskiness of an investment’s returns can be measured by calculating the standard deviation. Standard deviation measures the level of variation or dispersion from the average or mean. The higher the standard deviation, the greater the variability. Referring back to Table 1, the standard deviation for the returns of Duke and AutoZone are displayed in cells B10 and C10, respectively. To calculate the standard deviation of Duke’s annual returns, we enter this formula in cell B10: =STDEVP(B4:B8).

(It is worth noting that that this formula calculates the population standard deviation: the standard deviation of the given values. Normally we would calculate a sample standard deviation, since we are only using a subset of historical returns. This is a different formula, which estimates a population based on a sample. However, we will shortly be calculating the correlation and covariance between the returns of these two stocks, and Excel only calculates population correlations and covariances. For the sake of consistency, therefore, we use the population standard deviation formula.)

From our calculations, we see that Duke has a standard deviation of 16.27%, while AutoZone’s standard deviation is 24.11%. We now have a more complete picture of our investment choices. AutoZone has a significantly higher expected return, but carries with it a higher level of risk, as measured by standard deviation. The choice as to which stock is preferable comes down to your individual risk tolerances.

Up to this point, we have been considering these two stocks as alternatives to each other. Let us now consider them in the context of a two-stock investment portfolio. The column in Table 1 labeled Portfolio assumes equal investments (50% each) in DUK and AZO stock. The annual returns for this portfolio are a weighted average of each stock’s annual return. Let’s further assume the portfolio is rebalanced at the end of each year so that the year begins with an equal investment in each stock. Therefore, the formula entered in cell D4 is: =0.5*B4+0.5*C4. We then copy this formula to each cell in the range from D5:D8 and copy the average annual return formula from C9 to D9. We see that the average annual return (expected return) for this two-stock portfolio is 17.51%, which is the average of the two stocks’ expected returns.

Copying the standard deviation formula from cell C10 to D10, we see that the portfolio standard deviation is 15.05%, which is below the standard deviations of either stock. This illustrates the diversification benefits of investing in multiple risky assets. While the standard deviation of the portfolio is lower than that of DUK and AZO individually, the expected return of the portfolio—17.51%—is below that of AutoZone (26.36%). Again, the choice is dictated by your risk-return preferences.

Correlation Between Assets

The portfolio standard deviation is not merely the weighted average of the standard deviations of DUK and AZO. If that were the case, the portfolio standard deviation would be 20.19% (0.5 × 16.27% + 0.5 × 24.11%). However, this ignores an important element in asset selection—the correlation between DUK and AZO. Correlation refers to how the returns of assets move relative to each other over time. Looking at the annual returns for DUK and AZO in Table 1, we see that they don’t always move in the same direction or with the same magnitude. This is most noticeable in 2008, when DUK fell 21.12% while AZO gained 16.31%.

The correlation between two assets is measured by the correlation coefficient, which can range from –1 to +1. A value of –1 indicates perfect negative correlation, which means the returns of the two assets move exactly in the opposite direction. On the other end of the spectrum is perfect positive correlation, where the correlation coefficient is +1, and the returns move exactly in the same direction. A correlation coefficient of zero means that the performances of the assets are not related. A non-zero correlation coefficient means that the performances of the assets are related, but unless the coefficient is either +1 or –1, there are other influences. Very rarely will you find two assets that are perfectly negatively or positively correlated. In fact, the returns of most assets tend to be positively correlated, meaning they move in the same direction to some extent. Companies that are in related businesses tend to be more highly correlated relative to those that are in very different industries.

To calculate the correlation between two assets in Excel, we use the CORREL function, which measures the correlation between two data ranges, or arrays. In this case, we are measuring the correlation between the ranges containing the annual returns for DUK and AZO. Therefore, in cell B11 we enter this formula: =CORREL(B4:B8,C4:C8). The result—0.0772—means that there is a low positive correlation between the returns of these two stocks. The lack of a strong positive correlation between these two stocks is the reason why the overall portfolio standard deviation is lower than that of the standard deviations of the individual stocks.

Another way in which we can measure the co-movement of returns is with covariance. In fact, the formula for covariance is the numerator of the correlation coefficient calculation. However, the correlation coefficient, with its values ranging from –1 to +1, is easier to interpret. Covariance has its place, though, especially when calculating portfolio statistics, which we will be doing a little later. As with correlation, Excel has a built-in covariance function. To calculate the covariance between the returns on DUK and AZO, we enter this formula in cell B12: =COVAR(B4:B8,C4:C8). The result is 0.00303.

Changing the Weights

Our analysis to this point has assumed equal investments in Duke Energy and AutoZone. By changing the weights we invest in these two stocks, we will change the risk-return profile of the portfolio. Table 2 shows how the expected return and standard deviation of the portfolio change as we change the weights in AZO and DUK. Cells A18:A28 contain the weights allocated to AZO, ranging from 0% to 100%. Since the weights in our two-stock portfolio must equal 100%, the weight invested in DUK is simply 100% less the percentage invested in AZO. So when 0% of the portfolio is invested in AZO, 100% is invested in DUK, such that 1-A18. The formula for the portfolio’s standard deviation (risk), in cell B18, is as follows: =SQRT((A18*$C$10)^2+((1-A18)*$B$10)^2+2*$B$10*$C$10*A18*(1-A18)*$B$11). We can then copy this formula down the rest of the range from B19:B28. The portfolio’s expected return for cell C18 is: =A18*$C$9+(1-A18)*$B$9. Again, we copy this formula down the range from C19:C28. We also create a scatter plot for the data range A18:B28.

Looking at the chart in Table 2, we see that the portfolio standard deviation is equal to that of DUK when 0% of the portfolio is invested in AZO. Likewise, the expected return for the portfolio equals that of DUK. If 100% of the portfolio is invested in AZO, the portfolio standard deviation and expected return are equal to that of AZO. When roughly 30% of the portfolio is invested in AZO, the portfolio standard deviation is at its lowest.

Mean Variance Optimization

As we show in Table 2, there are numerous combinations of weighting in DUK and AZO stock that we can use to create a portfolio. We mentioned that when the portfolio is invested approximately 30% AZO and 70% DUK, the standard deviation or risk of the portfolio appears to be at its lowest. The minimum variance portfolio is that portfolio which is the least risky, given our two risky assets, regardless of the expected return. It is also the beginning of the efficient frontier—the collection of portfolios offering a higher return with the same risk or the same return with less risk than other feasible portfolios. It is from this efficient frontier that investors will choose their investment portfolios. In future articles, we will discuss how to identify multiple portfolios along this efficient frontier. For now, however, we use the Solver function in Excel to identify the exact weightings in our two-asset minimum variance portfolio.

Table 3 shows the data table we use to calculate the weightings for the minimum variance portfolio. To launch the Solver, click on the Data tab in Excel 2007 and click the Solver button (Figure 1). Since we are looking to create the minimum variance portfolio using DUK and AZO, we wish to minimize the standard deviation in cell B18, which is the Target Cell we use in the Solver. We must, however, also enter the standard deviation formula for our two-stock portfolio. So we also enter the standard deviation formula into cell B18: =SQRT((B14*$B$10)^2+(B15*$C$10)^2+2*$B$10*$C$10*B14*B15*$B$11).

Next we select the Min radio button to the right of “Equal To:” 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 by adjusting the weightings of DUK and AZO. Therefore, the By Changing Cells will be the range $B$14:$B$15, which are the weightings for our two stocks. Lastly, we need to add a constraint to our function: specifically, that the sum of the weightings invested in these two stocks will equal 1.0 (100%). Therefore, we click the Add button and add the constraint B16=1. Once we are finished, we click the Solve button.

If all goes as planned, the Solver will find a solution in a few seconds. According to the Solver, investing 70.2% in DUK and 29.8% in AZO will give us the lowest standard deviation possible—13.95%. Again, this assumes that we are totally invested, since the weightings must equal 100%. Such a portfolio will also give us an expected return of 13.94%. As we recall from Table 2, this is very close to the 70/30 portfolio where the standard deviation chart bottomed out.


We have shown how to create a spreadsheet that generates some very useful portfolio statistics that can help investors judge the risk-return profile of multiple assets and investment portfolios. We started with a very simple two-stock example to build a framework for understanding the principles involved. In the Fourth Quarter 2011 issue of Computerized Investing, we will expand our examples to a more realistic multi-asset portfolio.


James from NM posted over 5 years ago:

Very interesting discussion. I'm looking forward to the Fourth Quarter for multi-asset portfolio optimatization. How do you maximize the Sharpe's Ratio?

James from OH posted over 5 years ago:

I just finished reading Wayne Thorp's article, "Mean Variance Optimization" in the Third Quarter 2011 issue of Computerized Investing. - - - He did an excellent job presenting the material and in the model development.

Please note. In my comments below, I am not picking on Wayne. He is spouting standard, prudent investment advice. It's that advice, which I've heard/read elsewhere, that I have reservations about.

I seen this material presented 2-3 other times and every time, the authors, including Wayne, state the investing objective to be "minimize risk. "While this is apparently the popular way to approach portfolio optimization, I think this is entirely the wrong objective. Instead, it should be "maximize return", subject to not exceeding some risk level. - - - Isn't that what most investors really want? Big returns, but they want to sleep at night.

The good news is that Wayne's Solver model solves this problem, too. All you need to do is open the Solver window, specify $B$17 in his model as the Target Cell, specify "Max", and change the constraint to "$B$18 <= X", where X is any level of risk (standard deviation) you are not willing to exceed.

I did this for X = 16% and the Solver answer is a return of 18.89% with 57.8% of the portfolio allocated to AZO. - - - This is materially better than the return of 13.94% which results when the objective is to "minimize risk". For a small increase in risk (from 13.95%, that's almost 14%, to 16%), an investor's portfolio after a few years will be materially larger. - - - Besides, I'd speculate that only a small portion of the people who read Wayne's and similar articles understand the significance of the difference between a risk of 14% and 16%.

Even without using Solver, you can scroll Wayne's values for AZO in Table 2 and determine what maximum level of risk you are willing to accept and determine if the returns are worth it in your mind. A common expression for such a heuristic optimization method is "look for the sweet spot."

Here's another take on this. Investors should not blindly focus on only risk. I can live with very high risk (standard deviation) in my returns, if the returns are sufficiently high enough to warrant the risk. In Wayne's DUK / AZO example, I could have been very comfortable putting 100% of my money in AZO since the returns were so much higher than other options and I wouldn't have lost money in any year. - - - Granted, the focus of Wayne's article was not on how to pick stocks, but, hopefully, this reasserts my earlier point that "minimizing risk" is the wrong objective.

Finally, early in the article, Wayne states, "Prudent investors choose to invest across several assets (to diversify) in hopes that gains in some assets will offset losses in others." I have heard/read this statement more than a couple times over the last few years. I feel this is a view/approach with extremely low expectations. If you adhere to it, I don't think over the long run that an investor could come close to the long term buy and hold strategy of investing in an S&P 500 index fund, which, historically, has produced average annual returns on the order of 7%-9% (not considering dividends, fees, or tax impact). - - - No doubt some readers will feel that I am mincing words or taking the wording too literally with my objections to these statements. That all that needs to be done is to add the words "more than" before the word "offset". To that extent, I agree. However, I never hear/read the words "more than" in such statements and I am concerned that, mentally, too many investors have set their expectations too low as a consequence. - - - This issue is even more exacerbated with the advice that is sometimes given: "Invest in stocks that are inversely correlated." Well, in a 2-stock portfolio that adheres to that advice, one could go up 25% and the other would go down 25%, resulting in a 0% gain. - - - What's the point of that?

Other opinions and better ideas are welcomed.

Bill from NJ posted over 5 years ago:

I tried but was unable to duplicate the Solver portion of the article in my own Excel worksheet. It would be incredibly helpful if the examples were provided as downloadable Excel worksheet files.

Ron from CO posted over 5 years ago:

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.

Fernando from FL posted over 5 years ago:

I think Wayne makes it clear under the sub-heading Mean Variance Optimization that the minimum variance portfolio is the beginning of the efficient frontier, which he further defines as including the collection of portfolios which offer higher return with less risk or the same return with less risk. This is only the beginning of further work to come in the next quarter.

David from CA posted over 5 years ago:

In order to get the Solver portion to work properly I believe you have to set cell B16 to =B14+B15.
Also to get the Expected Return at the end you need to set B17 to =B14*B9+B15*C9.

Jerry Kirkman from AZ posted over 3 years ago:

Thanks David

Good job finishing the article with the rest of the formulas.

Andrew Harrell from MS posted over 2 years ago:

UP OH! Upon further study, and printing out an enlarged Table 2, I see that your first standard deviation formula is for the weight changes in the single stock AZO. Its the second cell formula of the two stock standard deviations on the next page that is used for the Newton Raphson, multivariate calculus derivative quadratic form minimization Solver. Interesting. Thanks for the spreadsheet mathematical thought challenge.

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

Log In