Loan Amortization calculater (Canadian version)

[joe stern]

i am a Canadian mortgage broker and can only use the US version, Is there an
Excel canadain version Loan Amortization template. The original Excell loan
amortization is the US version (P=12, C=12). while i would like to use the
Canadian version (P=12, C=2)
thank you

 

[joeu2004]

i am a Canadian mortgage broker and can only use the US
version, Is there an Excel canadain version Loan Amortization
template. The original Excell loan amortization is the US
version (P=12, C=12). while i would like to use the Canadian
version (P=12, C=2)

I cannot find a ready-made template, but you might be able to
make the following modifications to the US template -- if the
Canadian mortgage payment is still monthly. (I see other options
online.)

First, the payment can be computed as follows:

=PMT((1 + rate/2)^(1/6) - 1, months, -loan)

where "rate" is the annual rate, "months" is the term of the
amortization (12*years), and "loan" is the amount of the loan.
This results of the above formula is consistent with the Canadian
mortgage calculator at
http://www.canadamortgage.com/calculators/amortization.cgi .

Second, the monthly interest rate is (1+rate/2)^(1/6)-1. This
might need to replace an expression of the form (1+rate)/12 in
one or more columns of the template. For efficiency, it would
be better to put that formula into one cell, then replace (1+rate)/12
with a reference to that cell (e.g. $C$3).

HTH.

Now, can you explain something about Canadian mortgages --
specifically the aforementioned calculator? What is the significance
of the so-called "mtg term" (mortgage term) of 3 yr, as
distinguished from the "amortization [term]"?

It does not seem to affect the PMT() computation. Is this how
a Canadian variable-rate mortgage is specified? Is the annual
rate guaranteed only for the "mortgage term"? In US terms, I
believe that would be the "fixed-rate term", and the "amortization
[term]" would be the "loan term".

 

[Fred Smith]

Canadian mortgages are compounded semi-annually, as you've found out. Most
people pay their mortgages monthly, so the rate must be converted for the
financial functions to work. Your formula does this.

Semi-annual compounding has been in federal legislation for a long time. It's a
quirk we have and isn't going to change. While banks would probably prefer to
compound more often, it has the advantage that it's difficult for the average
consumer to check their calculations.

Mortgages originally were issued at a fixed rate (6% was common) and a 25-year
term. Both you and the bank were locked in for this deal -- if you wanted to
make additional payments, or pay it out early, there was a penalty (typically
three months interest). This differs substantially from US mortgages which can
be prepaid without penalty, which is why you often have an upfront charge
("points") which essentially compensates the bank for this flexibility.

When interest rates started to rise in the early 70s, banks lost interest in
guaranteeing the rate for that long. So they came up with mortgages where the
rate was guaranteed for a shorter length of time (typically 1 to 5 years) -- the
"mortgage term". However, very few people could afford to pay off a mortgage in
5 years, so they calculated the payment as if the term was 25 years -- the
"amortization period".

To answer your question, the rate is guaranteed only for the "mortgage term".
You get to renegotiate with the original, or any other, bank at the end of the
term.

Variable rates are different. They work much like a line of credit, where
typically the rate is linked to prime. With a variable rate, the rate can
fluctuate during the term.

--
Regards,
Fred

i am a Canadian mortgage broker and can only use the US
version, Is there an Excel canadain version Loan Amortization
template. The original Excell loan amortization is the US
version (P=12, C=12). while i would like to use the Canadian
version (P=12, C=2)

I cannot find a ready-made template, but you might be able to
make the following modifications to the US template -- if the
Canadian mortgage payment is still monthly. (I see other options
online.)

First, the payment can be computed as follows:

=PMT((1 + rate/2)^(1/6) - 1, months, -loan)

where "rate" is the annual rate, "months" is the term of the
amortization (12*years), and "loan" is the amount of the loan.
This results of the above formula is consistent with the Canadian
mortgage calculator at
http://www.canadamortgage.com/calculators/amortization.cgi .

Second, the monthly interest rate is (1+rate/2)^(1/6)-1. This
might need to replace an expression of the form (1+rate)/12 in
one or more columns of the template. For efficiency, it would
be better to put that formula into one cell, then replace (1+rate)/12
with a reference to that cell (e.g. $C$3).

HTH.

Now, can you explain something about Canadian mortgages --
specifically the aforementioned calculator? What is the significance
of the so-called "mtg term" (mortgage term) of 3 yr, as
distinguished from the "amortization [term]"?

It does not seem to affect the PMT() computation. Is this how
a Canadian variable-rate mortgage is specified? Is the annual
rate guaranteed only for the "mortgage term"? In US terms, I
believe that would be the "fixed-rate term", and the "amortization
[term]" would be the "loan term".