0
$\begingroup$

This one requires some explanation.

What I want to do is calculate how much total monthly payment I would have to pay for x number of months on a loan to get the total balance down to where the loan will be paid off by paying ONLY THE MINIMUM payment for the last y number of months. So x would be the number of months I want to pay a higher payment, and y would be the number of months I want to pay minimum.

What I know for loans I want to do this with:

  • Current balance
  • Amount of minimum monthly payment
  • APR
  • Whether or not the loan is compounded daily or monthly
  • Number of months I want to pay higher amounts (variable, x)
  • Number of months I want to pay minimums (variable, y, always a number of months left in the loan after x number of months)

So for example if I have 3000 left on a loan, and the monthly payments are 300, I want to know how much I would have to pay in monthly payments for x number of months up until it would take y number of months to pay off the loan completely if I stopped paying higher payments and only paid the minimum for those y remaining months.

On a 0 interest loan this would look like:

Total remaining is 3000, monthly payments are 300. I have 2 months I want to pay higher payments so that I can get the loan down to where I only have 5 months left of minimum payments.

Using simple math I can see clearly that for months 1 and 2 I would pay 750 each, like so:

  • Month 1) 3000 - 750 = 2250
  • Month 2) 2250 - 750 = 1500
  • Month 3 (start paying minimum again)) 1500 - 300 = 1200
  • Month 4) 1200 - 300 = 900
  • Month 5) 900 - 300 = 600
  • Month 6) 600 - 300 = 300
  • Month 7) 300 - 300 = Paid

Let me know if I need to explain my goal more.

How would I calculate the higher monthly payment amounts for the first x months such that the minimum payment is sufficient to pay the loan off completely in the last y months?

$\endgroup$
1
$\begingroup$

Define variables:

$m = $ minumum monthly payment

$m+h=$ higher monthly payment

$r=$ ratio used to increase the debt on a monthly basis. If you are given a daily compound rate then you can work out the monthly from that. $r>1$, so for example a rate of 1.2% per month would give $r=1.012$.

$x = $ number of months paying at higher rate.

$y = $ number of months paying at lower rate.

$P=$ amount that you start with.

Let's work backwards. The last payment is m, which brings the debt down to 0.

The debt just before payment at end of month $x+y$ is $m$.

The debt at the start of that month is $\frac mr$

The debt just before payment at end of month $x+y-1$ is $\frac mr+m$.

The debt at the start of month $x+y-2$ is $\frac m{r^2}+\frac mr$

The debt just before payment at end of month $x+y-2$ is $\frac m{r^2}+\frac mr+m$.

We can see that the debt just after payment at end of month $x+y-y$ is $\frac m{r^y}+...+\frac m{r^2}+\frac mr$.

The payment at the end of month $x+y-y=x$ is the last payment at the higher value, so the debt just before the payment at the end of month $x$ is $\frac m{r^y}+...+\frac m{r^2}+\frac mr+m+h$.

The debt just before the payment at the end of month $x-1$ is $\frac m{r^{y+1}}+...+\frac m{r^3}+\frac m{r^2}+\frac {m+h}r+m+h$.

The debt just before the payment at the end of month $x-2$ is $\frac m{r^{y+2}}+...+\frac m{r^4}+\frac m{r^3}+\frac{m+h}{r^2}+\frac {m+h}r+m+h$.

The debt just before the payment at the end of month $x-(x-1)=1$ is $\frac m{r^{y+x-1}}+...+\frac m{r^x}+\frac {m+h}{r^{x-1}}+...+\frac{m+h}r+m+h$.

So the debt at the start of month is $P=\frac m{r^{y+x}}+...+\frac m{r^{x+1}}+\frac {m+h}{r^x}+...+\frac{m+h}{r^2}+\frac {m+h}r$

Thus $P=m\left(\frac 1{r^{y+x}}+...+\frac 1r \right) +h \left(\frac 1{r^x}+...+\frac 1{r^2}+\frac 1r \right)$

$P=m\left(\frac {1+r+...r^{y+x-1}}{r^{y+x}}\right)+h\left(\frac {1+r+...r^{x-1}}{r^x}\right)$

Let $R_{x+y}=\frac {1+r+...r^{y+x-1}}{r^{y+x}}$ and let $R_x=\frac {1+r+...r^{x-1}}{r^x}$

so that $P=mR_{x+y}+hR_x$

$hR_x=P-mR_{x+y}$

$h=\frac{P-mR_{x+y}}{R_x}$

It can be shown (geometric sequence) that $R_{x+y}=\frac {r^{y+x}-1}{(r-1)r^{y+x}}$ and that $R_x=\frac {r^x-1}{(r-1)r^x}$

so $h=\frac{P-m \frac {r^{y+x}-1}{(r-1)r^{y+x}}}{\frac {r^x-1}{(r-1)r^x}}$

$h=\frac{P(r-1)-m \frac {r^{y+x}-1}{r^{y+x}}}{\frac {r^x-1}{r^x}}$

$h=\frac{P(r-1){r^{y+x}}-m \left(r^{y+x}-1 \right)}{{r^{x+y}-r^y}}$

Excel will do fine - nothing fancy to calculate.

$\endgroup$
  • $\begingroup$ Looks like this works great, the only problem I now have is creating some way to plug in the variables into a computer program. The easiest would be Excel, but maybe there is other software that will help with this? $\endgroup$ – TomT64 Oct 8 '15 at 16:50

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.