Conducting Event Studies With Excel

For conducting event studies with Excel, you may refer to Simon Benninga's book Financial Modeling. Further, the following zip-archive entails a workbook that illustrates how event studies can be implemented in Excel. Once you open the zip-archive above, you will find a step-by-step example of how event studies are executed in Excel. The following is a brief overview of the different steps implied.

1. Calculate the returns of the firm's stock, as well as the returns of the reference index.
2. Match these two time series of returns together.
3. For each event, identify the sequences of firm and market returns you want to be included in the estimation window.
4. Next, calculate the alpha, beta and sigma coefficients (for each event) using the Excel formulas intercept, slope, and steyx respectively.
5. Based on the actual market returns on the event date and the other dates in the event window, use the alpha and beta value of the event to calculate expected returns throughout the event window. These returns represent the hypothetical returns one would expect had the event not have taken place.
6. By deducting these expected returns from the actual returns of the firm's stock throughout the event window, you will receive the abnormal returns.
7. Dividing the abnormal returns through the root mean square error (i.e., the Steyx-value) will yield the t-values you need for significance testing.

For a hands-on example of what these steps imply, you may want to watch this excellent (third party) YouTube-video:

As the video implicitly conveys, event studies with larger numbers of events (or firms and indices) are very tedious to implement in Excel. While you may automatize some parts of the process (i.e., the re-location of the estimation and event windows) using the Excel formulas countif and offset and iterate through your events by means of VBA, larger analyses require significant time - also for the recalculation of formulas Excel needs to perform per iteration (you have to assign some idle/sleep-time for this recalculation step in your macro to prevent errors). For initial guidance on how to use these formulas, please kindly refer to Benninga (2008) and the book's accompanying CD.

-- Please consider using our free server-side abnormal return calculators to perform your event study, including all test statistics --

References

Benninga, S. 2008. Financial modeling (3 ed.). Boston, MA: MIT Press.

References and further studies

S. Benninga 2008. Financial modeling (3 ed.).
Simon Benninga and Torben Voetmann

Simon Benninga is Professor of Finance at the Faculty of Management at Tel-Aviv University. He has advanced degrees from the Hebrew University (M.Sc. in mathematics, 1973) and Tel-Aviv University (Ph.D. in finance, 1977). Benninga’s academic areas of interest are corporate finance, term structure, and computational finance. He is the author of over 40 academic papers and of four books: Numerical Techniques in Finance (MIT Press, 1989) Corporate Finance:  A Valuation Approach (with Oded Sarig, McGraw-Hill, 1997), Financial Modeling (MIT Press, 1997, 4th edition in work), and Principles of Finance with Excel (Oxford University Press in 2005). Please visit his personal website on http://www.simonbenninga.com.

Dr. Voetmann has published in finance journals, including the Journal of Corporate Finance, Review of Finance and the European Journal of Finance, and has authored the chapter on event studies in Professor Benninga's Financial Modeling book. He currently works at The Brattle Group. Please find his full profile on our page on the use of event studies in litigation cases, which is curated by Dr. Voetmann.