I have a situation where I need to offer third parties an installment plan for paying back a debt. This installment plan has an 8% yearly rate, but with some strict rules.
The situation has 3 interesting values:
- The principal where we can add an 8% yearly rate (but not compound)
- Existing fees which are static (no yearly rates)
- Existing interest calculated
The payment priority is first interest, then fees and in the end, principal.
So in practice, let's say the debtor owes 1.000 USD (principal), 400 USD (fees) and 100 USD (existing interest). The debtor now agrees to a 12-month payment plan. The key is the debtor agrees to a time-period with the same installment every month.
The first couple of months would be only "interest + fees". When fees are paid, "interest + principal". As the principal goes down, the less monthly interest.
MY QUESTION:
I found it pretty easy to make the calculations by selecting a monthly installment. But switching it around and when my input is "months" - and I try to find the monthly installment I get lost.
What formula should I use here?
Let $p_k,f_k,i_k$ be the principal, the fees and the interest right after the payment at the $k$-th month respectively.
Also, let $x$ be the monthly installment, and let $r:=\frac{8}{100}\times\frac{1}{12}$ where we may suppose that $x$ is larger than $p_0r$.
If we have $$i_1=i_0+p_0r-x$$ $$i_2=i_1+p_0r-x$$ $$\vdots$$ $$i_m=i_{m-1}+p_0r-x$$ where $m$ is an integer such that $$i_{m}\ge 0\quad \text{and}\quad i_m+p_0r-x\lt 0$$ then we have $$i_m-i_0=m(p_0r-x)$$ from which $$0\le i_m=i_0+m(p_0r-x)\lt x-p_0r\implies 0\le\frac{i_0}{x-p_0r}-m\lt 1\implies m=\left\lfloor \frac{i_0}{x-p_0r}\right\rfloor$$ follows.
Then, we can see that $$p_m=p_0,\quad f_m=f_0,\quad i_m\ge 0$$ and that $$p_{m+1}=p_0,\quad f_{m+1}=f_m+i_m+p_0r-x$$
Similarly, if we have $$f_{m+2}=f_{m+1}+p_0r-x$$ $$\vdots$$ $$f_{n}=f_{n-1}+p_0r-x$$ where $n$ is an integer such that $$f_n\ge 0\quad \text{and}\quad f_n+p_0r-x\lt 0$$ then we have $$f_n-f_m=i_m+(n-m)(p_0r-x)$$ from which $$0\le f_n=f_m+i_m+(n-m)(p_0r-x)\lt x-p_0r\implies 0\le \frac{f_0+i_m}{x-p_0r}-n+m\lt 1$$ $$\implies n=m+\left\lfloor\frac{f_0+i_m}{x-p_0r}\right\rfloor=\left\lfloor\frac{f_0+i_0}{x-p_0r}\right\rfloor$$ follows.
Then, we can see that $$p_n=p_0,\quad f_n\ge 0$$ and that $$p_{n+1}=(r+1)p_n+f_n-x,\quad f_{n+1}=0,$$ i.e. $$\frac{p_{n+1}}{(r+1)^{n+1}}-\frac{p_n}{(r+1)^n}=\frac{f_n}{(r+1)^{n+1}}-\frac{x}{(r+1)^{n+1}},\quad f_{n+1}=0$$
Finally, if we have $$p_{n+2}=(r+1)p_{n+1}-x\iff \frac{p_{n+2}}{(r+1)^{n+2}}-\frac{p_{n+1}}{(r+1)^{n+1}}=-\frac{x}{(r+1)^{n+2}}$$ $$\vdots$$ $$p_{N-1}=(r+1)p_{N-2}-x\iff \frac{p_{N-1}}{(r+1)^{N-1}}-\frac{p_{N-2}}{(r+1)^{N-2}}=-\frac{x}{(r+1)^{N-1}}$$ where $N$ is an integer such that $$p_{N-1}\gt 0\quad\text{and}\quad (r+1)p_{N-1}-x\le 0$$ then we have $$\small\frac{p_{N-1}}{(r+1)^{N-1}}-\frac{p_{n}}{(r+1)^{n}}=\frac{f_n}{(r+1)^{n+1}}-x\sum_{j=n+1}^{N-1}\frac{1}{(r+1)^j}=\frac{f_n}{(r+1)^{n+1}}-x\cdot\frac{r+1}{r}\left(\frac{1}{(r+1)^{n+1}}-\frac{1}{(r+1)^{N}}\right)$$ from which $$\small 0\lt \frac{p_{N-1}}{(r+1)^{N-1}}=\frac{p_{n}}{(r+1)^{n}}+\frac{f_n}{(r+1)^{n+1}}-x\cdot\frac{r+1}{r}\left(\frac{1}{(r+1)^{n+1}}-\frac{1}{(r+1)^{N}}\right)\le \frac{x}{(r+1)^N},$$ i.e. $$0\lt (p_0r(r+1)+f_nr-x(r+1))(r+1)^{N-n-1}+x(r+1)\le rx\tag1$$ follows where $$r=\frac{8}{100}\times\frac{1}{12},\quad n=\left\lfloor\frac{f_0+i_0}{x-p_0r}\right\rfloor,\quad f_n=f_0+i_0+\left\lfloor\frac{f_0+i_0}{x-p_0r}\right\rfloor(p_0r-x)$$
Therefore, if your input is "$N$ months", then the monthly installment $x$ is given by the smallest integer satisfying $(1)$.
For your example where $p_0=1000,f_0=400$ and $i_0=100$, we get, with the help of Excel, the monthly installment $x$ for each given $N$ as follows :
$$ \begin{array}{c|lcr} \text{your input $N$ months} & 1 & 2 & 3 & 4 & 5 & 6 & 7 & 8 & 9 & 10\\ \hline \text{monthly installment $x$} & 1507 & 756 & 506 & 381 & 306 & 256 & 220 & 193 & 172 & 155\\ \end{array} $$ $$ \begin{array}{c|lcr} \text{your input $N$ months} & 11 & \color{red}{12} & 13 & 14 & 15 & 16 & 17 & 18 & 19 &20&21&22\\ \hline \text{monthly installment $x$} & 142 & \color{red}{130} & 121 & 112 & 105 & 99 & 93 & 89 & 84 & 80 & 77 & 73\\ \end{array} $$
where $x$ is the smallest integer such that $$0\lt \left(\frac{452}{45}+\left\lfloor\frac{500}{x-\frac{20}{3}}\right\rfloor\left(\frac{2}{45}-\frac{x}{150}\right)-\frac{151}{150}x\right)\left(\frac{151}{150}\right)^{N-1-\left\lfloor\frac{500}{x-\frac{20}{3}}\right\rfloor}+\frac{151}{150}x\le \frac{x}{150}$$