PORTFOLIO OPTIMIZATION - Two Assets

 

Problem. The riskless rate is 6.0%. Risky Asset 1 has a mean return of 14.0% and a standard deviation of 20.0%. Risky Asset 2 has a mean return of 8.0% and a standard deviation of 15.0%. The correlation between Risky Asset 1 and 2 is 0.0%. Graph the Efficient Trade-Off Line and the Risky Asset Trade-Off Curve.

 

Solution Strategy. Determine the Risky Asset Trade-Off Curve for two-asset portfolios by varying the proportion in the first asset and calculating the resulting portfolio’s standard deviation and expected return. Then, determine the Optimal Combination of Risky Assets by calculating the optimal proportion in the first asset and calculating the corresponding standard deviation and expected return. Finally, determine the Efficient Trade-Off Line by varying the amount in the Optimal Combination and calculating the corresponding standard deviation and expected return. Then graph everything.

 

FIGURE 1.  Spreadsheet for Portfolio Optimization - Two Assets

 

 

How To Build Your Own Spreadsheet Model.

 

1.       Inputs. Enter the inputs described above into the ranges B5:B7 and C6:C8.

 

2.       Expected Return – Riskless Rate. Calculate the Expected Return minus the Riskless Rate by entering =B5-$B$5  in cell D5 and copying that cell to the range D6:D7.

 

 FIGURE 2.  Spreadsheet Details for Portfolio Optimization - Two Assets

 

 

3.       Proportion in Risky Asset 1. In order graph the Risky Asset Trade-off Curve, we need to evaluate a wide range of values (-60.0% to 140%) for the Proportion in Risky Asset 1. Enter –60.0% in cell B23, –50.0% in cell B24, and highlight the range B23:B24. Then hover the cursor over the lower right corner and it turns to a “fill handle” (which looks like a “+” sign). Drag the fill handle down to B43.

 

4.       Standard Deviation. The x-axis of our graph is the portfolio’s standard deviation, which is calculated by the formula    . Enter

 

        =SQRT(B23^2*$C$6^2+(1-B23)^2*$C$7^2+2*B23*(1-B23)*$C$8*$C$6*$C$7)

 

        in cell C23 and copy the cell to the range C24:C43.

 

5.       Expected Return. The formula for a portfolio’s expected return is . Enter  =B23*$B$6+(1-B23)*$B$7  in cell D23 and copy the cell to the range D24:D43.

 

6.       Optimal Combination of Risky Assets. Using the notation that and , then the formula for the optimal proportion in the first asset is

       . In cell B44, enter

 

       =(D6*C7^2-D7*C8*C6*C7)/(D6*C7^2+D7*C6^2-(D6+D7)*C8*C6*C7)

 

       Calculate the corresponding Mean and Standard Deviation by copying the range C43:D43 to the

       range C44:D44. We want to create a  separate column for the Efficient Trade-Off Line, so move the formula in cell

       D44 to cell E44. To do this, select cell D44, click on  Edit  Cut, select cell E44, and click on  Edit  Paste.

 

7.       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 B45, 100.0% in cell B46, and 200.0% in cell B47.

·         Since the Riskless Asset has a standard deviation of zero, the standard deviation formula simplifies to , where  = standard deviation of the Optimal Combination of Risky Assets (or Tangent Portfolio). Enter  =B45*$C$44  in cell C45 and copy the cell to the range C46:C47.

·         The Expected Return formula is , were  expected return of the Tangent Portfolio. Enter  =$E$44*B45+$B$5*(1-B45)  in cell E45 and copy to the range E46:E47.

 

8.       Create The Graph. Highlight the range C23:E47 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 E2:J16.

 

9.       (Optional) Formatting The Graph. Here are some tips to make the chart look attractive:

 

·         Click on one of the Chart curves, then click on  Format  Selected Data Series. In the Format Data Series dialog box under the Patterns tab, select  None  for the Marker and click on OK. Repeat for the other curve.

·         Highlight individual points, such as the Riskless Asset, Tangent Portfolio, and Risky Assets 1 and 2, by clicking on a chart curve, then click a second time on an individual point (the four-way arrows symbol appears), then click on  Format  Selected Data Point. In the Format Data Point dialog box under the Patterns tab under the Marker, select a market Style, Foreground Color, Background Color, and increase the size to 8 pts and click on OK.

·         Click on the x-axis, then click on  Format  Selected Axis. In the Format Axis dialog box under the Scale tab, enter 0.25 for the Maximum and click on OK.

 

Investors prefer points on the graph that yield higher mean returns (further “North”) and lower standard deviations (further “West”). The graph shows that best combinations of high return and low risk (furthest in the “Northwest” direction) are given by the Efficient Trade-Off Line. Better combinations are simply not feasible. Since the Efficient Trade-Off Line is a combination of the Riskless Asset and a Tangent Portfolio, then all investors prefer to invest only in the Riskless Asset and a Tangent Portfolio.