Excel Project 1 - Portfolio Diversification

Calculate:

1. Arithmetic Average Return for each Portfolio

2. Geometric Average Return for each Portfolio

3. Standard Deviation of Returns for each Portfolio

4. Coefficient of deviation/arithmetic average return for each Portfolio

5. Make comments on effect of portfolio diversification on average returns and riskiness (measured by the standard deviation of returns) of the portfolios.

Compare all Portfolios on the basis of Coefficient of Variation and make a comment on portfolios Coefficient of Variation and the most efficient portfolio.

Excel Project 2 - Portfolio Diversification Calculate: 1. Arithmetic Average Return for each Portfolio. (The arithmetic average of the returns correclty measures the opp 2. Geometric Average Return for each Portfolio. (also known as compound annual return) 3. Standard Deviation of Returns for each Portfolio. 4. Coefficient of Variation=standard deviation/arithmetic average return for each Portfolio. 5. Make comments on effect of portfolio diversification on average returns and riskiness (measured by the standard 6. Make a comment on portfolios Coefficient of Variation. Compare all Portfolios on the basis of Coefficient of Variation and make a comment on portfolios Coefficient of Vari 1 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 100% Bonds 10/90 13.7 11.9 6.6 9 3.7 5.2 3.4 1.1 6.5 3.3 6.7 10.2 8.6 9.6 2.1 4.4 1.6 4.2 6 7.1 6.4 8.3 9.7 9.1 22.5 21.5 7.1 9.1 12.9 12.3 15.4 18 11 12.9 3.1 4.5 5.1 6.8 10.9 11.8 9.5 7.2 14.1 15 6.2 5.7 8.6 10.6 -4.2 -3.6 15.4 15.5 2.1 3.2 5.9 5.8 Portfolos with different weights of Bonds 20/80 30/70 40/60 10.2 8.5 6.8 11.3 13.7 16.1 6.7 8.3 9.8 -1.1 -3.4 -5.6 0.2 -2.9 -5.9 13.8 17.4 21.1 10.6 11.6 12.5 6.7 9 11.4 6.9 9.7 12.5 8.1 9.2 10.2 10.3 12.2 14.2 8.5 7.9 7.2 20.5 19.5 18.5 11.1 13.2 15.3 11.7 11.1 10.5 20.7 23.5 26.3 14.8 16.7 18.6 5.8 7.1 8.4 8.6 10.4 12.3 12.6 13.4 14.3 5 2.8 0.6 15.9 16.8 17.7 5.3 4.8 4.4 12.7 14.7 16.8 -3 -2.4 -1.8 15.5 15.5 15.5 4.3 5.4 6.5 5.7 5.6 5.5 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 7 -1.5 10.6 7.1 11.3 2.4 3 0.8 2 8.2 7.1 1 4.5 6.9 2.4 -4.5 7 0.6 9.3 6.2 9.3 6.4 5 2.1 4.1 7.7 1.2 4.6 6.2 5.3 4 -1.9 6.9 2.8 7.9 5.3 7.3 10.4 7 3.3 6.2 7.2 -4.4 8.2 7.9 3.7 5.6 0.7 6.8 4.9 6.6 4.4 5.2 14.6 9 4.6 8.4 6.7 -9.8 11.9 9.6 2.1 7.2 3.4 6.6 7.1 5.3 3.5 3.2 18.9 11.1 5.9 10.6 6.1 -14.9 15.6 11.2 0.4 8.8 6.2 1. Arithmetic Average Return 7.2 7.85 8.520455 9.211364 9.906818 2. Geometric Average Return 2. Geometric Average Return 3. Standard Deviation of Returns 5.2821317781 4.9719606 5.410548 6.451734 7.844936 4. Coefficion of Variation=standa 0.7336294136 0.6333708 0.635007 0.70041 0.791872 Source: 2014 The Merriman Financial Education Foundation ns correclty measures the opportunity cost of capital for investments of similar risk) ess (measured by the standard deviation of returns) of the portfolios.: diversification reduces variability because prices do not n portfolios Coefficient of Variation and the most efficient portfolio. th different weights of Bonds and Stocks 50/50 60/40 70/30 80/20 90/10 100% Stock S&P 500 5.1 3.4 1.8 0.1 -1.6 -3.2 18.5 21 23.5 26 28.5 31.1 11.4 12.9 14.5 16.1 17.6 19.2 -7.8 -10 -12.1 -14.2 -16.3 -18.4 -8.8 -11.7 -14.5 -17.3 -19.9 -22.6 24.9 28.7 32.6 36.5 40.5 44.5 13.5 14.4 15.3 16.2 17 17.9 13.9 16.4 18.9 21.5 24.2 26.8 15.4 18.3 21.3 24.3 27.4 30.6 11.2 12.3 13.3 14.3 15.3 16.3 16.1 18.1 20 22 23.9 25.8 6.6 6 5.4 4.7 4.1 3.4 17.5 16.5 15.5 14.5 13.5 12.4 17.4 19.5 21.7 23.9 26.1 28.4 9.8 9.2 8.6 7.9 7.2 6.5 29.2 32.1 35.1 38.1 41.2 44.4 20.6 22.6 24.5 26.5 28.6 30.6 9.7 10.9 12 13.1 14.1 15 14.1 16 17.9 19.8 21.8 23.8 15.1 15.9 16.7 17.5 18.3 19.1 -1.6 -3.8 -5.9 -8 -10.1 -12.2 18.6 19.4 20.2 21 21.9 22.6 3.9 3.5 3 2.5 2 1.6 18.9 21 23.2 25.4 27.6 29.8 -1.3 -0.7 -0.1 0.4 1 1.5 15.5 15.5 15.5 15.5 15.5 15.5 7.6 8.8 9.9 11 12.1 13.3 5.4 5.2 5 4.9 4.7 4.4 4 14.3 19 -14.7 -26.5 37.2 23.8 -7.2 6.6 18.4 32.4 -4.9 21.4 22.5 6.3 32.2 18.5 5.2 16.8 31.5 -3.1 30.5 7.6 10.1 1.3 37.6 23 33.4 6.4 9.3 3.9 2.5 1.2 23.3 13.1 7.1 12.9 5.5 -19.9 19.3 12.8 -1.3 10.4 9 6.1 11.5 2.6 1.5 -0.9 27.8 15.2 8.4 15.1 5 -24.6 23.1 14.4 -3 11.9 11.9 5.7 13.8 1.3 0.5 -2.9 32.5 17.4 9.7 17.4 4.4 -29.2 26.8 15.9 -4.7 13.5 14.8 5.4 16.1 0 -0.6 -5 37.3 19.5 10.9 19.7 3.7 -33.6 30.6 17.4 -6.4 15.1 17.9 4.9 18.4 -1.3 -1.7 -7 42.3 21.7 12.2 22.1 3.1 -37.7 34.3 18.8 -8.1 16.7 20.9 4.4 20.8 -2.6 -2.8 -9 47.3 23.9 13.5 24.5 2.5 -41.7 38 20.2 -9.8 18.2 24.1 10.62273 11.35682 12.09545 12.85909 13.62727 11.922727 28.6 21 -9.1 -11.9 -22.1 28.7 10.9 4.9 15.8 5.5 -37 26.5 15.1 2.1 16 32.4 0.1192272727 0.1040171588 Geomen Function-1 9.457182 11.16955 12.95519 14.79586 16.65563 18.54563 17.9812851351 0.890278 0.983511 1.071079 1.150615 1.222228 1.5554855 150.8152012857 ces variability because prices do not always move together Decimal Return Growth Factor 0.04 0.143 0.19 -0.147 -0.265 0.372 0.238 -0.072 0.066 0.184 0.324 -0.049 0.214 0.225 0.063 0.322 0.185 0.052 0.168 0.315 -0.031 0.305 0.076 0.101 0.013 0.376 0.23 0.334 1.04 1.143 1.19 0.853 0.735 1.372 1.238 0.928 1.066 1.184 1.324 0.951 1.214 1.225 1.063 1.322 1.185 1.052 1.168 1.315 0.969 1.305 1.076 1.101 1.013 1.376 1.23 1.334 Investment Value 104 118.872 141.45768 120.66340104 88.6875997644 121.679386877 150.639080953 139.793067125 149.019409555 176.438980913 233.605210729 222.158555403 269.70048626 330.383095668 351.197230695 464.282738979 550.17504569 578.784148066 676.019884941 888.966148697 861.408198088 1124.1376985 1209.57216359 1331.73895211 1349.05155849 1856.29494448 2283.24278171 3045.84587081 0.286 0.21 -0.091 -0.119 -0.221 0.287 0.109 0.049 0.158 0.055 -0.37 0.265 0.151 0.021 0.16 0.324 10.402% "Rate" Function 1.286 1.21 0.909 0.881 0.779 1.287 1.109 1.049 1.158 1.055 0.63 1.265 1.151 1.021 1.16 1.324 3916.95778986 4739.51892573 4308.22270349 3795.54420177 2956.72893318 3805.310137 4220.08894194 4426.87330009 5126.31928151 5408.26684199 3407.20811045 4310.11825972 4960.94611694 5065.1259854 5875.54614306 7779.22309341