Question

Go to www.mhhe.com/bkm and link to the material for Chapter 18, where you will 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.

a. Set up a spreadsheet to calculate each fund’s excess rate of return over T-bills in each month.

b. Calculate the standard deviation of each fund over the five-year period.

c. What was the beta of each fund over the five-year period? (You may wish to review the spreadsheets from Chapters 5 and 6 on the Index model.)

d. What were the Sharpe, Jensen, and Treynor measures for each fund?

Short answer

a. As below

b. 4.21% - US Growth fund; 4.05% - US Value Fund

c. 1.02 - US Growth fund; 1.03 - US Value Fund

d. As below

Step-by-step solution

Calculation of Excess rate of return ‘a’

Month	Total monthly returns				Excess returns
	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.59	-1.05	-1.38
Mar-04	-2.69	-2.46	-1.89	0.09	-2.78	-1.55	-1.98
Apr-04	0.22	2.52	1.7	0.08	0.14	2.44	1.62
May-04	2.53	1.39	1.85	0.06	2.47	1.33	1.79
Jun-04	-1.93	-7.46	-3.22	0.1	-2.01	-7.54	-3.3
Jul-04	0.99	-0.78	0.25	0.11	0.89	-0.88	0.15
Aug-04	1.52	2.49	1	0.11	1.41	2.38	0.89
Sep-04	0.21	1.46	1.29	0.11	0.1	1.35	1.18
Oct-04	5.22	4.66	4.46	0.15	5.11	4.55	4.35
Nov-04	3.24	3.6	3.01	0.16	3.09	3.45	2.86
Dec-04	-2.36	-3.48	-2.24	0.16	-2.52	-3.64	-2.4
Jan-05	3.62	0.79	2.09	0.16	3.46	0.63	1.93
Feb-05	-2.33	-2.86	-1.83	0.21	-2.49	3.02	-1.99
Mar-05	-268	-2.41	-1.87	0.21	-2.89	-2.62	-2.08
Apr-05	4.08	7.13	3.22	0.24	3.97	6.92	3.01
May-05	2.25	0.51	0.15	0.23	2.01	0.27	-0.09
Jun-05	3.55	5.28	3.82	0.24	3.32	5.05	3.59
Jul-05	-1.85	-0.85	0.93	0.3	-2.09	-1.09	-1.17
Aug-05	0.38	1.34	0.8	0.29	0.08	1.04	0.5
Sep-05	-1.88	0.3	-1.36	0.27	-2.17	0.01	-2.65
Oct-05	2.87	5.04	4.39	0.31	2.6	4.77	4.12
Nov-05	0.93	0.46	-0.19	0.32	0.62	0.15	-0.5
Dec-05	3.41	3.3	2.41	0.35	3.09	2.98	2.09
Jan-06	0	-1.82	0.57	0.34	-0.35	-2.17	0.22
Feb-06	0.71	0.34	1.65	0.37	0.37	0	1.31
Mar-06	0.53	-0.45	1.26	0.36	0.16	-0.82	0.89
Apr-06	-3	-5.84	-3	0.43	-3.36	-6.2	-3.37
May-06	-27	-1.55	0.26	0.4	-0.7	-1.98	-0.17
Jun-06	1.09	-2.36	0.45	0.4	-0.69	-2.76	0.05
Jul-06	2.7	3.6	2.18	0.42	2.2	3.3	1.78
Aug-06	2.63	2.87	2.7	0.41	2.21	2.45	2.28
Sep-06	2.56	1.92	3.16	0.41	2.15	1.51	2.75
Oct-06	0.75	1.94	1.99	0.42	0.34	1.53	1.58
Nov-06	2.32	0.22	0.77	0.4	1.9	-0.2	0.35
Dec-06	1.86	2.18	1.51	0.44	1.46	1.78	1.11
Jan-07	-2.3	-1.69	-1.96	0.38	-2.74	-2.13	-2.4
Feb-07	0.81	0.89	1.16	0.43	0.43	0.51	0.78
Mar-07	3.87	2.92	4.42	0.43	3.44	2.49	3.99
Apr-07	3.85	3.37	3.39	0.44	3.21	2.93	2.95
May-07	-2.02	-1.14	-1.46	0.41	-2.43	-1.55	-1.87
Jun-07	-4.74	0.79	-3.13	0.4	-5.14	-1.19	-3.53
Jul-07	1.12	1.11	1.28	0.4	0.72	0.71	0.88
Aug-07	2.54	4.91	3.88	0.42	2.12	4.49	3.46
Sep-07	0.39	2.19	1.35	0.32	0.07	1.87	1.03
Oct-07	5.86	-3.9	-3.87	0.32	-6.18	-4.22	-4.19
Nov-07	0.25	0	-1.13	0.34	-0.09	-0.34	-1.47
Dec-07	-5.39	-10.34	-6.04	0.27	-5.66	-10.61	-6.31
Jan-08	-2.67	-2.38	-2.58	0.21	-2.88	-2.59	-2.79
Feb-08	-0.89	-0.06	-0.9	0.13	-1.02	-0.19	-1.03
Mar-08	5.64	6.32	4.77	0.17	5.47	6.15	4.6
Apr-08	1.44	3	1.51	0.17	1.27	2.83	1.34
May-08	-7.18	-6.25	-8.35	0.17	-7.35	-6.42	-8.52
Jun-08	-1.8	0.4	-0.9	0.17	-1.97	-0.57	-1.07
Jul-08	1.37	-0.45	1.55	0.15	1.22	0.3	0.4
Aug-08	-7.5	-11.63	-9.42	0.12	-7.62	-11.75	-9.54
Sep-08	-18.16	-15.45	-16.52	0.15	-18.31	-15.6	-16.67
Oct-08	-7.16	-7.93	-6.96	0.08	-7.24	-8.01	-7.04
Nov-08	2.7	0.57	0.99	0.02	2.68	0.55	0.97
Dec-08	-10.76	-4.49	-8.22	0.09	-10.85	-4.58	-8.31
				Average	-0.59	-0.6	-0.55
				SD	4.05	4.21	3.81
				Beta	1.03	1.02	1
				Sharpe ratio	-0.15	-0.14	-0.14
				Alpha	-0.03	-0.04	0
				Treynor	-0.57	-0.58	-0.55

