Question

Realized Return for the S&P 500, Microsoft, and Treasury Bills, 2002-2014 S&P 500 Index Dividends Paid S&P 500 Realized Return Microsoft Realized Return 1-Month T-Bill Return Year End 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 1148.08 879.82 1111.92 1211.92 1248.29 418.30 1468.36 903.25 1115.10 1257.64 1257.60 1426.19 1848.36 2058.90 20.80 20.98 23.15 -22.1% 28.7% 109% -22 0% 6.8% 89% 15.8% 5.5% -37.0% 26 5% 158% 20.8% -44.4% 60 5% -65% -4.5% 4.8% 47% 27.86 27.19 25.44 26.59 32.67 39.75 42.47 0.0% 16.0% 32.4% 13 7% 27 5% 0 0% Total dividends paid by the 500 stocks in the portfolio, based on the number of shares of each stock in the index, adjusted until the end of the year, assuming they were reinvested when paid Using the data in the table EE a. What was the average dividend yield for the S&P 500 from 2002-2014? b. What was the volatility of the dividend yield? c. What was the average annual return of the S&P 500 from 2002-2014 excluding dividends (i.e., from capital gains only)? d. What was the volatility of the S&P 500 returns from capital gains? e. Were dividends or capital gains a more important component of the S&P 500's average returns during this period? Which were the more important source of volatility? a. What was the average dividend yield for the S&P 500 from 2002-2014? The average dividend yield is D96 (Round to two dec mal places) b. What was the volatility of the dividend yield? The volatility of the dividend yield is the square root of the variance of dividend yields c. What was the average annual return of the S&P 500 from 2002-2014 excluding dividends (i.e., from capital gains only)? Round to two decimal places.) annual retun is% (Round to two decimal places) d. What was the volatility of the S&P 500 returns from capital gains? Annual volatility of returns is the square root of the variance of returns e. Were dividends or capital gains a more important component of the S&P 500's average returns during this period? Which were the more important source of volatility? (Select from the drop-down menu.) Round to two decimal places.) V were more important for returns and volatility

Answer 1

The following answer deals with Parts a through d in the question:

The following Data Can be calculated using the Microsoft Excel or calculator;

Year End	Index	Dividends paid	S&P 500 Realized returns	Capital Gains Without Dividends	Dividend Yield
2001	1148.08
2002	879.82	14.53	-22.10%	-23.37%	1.65%
2003	1111.92	20.8	28.74%	26.38%	1.87%
2004	1211.92	20.98	10.88%	8.99%	1.73%
2005	1248.29	23.15	4.91%	3.00%	1.85%
2006	1418.3	27.16	15.80%	13.62%	1.91%
2007	1468.36	27.86	5.49%	3.53%	1.90%
2008	903.25	21.85	-37.00%	-38.49%	2.42%
2009	1115.1	27.19	26.46%	23.45%	2.44%
2010	1257.64	25.44	15.06%	12.78%	2.02%
2011	1257.6	26.59	2.11%	0.00%	2.11%
2012	1426.19	32.67	16.00%	13.41%	2.29%
2013	1848.36	39.75	32.39%	29.60%	2.15%
2014	2058.9	42.47	13.69%	11.39%	2.06%
Average				6.48%	2.03%
Standard Deviation				19.07%	0.25%

Answer 1. Dividend Yield = Dividend Paid/Current Stock Price

Average Dividend yield from Year 2002-2014 = 2.03%

Answer 2. The Volatility of the Dividend Yield is given by the standard deviation of the Dividend Yield from years 2002 to 2014.

Here we use the sample standard deviation where the denominator is taken as Square root of (n-1) instead of Square root of n in Standard Deviation formula.

Volatility for Dividend Yield = 0.25%

Answer 3. The average annual return on S&P Index is calculated using the following Capital returns formula:

Capital Gain % = (Current Stock Price - Previous Period Stock price)/Previous Period Stock Price

From the table above the Average Capital gain is calculated as = 6.48%

Answer 4. Volatility of S&P 500 returns considering only capital gains:

Here we use the sample standard deviation where the denominator is taken as Square root of (n-1) instead of Square root of n in Standard Deviation formula.

From the table above, Volatility for S&P 500 Capital Gains = 19.07%

Note: 1. The final part E should be answerable based on comparison of volatility of Dividend Yield versus S&P 500 Returns from Capital gains only.

2. For calculating Average in Excel, use formula AVERAGE(<Range>) where <Range> is the array of cells passed as arguments.

3. For calculating Standard Deviation in Excel, use formula STDDEV.S(<Range>) , where <Range> is the array of cells passed as arguments.