Spreadsheet Problem: Portfolio Returns and Weights

a. Create a spreadsheet like the one shown below. The spreadsheet should use the returns for assets A and B to form a portfolio return using the weights for each asset shown in cells E1 and E2. The average portfolio return and standard deviation should compute at the bottom of the column of portfolio returns. When you change the weights, the portfolio returns, average, and standard deviation should recalculate.

b. Use the Solver function to find the weights that provide the highest return for a standard deviation of 6 percent, 7.5 percent, 9 percent, 10.5 percent, 12 percent, and 13.5 percent. Report the weights and the return for each of these portfolio standard deviations. The Solver function is found in the Data tab. (You may have to enable the function through the File tab, then Options, then Add-ins.)
The Solver image illustrates the maximizing of the average return for the specific constraints. The constraints are that the weights must be between 0 and 1, inclusive, and must sum to 1.

Lastly, set the standard deviation constraint to the desired level. (LG9-5, LG9-6, LG9-7)

A B C D E F 1 A B Weight A = 0.50 Portfolio 2 -9.1% 20.11% Weight B = 0.50 5.51% 3 11.9% 4.56% Sum= 1 8.23% 4 -22.1% 7.17% -7.47% 5 28.7% 2.06% 15.38% 6 10.9% 7.70% 9.30% 7 4.9% -6.50% -0.80% 8 15.8% 1.85% 8.82% 9 3.5% 9.81% 6.66% 10 -5.5% 22.7% 8.60% 11 23.45% -12.19% 5.63% 12 15.06% 9.38% 12.22% 13 2.11% 29.93% 16.02% 14 16.00% 3.56% 9.78% 15 32.39% -12.66% 9.87% 16 13.69% 15.07% 14.38% 17 18 9.4% 6.8% Average Average = 8.1% 19 14.41% 12.04% = StDev StDev= 6.07%