Market adjusted buy and hold return calculation using monthly return data

[joon1]

Dear Madam/Sir,

I would like to calculate market-adjusted buy and hold return as follows:

BHAR(i) = { [(1+lret) of month 0] * [(1+lret) of month 1] * [(1+lret) of month 2] * [(1+lret) of month 3] *  ... * [(1+lret) of month 12] } - { [(1+lbenchret) of month 0] * [(1+lbenchret) of month 1] * [(1+lbenchret) of month 2] * [(1+lbenchret) of month 3] *  ... * [(1+lbenchret) of month 12] }

First, I merge CSRP monthly return data and Compustat fiscal year month( variable name:closemonth in the current file).

I have made sas program code below using posting in this community. However, I don't know how to use "closemonth" to compute buy hold return for the previous eleven months and return for the fiscal year month. 

%macro calret;
data c4;
set c3;
by permno;
lret=log(ret+1);
lmret=log(vwretd+1);
array laglret[11] laglret1-laglret11;
array laglmret[11] laglmret1-laglmret11;
%do j=1 %to 11;
laglret&j=lag&j(lret);
laglmret&j=lag&j(lmret);
%end;
if first.permno then count=1;
do i=count to 11;
laglret[i] = .;
laglmret[i] = .;
end;
count +1 ;
cumret= exp( sum(of lret laglret1-laglret11)) -1;
mcumret= exp( sum(of lmret laglmret1-laglmret11)) -1;
if last.permno then BHAR=cumret-mcumret;
%mend calret;
%calret;
run;

Any help will be highly appreciated.

Thank you

Sincerely,

Joon1

[mkeintz]

What do you want the output to look like?  Do you want rolling 12-month return-over-benchmark?  If so, then what is the significance of the closing month data, since the output would be monthly, and would have an updated value for every month.

Or do you want one output record per year, for the fiscal year closing month?

By the way, since you are probably using the same benchmark for every company in your analysis, there is no need to recalculate the rolling benchmark returns for each company over the same time span.  You can calculate it once, and store the benchmark returns in a two-way matrix, with 12 columns and one row for each year in your data.

But first please provide, in the form of a sas data step, what the output data should look like.

[joon1]

Thank you so much for your kind reply, mkeintz.

I want one output record per year, for the fiscal year closing month. Thank you so much for your help again. Joon1.

PERMNO	DATE	closemonth	RET	VWRETD	BHAR
78736	19910731	.	0.02439	0.0468
78736	19910830	.	0.04762	0.02682
78736	19910930	.	0.01705	-0.011
78736	19911031	.	0	0.01782
78736	19911129	.	-0.1285	-0.0373
78736	19911231	.	-0.1282	0.10678
78736	19920131	.	0.16177	-0.0012
78736	19920228	.	-0.0633	0.01335
78736	19920331	.	-0.0541	-0.0237
78736	19920430	.	-0.1429	0.01342
78736	19920529	.	-0.1833	0.00644
78736	19920630	6	-0.1837	-0.0193	calculated BHAR

[mkeintz]

That's not an annual file in your response.  It's a monthly file, in which one record out of 12 has a value for market-adjusted BHAR.

And because you have not provided the data in the form of a SAS data step, the program below is completely untested.

Also, per my previous comment, it doesn't make sense to calculate the benchmark returns over-and-over again, as per your initial program.  So my program below assumes you have, in addition to your C3 dataset, another dataset I will call BENCHMARK, containing DATE and VWRETD (the benchmark monthly return).  It will be a short file, since it only has one record per month over your entire time range.  (while C3 has one record per month, but for every PERMNO).

