Portfolio Theory Excel Project

B D E F H 1 J L M M N. N 0 P Q R S T U V W Y Z AA 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 A 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 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% 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% -8.25% 16.66% 5.57% 15.12% 0.38% 4.49% 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 TO 35 36 36 8 9 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.029 14.54% 4 45 6 47 48 49 50 51 A B D E F H J K L M N o P Q R S T V W 1 1. Compute arithmetic average retums, 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 Stock fund Bond Fund 5 6 Mean(AVERAGE)= STD(STEDEV)= Correlation_Coef(CORREL)= Rf= 7 8 0.032 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 15 w_rf= W stock= Ret_p= STD_p= Sharpe= 16 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 w_rf= w bond 23 24 Ret p- 25 STD_pr Sharpe= 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) 29 30 31 10% Return potfolio STD_p= Ret_p= Minimum STD portfolio W_stock=W_bonda 1 0 Ret_p= 32 0.9 0.1 1.2 0.8 0.2 1 1 W stock= w bond= Ret_pr STD_p= Sharpe_p= W stock=1 w bondo Ret_p= STD_p= 0.3 0.7 0.6 0.4 0.8 0.5 0.5 0.4 0.6 0.6 0.3 0.7 0.4 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 0.2 0.8 0.1 0.9 0.2 0 1 0 0 0.2 0.4 0.6 0.8 1 1.2 48 5. If you can use both funds and T-bill to construct a portfolio that generates an expected 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) 49 50 51 52 W stock= w rf= Wrf= 53 w_bonda W_Optimal_Portfolio= W_stock Ret_p= Ret cu W bonda STD_C= 56 Sharpe_p= Sharpe_c= 57 58 54 55 STD p= 59