turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- How to calculate annual compounding returns based ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-14-2016 11:11 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to user24feb

03-14-2016 12:29 PM

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mig7126

03-14-2016 11:54 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to user24feb

03-14-2016 12:29 PM

Thank you for the program - that is very helpful!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mig7126

03-14-2016 12:34 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mig7126

03-14-2016 12:35 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mig7126

03-14-2016 12:38 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mig7126

03-14-2016 07:37 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

03-14-2016 09:30 PM

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,