Compute compound return using monthly data

[Rochelle Zhang]

Dear statalisters,

My data has the following structure


clear
input int(firmid year) byte month double return
1000 1970 2 .00934434
1000 1970 3 .0173867
1000 1970 4 .00503076
1000 1970 5 .00073604
1000 1970 6 .01228391
1000 1970 7 .01249819
1000 1970 8 .00315777
1000 1970 9 -.01552438
1000 1970 10 .00465082
1000 1970 11 .0020976
1000 1970 12 -.00440306
1001 1970 1 -.01761758
1001 1970 2 -.00442809
1001 1970 3 .01371391
1001 1970 4 .01857399
1001 1970 5 -.0107689
1001 1970 6 -.0134988
1001 1970 7 -.01000505
1001 1970 8 .01642262
1001 1970 9 -.00124286
1001 1970 10 .01054097
1001 1970 11 .01086563
1001 1970 12 .00630591
end

My tasks are two-fold:

1. Compute a buy and hold return defined as = (1+ return of month 1) * ((1+ return of month 2)*….*(1+return of month12) -1

Do this for each year

1. If a year has less than 6 monthly returns, then set the return from 1 above to missing

Ultimately, I want to keep a annual data file that contains the buy and hold return for each firm year.

Could you give some advice on coding this?

Thanks,
Rochelle

[Andrew Musau]

There is a neat trick by Nick Cox from the following link on how to do a running product of a variable by group.

http://www.stata.com/statalist/archi.../msg00940.html


You may combine this with a simple count of observations by firmid and year

bys firmid year: gen count=_N
gen ret = 1+return
bysort firmid year : gen prod = sum(ln(ret))
by firmid year : replace prod = exp(prod[_N])
gen buy_hold_ret= prod-1
replace buy_hold_ret=. if count<6
drop prod ret count

[Rochelle Zhang]

Thank you Andrew, You are very helpful !!!

Your code works for my situation. I just want to make two comments/maybe questions.

1. my data is organized by firmid year and month,

bysort firmid year : gen prod = sum(ln(ret)) when I first test the above line with my data, I thought all months in the same year, will have the same prod value, I am surprised to see that

prod actually is the sum of all previous including current month ln(ret) value, i.e., for month 2 , prod = ln(ret from month 1) + prod = ln(ret from month 1), I guess this is my misunderstanding of the bysort function.


2. If my initial data in post #1 has missing values for return for a single month, for example for the first observation, if I change it to :

1000 1970 .

should I delete those months with missing returns in order to correctly count the number of non-missing returns for each year using your code

bys firmid year: gen count=_N

[Andrew Musau]

Hi Rochelle

Regarding 2), if you have missing returns for some months, you do not necessarily have to delete those months if you want a count of non missing. You could just change the way you count. Here is one way:

gen total=.
bys firmid year: replace total= cond(!missing(return), 1, 0)
bys firmid year: egen count = total(total)
drop total

I am not sure that I completely understand 1), but I will attempt to provide an answer. Since Stata has the sum function, we are using the property that the logarithm of a product is the sum of the logarithms of the factors (hence the summation). Grouping by fitmid and year will give you the same prod value for the same firm in a given year. However, if you are thinking in terms of an investor who holds a portfolio of stocks and you want the annual return of that investor (same prod value for each year), you can compute an average return across all firms for a given year. There are a few complications with this, however.

1) You need to decide what weight you give to each firm (if a portfolio, this should correspond to the holdings of the investor).
2) Each stock does not exactly have the same investment horizon (12 months), so you should look for a criteria to adjust the average to take into account this.

[Rochelle Zhang]

Thanks again for your response !!!

I completely understand it now.

Regards,
Rochelle