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

Solved
Occasional Contributor
Posts: 14

# 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:

 CUSIP DATE RET 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

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!

All Replies
Super Contributor
Posts: 355

## 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: 23,663

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

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: 23,663

## 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!

Super User
Posts: 13,498

## 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 and locked.

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

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