Buy-and-hold returns calculation stata (compounding effect)

[Bram Smith]

Hi all,

Currently I am doing some research on some specific sort of IPOs within Europe. Using datastream I have obtained monthly stock prices, starting from the first day listed in Datastream till 36 month after the IPO.

Now, I want to calculate buy-and-hold returns according to literature.

BHARi(t, T) = Πt = 1 to T (1 + Ri,t) - Π t = 1 to T (1 + RB,t)

I want to use this method because it takes into account the compounding effect of return when holding stock in your portfolio for the long-run.
From the monthly stock prices I have calculated raw returns using the following formula:

by id: gen rawreturn=price/price[_n-1]-1

This should represent the Ri,t in the first part of the equation.

I will use several benchmarks. One of them MSCI Europe. The returns are constructed in the same way

gen MSCIreturn=price/price[_n-1]-1

Now I am facing a problem because up till now I have not been able to figure out how to construct the BHARi(t,T) in stata.

1. The formula means that the returns need to be compounded/multiplied so for example (1+Ri,t)*(1+Ri,t+1)*etc. I am wondering if I have do this first before subtracting the benchmark returns. So should I first calculate AR for every it by doing (1+Ri,t) – 1(RB,t)? And then compound? Or the other way around?
2. It would be ideal to have BHAR returns for every month (1-36) for every firm in my sample, so I will be able to draw a graph for the returns over 36 months.

Furthermore, is there any quick and easy way to do this compounding in stata?

Any help would be appreciated!

Thanks, Bram

[Phil Bromiley]

Your question is a too long and complicated. I'm not even sure what you mean in your BHARi equation - we don't normally see "1 to T" in an equation, and I'm not sure you define pi. It is also good practice to use one term for each variable - don't call something rawreturns in one place and Rit elsewhere. Some of your question also deals with substantive issues about correct ways to calculate returns rather than Stata programming.

It would help if you looked at the FAQ. Providing code and data showing what you want (using the preferred tool for including data - see FAQ) would help. Then we can know what we're trying to program.

There may be routines for component returns for Stata, but I don't know them.

I think you could calculate this using a loop. First, make sure you've xtset your data.

You can probably just expand your equation and write it out as a generate statement with leads or lags. It would be long, but not difficult. Alternatively, you may be able to calculate what you want with a forvalues loop using leads and lags.

[Bram Smith]

Phil,

Thank you for your comment. My explanation is indeed not very clear, as I am very new to Stata and this forum I need to put my question differently. Let me try to explain better and try to use the right terminology. I will also add an example for only one firm. The BHARi(t,T) is an equation written down in finance related papers and is not a Stata code or formula. The purpose of my question is to transform this equation into Stata code so I can apply them to a large set of data.

Let me start with the data for one firm for only a short amount of periods and not using the second part of the equation for the "benchmark". So lets focus on
Π(1 + Ri,t), where i is the same as ID (so different firms) and t is the month (from t=0 to t=N). Π means the product of every return+1 at every month.

Data:

id	month	price
1	0	21,5
1	1	22
1	2	21,7
1	3	19,48
1	4	15,6
1	5	14,91
1	6	14,5
1	7	12,1
1	8	9,66
1	9	9
1	10	14,23
1	11	19

Now I start calculation return using

by id: gen return=price/price[_n-1]-1

Next, following the BHAR equation the variable that should be constructed is (1+Ri,t) which can be interpreted as the return for firm i (or "id" in Stata language) at time t (or in this case "month") +1

 by id: gen return1 = return+1

The next step is where my problem occurs in Stata. I need to calculate the product (this is what the Pi operatior stands for in the formula) of variable return1.
In this forum I found a similar problem and the following code was proposed:

 
egen double returnproduct = total(ln(return1))
replace returnproduct = exp(returnproduct)

The solutions are as follows:

month	price	return	return1	returnproduct
0	21,5			0,883721
1	22	0,0232558	1,0232560	0,883721
2	21,7	-0,0136364	0,9863636	0,883721
3	19,48	-0,1023041	0,8976958	0,883721
4	15,6	-0,1991787	0,8008214	0,883721
5	14,91	-0,0442308	0,9557692	0,883721
6	14,5	-0,0274983	0,9725017	0,883721
7	12,1	-0,1655172	0,8344828	0,883721
8	9,66	-0,2016529	0,7983471	0,883721
9	9	-0,068323	0,931677	0,883721
10	14,23	0,5811111	1,5811110	0,883721
11	19	0,3352073	1,3352070	0,883721

Now, for the variable returnproduct I would prefer something else. Now it gives me for every month the same value (which can be interpreted as the buy-and-hold return when buying the stock at time 0 and holding till time 11).

I would prefer to see in the returnproduct colum the product of the return at the end of each month. I calculated in excel and i believe this should be the values I want:

returnproduct 1,023255814
1,009302326
0,906046512
0,725581395
0,693488372
0,674418605
0,562790698
0,449302326
0,418604651
0,661860465
0,88372093

Do you have any idea how to replicate this in stata?

[Clyde Schechter]

OK, much clearer. The code below shows two methods to do this. (Note: I converted your decimal points to dots to make my life simpler, feel free to go back to commas on your system.) The first follows the formula used in your finance books. The second one suggests that the authors of the finance books would benefit from applying some elementary algebra.

clear
input id    month    price
1    0    21.5
1    1    22
1    2    21.7
1    3    19.48
1    4    15.6
1    5    14.91
1    6    14.5
1    7    12.1
1    8    9.66
1    9    9
1    10    14.23
1    11    19
end

xtset id month
by id (month), sort: gen double return = price/L.price - 1

//    RETURN PRODUCT METHOD 1
gen rp1 = double return + 1
by id (month), sort: gen double product_return = 1 if _n == 1
by id (month): replace product_return = L.product_return*rp1 if _n > 1

//    RETURN PRODUCT METHOD 2
by id (month), sort: gen double product_return_method_2 = price/price[1]

You will see that, at least as far as the precision shown in the browser or list output, these give identical results and agree with your Excel calculations, and the second is evidently much more straightforward. If, however, you check whether the two results are the same with -assert- you will find that they are not. They differ many decimal places out. (-assert float(product_return) == float(product_return_method_2)- passes.) That is because the first method brings in multiple episodes of rounding error during the calculations, whereas the first incorporates only one episode of rounding error. So the second method is most likely more accurate. While the difference is too small for practical purposes when we are just looking at 11 months, if you have a long time series extending over hundreds of months, the difference can be appreciable. So for both simplicity and accuracy, I recommend method 2.

[Helen Chang]

Hi Bram,

I am trying to get the buy-and-hold return over the quarter using Stata. Would you mind sharing your stata code of computing buy-and-hold return? Also, did you get all the necessary data from CRSP-stock/security files-CRSP daily stock file or from CRSP monthly stock file?

Thank you very much for your help in advance.

[Claire Steijn]

Hi Helen,
I have to calculate the raw buy-and-hold return and the abnormal buy and hold return from August 2008 through March 2009, which is calculated as the raw return minus the expected return (based on the market model estimated over the 60-month period ending in July 2008). Would you mind sharing your Stata code that you used to calculate this?

Thank you very much for your help in advance!

Claire