"joe stern" wrote:
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/calcul...ortization.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".