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

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

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

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; ... View more

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