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=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