Click here to download a free feature-packed Canadian mortgage calculator spreadsheet! This Microsoft® Excel® template lets you choose a compound period (e.g. semi-annual for Canadian mortgages) and a variety of different payment frequencies (annually, semi-annually, quarterly, bi-monthly, monthly, semi-monthly, bi-weekly, and weekly). It also lets you see how making periodic extra payments (prepayments) can save you money and help pay off your mortgage sooner. You can also calculate the outstanding balance at the end of a given term.

Screenshot

Canadian Mortgage Calculator for Excel Calculate the payment and outstanding balance for a Canadian mortgage using this unique calculator. It allows you to specify the mortgage term, periodic extra payments, compound period, and payment frequency (including weekly and bi-weekly payments). The amortization schedule lets you add unscheduled additional prepayments. No Installation, No Macros - Just a simple spreadsheet

How to Use the Canadian Mortgage Calculator

What is Unique About Canadian Mortgages?

From what I can tell (having never actually lived in Canada), there are two main differences between US and Canadian mortgages:

1. The Canadian mortgage rates that are quoted are based on a semi-annual compound period. The rates for US mortgages assume a monthly compound period.
2. With a Canadian mortgage, your rate usually depends upon the Term that you choose (e.g. 6 months, 1 yr, 2 yr, 3 yr, 5 yr, 7 yr, or 10 years), which is essentially the length of time that you are under contract for the specified mortgage rate.

For more information, see the references and resources listed below.

Accelerated Bi-Weekly Mortgages

This calculator can estimate the effect of an Accelerated Bi-Weekly payment plan, a common type of Canadian Mortgage plan. The first thing to realize is that "accelerated" means that rather than a normal bi-weekly payment, you are actually making an extra payment on the principal. By tradition, the amount of the extra payment is based on the normal monthly payment, such that by the end of a year you will have paid the equivalent of one extra monthly payment.

Example: A 100,000 mortgage at 5% interest, compounded semi-annually, with an amortization period of 25 years, results in a monthly PI (principal + interest) payment of $581.60 (rounded). The total payments for the year would be 7560.80.

In an Accelerated Bi-weekly plan, each bi-weekly payment would be 1/2 of 581.60 or $290.80. There are 26 bi-weekly payments in a year so the difference between 581.60*12 and 581.60/2*26 is 581.60, or one extra monthly payment per year. A normal bi-weekly payment, found by setting the Payment Frequency to bi-weekly, would be $268.14 rounded. To simulate an Accelerated Bi-weekly plan with this Canadian Mortgage Calculator, you would then need to enter an Extra Payment of 290.80-268.14=22.66.

Accelerated Weekly plans are similar, but each weekly payment would be 1/4 of 581.60 or $145.40. The normal weekly payment would be $134.00, so the extra payment would be 145.40-134.00=11.40.

Another approach to estimate the effect of making one extra monthly payment each year is to choose the Monthly option in the Payment Frequency and set the Extra Payment equal to payment/12.