In: Finance
Year Consolidated Edison Central Valley
Community Bancor
2008 -15.9% -43.6%
2009 23.5% -10.2%
2010 14.7% 1.5%
2011 31.3% -3.3%
2012 -6.4% 43.1%
2013 4.2% 47.7%
2014 24.9% 0.4%
2015 1.1% 10.6%
2016 18.9% 69.1%
2017 19.5% 2.4%
This Table,contains the annual returns from 2008 to 2017 for two stocks, Consolidated Edison (ED) and Central Valley Community Bancor (CVCY). Use Excel to create a spreadsheet that calculates the average, standard deviation, and correlation coefficient for the two stocks. Just comparing the two stocks in isolation, does one seem more attractive tan the other? Next, calculate the average return and standard deviation for several portfolios, starting with one that invests 100% in ED and 0% in CVCY, then decreasing the percent invested in ED and increasing the investment in CVCY in 10% increments until you reach a portfolio of 0% ED and 100% CVCY (that would be 11 portfolios total). Draw a graph showing the standard deviation (x-axis) and average return (y-axis) for each portfolio. What lesson does this exercise illustrate?
The average, standard deviation, and correlation coefficient of each stock are calculated using AVERAGE, STDEV.S and CORREL functions in Excel respectively.
The formulas are shown below :
ED seems more attractive than CVCY because although their average returns are nearly equal, the standard deviation of ED is much lower. Thus, by investing in ED instead of CVCY, an equal return could be earned with much lower risk.
Average return of two-asset portfolio Rp = w1R1 + w2R2,
where Rp = average return
w1 = weight of Asset 1
R1 = expected return of Asset 1
w2 = weight of Asset 2
R2 = expected return of Asset 2
Standard deviation for a two-asset portfolio σp = (w12σ12 + w22σ22 + 2w1w2Cov1,2)1/2
where σp = standard deviation of the portfolio
w1 = weight of Asset 1
w2 = weight of Asset 2
σ1 = standard deviation of Asset 1
σ22 = standard deviation of Asset 2
Cov1,2 = covariance of returns between Asset 1 and Asset 2
Cov1,2 = ρ1,2 * σ1 * σ2, where ρ1,2 = correlation of returns between Asset 1 and Asset 2
We calculate the average and standard deviation of the portfolios as below :
The formulas are shown below :
The graph is below :
This exercise illustrates that by investing in a portfolio of stocks whose returns have low correlation, the risk of the portfolio will be lower than the risk of the individual stocks.