# Thread: Mortgage Calculations for CANADA (Excel 97/2000)

1. ## Mortgage Calculations for CANADA (Excel 97/2000)

I was wondering if anyone in this forum knows how to create mortgage calculations/amortization tables/etc. for <font color=red>Canadian</font color=red> mortgages. The built-in formulas in Excel are only suitable for US calculations. Canada is a bit different.

Drew

2. ## Re: Mortgage Calculations for CANADA (Excel 97/2000)

Try this:
<A target="_blank" HREF=http://www.epilogicconsulting.com/down.html>http://www.epilogicconsulting.com/down.html</A>

3. ## Re: Mortgage Calculations for CANADA (Excel 97/2000)

Thanks for the tip, but unfortunately the file doesn't seem to be available on that site anymore.

Drew

4. ## Re: Mortgage Calculations for CANADA (Excel 97/2000)

Sorry for the duff lead!
It may be worth getting in touch with them - the site seems fairly current with a 'last updated' date on that page of just last month. (There's an email link at the bottom of the page.)

5. ## Re: Mortgage Calculations for CANADA (Excel 97/2000)

Canadian mortgages are traditionally compounded semi-annually. There used to be a requirement in the Bank Act that interest could only be "compounded semi-annually, not in advance" - I *think* that requirement is gone now, but the tradition lives on.

On a blended (re)payment loan, every payment creates a new implicit compounding period. The problem is that when you set up a loan with a face rate of 12% by charging 1% per month the effective rate is ~13% (1% compounded 12 times = (1+1%)^12-1 = 12.683%). A Canadian mortgage with a face rate of 12% woudl have an effective annual rate of 12.36% (1+6%)^2-1 = 12.36%. The required monthly charge is the twelfth root of that rate or (1.236)^(1/12)-1 = 0.976%. Since the term "12.36%" was determined by compounding the (half-yearly) nominal rate twice, the equivalent is to take the sixth root (representing half a year or six months) of the nominal semi-annual rate, or (1+6%)^(1/6)-1.

If you charge a monthly rate (assuming monthly payments) equal to the sixth-root of one-half the nominal annual rate you will have the correct Canadian monthly interest factor - don't forget that the mortgage will likely be paid in advance, so the monthy payments will be (very slightly) smaller than if you calculate them as payments in arrears. Drop me an e-mail or post your s/sheet if you want more clarification.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•