2

I'm in the classic 'pay extra on the house vs invest' argument with my partner (not the subject of this question) and I'm trying to understand what exactly happens when I pay extra on a mortgage. I have my amortization schedule that I've reproduced in Excel and I understand the math behind how the principal and interest is calculated and the change in allocation over the years. I also see what happens when I plug in an extra $X monthly using online calculators.

What I don't understand is what actually happens to the amortization table and the distribution between principal and interest payments.

I assumed that any extra payments were just 'saved' and once the principal was equal to the saved payments, the loan terminated and the sum of the skipped interest payments was my savings. However, that's not what the calculators are telling me and I want to understand why.

My (incorrect) understanding is as follows:

A hypothetical 30-year mortgage with 4% interest on $300,000 (ignoring all additional costs) would result in a monthly payment of $1,432.25. I then assumed that if I paid an extra $100 per month every month from the start of the mortgage, then on the 335th payment I would have a balance of $34,300.03 and a 'saved' $33,500, so I'd pay $800.03 to pay off the mortgage and my savings would be the amortized interest payments from months 336-360 (minus a bit from the partial 336th payment) for a savings of ~$1,506. The calculator I'm using is telling me that I'd reach payoff at month 318 and save $28,746. While that's obviously much better, it won't show me the underlying calculations.

5

The underlying calculations are simple, and you can easily replicate them.

Let's look at the first few months.

Month 1:

  • Balance = Initial balance = 300,000
  • Interest = Balance * Rate = 300,000 * (0.04 / 12) = 1,000
  • Principal = Minimal Payment + Additional payment - Interest = 1,432.25 + 100 - 1,000 = 532.25

Month 2:

  • Balance = Month 1 balance - Month 1 principal = 300,000 - 532.25 = 299,467.75
  • Interest = Balance * Rate = 299,467.75 * (0.04 / 12) = 998.23
  • Principal = Minimal Payment + Additional payment - Interest = 1,432.25 + 100 - 998.23 = 534.02

Month 3 (skipping the formulas):

  • Balance = 298,933.73
  • Interest = 996.45
  • Principal = 535.80

You would then repeat these steps until the balance reaches zero, which you will see happening in 318 months. Summing up the interest paid shows a reduction of lifetime interest of 28,746.23, which again is consistent with the calculator.

  • I think I understand - I assumed that the interest amount was fixed for any given month after being calculated at the creation of the amortization table. It's actually variable based on the remaining balance at the point of calculation for the given payment period, right? The only fixed amount is the minimum payment as defined in the table? – Brian R Apr 11 '18 at 18:04
  • 1
    Yes, interest is simply balance times monthly interest rate, calculated every month. As the balance goes down, so does interest. * Note: there are mortgages that work in a different manner but that's the "default" way. – void_ptr Apr 11 '18 at 18:08
  • 1
    Well I guess I just fundamentally misunderstood how the system worked - I accepted this answer because the math was called out a bit more clearly. – Brian R Apr 11 '18 at 18:12
1

On payment 1, the interest is $1000 and you are paying $1432.25 or $432.25 in principal. An extra payment of $430 or so in principal will push you one month ahead on the amortization schedule. i.e. by paying the next month's 'principal due' you move ahead a month.

1.04^30 = 3.24, and 3.24*that $430 is $1394. My 'back of envelope' math is pretty close to the real numbers you'd get by using a spreadsheet or a financial calculator.

This is a spreadsheet I created some time ago. It's what an amortization table would look like. As I note above, I think of a prepayment in terms of moving you forward on the table.

enter image description here

When I look to make the month 2 payment, I see that month 3 shows $435.13 principal due, so I pay that, as an extra amount. Below, you see that the 'months remaining' drops an extra month by doing so, and the interest saved is $997.11, the effect of the near full 30 years of compounding.

(Note - I created the sheet as a response to a scam that made the rounds back them, the "Money Merge Account". In a weekend I wrote a spreadsheet that had all the features claimed by a $3500 piece of software. The company went out of business, but the software is now sold under a new name. Crazy stuff)

enter image description here

  • 1
    Congratulations on being able to calculate the 30th power of 1.04 on the back of your envelope! :-) – Dilip Sarwate Apr 12 '18 at 16:59

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.