topic Re: Annual buy and hold return computation from monthly return in SAS Programming

Annual buy and hold return computation from monthly return

kimx0961 — Fri, 02 Sep 2016 18:48:40 GMT

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

Re: Annual buy and hold return computation from monthly return

ballardw — Fri, 02 Sep 2016 19:16:32 GMT

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.

Re: Annual buy and hold return computation from monthly return

kimx0961 — Mon, 05 Sep 2016 17:08:25 GMT

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

Re: Annual buy and hold return computation from monthly return

xiaobao — Thu, 25 Jun 2020 18:56:21 GMT

data want;
set have;
ln_return = log(monthlyreturn+1);
run;

proc sort data = want by permno year month;

proc means data = have noprint;
by gvkey year;
var ln_return;
output out = want1 sum = ln_annreturn;
annreturn = exp(ln_annreturn) - 1;
run;

Re: Annual buy and hold return computation from monthly return

mkeintz — Fri, 26 Jun 2020 04:47:46 GMT

If YEAR designates fiscal year, then you want to accumulate up through the 3rd record following the last month record for a given year, as in:

data bhret;
  set have;
  by gvkey year;
  bhret+log(1+ret);
  if lag3(last.year);
  bhret=exp(bhret)-1;
  output;
  call missing bhret;
run;

Note this assumes that there are no missing monthly returns.

But maybe (you're using Compustat data right?) you have a variable FYR indicating the month number for the end of fiscal year (i.e. if FYR=6 then fiscal year ends in June). If so, then

data bhret;
  set have;
  by gvkey;
  bhret+log(1+ret);
  if lag3(month)=fyr;
  bhret=exp(bhret)-1;
  output;
  call missing bhret;
run;

This also assume no missing months.

A couple notes:

The "bhret+log(1+ret);" is a "summing statement", which tells sas to retain the resulting value in variable bhret. I.e. don't automatically reset bhret to missing.

The "if lag3(...)" statement is a subsetting if, keeping only those records in which the 3rd preceding record satisfy the specified condition.

One a record is output, the programmer now needs to reset the bhret to missing, so every record has only results built on record following the previous annual return.