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?
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.