Investment Portfolio Optimization: Analysis and Recommendations

Performance Evaluation and Optimization

Based upon your consultations with Weaver, you have drawn up a list of issues that need to be addressed:

1. How well did the Fund’s portfolio perform over the nine?year period in terms of average monthly return and average monthly standard deviation? How well did the risky part of the portfolio perform? (To calculate this for each month, take out the 10% of the return that represents the T-bill return, which will leave the remaining 90% that consists of the risky assets. Then, divide that number by 0.9)

2. Calculate the investment proportions required to achieve the optimal (tangency) risky portfolio and the minimum variance portfolio. (Use Solver in Excel to achieve this.) What is the Sharpe Ratio of each? 
(When solving for the variance?covariance matrix, use “var.s” and “covariance.s” as your variance and covariance  formulas. The “s” in the formula refers to using a sample of the data, not the entire population of data.)

Tip: Set up Solver to spin through all the possible weights of the asset classes to find the set of weights that maximize the value in the cell containing your Sharpe ratio formula to find the tangency portfolio. Do the same thing to find the MVP, except you are trying to find the set of weights that minimize the portfolio variance.

I have posted the 3?asset example spreadsheet that I used in class that you may use as a template to build your spreadsheet. The videos on Carmen walk you how to use Solver to complete this.

3. Examine the weights of your tangency portfolio and MVP. If a weight is negative, that means a short position. If a weight is greater than 1, more than 100% of the portfolio’s total value is being invested in that asset. Do these weights look reasonable to you? 

4. The Fund is concerned about the use of short positions in the construction of the risky portfolio. Recompute the 3?asset tangency portfolio after adding a constraint to prevent shortselling/borrowing. What is the Sharpe Ratio under this constraint, and how does it change the investment weights? (To do this, re-run the Solver calculation, but check the box in Solver that reads “Make Unconstrained Variables Non-Negative”.)

5. Repeat the analysis in #4, except allow for short positions of up to 25% of the portfolio. To accomplish this, add a  constraint for each weight cell so that Solver constrains each weight to being greater than or equal to -0.25.

6. Calculate the investment proportions required to construct a complete portfolio (i.e. one which mixes the optimal risky portfolio from question #2 above with T?bills, discussed in Chapter 5) that has an expected return equal to the present (complete) portfolio's expected return. What is the expected standard deviation of return on such a portfolio?

(Note: This simply requires algebra to solve for the two weights. Similar to problem 13a. in Chapter 5.)

7. Suggest a complete portfolio allocation between the optimal risky portfolio and T?bills to achieve the Fund's objective of a 'floor' rate of return equal to 0.007 (0.7%) per month. What is the standard deviation of the portfolio? 

8. Weaver is also interested in knowing if the Fund should include three more asset classes in its portfolio: international stocks, real estate and hedge funds. Make a case for or against the inclusion of these three asset classes in the Fund's overall portfolio. Justify your decision by solving for the new tangency portfolio after including the three new asset classes. What is the Sharpe ratio of the new tangency portfolio? Then repeat what you did in question #7 by calculating the complete portfolio allocation required to achieve the floor rate of return (0.7%/month) mandated by the Fund with this expanded universe of assets. What is the expected standard deviation of the new complete portfolio?

9. Repeat what you did in #5, constraining the weights so that each is greater than or equal to -0.25. What is the Sharpe ratio after you do this?

10. Finally, after all this analysis, what is your recommendation? You can stay with The Fund’s current allocation, choose a set of weights based on your 3-asset, 6-asset, short/no-short optimizations, a blend of all the above, or none of the above. What do you tell your client?