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.
Advertisem*nt
categories: Mortgage Calculators
Canadian Mortgage Calculator
for Excel
Download
⤓ Excel
For: Excel 2010 or later & Excel for iPad/iPhone
⤓ Google Sheets
License: Personal Use (not for distribution or resale)
"No installation, no macros - just a simple spreadsheet" - by Jon Wittwer
Advertisem*nt
Description
Calculate the payment and outstanding balance for a Canadian mortgageusing this 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.
Update 4/22/13: The calculator now includes accelerated bi-weekly and weekly options. Just choose "Acc Bi-weekly" or "Acc Weekly" from the payment frequency drop-down box (see below for more info).
related blog articles
10 Strategies for Paying Off Your Mortgage Early
How to Use the Canadian Mortgage Calculator
The spreadsheet is pretty self explanatory, and many of the cells contain pop-up comments that provide information about the inputs and calculations. Basically, you just enter values in the white-background cells, and see what happens to the payment, total interest, outstanding balance, etc. To add irregularly scheduled prepayments, enter the numbers in the "Additional Payment" column (yellow cell background). If you have questions about using this calculator, please contact Vertex42.
What is Unique About Canadian Mortgages?
From what I can tell (having never actually lived in Canada), there are two main differences between Canadian and US mortgages:
- 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.
- 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 allows you to analyze the effect of an Accelerated Bi-Weekly payment plan, a common type of mortgage repayment plan. The first thing to realize is that "accelerated" means that rather than a normal bi-weekly payment, you are also making an extra payment on the principal. By tradition, the "accelerated bi-weekly" payment is defined as 1/2 a normal monthly payment. The result is that by the end of a year you will have paid the equivalent of one extra monthly payment towards the principal.
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.
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 way 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.
Online Canadian Mortgage Calculators
We used a number of different online calculators to verify this spreadsheet. Keep in mind that some online calculators do not round the payment and interest to the nearest cent, so if you see a small discrepancy in the calculations, this is likely the issue. Our spreadsheet DOES round, and it also adjusts the last payment to bring the balance to zero. Please note the disclaimer, and report any errors you may find in our spreadsheet. Thanks!
- Canadian Mortgage Calculator at dinkytown.net
References & Resources
- Canada Mortgage and Housing Corporation : Contains a lot of excellent material and guides for buying and selling homes in Canada, such as information about CMHC Mortgage Loan Insurance.
- A Guide to Mortgage Interest Calculations in Canada at http://www.yorku.ca/amarshal/mortgage.htm: Includes some Excel spreadsheets!
- Mortgage Calculators : Provides a formula for converting between Canadian and US mortgage rates. There are many other great calculators on this site, also.
- Compound Interest on Wikipedia.com.
- Online Canadian Mortgage Payment Calculator - by CanEquity.com
Disclaimer: The calculations in this spreadsheet are estimates. We believe the calculations to be correct, but do not guarantee the results. Please consult your financial advisor or lending institution before making any final financial decisions.
