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 Morningstar.com.

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 (AZO) 16.27% + 0.5 (AZO) 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.

Conclusion

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.