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

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?

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.

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.

• Yes. There's a formula, and I've worked it out a few times, but with spreadsheets today, it's simpler to just let the spreadsheet calculate all the payments and see what's left over at the end.
– Jay
Commented Apr 27 at 16:46

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

$s=\sum_{k=1}^{n}\frac{d}{(1+r)^k}=\frac{d-d(r+1)^{-n}}{r}$

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
``````