I can not find the mistake, the result should show on green area. the function is right.

Sub CAR() 'Divide the data into many estimation periods and event period windows Dim no_date As Integer no_date = Range("Q19:Q152").Rows.Count Dim no_stock As Integer no_stock = Range("R19:R152").Columns.Count Dim date_number() ReDim date_number(1 To no_date, 1 To no_stock)

'Count the number of event days 'no_befdate is the number of data date no_befdate = 0 For i = 1 To no_date If Range("Q19:Q152").Cells(i, 1).Value

'If -110

'Input estimation dates Dim Estdate() ReDim Estdate(1 To no_date, 1 To 1) For i = 1 To no_date Estdate(i, 1) = Range("Q19:Q152").Cells(i, 1) Next i

'Create Sums Dim sumx Dim sumy Dim sumxy Dim sumx2 For i = 1 To no_date If date_number(i, 1) >= -110 And date_number(i, 1)

'Calculating abnormal stock returns for each event day 'Dim abnormal stock returns Dim abret() ReDim abret(1 To no_date, 1 To 1) Dim CAR For i = 1 To no_date If date_number(i, 1) >= -1 And date_number(i, 1)

For i = 1 To no_date If date_number(i, 1) >= -1 And date_number(i, 1)

Range("AG19:AG28").Cells(i, 1).Value = CAR

End Sub

