Question

In: Finance

Year   Consolidated Edison   Central Valley Community Bancor 2008   -15.9%   -43.6% 2009   23.5%   -10.2% 2010   14.7%   1.5%...

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?

Solutions

Expert Solution

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.


Related Solutions

Consolidated Statements of Income Years ended December 31 ($ millions) 2010 2009 2008 Net sales $26,662...
Consolidated Statements of Income Years ended December 31 ($ millions) 2010 2009 2008 Net sales $26,662 $23,123 $25,269 Operating expenses Cost of sales 13,831 12,109 13,379 Selling, general and administrative expenses 5,479 4,907 5,245 Research, development and related expenses 1,434 1,293 1,404 Loss/(gain) from sale of business -- -- 23 Total operating expenses 20,744 18,309 20,051 Operating income 5,918 4,814 5,218 Interest expenses and income Interest expense 201 219 215 Interest income (38) (37) (105) Total interest expense 163 182...
Year 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 Dividend $         7.46 $         7.91...
Year 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 Dividend $         7.46 $         7.91 $         8.38 $         8.80 $         9.24 $         9.70 $       10.28 $       10.79 $       11.33 $       12.01 Find both the arithmetic growth rate and the geometric growth rate of the dividends for Davy's Crock Pot Stores. Assuming the January​ 1, 2011 price of the stock is $110.00​, determine the current required rate of return for the company​ (use the geometric growth rate to calculate the...
Year Stock A Returns Stock B Returns 2008 -18.00% -14.50% 2009 33.00 21.80 2010 15.00 30.50...
Year Stock A Returns Stock B Returns 2008 -18.00% -14.50% 2009 33.00 21.80 2010 15.00 30.50 2011 -0.50 -7.60 2012 27.00 26.30 Calculate the average rate of return for each stock during the period 2008–2012. Assume that someone held a portfolio consisting of 50 percent Stock A and 50 percent Stock B. What would have been the realized rate of return on the portfolio in each year from 2008 through 2012? What would have been the average return on the...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT