Compound inteterest

[JJ71]

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

[JJ71]

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

[SASKiwi]

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...

[JJ71]

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;

[VDD]

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.

[JJ71]

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.

[Reeza]

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.

[JJ71]

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.

[VDD]

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

[Reeza]

Make it easy for us to help you. 

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;

[JJ71]

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

[Reeza]

You can use LAG(), LAG1(), LAG2(), LAG3() to retrieve the previous values as needed.