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 investmentsthe 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 articles 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 Prizewinning 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 stocks 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 Excels 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:K11B12:K12),(B4:K11B12:K12))/8. Where:

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 .