DATA Step, Macro, Functions and more

How to calculate compounded return

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

How to calculate compounded return

* a) CAR1 (3-day window);
proc means data=sample noprint;
   *define the even window and check that the start date is not far from report date;
   where -1<=td_count<=1 and intck('day',rdq,date)<=5;
   by permno rdq;
   var ret;
   output out=CAR1 (rename=(_freq_=daysCAR1)) sum=CAR1;
run;

* b) CAR2 (between consequent quarterly earnings announcement dates);
proc means data=sample noprint;
   where 3<=td_count and date<=leadrdq1;
   by permno rdq;
   var ret;
   output out=CAR2 (rename=(_freq_=daysCAR2)) sum=CAR2;
run;

I am trying to calculate returns around earnings announcement dates. The code above calculates summed returns. Does anybody know how to calculate compounded returns?


Accepted Solutions
Solution
‎01-10-2018 07:53 PM
Frequent Contributor
Posts: 76

Re: How to calculate compounded return

[ Edited ]

Thanks do you think my code is correct. I am trying to calculate compounded returns between earnings announcement dates. I am just curious how the code treats the 0 returns. These are not missing values. 

proc univariate data=have2 noprint;
  where 3<=td_count and date<=leadrdq1;
  by permno rdq;
  var _ret_plus1;
  output out=need2 n=n geomean=gm_ret_plus1;
run;

data Ret2;
  set need2;
  compound_ret=gm_ret_plus1**n-1;
run; 

 

View solution in original post


All Replies
Super User
Posts: 23,224

Re: How to calculate compounded return

Posted in reply to Agent1592

It's late so my brain isn't working at the moment but I think there's a trick of some sort using either geometric means or log() of a value.  

Super User
Posts: 23,224

Re: How to calculate compounded return

Posted in reply to Agent1592

It's late so my brain isn't working at the moment but I think there's a trick of some sort using either geometric means or log() of a value.  

PROC Star
Posts: 1,209

Re: How to calculate compounded return

Posted in reply to Agent1592

So rdq is the announcement date right? And td_count represents the days until/since the announcement date?

 

So what does your desired output data set look like?

Frequent Contributor
Posts: 76

Re: How to calculate compounded return

Yes rdq is the announcement date. For the first types of returns CAR1 I am trying to calculate compounded returns around the announcement date (1 day before and 1 day after). -1<=td_count<=1 

For the second types of returns CAR2 I am trying to calculate returns around announcements dates 3 days after the announcement until the following announcement. (3<=td_count and date<=leadrdq1).

Super User
Posts: 10,679

Re: How to calculate compounded return

Posted in reply to Agent1592

Check function  FINANCE() ,maybe you could find answer in it.

Trusted Advisor
Posts: 1,309

Re: How to calculate compounded return

[ Edited ]
Posted in reply to Agent1592

Since proc univariate can produce a geometric mean, it can help generate a compound return:

 

data have2;
  set have;
  _ret_plus1=ret+1;
run;

proc univariate data=have2 noprint;
  where -1<=td_count<=1 and intck('day',rdq,date)<=5;
  by permno rdq;
  var _ret_plus1;
  output out=need n=n geomean=gm_ret_plus1;
run;

data want;
  set need;
  compound_ret=gm_ret_plus1**n-1;
run; 
Frequent Contributor
Posts: 76

Re: How to calculate compounded return

Thank you for the proposed solution. This is excellent. Do you know why there is a column called number of non-missing values. Why is it set to 3? Is it from the univarariate proc?

Trusted Advisor
Posts: 1,309

Re: How to calculate compounded return

Posted in reply to Agent1592

You need to know the number of non-missing values of _RET_PLUS1 because the geometric mean of _RET_PLUS1 is the constant value that would be required on each day to get to the compound return of the actual varying values of RET.   So you need to know how many days were used to get the geometric mean, which you see used  in may DATA WANT step.

 

Now perhaps you always know the number of days satisfying your WHERE filter, but my solution adapts to instances of missing records within the date ranges.

Solution
‎01-10-2018 07:53 PM
Frequent Contributor
Posts: 76

Re: How to calculate compounded return

[ Edited ]

Thanks do you think my code is correct. I am trying to calculate compounded returns between earnings announcement dates. I am just curious how the code treats the 0 returns. These are not missing values. 

proc univariate data=have2 noprint;
  where 3<=td_count and date<=leadrdq1;
  by permno rdq;
  var _ret_plus1;
  output out=need2 n=n geomean=gm_ret_plus1;
run;

data Ret2;
  set need2;
  compound_ret=gm_ret_plus1**n-1;
run; 

 

Trusted Advisor
Posts: 1,309

Re: How to calculate compounded return

Posted in reply to Agent1592

You have asked a good question, which you can answer by looking at some data with zero returns.  It's your turn now, to not just  use the code I suggested, but to understand it through testing.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 272 views
  • 4 likes
  • 5 in conversation