DATA Step, Macro, Functions and more

Calculating Balance Run Down

Reply
Occasional Contributor
Posts: 9

Calculating Balance Run Down

I have a dataset that contains multiple rows per account.  The number of rows = the number of months since an account has been open, with the first row being zero.


The table shows how the balance has changed each row (month).  I would like to calculation what the expected balance change should have been had the customer kept to their agreed schedule of payments. 

 

For the first row (where AgeInMonths = 0), the ExpectedBalance should always be equal to the InitialLoanAmount.

For subsequent rows, I need to work out what the ExpectedBalance was for the row before and apply an Interest & Repayment calculation to that value to come up with that months ExpectedBalance. 

I need to continue doing this until i get to a new AccountNumber where I should start again.

 

Does anyone know how I would do this?  

Thanks

Jamie

Super User
Posts: 5,516

Re: Calculating Balance Run Down

Making some reasonable guesses about what your variable names are and what formulas should be applied:

 

data want;

set have;

by AccountNumber;

retain Expected_Balance;

if first.AccountNumber then Expected_Balance = InitialLoanAmount;

else do;

   Expected_Change = Expected_Balance * (1 + Interest) - Repayment;

   Expected_Balance = Expected_Balance + Expected_Change;

end;

run;

 

This program is obviously untested, and assumes that your data set is sorted in order by AccountNumber AgeinMonths.

  

Ask a Question
Discussion stats
  • 1 reply
  • 127 views
  • 0 likes
  • 2 in conversation