PROB SET 10 E + F+ G +H 1. Use the Markowitz Portfolio Optimizer Excel spreadsheet for this problem if you would like. It isnt necessary but helpful, it’s in the Assignments section of Blackboard. You will have to install the Excel “Solver” add-in, by clicking on “File,” then click “Options” and then click on “Add-Ins.” In the list of “Add-Ins,” click on “Solver Add-in,” and then click “Go” at the bottom. Check the box next to “Solver Add-In,” and then click “OK.” After you install the Solver Add-In, click on the “Data” tab from the main home screen in Excel, and you should see “Solver” in the Analysis area to the far top right side of the screen. You’re now ready to go!
You have the following information on monthly international stock returns and risk, all measured in U.S. dollars. Ignore the risk-free rate for this problem. Enter the returns, standard deviations and correlations coefficient values below into the Markowitz Portfolio Optimizer Excel spreadsheet:
Monthly Stock Return (%) Standard Deviation (%)
U.K. 1.26% 3.50%
Canada 1.54% 3.90%
Mexico 2.25% 6.05%
U.S. 1.15% 3.16%
Correlation Coefficients
U.K. Can. Mex. U.S.
U.K. 1.000
Canada 0.755 1.000
Mexico 0.725 0.695 1.000
U.S. 0.682 0.754 0.676 1.000
a. Based on the information above, calculate and report the Sharpe Performance Measure (SHP) for each country (Return/Risk). Be sure to show your calculations for each answer.
b. For an equally weighted portfolio of 25% invested in each country (enter those values manually as the Weights in Column F as whole numbers, i.e. 25), report the % portfolio return R(p), the % portfolio standard deviation Std. Dev(p), the portfolio SHP Sharpe(p), all from Excel (you don’t need Solver for this part). Separately, calculate and report the SHP (compare the portfolio SHP to the US stock market SHP), and the % “annualized ?R for a U.S. investor at the domestic-equivalent, U.S. risk level,” where:
R = SHP x 3.16 (US) x 12 months.
c. Solve for the Optimal International Portfolio (OIP) for a U.S. investor, and report the percentages in invested in each stock market. (With cursor in the cell next to Sharpe(p), click the Data tab, and then click Solver, and then click “Solve” in the menu box and then the weights in Column F “Weights” will be the weights for the OIP.)
d. For the OIP, report the return (%), standard deviation (%), SHP, the SHP, and the annualized ?R for a U.S. investor at the domestic-equivalent, U.S. risk level (R = SHP x 3.16 (US) x 12 months).
e. Calculate, report and compare the % annual return from the US market to the % annual return for the OIP, and calculate and report the difference in those annual returns (%). Report and compare the risk of the U.S. stock market to the risk of the OIP (report the % standard deviations, not annualized). For the % “annualized ?R for a U.S. investor at the domestic-equivalent, U.S. risk level” from part d, explain what that means in a complete essay, and refer specifically to your answers from the first part (the difference in % annual returns and the difference in risk between the U.S. market and the OIP).
f. Suppose that the correlation between the US and Canada goes from 0.755 to 0.822, and the correlation between US and Mexico goes from 0.676 to 0.527. Recalculate the OIP and report the percentages for each country.
g. Explain your answer from part f in an essay, especially explaining how, why, and in what direction the percentages in Canada and Mexico changed (report those percentages in your answer). Discuss how correlation between two markets is related to risk and diversification when combining those stocks in a portfolio.
Set the correlations for Canada and Mexico back to their original values for the next parts.
h. Suppose a U.S. investor wants to keep a minimum of 60% invested in the U.S. Calculate the OIP with that new constraint and report the percentages invested in each country. (Click on Solver, and change the last constraint from “$F$8 >=0” to “$F$8 >= .60” by highlighting that line, and clicking Change. This will impose the constraint that the minimum U.S. investment is 60%). Report the % return, risk (%), and SHP, and calculate and compare to the SHP from the OIP in part c.
i. Repeat the analysis for a minimum of 75% invested in the U.S. Calculate and report the change in SHP from the OIP in part c.
j. Does the “home bias” in parts h and i help or hurt the U.S. investor? How do we know? Explain in a complete and convincing essay or a full paragraph or more, and refer specifically to your numerical results from parts d, h, and i, e.g., report and compare % returns, risk (%) and SHP measures.