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

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.

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!

• Is there any difference in the results if you remove the ROUND function in Column D? – DJohnM Apr 13 '18 at 3:33
• @DJohnM In practical terms, probably not much, but it (a) mirrors more closely what actually happens, and (b) will look "cleaner" (although you could also do that with column formatting). – TripeHound Apr 13 '18 at 7:18
• Not rounding would technically be more accurate, but I'd be surprised if your lender goes to the effort of tracking partial pennies on your principal and interest balances. Rounding should reflect the way they calculate your bills, even if it's not exactly correct. – CactusCake Apr 13 '18 at 14:10
• @CactusCake I would contend that "the way they calculate your bills" is – by definition – "exactly correct". – TripeHound Apr 13 '18 at 14:43
• Good point. Now that I think about it, you're probably right that it does specify rounding in most contracts, specifically for the pedants like us! And now I feel irrationally compelled to say that that's my 2.00000001 cents. And I feel irrationally cheated out of a billionth of a metaphorical penny. Hmmm. – CactusCake Apr 13 '18 at 15:47

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
``````