PORTFOLIO OPTIMIZATION - Many Assets

 

Suppose that you had N risky assets, rather than just two risky assets. How would you calculate the Efficient Trade-Off Line and the Risky Asset Trade-Off Curve in this case? It turns out that it is much easier to handle N risky assets in a spreadsheet than any other way. The figure below shows the results of the N=5 risky assets case, including a bar chart of the portfolio weights of the optimal (tangent) portfolio.

 

FIGURE 1.  Spreadsheet for Portfolio Optimization - Many Assets.

 

 

 

How To Build This Spreadsheet Model.

 

1.      Inputs. Enter the expected return inputs into the range C5:C10, the standard deviation inputs into the range D6:D10, the correlation inputs in the triangular range from B15 to B18 to E18, and 0.0% in the cell D5.

                       

2.      One plus the Expected Return and 100%.  It will be useful to have a column based on one plus the expected return and another column with just the number 100%. Enter  =1+C5  in cell E5 and copy the cell to the range E6:E10. Enter 100%  in cell F6 and copy the cell to the range F7:F10.

 

3.      Fill Out the Correlations Table (Matrix). The correlations table (matrix) from B14:F18 has a simple structure. All of the elements on the diagonal represent the correlation of an asset return with itself. For example, B14 is the correlation of the Asset 1 return with the Asset 1 return, which is one. C15 is the correlation of Asset 2 with 2, and so on. Enter 100.0% into the diagonal cells from B14 to F18. The off-diagonal cells in the upper triangular range from C14 to F14 to F17 are the “mirror image” of the lower triangular range from B15 to B18 to E18. In other words, the correlation of Asset 2 with Asset 1 in C14 is equal to the correlation of Asset 1 with Asset 2 in B15. Enter  =B15  in cell C14, =B16 in D14, =B17 in E14, etc. Each cell of the upper triangular range from C14 to F14 to F17 should be set equal to its mirror image cell in the lower triangular range from B15 to B18 to E18.

4.      Transposed Standard Deviations.  In addition to the standard deviation input range which runs vertically from top to bottom, it will be useful to have a range of standard deviations that runs horizontally from left-to-right. This can be done easily by using one of Excel's Matrix commands to transpose a range. Highlight the range B22:F22. Then, type  =TRANSPOSE(D6:D10), hold down the Shift and Control buttons simultaneously, and while continuing to hold them down, press Enter. The resulting formula should have braces around it {=TRANSPOSE(D6:D10)}.

 

5.      Variances and Covariance’s Table (Matrix). The Variances and Covariances Table (Matrix) in the range B26:F30 has a simple structure. All of the elements on the diagonal represent the covariance of an asset return with itself, which equals the variance. For example, B26 is the covariance of the Asset 1 return with the Asset 1 return, which equals the variance of asset 1. C27 is the variance of Asset 2, and so on. The off-diagonal cells are covariances. For example, C26 is the covariance of the Asset 1 return with the Asset 2 return and is calculated with the formula for the Covariance(Asset 1, Asset 2) = (Std Dev 1) * (Std Dev 2) * Correlation(Asset 1, Asset 2). Enter  =C$22*$D6*C14  in cell C26. Be very careful to enter the $ absolute references exactly right. Then copy C26 to the range B26:F30.

 

FIGURE 2. Spreadsheet Details for Portfolio Optimization - Many Assets.

 

 

 

6.      Hyperbola Coefficients. In a Mean vs. Standard Deviation graph, the Efficient Frontier is a hyperbola. The exact location of the hyperbola is uniquely determined by three coefficients, unimaginatively called A, B, and C. The derivation of the formulas can be found in Merton (1972).[1] They are easy to implement using Excel's matrix functions. In each case, you type the formula and then, hold down the Shift and Control buttons simultaneously, and while continuing to hold them down, press Enter.

 

      ·         For A: =MMULT(MMULT(TRANSPOSE(F6:F10),MINVERSE(B26:F30)),F6:F10) in cell C37.

