Many Canadians are mystified by the mortgage calculations. They will often find that they can figure out loan interest and payments, but mortgages baffle them. The simple explanation of this is that loans are usually very simple to deal with, since the interest is compounded with every payment. Therefore, a loan at 6%, with monthly payments and compounding simply requires using a rate of 0.5% per month (6%/12 = 0.5%).
Unfortunately, mortgages are not as simple. With the exception of variable rate mortgages, all mortgages are compounded semi-annually, by law. Therefore, if you are quoted a rate of 6% on a mortgage, the mortgage will actually have an effective annual rate of 6.09%, based on 3% semi-annually. However, you make your interest payments monthly, so your mortgage lender needs to use a monthly rate based on an annual rate that is less than 6%. Why? Because this rate will get compounded monthly. Therefore, we need to find the rate that compounded monthly, results in an effective annual rate of 6.09%. Mathematically, this would be:
(1+rM)12-1 = 0.0609
rM = (1.0609)1/12
rM = 0.493862…%
Notice, that the annual equivalent of his rate is slightly less than 6%, at 5.926% (0.493862 x 12 = 5.926%). In other words, 5.926% compounded monthly is 6.09% annually. By the way, I recommend to my students learning this for my university courses that they use 8 decimals in their interest rate to assure that they can be accurate to the penny.
(Now if you are starting to feel nauseated, and would like a simpler approach, skip to the bottom of his page and download the one of the simple mortgage calculator spreadsheets I have written.)
On the other hand, if you want another more conceptual explanation, you can follow the following link. This file requires a PDF reader, such as Adobe Reader.
If you are comfortable using the formula to calculate the present value of an annuity, this is the rate you will use, and the number of months in the amortization (300 for 25 years, 240 for 20 years, etc.) is the number of payments. For a 25-year mortgage at this monthly rate, the present value factor is 156.297225….
Let do an example. Let’s assume a mortgage of $100,000 at a quoted rate of 6%. The principal of the mortgage is the present value. So we know:
Principal = (PV Factor)x(Payment)
so
Payment = (Principal)/(PV Factor)
Payment = ($100,000)/(156.297225…)
Payment = $639.81
You can do this quite easily on a financial calculator. Assuming that you have the calculator properly cleared, you can enter:
| Value Entered | Button Pushed |
|---|---|
| 100000 | [PV] |
| 300 | [n] |
| 0.493862 | [i] |
Pushing [COMP][PMT] will return -639.81.
You can get more information about using two of the more popular financal calculators here:
- PowerPoint Slides showing use of the Sharp EL-733A Calculator for Mortgages, Loans, Leases and Bonds
- Using the TI BAII Plus
These files require a PDF reader, such as Adobe Reader.
Remember, these calculations are for the mortgage itself, and do not include any life insurance premiums added to the payment or property taxes that may get added. Also, some lenders will round up the payment to the next dollar. This simply means that the mortgage gets paid down slightly faster, since those extra pennies are applied to principal.
Some Mortgage Calculators – Excel files
Monthly Payment Mortgage Calculator – No Amortization Table This spreadsheet file allows you to compare up to five mortgages – different rates, principals, amortization terms, etc.
Monthly Payment Mortgage Calculator – With Amortization Table This spreadsheet file calculates the payment given the principal, amortization term and nominal or quoted rate and computes the amortiztion table for five years. You can get a longer amortization table by simply copying the last line as many times as necessary. You can also study the impact of making extra payments on any monthly payment date.
Weekly Payment Mortgage Calculator – With Amortization Table This spreadsheet file calculates the payment given the principal, amortization term and nominal or quoted rate and computes the amortiztion table for 261 weeks (five years). You can get a longer amortization table by simply copying the last line as many times as necessary. You can also study the impact of making extra payments on any weekly payment date. Note that the assumption is that this is the typical weekly-pay mortgage with the payment based on one-quarter the monthly payment on the nominal amortization. The actual amortiztion term is provided as well.
Extra Payments
What is the impact of an extra, lump-sum payment? Every penny of an extra payment will reduce your principal outstanding and start saving you interest immediately. The spreadsheets above that have amortization tables allow you you determine the impact of lump-sum extra payments made on any payment date.
Let’s extend the example that we used above. Suppose one year after taking out the $100,000, 6%, 5-year mortgage, you received an unexpected $2000 windfall and decided to apply half of this to your mortgage. Without the extra payment, you would be owing $89,836.47 at renewal after five years. With the extra payment this is reduced by $1,266.76 to $88,569.71. It should not surprise to you to learn that this is a 6.09% compound annual return on your $1000, since that is the effective annual rate on the mortgage. This 6.09% is tax-free, which is roughly equivalent to a 9.5-10% rate of return on a pre-tax basis for people earning interest outside an RRSP or other tax-sheilding vehicle. That is excellent, considering that it is close to a risk-free return.
Feedback and Comments
I will be adding to this page and I welcome your suggestions and comments. You can email me at amarshal@yorku.ca
