Compare the effect of different mortgage payment frequencies with this spreadsheet. A real eye-opener for potential homeowners!
I recently took out a new mortgage. I wanted to investigate how much money I could save by making my payments at a faster rate.
Being an geek, I decided to code up a comparison in Excel (get it from the link at the bottom of this post). It gives you the
- balance at the end of term
- total interest paid at the end of term
- amount of principle you’ve paid at the end of term
You simply enter the mortgage parameters – the loan amount, interest rate, amortization period, term and interest compounding period.
Here are typical results for series of parameters
- loan amount (I chose $200k)
- annual mortgage rate (3.5% is common at this point in Ontario – my home province),
- amortization period (20 years is standard),
- term (5 years for my comparison)
- and an interest compounding period (I’m in Canada, so I chose semi-annual)
After you click “Calculate Mortgage”, some clever VBA calculates
- the payment amount,
- the total interest paid at term,
- the amount of the principal you’ve whittled away at term,
- and the balance at owing term
for all common payment frequencies – everything from annual to accelerated weekly payments.
For the figures I chose, the results were surprising.
For annual, semi-annual, quarterly, bi-monthly, month, semi-monthly, bi-weekly and weekly payments, I would still owe the bank around the same amount – all around $162171, give or take a dollar.
I did not expect that. For some reason, I thought that paying your mortgage at a faster rate would reduce the balance owing at term.
Only the accelerated payment schemes significantly reduced the balance at term – about $155770 to $155798.
Why is that? It’s not magic once you start looking at the detail.
With accelerated weekly and bi-weekly payments, you’re effectively making an extra monthly payment every year; for the figures above, that about an extra $1170 a year for both accelerated schemes (over and above the other payment schedules). That just pays down your mortgage at a greater rate.
So what did I conclude?
Unless you choose an accelerated scheme, the payment frequency makes little difference to the balance at the end of term. So I just aligned the payment frequency to the frequency with which I get paid – bi-weekly
Happy house hunting!
Get Excel Spreadsheet to Compare Effect of Mortgage Payment Schemes on Balance Owning at Term