Question

Find five years of monthly returns for two mutual funds, Vanguard’s U.S. Growth Fund and U.S. Value Fund, as well as corresponding returns for the S&P 500 and the Treasury-bill rate. (Use Spreadsheet.xls)

a. Calculate each fund’s excess rate of return over T-bills in each month. (Negative values should be indicated by a minus sign. Leave no cells blank - be certain to enter "0" wherever required. Do not round intermediate calculations. Round your answers to 2 decimal places.)

	Total Monthly Returns				Excess Returns
Month	Vanguard	Value Fund	S&P	T-bills	Vanguard %	Value Fund %	S&P %
Jan-04	1.52	−0.07	1.35	0.07
Feb-04	−0.53	−0.99	−1.32	0.06
Mar-04	−2.69	−2.46	−1.89	0.09
Apr-04	0.22	2.52	1.70	0.08
May-04	2.53	1.39	1.85	0.06
Jun-04	−1.93	−7.46	−3.22	0.08
Jul-04	0.99	−0.78	0.25	0.1
Aug-04	1.52	2.49	1.00	0.11
Sep-04	0.21	1.46	1.29	0.11
Oct-04	5.22	4.66	4.46	0.11
Nov-04	3.24	3.60	3.01	0.15
Dec-04	−2.36	−3.48	−2.24	0.16
Jan-05	3.62	0.79	2.09	0.16
Feb-05	−2.33	−2.86	−1.83	0.16
Mar-05	−2.68	−2.41	−1.87	0.21
Apr-05	4.08	7.13	3.22	0.21
May-05	2.25	0.51	0.15	0.24
Jun-05	3.55	5.28	3.82	0.23
Jul-05	−1.85	−0.85	−0.93	0.24
Aug-05	0.38	1.34	0.80	0.3
Sep-05	−1.88	0.30	−2.36	0.29
Oct-05	2.87	5.04	4.39	0.27
Nov-05	0.93	0.46	−0.19	0.31
Dec-05	3.41	3.30	2.41	0.32
Jan-06	0.00	−1.82	0.57	0.35
Feb-06	0.71	0.34	1.65	0.34
Mar-06	0.53	−0.45	1.26	0.37
Apr-06	−3.00	−5.84	−3.01	0.36
May-06	−0.27	−1.55	0.26	0.43
Jun-06	1.09	−2.36	0.45	0.4
Jul-06	2.70	3.60	2.18	0.4
Aug-06	2.63	2.87	2.70	0.42
Sep-06	2.56	1.92	3.16	0.41
Oct-06	0.75	1.94	1.99	0.41
Nov-06	2.32	0.22	0.77	0.42
Dec-06	1.86	2.18	1.51	0.4
Jan-07	−2.30	−1.69	−1.96	0.44
Feb-07	0.81	0.89	1.16	0.38
Mar-07	3.87	2.92	4.42	0.43
Apr-07	3.65	3.37	3.39	0.44
May-07	−2.02	−1.14	−1.46	0.41
Jun-07	−4.74	−0.79	−3.13	0.4
Jul-07	1.12	1.11	1.28	0.4
Aug-07	2.54	4.91	3.88	0.42
Sep-07	0.39	2.19	1.35	0.32
Oct-07	−5.86	−3.90	−3.87	0.32
Nov-07	0.25	0.00	−1.13	0.34
Dec-07	−5.39	−10.34	−6.04	0.27
Jan-08	−2.67	−2.38	−2.58	0.21
Feb-08	−0.89	−0.06	−0.90	0.13
Mar-08	5.64	6.32	4.77	0.17
Apr-08	1.44	3.00	1.51	0.17
May-08	−7.18	−6.25	−8.35	0.17
Jun-08	−1.80	−0.40	−0.90	0.17
Jul-08	1.37	0.45	1.55	0.15
Aug-08	−7.50	−11.63	−9.42	0.12
Sep-08	−18.16	−15.45	−16.52	0.15
Oct-08	−7.16	−7.93	−6.96	0.08
Nov-08	2.70	0.57	0.99	0.02
Dec-08	−10.76	−4.49	−8.22	0.09
	−0.34	−0.34	−0.30	0.25

b. Calculate the standard deviation of each fund over the five-year period. (Do not round intermediate calculations. Round your answers to 2 decimal places.)

	Standard Deviation
Vanguard’s U.S. Growth Fund		%
U.S. Value Fund		%

c. What was the beta of each fund over the five-year period? (Do not round intermediate calculations. Round your answers to 2 decimal places.)

	Beta
Vanguard’s U.S. Growth Fund
U.S. Value Fund

d. What were the Sharpe, Jensen, and Treynor measures for each fund? (Negative values should be indicated by a minus sign. Leave no cells blank - be certain to enter "0" wherever required. Do not round intermediate calculations. Round your answers to 2 decimal places.)

	Vanguard’s U.S. Growth Fund	U.S. Value Fund
Sharpe measure
Jensen measure
Treynor measure

Answer 1

Excess Return = Fund Returns - T-bills

Standard Deviation can be calculated using STDEV.S function in excel

Beta can be calculated using SLOPE function in excel with S&P returns as x and fund returns as y.

Average Returns are average monthly returns for each fund, S&P and T-bills

Sharpe Ratio = (Rp - Rf) / SDp, where Rp - Avg. Fund Returns, Rf - Avg. T-bill returns, SDp - Std. Dev. of portfolio

