Canadian Mortgage Formula

[steve]

I'm trying to create an amortization table in Excel for a
Canadian Mortgage but I can't find the right formula. PMT
does not work in Canada since interest is calculated on
the balance every 6 months but the payments are made
monthly. I don't get it.

Let me know at (e-mail address removed)

Thank you

 

[Norman Harker]

Hi Steve!

I believe that the Canadian mortgages are based upon an annual nominal
interest rate compounded two times per year. Thus if the quoted rate is 8%
this is equivalent to 4% per half year effective.

If payments are made monthly you need to find the effective rate per month
that is equivalent to 8% per annum nominal compounded half yearly.

To get this conversion for monthly payments use:

=(1+CanRate/2)^(2/12)-1
for 8% CanRate you can calculate the equivalent monthly effective rate
using:
=(1+8%/2)^(2/12)-1 returns 0.65582%

This rate is equivalent to 0.65582 * 12 = 7.86984% in comparison to the US
(normal) APR (Nominal compounded monthly).

With the monthly equivalent to hand, it's now possible to calculate the
payments:

=PMT((1+8%/2)^(2/12)-1,240,100000,0,0)
Returns: -828.357482279282

In setting up your amortization table you can use a monthly frequency and
use the calculated monthly effective rate to get the interest paid each
month. If that is deducted from the payment made, the balance is the
principal repayment. That is deducted from the previous month's balance to
get the balance for the current month.


--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Kevin Stecyk]

"Norman Harker" wrote ...

Hi Steve!

I believe that the Canadian mortgages are based upon an annual nominal
interest rate compounded two times per year. Thus if the quoted rate is 8%
this is equivalent to 4% per half year effective.

If payments are made monthly you need to find the effective rate per month
that is equivalent to 8% per annum nominal compounded half yearly.

To get this conversion for monthly payments use:

=(1+CanRate/2)^(2/12)-1
for 8% CanRate you can calculate the equivalent monthly effective rate
using:
=(1+8%/2)^(2/12)-1 returns 0.65582%

This rate is equivalent to 0.65582 * 12 = 7.86984% in comparison to the US
(normal) APR (Nominal compounded monthly).

With the monthly equivalent to hand, it's now possible to calculate the
payments:

=PMT((1+8%/2)^(2/12)-1,240,100000,0,0)
Returns: -828.357482279282

In setting up your amortization table you can use a monthly frequency and
use the calculated monthly effective rate to get the interest paid each
month. If that is deducted from the payment made, the balance is the
principal repayment. That is deducted from the previous month's balance to
get the balance for the current month.

I just wanted to comment: Excellent and thorough answer!

Best regards,
Kevin

 

[Norman Harker]

Hi Kevin!

Thanks for kind comments as they are always appreciated. I don't know why
the Canadians use this rate quotation basis but I suspect it is linked to
Government issued bonds that use a 6 monthly compounding frequency. Mind
you, it could just be to confuse those south of the border; a commendable
(but easily achieved) aim <vbg>

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Kevin Stecyk]

"Norman Harker" wrote ...

Thanks for kind comments as they are always appreciated. I don't know why
the Canadians use this rate quotation basis but I suspect it is linked to
Government issued bonds that use a 6 monthly compounding frequency. Mind
you, it could just be to confuse those south of the border; a commendable
(but easily achieved) aim <vbg>

Hi Norman,

I am not really sure why we use that system either. Your suspicion could
very well be correct. Or someone just began doing it that way long ago and
it became the standard practice here.

I thought your answer was exceptionally well done, and it should prove very
helpful to Steve.

Best regards,
Kevin

 

[Guest]

Did you get a formula for Candian Mortgages...

I'm looking for the same

----- steve wrote: ----

I'm trying to create an amortization table in Excel for a
Canadian Mortgage but I can't find the right formula. PMT
does not work in Canada since interest is calculated on
the balance every 6 months but the payments are made
monthly. I don't get it

Let me know at (e-mail address removed)

Thank yo

 

[Domenic]

Hi Jim,

For calculating Canadian Mortgages, which are calculated semi-annually,
not in advance, try the following formula:

=PMT(((10%/2+1)^2)^(1/12)-1,300,100000)

Based on the following:

Interest Rate: 10%
Amortization Period: 25 Years (300 months)
Mortgage Amount: $100,000

Also, if you're looking for an amortization table, I have one that I can
e-mail you. Let me know if you're interested. It's one that was
provided by Microsoft and that I adapted for Canadian Mortgages.

BTW, the file is in the Macintosh format. But you shouldn't have any
problems with it.

Hope this helps!

 

[Fred Smith]

Canadian mortgages have interest compounded semi-annually. As payments are
normally monthly, you need to calculate the equivalent monthly rate in order to
calculate the proper payment. This is what your formula does: it takes the
semi-annual rate (A/2), and converts it to a monthly rate (^1/6) as there are
six months in half a year.

Converting to any other mode simply requires calculating the number of periods
in half a year. Ergo, change the 6 to 12 for semi-monthly, 13 for bi-weekly and
26 for weekly. And, of course, adjust B for the increased number of periods. You
may find it easier to have B in years, and calculate the number of periods in
your PMT function.

Does this help?