Portfolio Optimization

1. Calculate the monthly returns of 10 assets using data from Bloomberg, Capital IQ, FactSet, FINRA, or Reuters. These can be stocks, mutual funds, or ETFs. You should have a minimum of 40 observations in each asset. Hint: (new/old)-1 OR (new-old)/old
2. Find or calculate the 1-month yield on a US treasury bond Hint: The Fed has an index called Constant Maturity.
3. Calculate excess returns by subtracting each months risk-free rate from each stock return in each month.
4. Calculate the covariance matrix using the excess returns from the 10 assets. The covariance function can be found in data analysis under the data tab. Remember to fill in the other triangle since the matrix is symmetric. The matrix should be 10x10.
5. Calculate the average excess return for each asset. This will be your expectation for future returns in a single period. (In reality, we would use a forward-looking prediction but we are keeping it simple here.)
6. Calculate the portfolio excess return using matrix multiplication. Hint: Use the mmult function and Shift-Control-Enter when done. ret = w x r
7. Calculate the portfolio variance using matrix multiplication. Keep in mind whether your weight vector is already transposed or not. Hint: var = w x x w
8. Calculate the Sharpe ratio, and the sum of the weights
9. Use the solver to find the weights that maximize the Sharpe ratio. The sum of all weights must equal 100%. (Make sure that the solving method is set to GRG Nonlinear in the Solver dialog box. If the results appear to be unreasonable, try running the solver a few more times with a different set of starting weights.)
10. Annualize the portfolio excess return, standard deviation, and Sharpe ratio Hint: [1+r]¹²-1, 12