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 Answer 1

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$
1
  • $\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
    Commented Oct 8, 2015 at 16:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .