0

If a debt accrues interest at an annual rate of 8% on the principal balance up to $1,500, and it accrues interest at an annual rate of 18% on the portion of the principal balance above $1,500, and if the debt is paid in equal monthly payments in either 3 years, 4 years or 5 years, how do I calculate the monthly payment for each scenario?

  • 1
    The frequency at which interest is compounded must be specified, I think. (Typically it's monthly, but might not be.) Also, are payments made at the beginning of the period, or the end? – RonJohn Aug 20 at 21:51
  • 4
    is this your homework? – JohnFx Aug 20 at 23:59
5

You will need to solve this problem iteratively. A spreadsheet is a good tool for iterative calculations.

One approach is:

  1. Start with the higher of the two interest rates.
  2. Compute the monthly payment given that interest rate, the total balance, and the number of payments. If you have already reached Step 11, use the value you most recently calculated in Step 11.
  3. Compute the number of payments required to pay off the threshold balance at the sub-threshold balance's interest rate. (This value will probably not be a whole number of payments.)
  4. Subtract the result of Step 3 from the total number of periods. Again, this value will probably not be a whole number of periods.
  5. During the first __ number of periods (calculated in Step 4) calculate the amount of interest paid per period at the sub-threshold rate. This is the threshold balance times the sub-threshold interest rate per period.
  6. Subtract the amount calculated in Step 5 from the monthly payment most recently calculated in Step 2 or 11.
  7. Calculate the net present value of the above-threshold portions of the payments. Use the above-threshold interest rate, the portion of the payment calculated in Step 6, and the number of payments calculated in Step 4.
  8. Add the value calculated in Step 7 to the threshold balance.
  9. Subtract the initial principal from the value calculated in Step 8.
  10. If the value calculated in Step 9 is within rounding error of zero, stop. The last value calculated in Step 2 or 11 is your steady monthly payment.
  11. If the value calculated in Step 9 is not within rounding error of zero, multiply the value most recently calculated in Step 2 or 11 by the initial principal, and divide by the value most recently calculated in Step 8. Replace the guessed monthly payment with this new guess. Repeat Steps 3 through 11 until you can stop at Step 10.

Using this algorithm with the original post's values of an APR that increases from 8% to 18% at a threshold balance of $ 1,500, assuming compounding at 12 equal-length months per year, and assuming an initial balance of $ 2,500, I calculate:

  • 36 monthly payments of $ 80.56, or
  • 48 monthly payments of $ 63.38, or
  • 60 monthly payments of $ 53.19.

These values are slightly inaccurate, because they assume the effective marginal rate switches over perfectly during the period when the balance goes through the threshold.

Sanity checks:

  • 36 months' total interest = $ 400.13, or about 10.67 % per year of half the original balance.
  • 48 months' total interest = $ 542.27, or about 10.84 % per year of half the original balance.
  • 60 months' total interest = $ 691.35, or about 11.06 % per year of half the original balance.
  • Initial average interest rate = 12.00 % APR.
  • Final interest rate = 8.00 % APR.
4

Treat the debt as 2 debts. Treat the first loan as having principal 1500 lower than the actual principal, and add the interest from the 1500 balance a the lower APR to it (in your example this would be the monthly interest for an 8% APR on a 1500 loan, or ~30). When the "first" loan is paid down (principal falls to or below 1500), you will (possibly/likely) have 1 month of partial payment, then the "second" loan which is the remaining principal at the 1500 rate.

  • Should the "~30" be "~15"? – Jasper Aug 21 at 19:40
2

Trying out an iterative approach.

Starting with initial balance of $2500.

s  = 2500
r1 = 8/100/12
r2 = 18/100/12
q  = 1500

n = 36

The following function sets p to s, then iterates p = Min[p, q] (1 + r1) + Max[p - q, 0] (1 + r2) - d for n months. Finally the absolute value of p is returned.

f[d_] := Module[{p = s},
  Do[p = Min[p, q] (1 + r1) + Max[p - q, 0] (1 + r2) - d, {n}];
  Abs[p]]

The aim is to find a value of d that minimises Abs[p].

Plotting values f for d ranging from 80.5 to 80.6

ListPlot[Table[{x, f[x]}, {x, 80.5, 80.6, 0.01}]]

enter image description here

A value of d = 80.56 brings the balance of the 3 year loan close to zero.

So payments for 3 years: $80.56, for 4 years: $63.38 and for 5 years: $53.19

Total interest for the 3 year loan = $80.56*36 - $2500 = $400.16

For the 4 and 5 year loans total interest is $542.24 and $691.40 respectively.

Check

Checking with a reduced example: repay in 6 months

n = 6

ListPlot[Table[{x, f[x]}, {x, 428.8, 429, 0.01}]]

enter image description here

d = 428.95

Calculating the balance month-by-month to check.

1500.00 (1 + r1) + 1000.00 (1 + r2) - d = 2096.05
1500.00 (1 + r1) +  596.05 (1 + r2) - d = 1686.04
1500.00 (1 + r1) +  186.04 (1 + r2) - d = 1269.88
1269.88 (1 + r1) +    0.00 (1 + r2) - d =  849.40
 849.40 (1 + r1) +    0.00 (1 + r2) - d =  426.11
 426.11 (1 + r1) +    0.00 (1 + r2) - d =    0.00

The loan balance is reduced to zero in six months. The method checks out.

1

If you are comfortable with spreadsheets, this is the way to go -

enter image description here

The interest cell shows

=IF(F16>1500,10+(F16-1500)*0.015,F16*0.08/12)

It seemed to me the "trick" is to calculate the interest for each period, and this is the way to do it automatically. I used an initial loan of $2500, in cell F16. The interest is simply one of 2 choices. If the balance is over 1500, it equals $10 (one month out of $120/yr at 8%) plus 1-1/2% of the excess. If less than $1500, it's just .08/12.

The full sheet for 36 months -

enter image description here

It took just a few guesses to get to the nearest dollar, an $80 monthly payment. A few more gusses, and you'd have the answer to the nearest cent, $80.56

Note, edited to correct typo. My answer now agrees with Chris'. Same result, a bit different method.

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.