Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    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
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Canadian Mortgage Schedule (2000)

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

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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