·         For B: =MMULT(MMULT(TRANSPOSE(F6:F10),MINVERSE(B26:F30)),E6:E10) in cell C38.

·         For C: =MMULT(MMULT(TRANSPOSE(E6:E10),MINVERSE(B26:F30)),E6:E10) in cell C39.

 

7.      Miscellaneous. It will simplify matters to create range names for various cells. Put the cursor in cell C37, click on  Insert  Name  Define, enter the name “A” and click on OK. Repeat this procedure to give cell C38 the name “B”, give cell C39 the name “C.” (Excel does not accept plain “C”), give cell C40 the name “Delta”, give cell C41 the name “Gamma”, and give cell E5 the name “R.” (again, Excel does not accept plain “R”). Enter   =A*C.-(B^2)  in cell C40 and enter  =1/(B-A*R.)  in cell C41. Some restrictions do apply on the range of permissible input values. The variable Delta must always be positive or else the calculations will blow-up or produce nonsense results. Simply avoid entering large negative correlations for multiple assets and this problem will be taken care of.

 

8.      Individual Risky Assets. In order to add the individual risky assets to the graph, reference their individual standard deviations and expected returns. Enter  =D6  in cell D44 and copy down to the range D45:D48. Enter  =C6 in cell G44 and copy down to the range G45:G48.

 

9.      Expected Return. Using the three Hyperbola coefficients, we can solve for the expected return on the upper and lower branches of the hyperbola which correspond to a standard deviation of 25% and then fill in intermediate values in order to generate the Efficient Frontier graph.

 

      ·         For the upper branch, enter =(2*B-(4*B^2-4*A*(C.-(0.25^2)*Delta))^(0.5))/(2*A)-1 in cell E49.

·         For the lower branch, enter =(2*B+(4*B^2-4*A*(C.-(0.25^2)*Delta))^(0.5))/(2*A)-1 in cell E69.

·         Fill out in index for expected return by entering 0 in cell C49, entering 1 in cell C50, selecting the range C49:C50, and dragging the fill handle (in the lower right corner) down the range C51:C69.

·         Fill in the intermediate values by entering  =$E$49+($E$69-$E$49)*(C50/20)  in cell E50 and copying the cell down the range E51:E68.

 

10.  Standard Deviation. Again using the three Hyperbola coefficients, we can solve for the Efficient Frontier standard deviation which corresponds to any particular value of expected return. Enter =((A*(1+E49)^2-(2*B*(1+E49))+C.)/(A*C.-(B^2)))^(1/2) in cell D49 and copy it down the range D50:D69.

 

11.  Tangent Portfolio. The Optimal Combination of Risky Assets (or Tangent Portfolio) can be calculated using Excel's matrix functions. In each case, you type the formula and then, hold down the Shift and Control buttons simultaneously, and while continuing to hold them down, press Enter.

 

      ·         For the portfolio weights: Select the range I44:I48, then type    

            =Gamma*MMULT(MINVERSE(B26:F30),(E6:E10-R.*F6:F10))

      ·         For expected returns: enter  =MMULT(TRANSPOSE(I44:I48),E6:E10)-1  in cell F70.

      ·         For standard deviations: enter =SQRT(MMULT(MMULT(TRANSPOSE(I44:I48),B26:F30),I44:I48))  in cell D70.

 

12.   Efficient Trade-Off Line. The Efficient Trade-Off Line is a combination of the Riskless Asset and the Risky Asset Optimal Combination. It can be calculated as follows:

 

      ·         Enter 0.0% in cell C71, 100.0% in cell C72, and 200.0% in cell C73.

      ·         As before, the standard deviation formula simplifies to . Enter =C71*$D$70  in cell D71 and copy the cell to the range D72:D73.

      ·         The Expected Return formula is . Enter =$F$70*C71+$C$5*(1-C71)  in cell F71 and copy to the range F72:F73.

 

