DATA Step, Macro, Functions and more

Annual buy and hold return computation from monthly return

Reply
Contributor
Posts: 26

Annual buy and hold return computation from monthly return

Hi,

 

I would like to compute annual buy and hold return using monthly return like the following.

annual ruturn = (1+January return)(1+February return)(1+March return)-----(1+December return) - 1

The data structure I have is as follows:

gvkey    year month monthly return

00001   2000    1       0.002

00001   2000    2       0.001

00001   2000    3       0.003

 

Do you have a simple SAS code I can refer using retain statement (without using macro)?

Thank you for your help

 

Super User
Posts: 10,552

Re: Annual buy and hold return computation from monthly return

[ Edited ]

This should do what you want but the value is only going to be for the entire year if the last value in each year corresponds to Dec.

I assumed that since you have a key value that you wanted this for each key and that the data is sorted by gvkey year and month.

If you only want the final value add at the end of the datastep: If Last.year then output;

 

data want;
   set have;
   by gvkey year;
   retain AnReturn;
   if first.year then AnReturn= (1+ MonthReturn);
   else AnReturn= AnReturn*(1+MonthReturn);
run;

However I suspect you are going to run into precision problems (under flow). you may need to consider an approach with logs or shift ot integer arithmetic and shift back.

Contributor
Posts: 26

Re: Annual buy and hold return computation from monthly return

Thank you for your kind response

It will be grateful if you can provide further guidance regarding how to obtain annual holding return in three months after fiscal year-end. How can I revise your code that produces annual buy and hold return using monthly return.

For instance, if fiscal year-end is 12/31/2014,

beginning date (begdate): 04/01/2014
ending date (enddate)? : 03/31/2015
Thanks again for your help


Ask a Question
Discussion stats
  • 2 replies
  • 442 views
  • 1 like
  • 2 in conversation