Question

I really would appreciate it if someone could provide me with the Excel formulas to calculate the last five (5) columns of the spreadsheet listed below.

# of Trading Days Before/After Event Date	DATE	Closing Prices Procter&Gamble	MKT-Rf	Rf	Returns Honeywell	Expected Returns Procter&Gamble (ER)	Abnormal Returns Procter&Gamble (AR)	AR t-test	AR Significant (Y/N) ?	Cumulative Abnormal Returns Procter&Gamble (CAR)
-270	1/2/2004	49.4950	-0.17	0.003
-269	1/5/2004	49.7400	1.2	0.003	0.4950056
-268	1/6/2004	49.4900	0.2	0.003	-0.5026136
-267	1/7/2004	49.0500	0.34	0.003	-0.8890745
-266	1/8/2004	49.5650	0.45	0.003	1.0499491
-265	1/9/2004	49.8200	-0.72	0.003	0.514478
-264	1/12/2004	50.1250	0.57	0.003	0.6122039
-263	1/13/2004	49.8500	-0.51	0.003	-0.5486324
-262	1/14/2004	50.0150	0.8	0.003	0.330995
-261	1/15/2004	49.7550	0.15	0.003	-0.5198401

Answer 1

Closing price of P&G	Returns on P&G	Mkt-Rf	Market Index returns	Beta of P&G
49.495		-0.17	-0.167
49.74	0.49500	1.2	1.203
49.49	-0.50261	0.2	0.203
49.05	-0.88907	0.34	0.343
49.56	1.03976	0.45	0.453
49.82	0.52462	-0.72	-0.717
50.12	0.60217	0.57	0.573
49.85	-0.53871	-0.51	-0.507
50.01	0.32096	0.8	0.803
49.75	-0.51990	0.15	0.153
Avg.return on P&G	0.05914
Avg.return on index			0.27856
			Beta of P&G	0.323356
			expected return as per CAPM would be
	RF						3.0%
	Average historical annual excess return for U.S. stocks						0.27800
	Beta of the stock						0.323
	Expected return = risk free rate + (beta x market return premium)						11.0%

Abnormal Returns = Actual Returns – Normal Returns

APM Formula and Calculation

CAPM is calculated according to the following formula:

Where:

Ra = Expected return on a security
Rrf = Risk-free rate
Ba = Beta of the security
Rm = Expected return of the market

Note: “Risk Premium” = (Rm – Rrf)