Canadian Version (P=12, C=2)

Q: 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)

A: 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".