1. Calculate the sample mean, standard deviation for the following assets: [2 pts] 3-month Treasury bills (T-bills) 10-year Treasury bonds (T-bonds) The stock as aforementioned 2. In practice, we would consider T-bills as a risk-free asset; that is to say, well treat the sample mean of T-bills as the risk-free interest rate. Calculate the risk premium and the Sharpe ratio for the T-bonds and the stock. [1 pts] 3. Calculate the correlation between the T-bonds and the stock. [1 pt] 4. You would hold a portfolio between the T-bonds and the stock. Please tabulate the expected return and the standard deviation for this portfolio given the different proportions of investments in the stock and the T-bonds. [2 pts] 5. Calculate the Sharpe ratio for this portfolio under each scenario (i.e. fill in column F in spreadsheet 2). [2 pts] 6. What is the proportion of your investments in each asset for the optimal risky portfolio (i.e. the portfolio with the highest Sharpe ratio)? And, what is the highest Sharpe ratio? [2 pt] For this question, Id encourage you fine-tune the investment weights in order to find the exact portfolio of the highest Sharpe ratio. Useful Excel Commands: For the sample average: average(Data Range) For the sample variance: var.s(Data Range) For the sample standard deviation: stdev.s(Data Range) Covariance b/w asset 1 and asset 2: Covariance.s(Data Range for asset1, Data Range for asset2). Correlation b/w asset 1 and asset 2: correl(Data Range for asset1, Data Range for asset2).

