Portfolio Optimization Techniques

Portfolio Assignment

1. Calculate all of the portfolio statistics:
a. Browse through the sheet. There is a vector named Ret. These are the mean returns over the period. We will use these as the proxy expected return for each stock.
b. Several lines below this are the correlation matrix and the covariance matrix. The relevant portion of the covariance matrix is named VCOV.
c. Below VCOV are three vectors named Wgt, Wgt_A, and Wgt_B. These are the weights for the assets in three separate portfolios. These are in lines 84, row 87, and row 88.
You must compute the mean (expected return) and standard deviation for each of these three portfolios in the designated spots using matrix functions. (Use CAPM for returns)
d. First, calculate the return and risk of the equal weighted portfolio.
2. Below the equal weighted Portfolio you will see Portfolio A and Portfolio B. Compute the return and risk of each of these portfolios. Of course you will get same answer as the equal weighted portfolio since they all have the same weights right now. Next, set up and run Solver twice; once for portfolio A, with the objective of maximizing return, and once again for portfolio B, minimizing the Standard deviation. Your changing cells are the vector of weights (Wgt_A and Wgt_B respectively) and you need a constraint that the weights add to 100%.
Add a constraints to limit the weights as indicated in row 86
3. Calculate the covariance and correlation between Portfolio A and B.
4. Calculate the Return and standard deviation of a Combined Portfolio made up of Wa of Portfolio A and (1-Wa%) of portfolio B.
5. Create a Data Table of Risk and Return on that complete portfolio altering the weight in portfolio A. Go from -100% to 250% by 10% increments.
7. Make a table (NOT a Data Table) of other points you should have on your chart.
8. Make a Chart that plots all of the results.
a. Begin with making a chart of the efficient frontier.
b. Next add all of the individual assets.
c. Next add the two efficiently optimized portfolios (A and B)
d. Next, add the Market Index.
e. Finally, add the optimal portfolio (from step 4).
9. Use the Combined Portfolio on the efficient frontier for you client. Assume your client has just given you money to invest in this portfolio. The amount should be in cell TotalInvestment.
a. Compute the weight on each stock that will end up in the final portfolio.
b. Look up the stock prices and enter them into the table
c. Compute the Dollar Value to be invested in each stock.
d. Compute the number of shares of each stock that you need to buy to form this portfolio.
e. As you change Wa, you should be able to see this portfolio slide along the efficient frontier.
10. Plot the CAL that goes through the Client Portfolio.
11. Compute the Wa that maximized the Sharpe Ratio.