An event study is an empirical analysis that examines the impact of a particular event on the value of a subject. This task employs this method to assess the effect of earnings announcements on returns of a portfolio of 10 stocks. Returns data of each stock is pro- vided with one announcement date in October 1998. In particular, you are required to compute the cumulative abnormal returns (CARit) of each stock given its announcement date and the cross-sectional average of the cumula- tive abnormal return (CARt) of the portfolio. There are a number of models and methods to determine the expected returns and undertake such event study. Below are specific instructions for you to complete the task. First, for each announcement date, t, estimate the expected returns @it by using the mar- ket model (1), assuming that those assumptions under the market model are satisfied. lit = ; + Birmt + Eit (1) ; and ; are obtained by running the regression model (1) using stock returns during the estimation period for that announcement date. Second, the announcement date is defined as day 0. The estimation period goes from day t = -110 to day t = -11, while the event window goes from day t = -1 to day t= 10. Third, the cumulative abnormal returns (CARit) of each stock on its announcement date are computed as follows: t CARit = ir (2) T=-1 Last, the cross-sectional average of the cumulative abnormal return (CARt) of the port- folio is computed by the formula: 10 CAR+ = + C ARit (3) i=1 More information about event studies can be found here: Eckbo, B. E. (Ed.). (2008). Handbook of empirical corporate finance set. Elsevier. Chap- ter 1 - Econometrics of Event Studies. MacKinlay, A. C. (1997). Event studies in economics and finance. Journal of economic literature, 35(1), 13-39. 2 Spreadsheet Guidance You are provided the daily returns of 10 stocks for 134 days from 7 May 1998 to 13 November 1998 in the spreadsheet named PEAD. This sheet (only) will be marked. With the given (stock and market) returns data and announcement dates, two functions have been (poorly) written in the Module1, following the instructions in the Background session, to compute the (CARit) of each stock and the (CARt) of the port Therefore, after fixing the provided code for the two functions, you will need to call the function to compute the (CARit) of each stock in the first 10 green cells and call the function to compute the (CARt) of the portfolio in the last green cell as shown in the attached picture below. The function to compute the (CARit) of each stock in the first 10 green cells will have the form as: CAR(Stock Ret, Market Ret, Eventdate, Dates) where StockRet is the one-dimensional array storing 134 returns of each stock and already labelled in the Spreadsheet by the stock ticker, for example BES, BUR, FOS, HAL, etc. Market Ret is the one-dimensional array storing all the market returns for given 134 days and already labelled in the Spreadsheet as mrktret. Eventdate is the event date of each stock and given in the column before the green cells as in above picture. The event date for each stock is labelled as Eventdate_BES, Eventdate_BUR, Eventdate_FOS, etc. Dates is the one-dimensional array storing all the available (134) dates including the estimation period, event window, and days outside these two periods, and already labelled as dates in the Spreadsheet. mrktret and dates are the same for all stocks. For example, the function will be called to compute the (CARit) of stock BES is: CAR(BES, mrktret, Eventdate_BES, dates) and the function to compute the (CARt) of the portfolio in the last green cell will have the form: PortfolioC AR(AIIC AR) where ALICAR is the area storing all the CAR of 10 stocks and labelled as AllCAR. Note: The addition sheet named Sample provides the same set of data for you to practise. This sheet will not be graded. You may use this sheet to debug the functions. A VBA subroutine is attached to this sheet which solves a similar question in the Spread- sheet PEAD. If you successfully debug the subroutine (get the data to draw the graph 3 attached in that sheet), you will get 90% of the logic to debug the functions. Please note that the graph shows the changes in AAR and CAAR, not (CARit) and (CAR), but these terms are all relevant and share a large part of the study. Daily Returns of 10 Stocks in a Portfolio This sheet is not locked and will not be marked. You may use this sheet to debug your function. A VBA subroutine is attached to this sheet for you to practise (and will not be marked). It solves a similar question with the function in PEAD sheet. If you successfully debug the subroutine (get the data to draw the below graph), you will get 90% of the logic to debug function. DATE BES 1998/5/7 1998/5/8 1998/5/11 1998/5/12 1998/5/13 1998/5/14 1998/5/15 1998/5/18 1998/5/19 1998/5/20 1998/5/21 1998/5/22 100g/5/20 BUR FOS HAL KEL -0.00769 0.00657 -0.00769 0.01107 0.00474 -0.00979 0.01883 0.00657 0.02948 -0.00769 -0.00978 -0.00979 0.00344 0.01883 -0.00769 -0.00769 0.01827 -0.00978 0.01883 0.01883 0 0.01883 -0.00978 -0.00978 0.00785 -0.00565 -0.00978 0.01107 -0.01112 -0.02415 -0.00769 0.00657 -0.00338 0.00728 0.01883 -0.00979 0.00452 -0.00434 -0.00978 -0.00769 0.02697 0.00435 0 0.01883 -0.00547 0.00145 -0.00474 -0.00978 A0125 001876 n1191 O 0127 PEP QUA SCL 0.00112 -0.03195 -0.01639 0.00894 0.02145 -0.01667 0.01107 0.00162 0.03531 0.00657 0.01774 0.02319 -0.00979 -0.01426 -0.00267 -0.00769 0.00482 -0.01471 0.01883 -0.0384 -0.02985 -0.00978 0.00666 -0.01099 0.00768 0.00331 -0.00444 0.01883 0.06755 0.02232 -0.00978 -0.0108 -0.00546 0.00768 -0.00468 0.00768 001883 01251 0327 UNI -0.00753 -0.01062 0 0.03068 -0.01488 0.01511 -0.01179 -0.02219 0.00157 -0.00313 -0.00705 0.00158 LO02207 WRI MRKTRET 0.01858 -0.00546 -0.00318 -0.01672 0.00768 0.00542 0.00927 0.00327 -0.003 0.01685 -0.01194 -0.00127 -0.00753 0.01429 0.00119 -0.01062 0 -0.0011 0.00583 0.00108 -0.00291 0 -0.00541 -0.00866 0.02319 0.00326 0.0021 -0.04816 0.001016389 -0.00245 0 0.001069222 -0.00179 0 0.00329 -0.0036 O 02281 0787 001367 Stock Eventdate CAR BES 1998/10/14 BUR 1998/10/21 FOS 1998/10/27 HAL 1998/10/29 KEL 1998/10/30 PEP 1998/10/13 QUA 1998/10/22 SCL 1998/10/22 UNI 1998/10/21 WRI 1998/10/28 PortfolioCAR Daily Returns of 10 Stocks in a Portfolio This sheet is not locked and will not be marked. You may use this sheet to debug your function. A VBA subroutine is attached to this sheet for you to practise (and will not be marked). It solves a similar question with the function in PEAD sheet. If you successfully debug the subroutine (get the data to draw the below graph), you will get 90% of the logic to debug function. Stock Eventdat CAR DATE BES BUR FOS HAL KEL PEP QUA SCL UNI WRI MRKTRET 1998/11/4 0.005714 -0.005944 0.065134 -0.009917 0.022018 0.004877 -0.011555 -0.007973 0.042254 0.010828 0.013189 1998/11/5 0.002273 0.005714 0.007194 0.025042 0.019749 0.003806 0.037194 0.029851 0.011442 0.00306 0.008808 1998/11/6 -0.017007 0.002273 0 0.003257 0 0.012352 0.005714 0.004877 -0.010181 0.018307 0.010636 1998/11/9 0.008074 -0.017007 0.028571 -0.060065 -0.014085 0.014811 0.002273 0.003806 0.005714 0.010487 -0.00104 1998/11/10 0.001053 0.008074 0.033298667 -0.063903 -0.008929 0.018732 -0.017007 0.012352 0.002273 0.014085 -0.002224 1998/11/11 -0.021814 -0.0032865 0.043987167 -0.023985 -0.009009 0.004849 0.008074 0.014811 -0.017007 0.005848 -0.000756 1998/11/12 -0.022362267 -0.0045065 0.054675667 -0.009452 -0.001818 0.013189 0.003581 0.018732 -0.025727667 -0.002389 0.000695 1998/11/13 -0.027306006 -0.0057265 0.065364167 0.022004333 -0.001821 0.008808 0.0064815 0.004849 -0.037088167 -0.010626 0.003099 An event study is an empirical analysis that examines the impact of a particular event on the value of a subject. This task employs this method to assess the effect of earnings announcements on returns of a portfolio of 10 stocks. Returns data of each stock is pro- vided with one announcement date in October 1998. In particular, you are required to compute the cumulative abnormal returns (CARit) of each stock given its announcement date and the cross-sectional average of the cumula- tive abnormal return (CARt) of the portfolio. There are a number of models and methods to determine the expected returns and undertake such event study. Below are specific instructions for you to complete the task. First, for each announcement date, t, estimate the expected returns @it by using the mar- ket model (1), assuming that those assumptions under the market model are satisfied. lit = ; + Birmt + Eit (1) ; and ; are obtained by running the regression model (1) using stock returns during the estimation period for that announcement date. Second, the announcement date is defined as day 0. The estimation period goes from day t = -110 to day t = -11, while the event window goes from day t = -1 to day t= 10. Third, the cumulative abnormal returns (CARit) of each stock on its announcement date are computed as follows: t CARit = ir (2) T=-1 Last, the cross-sectional average of the cumulative abnormal return (CARt) of the port- folio is computed by the formula: 10 CAR+ = + C ARit (3) i=1 More information about event studies can be found here: Eckbo, B. E. (Ed.). (2008). Handbook of empirical corporate finance set. Elsevier. Chap- ter 1 - Econometrics of Event Studies. MacKinlay, A. C. (1997). Event studies in economics and finance. Journal of economic literature, 35(1), 13-39. 2 Spreadsheet Guidance You are provided the daily returns of 10 stocks for 134 days from 7 May 1998 to 13 November 1998 in the spreadsheet named PEAD. This sheet (only) will be marked. With the given (stock and market) returns data and announcement dates, two functions have been (poorly) written in the Module1, following the instructions in the Background session, to compute the (CARit) of each stock and the (CARt) of the port Therefore, after fixing the provided code for the two functions, you will need to call the function to compute the (CARit) of each stock in the first 10 green cells and call the function to compute the (CARt) of the portfolio in the last green cell as shown in the attached picture below. The function to compute the (CARit) of each stock in the first 10 green cells will have the form as: CAR(Stock Ret, Market Ret, Eventdate, Dates) where StockRet is the one-dimensional array storing 134 returns of each stock and already labelled in the Spreadsheet by the stock ticker, for example BES, BUR, FOS, HAL, etc. Market Ret is the one-dimensional array storing all the market returns for given 134 days and already labelled in the Spreadsheet as mrktret. Eventdate is the event date of each stock and given in the column before the green cells as in above picture. The event date for each stock is labelled as Eventdate_BES, Eventdate_BUR, Eventdate_FOS, etc. Dates is the one-dimensional array storing all the available (134) dates including the estimation period, event window, and days outside these two periods, and already labelled as dates in the Spreadsheet. mrktret and dates are the same for all stocks. For example, the function will be called to compute the (CARit) of stock BES is: CAR(BES, mrktret, Eventdate_BES, dates) and the function to compute the (CARt) of the portfolio in the last green cell will have the form: PortfolioC AR(AIIC AR) where ALICAR is the area storing all the CAR of 10 stocks and labelled as AllCAR. Note: The addition sheet named Sample provides the same set of data for you to practise. This sheet will not be graded. You may use this sheet to debug the functions. A VBA subroutine is attached to this sheet which solves a similar question in the Spread- sheet PEAD. If you successfully debug the subroutine (get the data to draw the graph 3 attached in that sheet), you will get 90% of the logic to debug the functions. Please note that the graph shows the changes in AAR and CAAR, not (CARit) and (CAR), but these terms are all relevant and share a large part of the study. Daily Returns of 10 Stocks in a Portfolio This sheet is not locked and will not be marked. You may use this sheet to debug your function. A VBA subroutine is attached to this sheet for you to practise (and will not be marked). It solves a similar question with the function in PEAD sheet. If you successfully debug the subroutine (get the data to draw the below graph), you will get 90% of the logic to debug function. DATE BES 1998/5/7 1998/5/8 1998/5/11 1998/5/12 1998/5/13 1998/5/14 1998/5/15 1998/5/18 1998/5/19 1998/5/20 1998/5/21 1998/5/22 100g/5/20 BUR FOS HAL KEL -0.00769 0.00657 -0.00769 0.01107 0.00474 -0.00979 0.01883 0.00657 0.02948 -0.00769 -0.00978 -0.00979 0.00344 0.01883 -0.00769 -0.00769 0.01827 -0.00978 0.01883 0.01883 0 0.01883 -0.00978 -0.00978 0.00785 -0.00565 -0.00978 0.01107 -0.01112 -0.02415 -0.00769 0.00657 -0.00338 0.00728 0.01883 -0.00979 0.00452 -0.00434 -0.00978 -0.00769 0.02697 0.00435 0 0.01883 -0.00547 0.00145 -0.00474 -0.00978 A0125 001876 n1191 O 0127 PEP QUA SCL 0.00112 -0.03195 -0.01639 0.00894 0.02145 -0.01667 0.01107 0.00162 0.03531 0.00657 0.01774 0.02319 -0.00979 -0.01426 -0.00267 -0.00769 0.00482 -0.01471 0.01883 -0.0384 -0.02985 -0.00978 0.00666 -0.01099 0.00768 0.00331 -0.00444 0.01883 0.06755 0.02232 -0.00978 -0.0108 -0.00546 0.00768 -0.00468 0.00768 001883 01251 0327 UNI -0.00753 -0.01062 0 0.03068 -0.01488 0.01511 -0.01179 -0.02219 0.00157 -0.00313 -0.00705 0.00158 LO02207 WRI MRKTRET 0.01858 -0.00546 -0.00318 -0.01672 0.00768 0.00542 0.00927 0.00327 -0.003 0.01685 -0.01194 -0.00127 -0.00753 0.01429 0.00119 -0.01062 0 -0.0011 0.00583 0.00108 -0.00291 0 -0.00541 -0.00866 0.02319 0.00326 0.0021 -0.04816 0.001016389 -0.00245 0 0.001069222 -0.00179 0 0.00329 -0.0036 O 02281 0787 001367 Stock Eventdate CAR BES 1998/10/14 BUR 1998/10/21 FOS 1998/10/27 HAL 1998/10/29 KEL 1998/10/30 PEP 1998/10/13 QUA 1998/10/22 SCL 1998/10/22 UNI 1998/10/21 WRI 1998/10/28 PortfolioCAR Daily Returns of 10 Stocks in a Portfolio This sheet is not locked and will not be marked. You may use this sheet to debug your function. A VBA subroutine is attached to this sheet for you to practise (and will not be marked). It solves a similar question with the function in PEAD sheet. If you successfully debug the subroutine (get the data to draw the below graph), you will get 90% of the logic to debug function. Stock Eventdat CAR DATE BES BUR FOS HAL KEL PEP QUA SCL UNI WRI MRKTRET 1998/11/4 0.005714 -0.005944 0.065134 -0.009917 0.022018 0.004877 -0.011555 -0.007973 0.042254 0.010828 0.013189 1998/11/5 0.002273 0.005714 0.007194 0.025042 0.019749 0.003806 0.037194 0.029851 0.011442 0.00306 0.008808 1998/11/6 -0.017007 0.002273 0 0.003257 0 0.012352 0.005714 0.004877 -0.010181 0.018307 0.010636 1998/11/9 0.008074 -0.017007 0.028571 -0.060065 -0.014085 0.014811 0.002273 0.003806 0.005714 0.010487 -0.00104 1998/11/10 0.001053 0.008074 0.033298667 -0.063903 -0.008929 0.018732 -0.017007 0.012352 0.002273 0.014085 -0.002224 1998/11/11 -0.021814 -0.0032865 0.043987167 -0.023985 -0.009009 0.004849 0.008074 0.014811 -0.017007 0.005848 -0.000756 1998/11/12 -0.022362267 -0.0045065 0.054675667 -0.009452 -0.001818 0.013189 0.003581 0.018732 -0.025727667 -0.002389 0.000695 1998/11/13 -0.027306006 -0.0057265 0.065364167 0.022004333 -0.001821 0.008808 0.0064815 0.004849 -0.037088167 -0.010626 0.003099