0
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ Why aren't you stating what your formula is in the question? $\endgroup$ – John Jul 3 at 17:43
  • $\begingroup$ i am going to put the answer and derivation logic in the answer. as per guidelines, it is the preferred method $\endgroup$ – solver149 Jul 3 at 17:54
  • $\begingroup$ @John - do you mind giving your inputs on the below formula $\endgroup$ – solver149 Jul 9 at 21:56
0
$\begingroup$

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

$\endgroup$

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.