Use the following data to determine an optimal asset allocation with short sale constraints using the...

 

Transcribed Image Text:

Use the following data to determine an optimal asset allocation with short sale constraints using the Excel solver. The risk free rate is 1.7%. Estimate the two asset allocations that: . • Maximizes the Sharpe Ratio Minimizes the variance. . Large Cap Bonds Table 1: Variance Covariance Matrix Large Cap Bonds Emerging Markets Europe .01 .05 -.04 .10 .01 Emerging Markets .03 Europe .05 .30 .06 -.04 Large Cap Bonds .03 .06 Table 2: Expected Returns and Standard Deviations E(r) Stdev 6.0% 8.0% 10.0 % 15.0% Emerging Markets Europe 40 .02 31.6% 54.8% 63.2 % 70.7% .02 .50 2. Choose one of your asset allocations, and find an exchange traded fund (ETF) for each of the four asset classes. Make sure the ETF has data starting on January 1, 2019. 3. Generate daily return streams for each of your four ETFs starting on January 1, 2019 and ending on December 31, 2019. Next, generate a daily return stream for your portfolio over the same period (use your weightings). Simulate what would happens to $100 invested in your portfolio on January 1, 2019. Report the total return and annualized standard deviation for your portfolio returns during 2019. Report the minimum value and draw-down of the investment during 2019. 4. Repeat and generate daily return streams for each of your four ETFs starting on January 1, 2020 and ending on December 31, 2020. Next, generate a daily return stream for your portfolio over the same period (use your weightings). Simulate what would happens to $100 invested in your portfolio on January 1, 2020. Report the total return and annualized standard deviation for your portfolio during 2020. Report the minimum value and draw-down of the investment during 2020. 5. Discuss the performance of your portfolio in 2019 versus 2020. Use the following data to determine an optimal asset allocation with short sale constraints using the Excel solver. The risk free rate is 1.7%. Estimate the two asset allocations that: . • Maximizes the Sharpe Ratio Minimizes the variance. . Large Cap Bonds Table 1: Variance Covariance Matrix Large Cap Bonds Emerging Markets Europe .01 .05 -.04 .10 .01 Emerging Markets .03 Europe .05 .30 .06 -.04 Large Cap Bonds .03 .06 Table 2: Expected Returns and Standard Deviations E(r) Stdev 6.0% 8.0% 10.0 % 15.0% Emerging Markets Europe 40 .02 31.6% 54.8% 63.2 % 70.7% .02 .50 2. Choose one of your asset allocations, and find an exchange traded fund (ETF) for each of the four asset classes. Make sure the ETF has data starting on January 1, 2019. 3. Generate daily return streams for each of your four ETFs starting on January 1, 2019 and ending on December 31, 2019. Next, generate a daily return stream for your portfolio over the same period (use your weightings). Simulate what would happens to $100 invested in your portfolio on January 1, 2019. Report the total return and annualized standard deviation for your portfolio returns during 2019. Report the minimum value and draw-down of the investment during 2019. 4. Repeat and generate daily return streams for each of your four ETFs starting on January 1, 2020 and ending on December 31, 2020. Next, generate a daily return stream for your portfolio over the same period (use your weightings). Simulate what would happens to $100 invested in your portfolio on January 1, 2020. Report the total return and annualized standard deviation for your portfolio during 2020. Report the minimum value and draw-down of the investment during 2020. 5. Discuss the performance of your portfolio in 2019 versus 2020.

Answer rating: 100% (QA)

To determine the optimal asset allocation using the Excel solver we need to maximize the Sharpe Ratio and minimize the variance Well start by construc... View the full answer