13.   Create And Locate The Graphs. Highlight the range D44:G73 and then choose  Insert  Chart from the main menu. Select an  XY(Scatter)  chart type and make other selections to complete the Chart Wizard. Place the graph in the range G3:K19. Highlight the range I44:I48 and then choose  Insert  Chart from the main menu. Select a  Column  chart type and make other selections to complete the Chart Wizard. Place the graph in the range G21:K34. Optionally, one can format the graph as discussed in the previous section.

 

The graphs show several interesting things. First, look at the Efficient Frontier Curve. At a standard deviation of 20% (same as all of the individual assets), it is possible to achieve a mean return of nearly 13% despite the fact that 12% is the highest mean return offered by any individual asset. How is this possible? The answer is that it is possible to sell (or short sell) low mean return assets and use the proceeds to invest in high mean return assets. Said differently, put a negative portfolio weight (short sell) in low mean assets and “more than 100%” in high mean assets.

 

Second, the bar chart shows that the optimal (tangent) portfolio represents a trade-off between exploiting higher means vs. lowering risk by diversifying (e.g., spreading the investment across assets). On the one hand it is desirable to put a larger portfolio weight in those assets with higher mean assets (#4 and #5 in this example). On the other hand, spreading assets more (getting closer to 20% per each of the five risky assets) would lower the overall risk of the portfolio. Hence, the optimal portfolio does not put 100% in the high mean assets, nor does it put 20% in each asset, but instead finds the best trade-off possible between theses two goals.

 

Third, you should be delighted to find any mispriced assets, because these are delightful investment opportunities for you. Indeed, many investors spend money to collect information (do security analysis) which identifies mispriced assets. The bar chart shows you how to optimally exploit any mispriced assets that you find. Below is a list of experiments that you might wish to perform. Notice as you perform these experiments that the optimal portfolio exploits mispriced assets to the appropriate degree, but still makes the fundamental trade-off between gaining higher means vs. lowering risk by diversifying.

 

·         What happens when an individual asset is underpriced (high mean return)?

·         What happens when an individual asset is overpriced (low mean return)?

·         Is it possible for a very low mean return to optimally generate a negative weight (short sell)?

·         What happens when an individual asset is mispriced due to a low standard deviation?

·         What happens when an individual asset is mispriced due to a high standard deviation?

·         What happens when the riskless rate is lowered?

·         What happens when the riskless rate is raised?

·         What happens when risky assets 1 and 2 have a 99% correlation?

 

Fourth, in general terms the optimal (tangent) portfolio is not the same as the market portfolio. Each individual investor should determine his or her own tangent portfolio based on his or her own beliefs about asset means, standard deviations, and correlations. If an individual investor believes that an particular asset is mispriced, then this belief should be optimally exploited. Only under the special conditions and the restrictive assumptions of CAPM theory would the tangent portfolio also be the market portfolio.

 

Here are some additional projects / enhancements you can do:

 

·         Obtain historical data for different asset classes (and/or assets in different countries) and calculate the means, standard deviations, and correlations. Then, forecast future means, standard deviations, and correlations using the historical data as your starting point, but making appropriate adjustments. Input those means, standard deviations, and correlations into the spreadsheet model in order to determine the optimal portfolio. If you have monthly data, you can switch from annual returns to monthly returns by simply entering the appropriate monthly returns numbers and then rescaling the graph appropriately (see how to format the graph scale in the previous section).

·         Create a Dynamic Chart by adding “spinners” (see other Dynamic Charts for examples of how to do this)

·         Expand the number of risky assets to any number that you want. For example, expanding to six risky assets simply involves adding a sixth: (1) expected return in cell C11, (2) standard deviation in cell D11, (3) one plus expected return in cell E11, (4) 100% in cell F11, (5) row of correlations in the range B19:F19 and column in the range G14:G19, (6) row of variances / covariances in the range B31:F31 and column in the range G26:G31. Finally, reenter all of the matrix functions changing their references to the expanded ranges.  Specifically, reenter matrix functions for: transposed standard deviations, A, B, C, tangent portfolio weights, tangent portfolio expected return, and tangent portfolio standard deviation.