I am trying to find how to work out the period of time it will take to pay off a loan. There are plenty of online calculators out there but I cannot find the formula they use. I want to recreate the formulas in excel.

I have found this site which gives variations of the compound interest formula. It states

In order to work out calculations involving monthly additions, you will need to use two formulae - our original one, listed above, plus the 'future value of a series' formula for the monthly additions.

Giving the formulas (for contributions at the end of a month) as;

Compound interest for principal:


Future value of a series:

PMT × (((1 + r/n)^(nt) - 1) / (r/n))


(P(1+r/n)^(nt)) + (PMT × (((1 + r/n)^(nt) - 1) / (r/n)))


A   =  the future value of the investment/loan, including interest
P   =  the principal investment amount (the initial deposit or loan amount)
PMT =  the monthly payment
r   =  the annual interest rate (decimal)
n   =  the number of times that interest is compounded per unit t
t   =  the time (months, years, etc) the money is invested or borrowed for

I have the principle, the annual interest rate and the monthly payment. I assume n, the number of times that the interest is compounded is 12, for months in the year (online calculators seem to support this).

I worked out the 'future debt' using this formula and if I manually change the time, I can tell that for example;

P   = -20000
r   = 1.1
PMT = 400

that the time t to pay off the loan would be roughly 4 years, 3 months (4.25).

However, this is pretty fiddly having to change the time in two places in the formula and 'eyeballing' when the future debt (total) is close to zero.

I believe I would like a variation of the total formula above to be something like

t = ...

Can someone rearrange the formula for this? (or provide the correct one)

I have checked a number of questions but most just want to find out the future value using a fixed time.

  • 2
    (This is why you learn Algebra in high school.)
    – RonJohn
    Jul 25, 2021 at 9:22
  • 1
    To compute the number of periods needed to pay off a loan, given an interest rate, payment amount, starting balance and ending balance, use the =NPER() function.
    – RonJohn
    Jul 25, 2021 at 9:27
  • Perhaps this answer will help : What is the formula for loan payoff date? Jul 25, 2021 at 10:32

1 Answer 1


In your formula ("Compound interest for principal"), P(1+r/n)^(nt) - it is implied that r is a nominal interest rate compounded according to the frequency n per annum. So a specific nominal rate for the particular compounding frequency, rather than an effective annual rate.

The future value of a series with deposits made at the end of each period is obtained by summing multiple versions of the above formula (with PMT instead of P):

enter image description here

The formula for a loan with payments at the end of each period can be obtained similarly. Instead of compounding forward to a future value it discounts the payments back to the present (initial) value of the loan: s. This formula can be expressed for t, the number of years to pay off the loan:

enter image description here

For example, with 1.1% nominal interest compounded monthly

s = 20000
r = 0.011
n = 12
PMT = 400

∴ t = -(Log[1 - (r s)/(n PMT)]/(n Log[(n + r)/n])) = 4.26713 years

Also in Excel, as suggested by RonJohn, documented here, the same result:

enter image description here

  • Thank you, in libreoffice calc the formula is =NPER(C6/12,C7,-C5,,0)
    – myol
    Jul 25, 2021 at 14:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .