BookmarkSubscribeRSS Feed
JJ71
Calcite | Level 5

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?

customerpaymentinterestyearmonthweek
xxx142.59.00%201616021608
xxx142.510.25%201616031612
xxx142.510.25%201616041616
xxx142.510.50%201616051621
xxx142.510.50%201616061625
xxx142.510.50%201616071629
xxx142.510.50%201616081634
xxx142.510.50%201616091638
xxx142.510.50%201616101642
xxx142.510.50%201616111647
xxx142.510.50%201616121651
xxx142.510.50%201717011703
xxx142.510.50%201717041714
xxx142.510.50%201717041714
xxx142.510.50%201717041715
xxx142.510.50%201717051720
xxx142.510.50%201717061724
xxx142.510.50%201717071728
xxx142.510.50%201717081733
xxx142.510.25%201717091737
11 REPLIES 11
JJ71
Calcite | Level 5

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

22/29/2016           299.00%142.5               1.02 
33/31/2016           3110.25%142.5               2.48 
44/30/2016           3010.25%142.5               3.60 
55/31/2016           3110.50%142.5               5.08 
66/30/2016           3010.50%142.5               6.15 
77/31/2016           3110.50%142.5               7.62 
88/31/2016           3110.50%       142.50               8.90 
99/30/2016           3010.50%142.5               9.84 
1010/31/2016           3110.50%142.5             11.44 
1111/30/2016           3010.50%142.5             12.30 
1212/31/2016           3110.50%142.5             13.98         1,649.91
11/31/2017           3110.50%142.5             15.98 
22/28/2017           2810.50%142.5             15.59 
33/31/2017           3110.50%142.5             18.53 
44/30/2017           3010.50%142.5             19.16 
55/31/2017           3110.50%142.5             21.07 
66/30/2017           3010.50%142.5             21.62 
77/31/2017           3110.50%142.5             23.61 
88/31/2017           3110.50%142.5             24.88 
99/30/2017           3010.25%       142.50             24.70 
1010/31/2017           3110.25%   
1111/30/2017           3010.25%   
1212/31/2017           3110.25%           3,117.54
SASKiwi
PROC Star

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
Calcite | Level 5

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
Ammonite | Level 13 VDD
Ammonite | Level 13

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
Calcite | Level 5

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
Super User
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
Calcite | Level 5

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
Ammonite | Level 13 VDD
Ammonite | Level 13

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

Reeza
Super User

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
Calcite | Level 5

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
Super User
You can use LAG(), LAG1(), LAG2(), LAG3() to retrieve the previous values as needed.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 11 replies
  • 1608 views
  • 3 likes
  • 4 in conversation