1

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:

P(1+r/n)^(nt)

Future value of a series:

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

Total:

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

Where

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.

3
  • 2
    (This is why you learn Algebra in high school.)
    – RonJohn
    Jul 25 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 at 9:27
  • Perhaps this answer will help : What is the formula for loan payoff date? Jul 25 at 10:32
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

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

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.