Calcite | Level 5

## Compound inteterest

Good day,  I am having to calculate compound Interest.  Below is an example of the data.  I need to calculate the interest for each month and for the year.  I know the term of the loan is 20 months.   I am not sure to do a first.year or in a do loop?  Any suggestions?

 customer payment interest year month week xxx 142.5 9.00% 2016 1602 1608 xxx 142.5 10.25% 2016 1603 1612 xxx 142.5 10.25% 2016 1604 1616 xxx 142.5 10.50% 2016 1605 1621 xxx 142.5 10.50% 2016 1606 1625 xxx 142.5 10.50% 2016 1607 1629 xxx 142.5 10.50% 2016 1608 1634 xxx 142.5 10.50% 2016 1609 1638 xxx 142.5 10.50% 2016 1610 1642 xxx 142.5 10.50% 2016 1611 1647 xxx 142.5 10.50% 2016 1612 1651 xxx 142.5 10.50% 2017 1701 1703 xxx 142.5 10.50% 2017 1704 1714 xxx 142.5 10.50% 2017 1704 1714 xxx 142.5 10.50% 2017 1704 1715 xxx 142.5 10.50% 2017 1705 1720 xxx 142.5 10.50% 2017 1706 1724 xxx 142.5 10.50% 2017 1707 1728 xxx 142.5 10.50% 2017 1708 1733 xxx 142.5 10.25% 2017 1709 1737
11 REPLIES 11
Calcite | Level 5

## Re: Compound inteterest

Below is my example in excel.  I need to replicate this in SAS.  I have +4000 customers to run this program through.

 2 2/29/2016 29 9.00% 142.5 1.02 3 3/31/2016 31 10.25% 142.5 2.48 4 4/30/2016 30 10.25% 142.5 3.60 5 5/31/2016 31 10.50% 142.5 5.08 6 6/30/2016 30 10.50% 142.5 6.15 7 7/31/2016 31 10.50% 142.5 7.62 8 8/31/2016 31 10.50% 142.50 8.90 9 9/30/2016 30 10.50% 142.5 9.84 10 10/31/2016 31 10.50% 142.5 11.44 11 11/30/2016 30 10.50% 142.5 12.30 12 12/31/2016 31 10.50% 142.5 13.98 1,649.91 1 1/31/2017 31 10.50% 142.5 15.98 2 2/28/2017 28 10.50% 142.5 15.59 3 3/31/2017 31 10.50% 142.5 18.53 4 4/30/2017 30 10.50% 142.5 19.16 5 5/31/2017 31 10.50% 142.5 21.07 6 6/30/2017 30 10.50% 142.5 21.62 7 7/31/2017 31 10.50% 142.5 23.61 8 8/31/2017 31 10.50% 142.5 24.88 9 9/30/2017 30 10.25% 142.50 24.70 10 10/31/2017 31 10.25% 11 11/30/2017 30 10.25% 12 12/31/2017 31 10.25% 3,117.54
PROC Star

## Re: Compound inteterest

This is a common question in the community. Searching on COMPOUND will likely get you your answer, Check this post out which includes the COMPOUND function:

https://communities.sas.com/t5/SAS-Programming/calculate-amount-compounded-annually-amp-compounded-m...

Calcite | Level 5

## Re: Compound inteterest

SASKIWI I have had a look at that solution and it does not work for what I am trying to do.

data data2 ;

set data1;

by customer year month ;

interest = interestrate/1; /*to create a fraction*/

do until (last.year);

if first.year then

balance = compound(payment, . , interest, days);

output;

end;

run;

Ammonite | Level 13

## Re: Compound inteterest

with your question concerning inters rate don't you also have to take into account both dates for related to when accruals happen and if the payment is made at the being of the month or after the 15th?  and if there are additional payments made with the normal payment.

Calcite | Level 5

## Re: Compound inteterest

VDD we are doing a simple compound interest.  We where going to look at when interest changes in the month but decided not to.  this example I gave is a term of 20 mth .  I have 1 that is just 2 months.

Super User

## Re: Compound inteterest

Please clarify your question. Post a small example of exactly what your input data is and exactly what you expect as output. Your second post doesn't have column headers so I would have to make a lot of assumptions. Post data as text, not as an attachment please.
Calcite | Level 5

## Re: Compound inteterest

Hi Reeza,

Customer X pays R142.50 for 20 mths.  Start date is Feb2016 and end date is Nov2017.  His interest rate changes once in the 20 months from 9% to 10.25%.    In the first year I can work out in excel that his interest is 82.41 and the balance is 1641.91.  By Nov2017 using the closing balance of 2016 I get a compound balance of 3117.94.  I need to replicate this in SAS as I have over 4000 customers with varying begin and end dates.

In my second post the columns are month, date in the mth, interest rate, payment, interest earned, closing balance.

Hope this helps and hope you can help me please.

Ammonite | Level 13

## Re: Compound inteterest

you need to include loan amount in your sample unless I missed that.

Super User

## Re: Compound inteterest

Post a small example of exactly what your input data is and exactly what you expect as output. Your second post doesn't have column headers so I would have to make a lot of assumptions. Post data as text, not as an attachment please.What you've posted earlier is unclear, and you're asking me to spend time trying to string together several posts. Assume you're starting from scratch.

In general, you likely only need to use RETAIN and a data step. Fo the different accounts you need to reset but that's relatively trivial.

This is likely what you need, replacing the variable names to match yours. Note that the variable being RETAINed should be a new variable. If it already exists on the dataset you'll get unexpected behaviour.

Good Luck.

``````data want;
set have;

by customer;

retain balance; *keeps balance value across rows;

if first.customer then balance=0; *set balance to 0 for first record of each customer;

interest = balance * (1 + interestRate); *calculate interest rate;
balance = sum(balance, interest);*calculate balance;

run;

``````
Calcite | Level 5

## Re: Compound inteterest

customer  year month days payment interest  calc_int  Yr_bal

x              2016  01        31      0                                         0

x              2016 02        28     142.5      9%         1.02

X              2016 03       31     142.50   10.25     2.48

.

x               2016 12        31     142.50   10.25    13.98  1649.91

x                2017 01      30      142.40   10.25    15.98

.

.

x               2017 09         30     142.50  10.25       24.70  3117.54

for year 2017

Feb2017 calc_int =  previous yr balance+previous mth payment+current mth payment*interest*days/365;

Mar2017 cal int   = previous yr balance+ prev 3 mths payments*interest*days/365;

End 2017 yr_bal = previous yr balance+calc int+ payment

Jan2018 calc int - previous yr balance+current mth payment*interest*days/365;

Feb 2018 calc int = previous yr balance+ prev 3 mths payments*interest*days/365;

End 2018 yr_bal = previous yr balance+calc int+ payment for 2018

Super User

## Re: Compound inteterest

You can use LAG(), LAG1(), LAG2(), LAG3() to retrieve the previous values as needed.
Discussion stats
• 11 replies
• 1608 views
• 3 likes
• 4 in conversation