How to Run an Event Study in Excel

This worked example uses Event 10001, Adobe Systems against the SP500 on 30 April 1997, from the downloadable ARC sample. Every displayed result below is calculated from the three files with log returns, a 120-trading-day estimation period from relative days -130 through -11, and a 21-day event window from -10 through +10. They come from the steps described here; ARC can differ in implementation details and reports a broader result set.

1. Open and arrange the sample files

Download 01_RequestFile.csv, 02_FirmData.csv and 03_MarketData.csv. In Excel's text-import dialog choose a semicolon delimiter, no header row, and dd.mm.yyyy for dates. The request file identifies Event 10001, Adobe Systems, SP500 and the event date. Filter the firm and market files to those names, then join them by their matching date.

On an Analysis worksheet, use columns A:J: Date, Firm price, Market price, Firm log return, Market log return, Relative day, Estimation flag, Expected return, AR and Running CAR. Include one prior trading day in row 2. Put relative day -130 in row 3, -11 in row 122, -10 in row 123 and +10 in row 143. That makes the exact ranges in the formulas below visible and auditable.

2. Enter the calculation formulas

In D3 enter =LN(B3/B2) and in E3 enter =LN(C3/C2), then fill both down through row 143. In F3 write -130 and increase by one per row through F143. Put =AND(F3>=-130,F3<=-11) in G3 and fill down. This uses the log-return convention of the ARC sample documentation and puts a non-trading event date on the later trading day.

Place the summary labels in A147:A152 and the following formulas in B147:B152:

• Alpha, B147: =INTERCEPT(D$3:D$122,E$3:E$122)
• Beta, B148: =SLOPE(D$3:D$122,E$3:E$122)
• S_AR, B149: =STEYX(D$3:D$122,E$3:E$122)
• CAR(-10,10), B150: =SUM(I123:I143)
• CAR(-1,1), B151: =SUM(I132:I134)
• t statistic, B152: =B150/(SQRT(21)*B149)

In H123 enter =$B$147+$B$148*E123, in I123 enter =D123-H123, and in J123 enter =I123. Fill H and I through row 143; in J124 enter =J123+I124 and fill through J143. If you want a two-sided p-value, use =T.DIST.2T(ABS(B152),118); this tutorial deliberately gives the formula only, not an unverified displayed p-value.

3. Check the 21 event-window results

Format AR and CAR as percentages with four decimal places. The check script rounds the following values in the same way.

The residual standard deviation uses the 120 estimation-period residuals with M - 2 degrees of freedom. For this single event, the displayed t statistic divides CAR by SQRT(21)*S_AR. It is a teaching calculation, not a replacement for a full cross-sectional event-study test.

Limits of hand calculations

Excel makes the mechanics transparent, but a hand-built workbook is easy to misalign when dates, firms or windows multiply. Keep source data, formulas and the event-date rule together; do not silently replace missing trading dates. For the concepts behind AR and CAR, see How to calculate abnormal returns. For model choice and inference, see Expected-return models and Significance tests.

FAQ