3

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.

Improve this question

2 Answers 2

Reset to default
6

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.

Improve this answer
7
  • 1
    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, 2018 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, 2018 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, 2018 at 18:12
  • I think you deliberately kept the calculation of the monthly periodic rate simple, by dividing the quoted rate by 12. Might be worth noting, it is only accurate if the quoted rate is equal to the APR. You'd normally have to compute the effective annual rate (EAR), and then obtain the monthly rate from that by taking the 12th root. monthly_rate = (1+EAR)^(1/12) - 1. It's close to quote/12, but not quite. E.g. In Canada, quoted interest rates on fixed-rate mortgages compound semi-annually. So to get the monthly rate from a fixed rate quote, you'd do rate = (1+quote/2)^(1/6) - 1
    – pf_init_js
    Jul 26, 2022 at 23:02
  • @init_js Could be country-specific - in the US, mortgage annual interest rates are quoted exactly equal to monthly interest rate times 12.
    – void_ptr
    Jul 26, 2022 at 23:06
2

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .