What is the math used to calculate the impact that overpaying a mortgage has an an amortization table?
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 0,000 (ignoring all additional costs) would result in a monthly payment of ,432.25. I then assumed that if I paid an extra 0 per month every month from the start of the mortgage, then on the 335th payment I would have a balance of ,300.03 and a 'saved' ,500, so I'd pay 0.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 ~,506. The calculator I'm using is telling me that I'd reach payoff at month 318 and save ,746. While that's obviously much better, it won't show me the underlying calculations.
(2)2 Comments
Sorted by latest first Latest Oldest Best
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.
On payment 1, the interest is 00 and you are paying 32.25 or 2.25 in principal. An extra payment of 0 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 0 is 94. 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.
When I look to make the month 2 payment, I see that month 3 shows 5.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 7.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 00 piece of software. The company went out of business, but the software is now sold under a new name. Crazy stuff)
Terms of Use Privacy policy Contact About Cancellation policy © freshhoot.com2026 All Rights reserved.