Calculation of Excess rate of return ‘b’

From the excel data,

4.21% - US Growth fund

4.05% - US Value Fund

Calculation of beta ‘c’

From the excel data,

1.02 - US Growth fund

1.03 - US Value Fund

Calculation of Sharpe, Jensen and Treynor measure ‘d’

Formula for Sharpe: E(r_P – r_f) / P

Formula for Treynor: E(r_P – r_f) /βP

Formula for Jenson α_P= E(r_P) -[r_f + β_P[E₍r_{M) -}r_f]

Summary Output Vaguard
Regressive Statistics
Multiple R	0.97
R Square	0.94
Adjusted R Square	0.94
Standard Error	1.02
Observations	60

	df	SS	MS	F
Regression	1	907.97	907.97	876.67
Residual	58	60.07	1.04
Total	59	968.04

	Coefficients	Standard Error	t Stat	P Value
Intercept	0.03	0.13	0.22	0.83
S&P	1.03	0.03	29.61	0

Summary Output Value Fund
Regressive Statistics
Multiple R	0.93
R Square	0.86
Adjusted R Square	0.85
Standard Error	1.61
Observations	60

	df	SS	MS	F
Regression	1	896.12	896.12	343.89
Residual	58	151.14	2.61
Total	59	1047.26

	Coefficients	Standard Error	t Stat	P Value
Intercept	0.04	0.21	0.17	0.86
S&P	1.02	0.06	18.54	0