data want;

  ** Generate 12-month rolling benchmark returns    **
  ** Do this only once and save in an array         **;
  if _n_=1 then do until (end_of_benchmark);
    set benchmark end=end_of_benchmark;

    array bmrk_ret {2001:2019,1:12} _temporary_ ;
    array logplusone {12} _temporary_;

    y=year(date);
    m=month(date);
    logplusone{m}=log(1+vwret);
    if n(of logplusone{*})=12 then bmrk_ret{y,m}=exp(sum(of logplusone{*}))-1;
  end;

  set c3;
  by permno;
  if first.permno then call missing(of logplusone{*});
  m=month(date);
  y=year(date);
  logplusone{m}=log(1+ret);
  if closemonth^=.;
  ** Missing Paren, correction below **  if n(of logplusone{*})=12 then bhar=exp(sum(of logplusone{*})-1 - bmrk_ret{y,m};
  if n(of logplusone{*})=12 then bhar=exp(sum(of logplusone{*}))-1 - bmrk_ret{y,m};
run;
  

1. The array BMRK_RET has one row per year, and 12 columns.  It is used to store the 12-month rolling benchmark returns.  Be sure to set the array row bounds - I have 2001:2019 - to contain the entire date range you have in your study.
2. The "if _N_=1 ..." do group does the following:
   1. Reads every record from BENCHMARK, and extracts the year Y and month M from DATE.  Y and M will be used as indexes to the 2-dimensioanl array BMRK_RET.
   2. Put log(1+vwretd) into the 12-element array LOGPLUS1 - which is designed to always have the most recent 12 months.
   3. Once LOGPLUS1 has 12 non-missing values, the 12-month return is calculated and stored in row Y and column M of BMRK_RET.
3. The reason it's "IF _N_+1" is to tell sas to do the work in ! above only in the first iteration of the data step (i.e. just before it reads the first record from C3.
4. The same usage of the LOGPLUSONE array is applied to RET data from C3.
5. The "if closemonth^=.;" is a "subsetting if" (notice no THEN clause).  I.e. only qualifying records pass to the rest of the data step.  This is where only 1 record will be output instead of 12.
6. The last statement makes sure the LOGPLUSONE array is full, calculates the 12-month rolling return for the permno, and retrieves and subtracts the corresponding previously calculated benchmark return

Lags can often be very useful, but they are too much work for this particular application.  Instead it makes use of array that are marked "_TEMPORARY_", which means 2 things:

   (1) the array data will not be output, and
   (2) importantly, the contents of the arrays are retained across all observations. 

So not only can you retrieve the benchmark returns long after they are calculated, you also always have the most recent 12-months of permno returns in the array LOGPLUS1.

IMPORTANT;  This program assumes not only that the data are sorted by date, within permno, but also that there are no holes in the data.  This is because if there were a hole  (say missing "MARCH" for permno with closemonth=6), then the 12-month return would use the most recent data for every month but MARCH, whose data would be 12-months too old.

Editted note:  I noticed a missing paren in the code, commented the offending statement, and inserted a correct one.

[joon1]

Thank you so much, mkeintz for your kind advice.

Your program worked well as follows. However, I checked BHAR calculation of one company(permno=10001) and it seems that calculated BHAR based on your program is high (the attached excel sheet). It will be grateful if you can think of reasons why the gap exists. Also how I can add one line to enable program to calculate BHAR when at least 6 monthly observations are available related to your comment below.

"This is because if there were a hole  (say missing "MARCH" for permno with closemonth=6), then the 12-month return would use the most recent data for every month but MARCH, whose data would be 12-months too old."

data c4;
 ** Generate 12-month rolling benchmark returns **
 ** Do this only once and save in an array **;
 if _n_=1 then do until (end_of_benchmark);
 set benchmark end=end_of_benchmark;
 array bmrk_ret {1990:2019,1:12} _temporary_ ;
 array logplusone {12} _temporary_;
 y=year(date);
 m=month(date);
 logplusone{m}=log(1+vwretd);
 if n(of logplusone{*})=12 then bmrk_ret{y,m}=exp(sum(of logplusone{*}))-1;
 end;
 set c3;
 by permno;
 if first.permno then call missing(of logplusone{*});
 m=month(date);
 y=year(date);
 logplusone{m}=log(1+ret);
 if closemonth^=.;
 if n(of logplusone{*})=12 then bhar=exp(sum(of logplusone{*})-1 - bmrk_ret{y,m});
 run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
93110 at 399:17 93110 at 399:22
NOTE: There were 2745939 observations read from the data set WORK.BENCHMARK.
NOTE: There were 2745939 observations read from the data set WORK.C3.
NOTE: The data set WORK.C4 has 222689 observations and 33 variables.
NOTE: DATA statement used (Total process time):
real time 4.72 seconds
cpu time 4.71 seconds

Thank you so much

Joon1

[joon1]

Dear mkeniz,

I have difficulty to understand the generated output by sas program. I would like to compute the following BHAR:

Buy and Hold Abnormal return= ((1+return(m1))(1+return(m2))----(1+return(m12)) - (1+marketreturn(m1)(1+marketreturn(m2))---(1+marketreturn(m12)), where m1=closemonth-11, m2=closemonth-10, m3=closemonth-9, m4=closemonth-8, m5=closemonth-7, --- m12=closemonth

As shown in the excel sheet, the computed values in three different ways  are different. 

computed value by original definition above: 0.052

computed value by log and exponential: 0.023

computed value by current sas program: 0.388

It will be highly appreciative if you can give an idea how to fix program to generate computed value by original definition (0.052).

data c4;
** Generate 12-month rolling benchmark returns **
** Do this only once and save in an array **;
if _n_=1 then do until (end_of_benchmark);
set benchmark end=end_of_benchmark;
array bmrk_ret {1990:2019,1:12} _temporary_ ;
array logplusone {12} _temporary_;
y=year(date);
m=month(date);
logplusone{m}=log(1+vwretd);
if n(of logplusone{*})=12 then bmrk_ret{y,m}=exp(sum(of logplusone{*}));
end;
set ch2;
by permno;
if first.permno then call missing(of logplusone{*});
m=month(date);
y=year(date);
logplusone{m}=log(1+ret);
if closemonth^=.;
if n(of logplusone{*})=12 then bhar=exp(sum(of logplusone{*})- bmrk_ret{y,m});
run;