3 Question 1 T-Bills T-bonds Stock - Mean Std Dev (0) Question 2 T-bonds Stock Risk Premium Sharpe Ratio Question 3 The correlation between T-bonds and stock 4 Year T-Bills T-bonds 5 1990 7.81 6.872 6 1991 5.6 17.9336 7 1992 3.51 9.0548 8 1993 2.9 13.4198 9 1994 3.9 -6.4083 10 1995 5.6 23.9541 11 1996 5.21 0.9946 12 1997 5.26 11.312 13 1998 4.86 13.094 14 1999 4.68 -8.4734 15 2000 5.89 14.4891 16 2001 3.7786 4.0302 17 2002 1.6339 14.6641 18 2003 1.0182 1.2778 19 2004 1.2005 5.1862 20 2005 2.9633 3.103 21 2006 4.7855 2.2713 22 2007 4.6747 9.6431 23 2008 1.4742 17.6664 24 2009 0.0966 -5.8278 25 2010 0.1216 7.4457 26 2011 0.0428 16.6015 27 2012 0.0565 3.5862 28 2013 0.0277 -6.9025 29 2014 0.0164 10.1512 30 2015 0.0092 1.0665 31 2016 0.1886 0.7039 32 Stock Microsoft Deere 72.98843 -21.24674 121.7608 6.514507 15.11233 -4.631782 -5.563729 75.39714 51.62806 -7.939948 43.55822 78.47182 88.31913 18.36869 56.42965 46.25551 114.6036 -42.49447 68.36411 35.15106 -62.84799 8.165293 52.7378 -2.542285 -21.96227 7.107995 6.816497 44.60718 9.128112 16.23089 -0.938235 -6.780882 15.83933 42.43672 20.84288 99.02672 -44.38561 -58.2489 60.46717 45.18986 -6.524608 56.4632 -4.51569 -5.079743 5.798868 14.27506 44.29801 8.190923 27.56459 -0.651352 22.69186 -11.35429 15.07774 39.20808 33 34 H 1 A B D E F G Question 4: Please fill in the following table to calculate the expected return and standard deviation of the portfolio between the stock and the T-bonds. Note: the inputs for the stock and the T-bills (in row 11) come from question 1 and 3. 2 3 4 5 5 6 7 8 9 Question 5: Please fill in the Sharpe ratio in column F. For the risk-free rate, please use the interest rate implied by the T-bill from question 1. The investment opportunity set with the stock and the T-bill. Input Data Ers) Erb) OB PBS 10 11 12 Portfolio Weights Expected Return, E(rp) Std Dev Sharpe Ratio 13 Ws = 1-WB WB Col A*A3 +Col B*B3 Equation 6.6 14 0 1 15 0.05 0.95 16 0.10 0.90 17 0.15 0.85 18 0.20 0.80 19 0.25 0.75 20 0.30 0.70 21 0.35 0.65 22 0.40 0.60 23 0.45 0.55 24 0.50 0.50 25 0.55 0.45 26 0.60 0.40 27 0.65 0.35 28 0.70 0.30 29 0.75 0.25 30 0.80 0.20 31 0.85 0.15 32 0.90 0.10 33 0.95 0.05 34 1 0 35 36 37 Question 6 38 What is the maximal Sharpe Ratio for the portfolio? 39 40 41 What is the proportion in the stock and the T-bill to achieve the max Sharpe ratio? weight 43 Stock 44 T-bonds 45 46 47 42 3 Question 1 T-Bills T-bonds Stock - Mean Std Dev (0) Question 2 T-bonds Stock Risk Premium Sharpe Ratio Question 3 The correlation between T-bonds and stock 4 Year T-Bills T-bonds 5 1990 7.81 6.872 6 1991 5.6 17.9336 7 1992 3.51 9.0548 8 1993 2.9 13.4198 9 1994 3.9 -6.4083 10 1995 5.6 23.9541 11 1996 5.21 0.9946 12 1997 5.26 11.312 13 1998 4.86 13.094 14 1999 4.68 -8.4734 15 2000 5.89 14.4891 16 2001 3.7786 4.0302 17 2002 1.6339 14.6641 18 2003 1.0182 1.2778 19 2004 1.2005 5.1862 20 2005 2.9633 3.103 21 2006 4.7855 2.2713 22 2007 4.6747 9.6431 23 2008 1.4742 17.6664 24 2009 0.0966 -5.8278 25 2010 0.1216 7.4457 26 2011 0.0428 16.6015 27 2012 0.0565 3.5862 28 2013 0.0277 -6.9025 29 2014 0.0164 10.1512 30 2015 0.0092 1.0665 31 2016 0.1886 0.7039 32 Stock Microsoft Deere 72.98843 -21.24674 121.7608 6.514507 15.11233 -4.631782 -5.563729 75.39714 51.62806 -7.939948 43.55822 78.47182 88.31913 18.36869 56.42965 46.25551 114.6036 -42.49447 68.36411 35.15106 -62.84799 8.165293 52.7378 -2.542285 -21.96227 7.107995 6.816497 44.60718 9.128112 16.23089 -0.938235 -6.780882 15.83933 42.43672 20.84288 99.02672 -44.38561 -58.2489 60.46717 45.18986 -6.524608 56.4632 -4.51569 -5.079743 5.798868 14.27506 44.29801 8.190923 27.56459 -0.651352 22.69186 -11.35429 15.07774 39.20808 33 34 H 1 A B D E F G Question 4: Please fill in the following table to calculate the expected return and standard deviation of the portfolio between the stock and the T-bonds. Note: the inputs for the stock and the T-bills (in row 11) come from question 1 and 3. 2 3 4 5 5 6 7 8 9 Question 5: Please fill in the Sharpe ratio in column F. For the risk-free rate, please use the interest rate implied by the T-bill from question 1. The investment opportunity set with the stock and the T-bill. Input Data Ers) Erb) OB PBS 10 11 12 Portfolio Weights Expected Return, E(rp) Std Dev Sharpe Ratio 13 Ws = 1-WB WB Col A*A3 +Col B*B3 Equation 6.6 14 0 1 15 0.05 0.95 16 0.10 0.90 17 0.15 0.85 18 0.20 0.80 19 0.25 0.75 20 0.30 0.70 21 0.35 0.65 22 0.40 0.60 23 0.45 0.55 24 0.50 0.50 25 0.55 0.45 26 0.60 0.40 27 0.65 0.35 28 0.70 0.30 29 0.75 0.25 30 0.80 0.20 31 0.85 0.15 32 0.90 0.10 33 0.95 0.05 34 1 0 35 36 37 Question 6 38 What is the maximal Sharpe Ratio for the portfolio? 39 40 41 What is the proportion in the stock and the T-bill to achieve the max Sharpe ratio? weight 43 Stock 44 T-bonds 45 46 47 42