Optimization
Pick 9 stocks or so and utilize the Solver feature in Excel to find the optimal risky portfolio. That is, what is your portfolio return, standard deviation, AND percentages of your investments in each of the stocks.
Use the spreadsheet attached to assist you. (I have sent this already in an Announcement). Excel for Markowitz Optimization edited by Albert.xlsx
Here are some basic instructions.
1. Get monthly closing prices for the stocks for the past five years.
2. Calculate the monthly returns.
3. Use the monthly returns to find the portfolio return and the portfolio variance and standard deviation.
3A. Note that you have to do two scenarios:
Do the first part without shorting. This is easier. Your constraints must be: All weights must be greater than or equal to 0. The second constraint is the sum of the weights must add to 1. Hence, you must add TWO constraints.
4. Use the Solver to find the minimum variance portfolio. The Solver with change the weights invested in each stock until it finds the minimum variance along with the corresponding minimum portfolio return. You have to save these two numbers as well as the weights of your investments in each stock. (Note in Excel: Copy, Paste Special, Values… for the weights, remember to use the Transpose feature to change from rows to columns).
5. I recommend that you look for the stock that has the highest return AND put all your money in that stock and 0 in the other eight stocks. Then solve for the portfolio return and standard deviation. Copy those and the weights in our far right column.
6. Change the portfolio returns (using values between the lowest value and the highest value, that you already calculated.) and come up with 5 or 6 values of standard deviations and weights and store them. You will use the portfolio standard deviations and returns to create the graph of the efficient frontier. To do this you have to provide values for the portfolio returns and then the Solver will calculate the values for the minimum portfolio standard deviation. I have built in feature to do this but you have to add the constraint in the Solver display. You have to equate the new cell value to the cell value of the portfolio return.
7. To find the optimal risky portfolio, you have to MAXIMIZE the Sharpe Ratio. Hence, change the top cell from standard deviation to the cell with the Sharpe Ratio. Then change to max instead of min.
8. On your graph, you have to put a vertical and horizontal line to identify the optimal portfolio. Also you have to state the optimal weights. You should include this point in your graph and identify it.
Do the second part with shorting. Repeat from 4. Remove the constraints that all weights must be greater than or equal to 0. Keep the constraint that the sum of the weights must add to 1.
Final answers:
1. Without shorting: a graph with the optimal portfolio identified AND weights of the optimal portfolio.
2. With shorting: a graph with the optimal portfolio identified and weights of the optimal portfolio.
3. Compare the results between shorting and no shorting.
4. State the key lessons learned from this exercise.
ScholarMatic: Explanation & Answer
Your ready answer from a verified tutor is just a click away for as little as $14.99
Click Order Now to get 100% Original Answer Customized to your instructions!
