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)