Search

How to calculate annual compounding returns based on monthly return data
 Posted: 24 October 2015 12:31 AM [ Ignore ]
Newbie
Total Posts:  14
Joined  2014-11-08

Dear Joost,

My question is the same as the following one.

Currently I have multiple years’ data for multiple firms of the monthly stock returns.
Firm_ID year month return
101 2002 1 0.05
...
101 2014 12 0.03
102 2003 1 0.035
...
102 2007 12 0.045
.....
205 2004 1 0.065
...
If I calculate it by hand then:
annual compounding return=(1+ Ret_month1)*(1+Ret_month2)*(1+Ret_month3)*...*(1+Ret_month11)*(1+Ret_month12)

I found an answer in communities.sas.com as follows:

``` data want;     do until (last.year);           set have;           by firm_id year;           if first.year then                compound_ret=1;           compound_ret=compound_ret*(1+return);     end;run;  ```

I cannot understand the seventh code. I think the code should be: compound_ret=lag(compound_ret) * (1+return).

No matter which code I use, the compound_ret is lagger than the result by summing up these monthly return.

Could you help me? Thank you.

Regards,
Emily

 Profile

 Posted: 24 October 2015 07:32 AM [ Ignore ]   [ # 1 ]
Total Posts:  901
Joined  2011-09-19

hi Emily,

I prefer to use ‘retain’ and compound returns this way:

``` /* can be done without this 'key' variable, but it makes it more transparent what is happening */data withKey;set dsIn;key = firm_id || year; /* create unique id for each firm-year */run;/* next data step with 'by' needs sorting */proc sort data=withKey; by key;run;/* calculate the cumulative pre announcement return, only keep the last record for each 'key';*/data computeRet;set withKey;by key;retain cumRet; /* this will 'remember' cumRet over the rows */if first.key then cumRet = 1; /* new firmyear: reset cumulative return */cumRet = cumRet* (1+ret);if last.key then output; /* only keep last row for each firmyear */run;  ```

Best,

Joost

Signature