How to calculate annual compounding returns based on monthly return data starting with June month

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

How to calculate annual compounding returns based on monthly return data starting with June month

Hello,

 

I am trying to calculate annual compounding returns by stock based on monthly return data.  However, the calculation that I need to do for annual return does not follow the regular calendar interval (Jan - Dec); the annual return calculation is from July - June.  For example, I want to calculate the annual return from July 31, 1980 to June 30, 1981.  Right now, here is an excerpt of the data set:

 

CUSIPDATERET
138171019800630-0.01468
1381710198007310.188085
138171019800829-0.01812
1381710198009300.070111
138171019801031-0.06655
138171019801128-0.03178
138171019801231-0.07915
1381710198101300.02935
1381710198102270.106314
1381710198103310.044776
138171019810430-0.03571
138171019810529-0.0163
138171019810630-0.0916
138171019810731-0.05042
138171019810831-0.02832
138171019810930-0.02778
138171019811030-0.06381
1381710198111300.093264
138171019811231-0.02844
138171019820129-0.10244
1381710198202260.041304
1381710198203310.047872
1381710198204300.015228
138171019820528-0.032
138171019820630-0.04211
2605431019800630-0.00657
26054310198007310.085502
2605431019800829-0.04452
2605431019800930-0.00645
2605431019801031-0.0511
26054310198011280.061538
2605431019801231-0.0558
26054310198101300.066148
26054310198102270.076642
26054310198103310.049492
2605431019810430-0.0915
2605431019810529-0.07194
26054310198106300.041085
2605431019810731-0.0566
2605431019810831-0.12
2605431019810930-0.10182
26054310198110300.06701
26054310198111300.009662
26054310198112310.02201
2605431019820129-0.07619
2605431019820226-0.10825
26054310198203310.095954
2605431019820430-0.02151
2605431019820528-0.05495
2605431019820630-0.01395

 

What I would like to see is something like the following:

 

CUSIP             DATE             ANNUAL RET

1381710         19810630         annual cumulative return from 19800731 to 19810630

1381710         19820630         annual cumulative return from 19810731 to 19820630

2604310         19810630         annual cumulative return from 19810731 to 19820630

2604310         19820630         annual cumulative return from 19800731 to 19810630

..

 

Can you please help me with the SAS Code to do this?

 

Thanks!

 

 

 

 

 

               


Accepted Solutions
Solution
‎03-14-2016 12:30 PM
Occasional Contributor
Posts: 14

Re: How to calculate annual compounding returns based on monthly return data starting with June mont

Posted in reply to user24feb

Thank you for the program - that is very helpful!

 

 

View solution in original post


All Replies
Super Contributor
Posts: 340

Re: How to calculate annual compounding returns based on monthly return data starting with June mont

Try:

 

Data Have;
  Input CUSIP DATE YYMMDD8. RET;
  Format Date Date9.;
  Datalines;
1381710 19800630 -0.01468 
1381710 19800731 0.188085 
1381710 19800829 -0.01812 
1381710 19800930 0.070111 
1381710 19801031 -0.06655 
1381710 19801128 -0.03178 
1381710 19801231 -0.07915 
1381710 19810130 0.02935 
1381710 19810227 0.106314 
1381710 19810331 0.044776 
1381710 19810430 -0.03571 
1381710 19810529 -0.0163 
1381710 19810630 -0.0916 
1381710 19810731 -0.05042 
1381710 19810831 -0.02832 
1381710 19810930 -0.02778 
1381710 19811030 -0.06381 
1381710 19811130 0.093264 
1381710 19811231 -0.02844 
1381710 19820129 -0.10244 
1381710 19820226 0.041304 
1381710 19820331 0.047872 
1381710 19820430 0.015228 
1381710 19820528 -0.032 
1381710 19820630 -0.04211 
26054310 19800630 -0.00657 
26054310 19800731 0.085502 
26054310 19800829 -0.04452 
26054310 19800930 -0.00645 
26054310 19801031 -0.0511 
26054310 19801128 0.061538 
26054310 19801231 -0.0558 
26054310 19810130 0.066148 
26054310 19810227 0.076642 
26054310 19810331 0.049492 
26054310 19810430 -0.0915 
26054310 19810529 -0.07194 
26054310 19810630 0.041085 
26054310 19810731 -0.0566 
26054310 19810831 -0.12 
26054310 19810930 -0.10182 
26054310 19811030 0.06701 
26054310 19811130 0.009662 
26054310 19811231 0.02201 
26054310 19820129 -0.07619 
26054310 19820226 -0.10825 
26054310 19820331 0.095954 
26054310 19820430 -0.02151 
26054310 19820528 -0.05495 
26054310 19820630 -0.01395 
;
Run;

Data Want (Where=(Month(DATE) eq 6 & Cum_RET ne -1) Drop=dummy); 
  Set Have;
  Retain dummy;
  By CUSIP;
  If First.CUSIP Then dummy=0; * allows only full years;
  If Month(DATE) eq 7 Then dummy=1; * start in July;
  dummy=(1+RET)*dummy;
  Cum_Ret=dummy-1;
Run;
Solution
‎03-14-2016 12:30 PM
Occasional Contributor
Posts: 14

Re: How to calculate annual compounding returns based on monthly return data starting with June mont

Posted in reply to user24feb

Thank you for the program - that is very helpful!

 

 

Super User
Posts: 19,770

Re: How to calculate annual compounding returns based on monthly return data starting with June mont

 

@mig7126 Please mark the answer helpful, not your comment.

Thanks!

Occasional Contributor
Posts: 14

Re: How to calculate annual compounding returns based on monthly return data starting with June mont

Hi,

 

Now that I have the annual return data by stock and date (July - June annual interval), I want to do the following:

 

- For each stock and each date, I want to calculate a 3-year compounded return for the previous 3 years (call it Formation return) and a 3-year compounded return for the next 3 years (call it Evaluation return).

 

How do I do it in SAS?

 

Thanks! 

Super User
Posts: 19,770

Re: How to calculate annual compounding returns based on monthly return data starting with June mont


mig7126 wrote:

Hi,

 

Now that I have the annual return data by stock and date (July - June annual interval), I want to do the following:

 

- For each stock and each date, I want to calculate a 3-year compounded return for the previous 3 years (call it Formation return) and a 3-year compounded return for the next 3 years (call it Evaluation return).

 

How do I do it in SAS?

 

Thanks! 


Please post your new question as a new thread.

Super User
Posts: 11,343

Re: How to calculate annual compounding returns based on monthly return data starting with June mont

You may want to double check some of the monthly cumulative values by. I think that the repeated multiplication addition and subtraction may be running into precision issues. The solution shown by @user24feb yields a cumulative value of 0.1665568998 for Aug 1980 but a simple comparison of 0.188085 + ( -0.01812) is more like 0.169965. Repeated addition adds to the round off error. Noticeably also you should not have more than 6 significant decimals as that is the most in your data.

 

 

Occasional Contributor
Posts: 14

Re: How to calculate annual compounding returns based on monthly return data starting with June mont

Thanks for your reply, and pointing that out.  The calculation for cumulative return takes into account compounding effects, so you cannot do a simple arithmetic addition.  Therefore, the calculation should be as follows:  CUM_RET = ((1+ret1)*(1+ret2)*....(1+retn))-1.

 

Thanks,

 

 

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 539 views
  • 0 likes
  • 4 in conversation