Instructions: You should use the Excel template to do the computations; put the graphs in the space provided. You may work on this assignment either individually or up to 5 people. If you work in a group, please make a group on Canvas (directions are on there). Put your name(s) in the space provided. Hand the assignment in by uploading via the Assignment on Canvas (if you work as a team, only 1 person needs to upload the solution for the team). The assignment is to be uploaded to Canvas by 11:59PM on February 7.

1.Choose 3 domestic indexes from the Data tab on the template. Compute the following for each index and the 3-month US TBill Index from 2001-2020:

a.mean, wealth index, annualized return (4 each)

b.variance, standard deviation, Sharpe ratio, semi-deviation (4 each)

c.correlation matrix (also not for the 3-mo Bill - correlation of anything with a constant = 0) (12)

d.Graph the Growth of $1 for all four indices from 2001-2020. Label axes, legend, last point (ending wealth per $1). You must include proper labelling for full credit. (8)

2.Choose 2 of your 3 indexes. Enter the expected return, standard deviation, Sharpe, correlation, and covariance for them in the space provided. Compute the portfolio expected return, standard deviation, and reward-to-risk ratio for the listed weights on the template. (14)

a.Graph the efficient frontier of the combinations from Part 2. Label axes and data points. You must include proper labelling for full credit. (8)

3.For a portfolio containing your two indexes, calculate:

a.Weights, expected return, and standard deviation for the Minimum Variance Portfolio (6)

b.Weights, expected return, standard deviation, and Reward-to-Risk ratio for the Optimal Risky Portfolio (6) (NOTE: You may get a negative weight on one of the indexes. This is not realistic, but OK for our purposes).

c.Standard deviation for a portfolio with a target expected return equal to the expected return of the 50/50 weighted portfolio (assuming the risk-free asset is not available) (6)

d.Weights and standard deviation for a Complete Portfolio with the same target expected return as in the Part 3c 50/50 portfolio (assume the risk-free asset is available for lending and borrowing) (8)

You can either use a formula to get the weights for 3a, b, and d, or use the Excel Add-in Solver to do optimization.

Bloomberg Barclays US Inv Grade Corp 10.31% 10.12% 8.24% 5.39% 1.68% 4.30% 4.56% -4.94% 18.68% 9.00% 8.15% 9.82% -1.53% 7.46% -0.68% 6.11% 6.42% -2.51% 14.54% 9.89%

Bloomberg Barclays Capital US Aggregate 8.44% 10.25% 4.10% 4.34% 2.43% 4.33% 6.97% 5.24% 5.93% 6.54% 7.84% 4.21% -2.02% 5.97% 0.55% 2.65% 3.54% 0.01% 8.72% 7.51%

You may work in groups of up to no more than 5 people on the assignment. Your Data - Copy Paste Special Values here: Wealth Index (including T-bill) Semi-Deviation (NOT including T-bill EX1 S20 T NAME: Index Name => S&P 500 Bloomberg Barclays Capital US Aggregate Russell 2000 3mo Tbill S&P 500 Bloomberg Barclays Capital US Aggregate Russell 2000 3mo Tbill S&P 500 Bloomberg Barclays Capital US Aggregate Russell 2000 12/31/2001 -11.9% 8.4% 2.5% 4.5% 12/31/2002 -22.1% 10.3% -20.5% 1.8% 12/31/2003 28.7% 4.1% 47.3% 1.1% 12/31/2004 10.9% 4.3% 18.3% 1.3% 12/31/2005 4.9% 2.4% 4.6% 3.1% S&P 500 Bloomberg Barclays Capital US Aggregate Russell 2000 3TB 12/31/2006 15.8% 4.3% 18.4% 4.9% Possible Earned 1a. Exp Return 12/31/2007 5.5% 7.0% -1.6% 5.1% 1a. Exp Return 4 Wealth Index 12/31/2008 -37.0% 5.2% -33.8% 2.2% Wealth Index 4 Annualized Return 12/31/2009 26.5% 5.9% 27.2% 0.2% Annualized Return 4 1b. Variance 0.0000 (Assumed for T-Bill) 12/31/2010 15.1% 6.5% 26.9% 0.2% 1b. Variance 4 St Dev 0.0% 12/31/2011 2.1% 7.8% -4.2% 0.1% St Dev 4 Sharpe (Reward-to-Risk) Ratio 0.00 12/31/2012 16.0% 4.2% 16.3% 0.1% Sharpe (Reward-to-Risk) Ratio 4 Semi-Dev 0.0% 12/31/2013 32.4% -2.0% 38.8% 0.1% Semi-Dev 8 12/31/2014 13.7% 6.0% 4.9% 0.1% 1c. Corr Matrix 12 1c. Correlation Matrix 12/31/2015 1.4% 0.5% -4.4% 0.1% 1d. Growth of $1 Graph 8 S&P 500 Bloomberg Barclays Capital US Aggregate Russell 2000 12/31/2016 12.0% 2.7% 21.3% 0.4% 2 . Portf Wtd Std, E(r ) 14 S&P 500 12/31/2017 21.8% 3.5% 14.6% 0.9% 2a. Eff Frontier Graph 8 Bloomberg Barclays Capital US Aggregate 12/31/2018 -4.4% 0.0% -11.0% 1.9% 3a. MVP 6 Russell 2000 12/31/2019 31.5% 8.7% 25.5% 2.3% 3b. ORP 6 12/31/2020 18.4% 7.5% 20.0% 0.7% 3c. Target Portf [no TB] 6 Selected Indexes 1a. Exp Return 3d. Target Complete Portf 8 Index Names E( R) St Dev Var Correlation Covariance Wealth Index 100 0 Annualized Return 1b. Variance 0.0000 (Assumed for T-Bill) Tbill (Assume St Dev = 0% for TB) St Dev 0.0% Sharpe (Reward-to-Risk) Ratio 0.00 Semi-Dev 0.0% 0% 0% 2. Efficient Frontier Weight Weight St Dev Exp Return Rew-to-Risk Ratio 2a. Eff Frontier Graph 1d. Growth of $1 Graph 100% 0% 80% 20% 60% 40% 50% 50% 40% 60% 20% 80% 10% 90% 0% 100% 3a. Minimum Variance Reward-to- 0.00E+00 0.00E+00 Total E( R) St Dev Risk Ratio MV Portfolio 3b. Optimal Risky Portfolio (NOTE: You may get a negative weight - it's not completely realistic, but don't worry about it) Reward-to- 0.00E+00 0.00E+00 Total E( R) St Dev Risk Ratio OR Portfolio 3c. Risk-Free Asset is not Available Target Expected Return (from 50/50 Portfolio) = 0.00% Reward-to- 0.00E+00 0.00E+00 Total E( R) St Dev Risk Ratio Portfolio 50% 50% 3d. Risk-Free Asset is Available Borrowing at TB rate is OK) Target Expected Return (from 50/50 Portfolio) = 0.00% Reward-to- ORP Wt Tbill Wt Total E( R) St Dev Risk Ratio Portfolio