Chapter 6, Problem 22PS

Summary Introduction

To determine:

To set up the spreadsheet of the given data as per the Example 6.3 and to find the beta of Apple

Introduction:

The net loss or the net gain on any investment over a certain time period which is expressed as the investment's initial cost percentage is the rate of return or ROR.

Answer to Problem 22PS

The beta is 0.87

Explanation of Solution

Given Information:

The data is available in Connect. It shows a data for five year period.

From the Connect, the data is obtained as below:

	S&P		RF (T-bill)		Excess returns
Month	Return	Return	Return	Month		S&P
Jan-04	1.35	0.56	0.07	Jan-04	0.49	1.28
Feb-04	-1.32	0.00	0.06	Feb-04	-0.06	-1.38
Mar-04	-1.89	-3.19	0.09	Mar-04	-3.28	-1.98
Apr-04	1.70	0.00	0.08	Apr-04	-0.08	1.62
May-04	1.85	-0.25	0.06	May-04	-0.31	1.79
Jun-04	-3.22	2.39	0.08	Jun-04	2.31	-3.30
Jul-04	0.25	1.69	0.1	Jul-04	1.59	0.15
Aug-04	1.00	1.51	0.11	Aug-04	1.40	0.89
Sep-04	1.29	0.08	0.11	Sep-04	-0.03	1.18
Oct-04	4.46	-1.17	0.11	Oct-04	-1.28	4.35
Nov-04	3.01	1.97	0.15	Nov-04	1.82	2.86
Dec-04	-2.24	-3.02	0.16	Dec-04	-3.18	-2.40
Jan-05	2.09	-0.24	0.16	Jan-05	-0.40	1.93
Feb-05	-1.83	-15.28	0.16	Feb-05	-15.44	-1.99
Mar-05	-1.87	-7.84	0.21	Mar-05	-8.05	-2.08
Apr-05	3.22	2.25	0.21	Apr-05	2.04	3.01
May-05	0.15	4.91	0.24	May-05	4.67	-0.09
Jun-05	3.82	5.44	0.23	Jun-05	5.21	3.59
Jul-05	-0.93	-8.88	0.24	Jul-05	-9.12	-1.17
Aug-05	0.80	-5.37	0.3	Aug-05	-5.67	0.50
Sep-05	-2.36	-7.77	0.29	Sep-05	-8.06	-2.65
Oct-05	4.39	-7.52	0.27	Oct-05	-7.79	4.12
Nov-05	-0.19	-4.31	0.31	Nov-05	-4.62	-0.50
Dec-05	2.41	12.61	0.32	Dec-05	12.29	2.09
Jan-06	0.57	-3.43	0.35	Jan-06	-3.78	0.22
Feb-06	1.65	6.04	0.34	Feb-06	5.70	1.31
Mar-06	1.26	1.23	0.37	Mar-06	0.86	0.89
Apr-06	-3.01	6.39	0.36	Apr-06	6.03	-3.37
May-06	0.26	5.18	0.43	May-06	4.75	-0.17
Jun-06	0.45	4.63	0.4	Jun-06	4.23	0.05
Jul-06	2.18	-1.41	0.4	Jul-06	-1.81	1.78
Aug-06	2.70	6.11	0.42	Aug-06	5.69	2.28
Sep-06	3.16	3.60	0.41	Sep-06	3.19	2.75
Oct-06	1.99	0.61	0.41	Oct-06	0.20	1.58
Nov-06	0.77	0.52	0.42	Nov-06	0.10	0.35
Dec-06	1.51	6.70	0.4	Dec-06	6.30	1.11
Jan-07	-1.96	-0.16	0.44	Jan-07	-0.60	-2.40
Feb-07	1.16	-0.73	0.38	Feb-07	-1.11	0.78
Mar-07	4.42	-0.08	0.43	Mar-07	-0.51	3.99
Apr-07	3.39	1.87	0.44	Apr-07	1.43	2.95
May-07	-1.46	-0.24	0.41	May-07	-0.65	-1.87
Jun-07	-3.13	-6.32	0.4	Jun-07	-6.72	-3.53
Jul-07	1.28	-4.78	0.4	Jul-07	-5.18	0.88
Aug-07	3.88	8.79	0.42	Aug-07	8.37	3.46
Sep-07	1.35	-0.25	0.32	Sep-07	-0.57	1.03
Oct-07	-3.87	-6.86	0.32	Oct-07	-7.18	-4.19
Nov-07	-1.13	-5.59	0.34	Nov-07	-5.93	-1.47
Dec-07	-6.04	11.18	0.27	Dec-07	10.91	-6.31
Jan-08	-2.58	-3.13	0.21	Jan-08	-3.34	-2.79
Feb-08	-0.90	-9.60	0.13	Feb-08	-9.73	-1.03
Mar-08	4.77	8.78	0.17	Mar-08	8.61	4.60
Apr-08	1.51	-6.48	0.17	Apr-08	-6.65	1.34
May-08	-8.35	-15.56	0.17	May-08	-15.73	-8.52
Jun-08	-0.90	-11.01	0.17	Jun-08	-11.18	-1.07
Jul-08	1.55	-4.92	0.15	Jul-08	-5.07	1.40
Aug-08	-9.42	-29.61	0.12	Aug-08	-29.73	-9.54
Sep-08	-16.52	-13.77	0.15	Sep-08	-13.92	-16.67
Oct-08	-6.96	-26.04	0.08	Oct-08	-26.12	-7.04
Nov-08	0.99	-25.44	0.02	Nov-08	-25.46	0.97
Dec-08	-8.22	23.02	0.09	Dec-08	22.93	-8.31
					AVERAGE(F6:F65)	AVERAGE(G6:G65)
					-2.12	-0.55
Covariance matrix
S&P return		CORREL(F6:F65, G6:G65)
		0.3716121
Regression Statistics
Multiple R	0.37
R Square	B78*B78	0.1369
Adjusted R Square	0.12
Standard Error	8.32
Observation	60
	Coefficients	Standard Error	t Stat	P-value	P-value
Intercept	-1.65	1.08	-1.52	D85-B85	0.13
Spy	0.87	0.28	3.05		0

The Excel calculations given above give the price movement of S&P index and Apple. To get the covariance, the average of the excess returns is calculated.

Excel analysis is used on the given spreadsheet and the data is obtained from that.

From this, 0.37 is obtained as the multiple regressions. The R-Square is obtained as 0.14. The standard error value is 8.32.

Therefore, the beta is 0.87.