How to calculate the very last payment when repaying a debt?

Question

Suppose I'm repaying a fixed amount every month on a debt such as a credit card debt.

Generally, the very last payment will be less than the others, because the total owed will be less than the fixed payment aamount.

For example, if I pay $100 a month on a $300 loan with 12% APR, the fourth payment will be much smaller (about $6.14) because the loan has not accumulated $100 in interest over 3 months.

When calculating the number of periods it will take to repay such a loan, the result will not be a whole number (for example using NPER in excel with the example above give 3.0611 as the number of months required to pay off the loan).

What is the easiest way to calculate the last payment of a loan in such cases?

Answer 1

We can use the formula for the FV for the months where we pay the full amount, then calculate the interest on that to calculate the last payment.

In excel, that would be

= FV( rate, floor(nper, 1), pmt, pv) * (1 + rate)

For the example above, that would be

= FV( 0.01, floor(3.0611,1), -100, 300, 0) * (1 + 0.01)

which gives $6.14 as expected.

Answer 2

What is the easiest way to calculate the last payment of a loan in such cases?

Work out an amortization table in Excel. You need five columns:

Month    Pmt    Int    Prin    Bal

And work out how much interest accrues for each payment. It will be the monthly rate (12%/12 or 1% in your case) times the previous principal amount:

Month    Pmt    Int              Prin        Bal
0       0      0                (Pmt - Int)  300
1       100    (Bal[-1] * 1%)   (Pmt - Int)  Bal[-1] - Prin
2       100    (Bal[-1] * 1%)   (Pmt - Int)  Bal[-1] - Prin
...

Note that Bal[-1] means the Bal value from the previous row

Copy down until the Balance is zero or negative. The last payment will be the last non-zero Balance (the balance from the previous row) plus the interest accrued. If you are comfortable with Excel you could use MIN and MAX functions to make sure the balance never goes negative and calculates the last payment amount automatically.

In your case, the last payment amount should be $6.14 as you indicated.

You could also search online for pre-built amortization tables.

Answer 3

OP: What is the easiest way to calculate the last payment of a loan in such cases?

This method does not require Excel functions or an amortisation table.

As explained below, it makes use of 2 formulae:-

n = log(d/(d - r s))/log(1 + r)  . . .  finds the number of months

b = (d + (1 + r)^x (r s - d))/r  . . .  finds the balance

---

Last payment in 4 steps: find r, n, b & b(1 + r) as follows:-

Given the APR as 12% nominal compounded monthly find the monthly rate

r = 12%/12 = 1%

So with

monthly rate  r = 0.01
principal     s = 300
payment amt.  d = 100

Find how long the loan will take to pay down.

With n being the number of periods, the general equation for a loan is

from which an expression for n can be obtained

n = log(d/(d - r s))/log(1 + r)

∴ n = 3.06113

Disregarding the fractional month for the moment . . .

Derived from the following recurrence equation

b[x + 1] = b[x](1 + r) - d   where   b[0] = s

the balance b after x months is given by

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

so with x = 3 the balance b = 6.0803

If the final payment is made one month later the amount to pay will be

b(1 + r) = $6.14