Portfolio Theory Excel Project
1 w N 4 5 6 7 8 9 10 11 12 13 14 15 16 Bond Fund 16.75% 9.79% 2.82% 3.66% 1.99% 3.61% 15.98% 1.29% -0.78% 0.67% -2.99% 8.20% 32.81% 3.20% 13.73% 25.71% 24.28% -4.96% 8.22% 17.69% 6.24% 15.00% 9.36% 14.21% -8.04% 23.48% 1.43% 9.94% 14.92% 17 Year 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 18 Stock fund 3.56% 14.22% 18.76% -14.31% -25.90% 37.00% 23.83% -6.98% 6.51% 18.52% 31.74% -4.70% 20.42% 22.34% 6.15% 31.24% 18.49% 5.81% 16.54% 31.48% -3.06% 30.23% 7.49% 9.97% 1.33% 37.20% 22.68% 33.10% 28.34% 20.89% -9.03% -11.85% -21.97% 28.36% 10.74% 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 16.66% 5.57% 15.12% 0.38% 4.49% 35 36 36 37 38 39 40 41 42 43 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 10.74% 4.83% 15.61% 5.48% -36.55% 25.94% 14.82% 2.10% 15.89% 32.15% 13.52% 1.38% 11.74% 21.64% -4.23% 28.88% 4.49% 2.87% 1.96% 10.21% 20.10% -11.12% 8.46% 16.04% 2.97% -9.10% 10.75% 1.28% 0.69% 2.80% -0.02% 14.54% 44 45 46 47 48 IT 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 Historical Return Data Questions Calculate A B C D E F G H J K L M N O P Q R S T U V W 1 1. Compute arithmetic average returns, standard deviations and correlation coefficient for stock and bond funds. You can use historical mean return and standard deviation for expected return and risks when solving part 2-5. (10 points) 2 3 4 5 6 Stock fund Bond Fund Mean(AVERAGE) = STD(STEDEV)= Correlation_Coef(CORREL)= Rf= 0.032 7 8 9 10 2. Suppose your portfolio must yield an expected return of 10% and you can only invest in T-bill and stock fund, what is the proportion invested in the stock fund? What is the sharp ratio of your portfolio? (15 points) 11 12 13 14 W_rf= w_stock= Ret_p= 15 16 STD_p= Sharpe= 17 18 19 3. If you can only invest in T-bill and bond fund and your portfolio still needs to generate an expected return of 10%. What is the proportion invested in the bond fund? What is the sharp ratio of your portfolio? (15 points) 20 21 22 23 24 W_rf= W bond= Ret_p= STD_pr Sharpe= 25 26 27 28 4. If you can only use stock fund and bond fund to build a portfolio that generates an expected return of 10%. What is the proportion invested in the bond fund? What is the sharp ratio of your portfolio? Use the template given below to tabulate and draw the investment opportunity set of the two risky funds. Use investment proportions for the stock fund of 0% to 100% in increments of 10%. Use formular or solver function to find the weights for stock and bond funds that generate the minimum-variance portfolio. Compute the expected return and standard deviation of the minimum-variance portfolio (30 points) 10% Return potfolio STD_pr Ret_p= Minimum STD portfolio 29 30 31 32 33 34 Ret_p- 1.2 1 1 W_stock W_bond Ret_p= STD_pr Sharpe_p= W_stock W_bond- Ret p= STD_pr 35 36 37 38 39 w_stock=W_bonds 1 0 0.9 0.1 0.8 0.2 0.7 0.3 0.6 0.4 0.5 0.5 0.4 0.6 0.3 0.7 0.2 0.8 0.1 0.9 0 1 1 0.8 0.6 40 0.4 41 0.2 42 43 0 44 0.2 0.4 0.6 0.8 1 1.2 45 46 47 48 5. If you can use both funds and T-bill to construct a portfolio that generates an espected return of 10%. What is the proportion invested in the stock and bond funds within the optimal risky portfolio? What is the standard deviation of your complete portfolio? What is the sharp ratio of your complete portfolio? What is the proportion invested in the T-bill fund and each of the two risky funds? (30 points) Horizontal (Value) Axis Major Gridlines W_stock W_bond 49 50 51 52 53 54 55 56 57 58 59 60 61 62 W_rf= W_stock w bonda W_rfa W_Optimal_Portfolio Ret_c= STD_C= Sharpe_C Ret p= STD_p= Sharpe_ps Historical Return Data Questions 1 w N 4 5 6 7 8 9 10 11 12 13 14 15 16 Bond Fund 16.75% 9.79% 2.82% 3.66% 1.99% 3.61% 15.98% 1.29% -0.78% 0.67% -2.99% 8.20% 32.81% 3.20% 13.73% 25.71% 24.28% -4.96% 8.22% 17.69% 6.24% 15.00% 9.36% 14.21% -8.04% 23.48% 1.43% 9.94% 14.92% 17 Year 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 18 Stock fund 3.56% 14.22% 18.76% -14.31% -25.90% 37.00% 23.83% -6.98% 6.51% 18.52% 31.74% -4.70% 20.42% 22.34% 6.15% 31.24% 18.49% 5.81% 16.54% 31.48% -3.06% 30.23% 7.49% 9.97% 1.33% 37.20% 22.68% 33.10% 28.34% 20.89% -9.03% -11.85% -21.97% 28.36% 10.74% 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 16.66% 5.57% 15.12% 0.38% 4.49% 35 36 36 37 38 39 40 41 42 43 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 10.74% 4.83% 15.61% 5.48% -36.55% 25.94% 14.82% 2.10% 15.89% 32.15% 13.52% 1.38% 11.74% 21.64% -4.23% 28.88% 4.49% 2.87% 1.96% 10.21% 20.10% -11.12% 8.46% 16.04% 2.97% -9.10% 10.75% 1.28% 0.69% 2.80% -0.02% 14.54% 44 45 46 47 48 IT 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 Historical Return Data Questions Calculate A B C D E F G H J K L M N O P Q R S T U V W 1 1. Compute arithmetic average returns, standard deviations and correlation coefficient for stock and bond funds. You can use historical mean return and standard deviation for expected return and risks when solving part 2-5. (10 points) 2 3 4 5 6 Stock fund Bond Fund Mean(AVERAGE) = STD(STEDEV)= Correlation_Coef(CORREL)= Rf= 0.032 7 8 9 10 2. Suppose your portfolio must yield an expected return of 10% and you can only invest in T-bill and stock fund, what is the proportion invested in the stock fund? What is the sharp ratio of your portfolio? (15 points) 11 12 13 14 W_rf= w_stock= Ret_p= 15 16 STD_p= Sharpe= 17 18 19 3. If you can only invest in T-bill and bond fund and your portfolio still needs to generate an expected return of 10%. What is the proportion invested in the bond fund? What is the sharp ratio of your portfolio? (15 points) 20 21 22 23 24 W_rf= W bond= Ret_p= STD_pr Sharpe= 25 26 27 28 4. If you can only use stock fund and bond fund to build a portfolio that generates an expected return of 10%. What is the proportion invested in the bond fund? What is the sharp ratio of your portfolio? Use the template given below to tabulate and draw the investment opportunity set of the two risky funds. Use investment proportions for the stock fund of 0% to 100% in increments of 10%. Use formular or solver function to find the weights for stock and bond funds that generate the minimum-variance portfolio. Compute the expected return and standard deviation of the minimum-variance portfolio (30 points) 10% Return potfolio STD_pr Ret_p= Minimum STD portfolio 29 30 31 32 33 34 Ret_p- 1.2 1 1 W_stock W_bond Ret_p= STD_pr Sharpe_p= W_stock W_bond- Ret p= STD_pr 35 36 37 38 39 w_stock=W_bonds 1 0 0.9 0.1 0.8 0.2 0.7 0.3 0.6 0.4 0.5 0.5 0.4 0.6 0.3 0.7 0.2 0.8 0.1 0.9 0 1 1 0.8 0.6 40 0.4 41 0.2 42 43 0 44 0.2 0.4 0.6 0.8 1 1.2 45 46 47 48 5. If you can use both funds and T-bill to construct a portfolio that generates an espected return of 10%. What is the proportion invested in the stock and bond funds within the optimal risky portfolio? What is the standard deviation of your complete portfolio? What is the sharp ratio of your complete portfolio? What is the proportion invested in the T-bill fund and each of the two risky funds? (30 points) Horizontal (Value) Axis Major Gridlines W_stock W_bond 49 50 51 52 53 54 55 56 57 58 59 60 61 62 W_rf= W_stock w bonda W_rfa W_Optimal_Portfolio Ret_c= STD_C= Sharpe_C Ret p= STD_p= Sharpe_ps Historical Return Data Questions
