1

I am trying to resolve an issue I am having where a system calculated incorrect additional payments on a loan for a couple of months, resulting in the new current balance being incorrect. I want to fix the balance but I need to find a way of determining what the original starting balance was, since it wasn't logged anywhere.

I know the formulas for normal amortization but I need to be able to figure out the reverse with the following information:

  • Current Balance
  • Interest Rate
  • Monthly Payment
  • Additional Principal Payment if used.
  • No number of payments or length of loan known.

So knowing that, and if you were to create an amortization spreadsheet, how would you get the previous couple months?

Current formula for regular amortization.

Interest Rate = (X.XX/100)/12

New Balance = Current Balance + ((Minimum Payment - (Current Balance * Interest Rate)) + Additional Payment)

I need the reverse of the above. I've tried subtracting the values from Current Balance and looping it a couple times - I get close to what the original balance may be, but it's off a couple decimals of interest.

Update: Below is an example set of numbers using the normal amortization schedule that does work, and the new set of numbers to determine the original balance. However as you can see I am off. No additional payment in example below.

Normal monthly direction:

Starting Balance: $5000

Interest Rate: 5%

Monthly Payment: $300

After 3 Auto Payments the total is: $4,159.01 (This number is correct and has been verified in spreadsheet).

$Int = (5.0/100)/12;
$Months = 1;
$NewBalance = 5000;
while($autoMonths <= 3){
    $NewBalance = $NewBalance - ((300 - ($NewBalance * $Int)));
    $Months++;
}

Now reverse:

Starting Balance: $4,159.01

Interest Rate: 5%

Monthly Payment: $300

3 Auto Payments BACK, the total is: $5,003.49

$prevBalance = 4159.01;
$prevMonths = 1;
while($prevMonths <= 3){
    $prevBalance = $prevBalance + ((300 - ($prevBalance * $Int)));
    $prevMonths++;
}
  • I think you have a sign wrong in your formula. Obviously if your payment amount increases, your new balance should decrease. I think it should be NB = OB - (P - OB × R). So NB = OB (1 + R) - P, so OB = (NB + P) / (1 + R). [Note, P is Minimum Payment + Additional Payment.] – prl Apr 12 at 23:41
  • You'd need the timing and amount of the additional principal payments. – Hart CO Apr 12 at 23:43
  • Yes, this assumes that the additional payments are made at the same time as the regular payments and that both are made on the due date. – prl Apr 12 at 23:44
  • I will test your formula prl, the one we are using works and matches the amortization results for each continuing month. These are all done one a regular schedule. I have the additional payment too. I'm going to update my question with some example numbers based on what I was originally thinking - then I will test prl's formula tweak. – Travis Johnston Apr 13 at 13:08
1

Given

s = principal
r = periodic rate
d = periodic payment

the balance b remaining in month x is

b = (d + (1 + r)^x (r s - d))/r

Applying your figures

s = 5000
r = 5.0/100/12
d = 300

x = 3
b = (d + (1 + r)^x (r s - d))/r = 4159.01

Starting value, three months back

x = 0
b = (d + (1 + r)^x (r s - d))/r = 5000

Or, in your code

$Int = (5.0/100)/12;
$Months = 1;
$NewBalance = 5000;
while($Months <= 3){
    $NewBalance = ($NewBalance * (1 + $Int)) - 300;
    $Months++;
}

$prevBalance = 4159.01;
$prevMonths = 1;
while($prevMonths <= 3){
    $prevBalance = ($prevBalance + 300) / (1 + $Int);
    $prevMonths++;
}
  • This appears to work!! Need to do some more verification and need to add in an additional payment in some cases to principal. – Travis Johnston Apr 13 at 17:35
  • Wait why would the starting back formula work? You need to apply somewhere in that formula how many times back you are going. – Travis Johnston Apr 15 at 23:30
  • You mean b = (d + (1 + r)^x (r s - d))/r? It is the solution to this difference equation : b(x + 1) = b(x)*(1 + r) - d given b(0) = s. Solved here using Wolfram Alpha. YouTube explainer here. – Chris Degnen Apr 16 at 8:06
  • I mean where you have x = 0 and the same formula getting back to 5000. The code loop works but I'm just curious on the formula - as you would need to enter in the new balance somewhere, and tell it to go back 3 in order to get to 5000. So assigning 4159.01 to s and -3 to x or something. Its not a big deal though. – Travis Johnston Apr 16 at 15:30
  • @TravisJohnston The formula calculates from the principal s, telling you the balance in any month x. If it's any help, if you only have the balance b and d, r & x you can obtain the principal from s = ((1 + r)^-x (b r + d ((1 + r)^x - 1)))/r. Or you can just loop back as in your code. – Chris Degnen Apr 16 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.