1. ## Canadian Mortgage Schedule (2000)

I'm looking for a worksheet that will allow me to generate a table of outstanding mortgage balances for a Canadian mortgage (interest compounded half-yearly.

2. ## Re: Canadian Mortgage Schedule (2000)

You are not too specific about what you want so I will be general. You can use most of excel's financial functions (PMT comes to mind to calculate the periodic payment.

Rate is Ann Percentage Rate / 2 for compounded twice / year

Nper is the total number of payment periods. Yours would be 2 * number of years. If you pay more often it doesn't matter, Payment 1 is the total of st 6 months and payment 2 is the total of 2nd 6 months.

Steve

3. ## Re: Canadian Mortgage Schedule (2000)

Canadian mortgages are calculated differently than American - Try this link http://www.interest.com/hugh/calc/formula.html#can.
You also find some useful info at http://www.cmhc-schl.gc.ca/en/index.cfm .

Cheers

4. ## Re: Canadian Mortgage Schedule (2000)

Any mortgage is compounded on every payment date, since the payment is applied first to interest, and then to principal. The Canadian standard treatment of calculating interest semi-annually requires you to determine the monthly rate (assuming monthly payments) that will result in the same effective rate as the stated annual rate, compounded semi-annually.

The effective rate (the APR, in US terminology) is (1+r/2)^2 - 1 - ie, a 10% stated rate will carry an effective rate of (1+0.05)^2 - 1 = 1.1025 - 1 = 10.25%

The monthly rate is the rate that will equate to this effective annual rate when compounded monthly, or the twelfth root of the effective interest factor. If "R" is the effective annual rate, then the monthly ratre, Rm is: Rm = (1+R)^(1/12) -1. For a stipulated 10% rate, Rm = (1 + 0.1025)^(1/12) - 1 = 1.00816485 - 1 = 0.8165% (approx). BTW - You will often see the monthly factor calculated as (1+r/2)^(1/6) since the twelfth root of the semi-annual rate squared is the same as the sixth root of the semi-annual rate.

If you use the monthly rate in calculating interest (or in Cumprinc, Cumint, etc using the amortization period in years times 12 as the number of periods) you will arrive at the right Canadian results - unless your lender is using daily calculations - but even there, you will be within pennies....

5. ## Re: Canadian Mortgage Schedule (2000)

Thank you ... I should have known another Canadian would come through with a better explanation!

Cheers

#### Posting Permissions

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