Number of Installment terms remaining

Question

Consider an installment loan with the following details:

Principal = $ 6,000
Rate = 9.99%
Number of Terms = 60
Calculate monthly payment = $ 127.45

If the customer keeps making an equal payment of $ 127.45 every month, the loan is paid off in 60 months. But the customer can keep making extra payments for couple of months. For example, consider the customer making a total payment of $ 200 for month 3 and $ 350 for month 7. Due to this, the actual term the installment loan gets paid off will get reduced.

I am aware of methods that can iterate over the amortization schedule and calculate the remaining terms. I am looking for mathematical formula that is available to calculating remaining terms.

Answer 1

Since I could not find anything, following is the formula I derived by hand. But not sure about the drawbacks and or mistakes. Any opinion by the experts is welcome.

P - Initial Principal
r - rate for term. monthly rate or yearly rate based on term duration
n - total terms scheduled 
X - any additional payment over and above minimum payments summed together
E - equal monthly installment payment

First step is to understand the monthly payment derivation logic.

$P_0 = P$

$P_1 = P_0 + (P_0\ *\ r)\ -\ E$

$\ \ \ \ \ = P(1 +r)\ -\ E$

$\ \ \ \ = Pt - E $

Substitute t = 1 + r

$P_2 = P_1 + P_1r\ -\ E$

$\ \ \ \ = P_1(1 + r)\ -\ E$

$\ \ \ \ = P_1t\ -\ E$

$\ \ \ \ = (Pt\ -\ E) * t\ -\ E$

$\ \ \ \ = Pt^2\ -\ E(1 + t)$

$P_3 = P_2 + P_2\ *\ t\ -\ E$

$\ \ \ \ = Pt^3 - E(1+t+t^2) $

... extrapolating and generalizing the formula for 'n' terms

$P_n = P_{n-1}*t - E$

$\ \ \ \ = Pt^n -E(1 + t + t^2 + t^3 ... + t^{n-1}) $

$\ \ \ \ = Pt^n -E( \frac{t^n - 1}{t - 1}) $ $

Reverse substitute t = 1 + r 

$$ (Balance\ after\ n\ terms)\ P_n = P (1+r)^n - E\frac{((1+r)^n - 1)}{r}$$

Next step is to calculate the equal monthly installment i.e. 'E'. For this we need to ensure balance after 'n' terms is 0.

$P (1+r)^n - E\frac{((1+r)^n - 1)}{r}\ =\ 0$

$P (1+r)^n \ =\ E\frac{((1+r)^n - 1)}{r}\ $

$$ (Equal\ Monthly\ Installment)E\ =\frac{P\ r\ (1+r)^n}{((1+r)^n-1)} $$

The next question is to find remaining terms when extra payments are made. Whenever an extra payment is made, it is adjusted to the principal. When the principal is 0, installment loan is considered completely paid off.Assume the sum of all the extra payments to be 'X' which is adjusted to principal. Now our objective is to find the 'terms-y' where the principal is amount 'X'

$X = P_y = P(1+r)^y - E\frac{((1+r)^y - 1)}{r} $

$X = P(1+r)^y - E\frac{((1+r)^y - 1)}{r} $

Objective is to calculate 'y' in the above equation. Also, we are not changing the monthly payment amount 'E'. This is already calculated. Hence substitute.

$X = P(1+r)^y - \frac{P(r)(1+r)^n\ *\ ((1+r)^y - 1)}{((1+r)^n-1)(r)} $

$X = P(1+r)^y - \frac{P(1+r)^n\ *\ ((1+r)^y - 1)}{((1+r)^n-1)} $

Substitute t = 1 + r

$X = Pt^y - \frac{Pt^n\ *\ (t^y - 1)}{(t^n-1)} $

$X = \frac{Pt^y * (t^n-1)\ -\ Pt^n\ *\ (t^y - 1)}{(t^n-1)} $

$X = \frac{P\ t^{y+n}\ -\ Pt^y \ -\ Pt^{n+y}\ +\ Pt^n }{(t^n-1)} $

$X\ (t^n -\ 1) = P\ (t^n\ -\ t^y) $

$Xt^n -\ X = Pt^n\ -\ Pt^y $

$Pt^y = Pt^n\ +\ X\ -\ Xt^n $

$Pt^y = Pt^n\ +\ X(1\ -\ t^n) $

$t^y = \frac{Pt^n\ +\ X(1\ -\ t^n)}{P} $

taking log on both sides

$y\ log\ (t) = log\ (Pt^n\ +\ X(1\ -\ t^n))\ -\ log\ (P) $

$y = \frac{log\ (Pt^n\ +\ X(1\ -\ t^n))\ -\ log\ (P)}{log\ (t)} $

again reverse substitute t = 1 + r

$$(Adjusted\ Terms)\ y = \frac{log\ (P(1+r)^n\ +\ X(1\ -\ (1+r)^n))\ -\ log\ (P)}{log\ (1+r)} $$

$$Remaining\ Terms = Adjusted\ Terms\ -\ Current\ Position\ in\ Schedule $$

Answer 2

solver149's answer needs some improvement: There X should be replaced by: xᵃtʸ/tᵃ+xᵇtʸ/tᵇ+ .... ; where xᵃ, xᵇ, .... are the extra payments made at the end of aᵗʰ, bᵗʰ, .... months respectively ( Assuming y is sufficiently large such that y > a, b, .... ).

Explanation: Pₐ = Ptᵃ - E(1 + t + .... + t^(a-1)) - xᵃ; (Principals after Pₐ will be adjusted accordingly i.e. Multiply previous principal by t and subtracting E from it. After that, subtract xʳ (for r = b, c, ...., r < y, only) from it). And thus, we get: P_{y} = Ptʸ - E(1 + t + .... + t^(y-1)) - xᵃtʸ/tᵃ - xᵇtʸ/tᵇ - .... = 0.

He/She is just assuming that X = xᵃ + xᵇ + ...., which is wrong, because all the extra payments are not made together at the end of yᵗʰ month. They are made at their respective months.

Solve to get: y = (logP + n log(1+r) - log(P - (xᵃ/(1+r)ᵃ + xᵇ/(1+r)ᵇ + ...)*(1-(1+r)ⁿ)))/log(1+r)

With a little bit effort, we can show that the 'y' obtained by me is less than the 'y' obtained by solver149. Most of the banking systems use 'y' as obtained by solver149 and hence the customers have to face loss by paying EMI installments for more number of months.