How to calculate annual compounding returns based on monthly return data
Posted: 24 October 2015 12:31 AM   [ Ignore ]
Newbie
Rank
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 ]
Administrator
Avatar
RankRankRankRank
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=withKeyby 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 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile