Effect of Payment Frequency on Mortgage Balance at Term

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.A comparison of mortgage payment frequency on the balance owed at amortization

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

Leave a Comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.