0
$\begingroup$

I'm working on a problem where I need to calculate the monthly payments for a loan given a few constraints. These payments grow at a constant rate every 2 years (24 months) and pay off the entire loan in a given repayment period.

We are given the length of the repayment period in months, the beginning principle balance, and the annual interest rate.

The constraints are that the monthly payments cannot be less than interest due, but also cannot be less than half or more than 3 times a given amount (that amount is actually the fixed payment amortization for the same term).

For example if we have a \$51183 principle balance with 3.5% annual interest, then for a 10 year (120 month) term our first month's payment using this growing payment is \$282 and the final month's payment is $845. The limiting amount here is \$506, so the payments could not be lower the (506/2) = \$253 and must be less than (506*3) = \$1518.

How can I go about figuring out how to calculate the payment schedule, in particular coming up with the first and last month's payment and the growth rate of the payments?

$\endgroup$
  • $\begingroup$ I get 30% as the constant value by which the payments must be increased every two years. However this depends on your starting value of $ \$282$. Why did you choose $\$282$, aside from it being within the constraints? Also, usually when people pose constraints they are interested in optimizing something. Don't you want to find the optimal payment plan? $\endgroup$ – ben May 27 '14 at 18:37
  • $\begingroup$ I chose 282 because I'm attempting to replicate a calculation done by someone else. Their calculation yields 282 and 845 for that starting balance and interest rate. $\endgroup$ – user1569339 May 28 '14 at 18:49
0
$\begingroup$

Define the total repayment period $T$ in units of years. Then the payments $P_t$ in any single year $t$ are given by:

$$P_t=12P_1(1+\alpha)^{t/2}$$

where $P_1$ is the initial payment ($\$282$ in your example above), and $\alpha$ is the constant percentage by which the payments go up every 2 years.

If you don't like interpolating a yearly increase in payments (interpolting like this will alter the output slightly), then you can replace the exponent $t/2$ with $q$ where

$$q=\left\{\begin{matrix} t & if\:\:t\:\:is\:\:odd \\ t-1 & if\:\:t\:\:is\:\:even \end{matrix}\right.$$

The outstanding loan balance in any year $t$ is then the polynomial expansion:

$$B_t=B_0x^t-\sum_{k=1}^{t} P_kx^{(t-k+1)}$$

where $B_0$ is the initial loan balance before any payments and $x=(1+r)$, where $r$ is the interest rate.

The loan is completely paid off in the $T^{th}$ year:

$$B_T=0$$ and so

$$B_0x^T=\sum_{k=1}^{T} P_kx^{(T-k+1)}$$

Solve this equation for $\alpha$ (you could use excel's solver to do this) to get the percentage that the payments should go up every 2 years.

As for your constraints, just make sure $P_1>W/2$ and $P_T<3W$ where $W$ is the "limiting amount" ($\$560$ in your example). You can also input these constraints easily into excel's solver.

In your example of $T=10$, $r=0.035$, $B_0=\$51183$ and $P_1=\$282$, I get $\alpha=0.3006$ (i.e. $30.06\% $)

graph EDIT: Note that the polynomial equation $$B_0x^T=\sum_{k=1}^{T} P_kx^{(T-k+1)}$$ can be expressed as a geometric progression:

$$B_0x^T=x^{T+1}12P_1\sum_{k=1}^{T} Q^k \:\: ; \:\:\: Q=y^{1/2}x^{-1}, \:\: y=(1+\alpha)$$

and thus as a more convenient formula which might be easier to solve for $\alpha$:

$$B_0x^T=x^{T+1}12P_1 \frac{Q-Q^T}{1-Q}$$

For this to work you have to be okay with interpolating a yearly increase in the payment amount.

EDIT #2:

The question asks for a way to determine the initial and final payments ($P_1$ and $P_T$) given the information $T$, $B_0$, and $r$. However, these three given values do not determine a unique $P_1$ and $P_T$.

You can rest assured, at least, that if $P_1$ is greater than your minimum bound ($\$560/2$) then all $P_t$ will be greater than your minimum bound. Likewise, if $P_T$ is less than your max bound ($3\times \$560 $), then so will all $P_t$.

If your $P_1$ is right on the minimum bound and this results in a final payment $P_T$ which is greater than your max bound, then you've got a problem. You will have to adjust the parameters in order to keep the payments in bounds.

$\endgroup$
  • $\begingroup$ On further thought, I need a way of determining the initial payment and growth rate given the rules I describe. When given the first and last months' payments, I use (P[n]\P[0])^(1/n) where n is the number of payments, to determine the growth rate, which is good enough for an estimate. But there are occasions where I do not know the initial or final months payments and need to generate them based on the principle, interest rate, and predetermined repayment term length. $\endgroup$ – user1569339 May 28 '14 at 18:53
  • $\begingroup$ The information given does not determine a unique first and final payment amount. See edit #2 in my answer above. $\endgroup$ – ben May 29 '14 at 4:15
0
$\begingroup$

You can set up an amortization schedule such as below and it is a slick way to calculate the growth rate using GOAL SEEK of EXCEL given the initial payment. Attached you will see a setup that will give the solution for the example numbers that you cited. If you need any clarification let me know. The formula for the constraints are if pmt is less than Max (Int due, Fixed Amount/2) then Max of (int,due, Fixed Amount/2) and then it checks if pmt is greater than 3 times the fixed amount and if it is then fixed amount*3 all else it is the pmt. (Nested if). This ensures the payment to not exceed the bounds and if it does it assumes the bounds. enter image description here

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