How can I amortize 4 rates over a 30 year term?

Question

I've been offered a HELOC modification with the following terms:

Beginning balance $148,148.59,

• Initial rate and term is 1% for 60 months at a pmt of $374.60,
• The 2nd term begins Jan of yr 6, at 2% for 12 months at the pmt of $439.60,
• The 3rd term begins Jan of yr 7, at 3% for 12 months at the pmt of $508.88,
• The 4th term begins Jan of yr 8, at 4% for 396 months at the pmt of $581.85.

Since I have no intention of keeping the house or the mortgage for 40 years;

my question is if I pay an extra $1000 of principal every month for the first 7 years what will my principal balance at the beginning of year 8?

Also, how much interest will I have paid after 7 years.

My guess (purely a guess) is I would reduce principal by $113,352 to $34,796.59. And I will have paid approximately $5000 in interest. I kinda think my interest "guess" is all wet! I hope there is a mathematician out there who can help me understand this. I need to make a (informed) decision by May 1, 2018.

Answer 1

Your remaining principal balance will be $38,528.86. And you will have paid $8,238.03 in interest.

You can calculate this (or any other amortization) with a fairly basic spreadsheet.

Put your principle amount in the first column. In the next column, multiply it by the interest rate for that month (APR divided by 12). In the next column, enter the amount you'll be paying. In the next column, subtract the interest amount from the amount you're paying (this will be the principal reduction for that month). In the next row, back to the first column, subtract the principle reduction amount from the previous row's starting principle.

It'll end up looking something like this (I've added a few extra columns to make it explicitly clear what's going on where, but these are optional):

Here are the formulas so you can build your own:

Obviously when you get to month 61, 73 and 85 you need to update the rates in column C and the required payments in column E. You can play around with putting different amounts in column F to see how it affects the total amounts paid, term of the loan, etc. Have fun!

Answer 2

Using the formula for principal remaining

p[n] = (d + (1 + r)^n (r s - d))/r

where

p[n] is the principal remaining in month n
d is the periodic payment
r is the periodic interest rate
s is the principal

Adding $1000 to the repayments; calculating the first 60 months, then taking the principal remaining as the principal for the next 12 months, etc.

Initial principal, s = 148148.59

d = 374.60 + 1000
r = 0.01/12
n = 60
s = (d + (1 + r)^n (r s - d))/r = 71204.50

d = 439.60 + 1000
r = 0.02/12
n = 12
s = (d + (1 + r)^n (r s - d))/r = 55207.28

d = 508.88 + 1000
r = 0.03/12
n = 12
(d + (1 + r)^n (r s - d))/r = 38528.85

The principal remaining after seven years is $38,528.85

Calculating the interest

In first 60 months the interest paid is

60 * 1374.60 - (148148.59 - 71204.50) = 5531.91

In the next 12 months the interest paid is

12 * 1439.60 - (71204.50 - 55207.28) = 1277.97

and in the next 12 months it is

12 * 1508.88 - (55207.28 - 38528.85) = 1428.13

So the interest in the first seven years is $8,238.01

5531.91 + 1277.97 + 1428.13 = 8238.01