Jensen measure = [Rp - (Rf + betap x (Rm - Rf)], where Rm - Avg S&P Returns, Betap - Beta of a fund.

Treynor measure = (Rp - Rf) / Betap

	Total monthly returns				Excess Returns
Month	Vanguard	Value Fund	S&P	T-bills	Vanguard	Value Fund	S&P
Jan-04	1.52	-0.07	1.35	0.07	1.45	-0.14	1.28
Feb-04	-0.53	-0.99	-1.32	0.06	-0.60	-1.06	-1.39
Mar-04	-2.69	-2.46	-1.89	0.09	-2.76	-2.53	-1.96
Apr-04	0.22	2.52	1.70	0.08	0.15	2.45	1.63
May-04	2.53	1.39	1.85	0.06	2.46	1.32	1.78
Jun-04	-1.93	-7.46	-3.22	0.08	-2.00	-7.53	-3.29
Jul-04	0.99	-0.78	0.25	0.1	0.92	-0.85	0.18
Aug-04	1.52	2.49	1.00	0.11	1.45	2.42	0.93
Sep-04	0.21	1.46	1.29	0.11	0.14	1.39	1.22
Oct-04	5.22	4.66	4.46	0.11	5.15	4.59	4.39
Nov-04	3.24	3.60	3.01	0.15	3.17	3.53	2.94
Dec-04	-2.36	-3.48	-2.24	0.16	-2.43	-3.55	-2.31
Jan-05	3.62	0.79	2.09	0.16	3.55	0.72	2.02
Feb-05	-2.33	-2.86	-1.83	0.16	-2.40	-2.93	-1.90
Mar-05	-2.68	-2.41	-1.87	0.21	-2.75	-2.48	-1.94
Apr-05	4.08	7.13	3.22	0.21	4.01	7.06	3.15
May-05	2.25	0.51	0.15	0.24	2.18	0.44	0.08
Jun-05	3.55	5.28	3.82	0.23	3.48	5.21	3.75
Jul-05	-1.85	-0.85	-0.93	0.24	-1.92	-0.92	-1.00
Aug-05	0.38	1.34	0.80	0.3	0.31	1.27	0.73
Sep-05	-1.88	0.30	-2.36	0.29	-1.95	0.23	-2.43
Oct-05	2.87	5.04	4.39	0.27	2.80	4.97	4.32
Nov-05	0.93	0.46	-0.19	0.31	0.86	0.39	-0.26
Dec-05	3.41	3.30	2.41	0.32	3.34	3.23	2.34
Jan-06	0.00	-1.82	0.57	0.35	-0.07	-1.89	0.50
Feb-06	0.71	0.34	1.65	0.34	0.64	0.27	1.58
Mar-06	0.53	-0.45	1.26	0.37	0.46	-0.52	1.19
Apr-06	-3.00	-5.84	-3.01	0.36	-3.07	-5.91	-3.08
May-06	-0.27	-1.55	0.26	0.43	-0.34	-1.62	0.19
Jun-06	1.09	-2.36	0.45	0.4	1.02	-2.43	0.38
Jul-06	2.70	3.60	2.18	0.4	2.63	3.53	2.11
Aug-06	2.63	2.87	2.70	0.42	2.56	2.80	2.63
Sep-06	2.56	1.92	3.16	0.41	2.49	1.85	3.09
Oct-06	0.75	1.94	1.99	0.41	0.68	1.87	1.92
Nov-06	2.32	0.22	0.77	0.42	2.25	0.15	0.70
Dec-06	1.86	2.18	1.51	0.4	1.79	2.11	1.44
Jan-07	-2.30	-1.69	-1.96	0.44	-2.37	-1.76	-2.03
Feb-07	0.81	0.89	1.16	0.38	0.74	0.82	1.09
Mar-07	3.87	2.92	4.42	0.43	3.80	2.85	4.35
Apr-07	3.65	3.37	3.39	0.44	3.58	3.30	3.32
May-07	-2.02	-1.14	-1.46	0.41	-2.09	-1.21	-1.53
Jun-07	-4.74	-0.79	-3.13	0.4	-4.81	-0.86	-3.20
Jul-07	1.12	1.11	1.28	0.4	1.05	1.04	1.21
Aug-07	2.54	4.91	3.88	0.42	2.47	4.84	3.81
Sep-07	0.39	2.19	1.35	0.32	0.32	2.12	1.28
Oct-07	-5.86	-3.90	-3.87	0.32	-5.93	-3.97	-3.94
Nov-07	0.25	0.00	-1.13	0.34	0.18	-0.07	-1.20
Dec-07	-5.39	-10.34	-6.04	0.27	-5.46	-10.41	-6.11
Jan-08	-2.67	-2.38	-2.58	0.21	-2.74	-2.45	-2.65
Feb-08	-0.89	-0.06	-0.90	0.13	-0.96	-0.13	-0.97
Mar-08	5.64	6.32	4.77	0.17	5.57	6.25	4.70
Apr-08	1.44	3.00	1.51	0.17	1.37	2.93	1.44
May-08	-7.18	-6.25	-8.35	0.17	-7.25	-6.32	-8.42
Jun-08	-1.80	-0.40	-0.90	0.17	-1.87	-0.47	-0.97
Jul-08	1.37	0.45	1.55	0.15	1.30	0.38	1.48
Aug-08	-7.50	-11.63	-9.42	0.12	-7.57	-11.70	-9.49
Sep-08	-18.16	-15.45	-16.52	0.15	-18.23	-15.52	-16.59
Oct-08	-7.16	-7.93	-6.96	0.08	-7.23	-8.00	-7.03
Nov-08	2.70	0.57	0.99	0.02	2.63	0.50	0.92
Dec-08	-10.76	-4.49	-8.22	0.09	-10.83	-4.56	-8.29
Std. Dev.	4.08	4.24
Beta	1.028	1.022
Avg. Returns	-0.34	-0.34	-0.30	0.25
Sharpe	-0.145	-0.140
Jensen	-0.030	-0.037
Treynor	-0.575	-0.582