Working on a little project for work . I needed to find a Mortgage Formula, that would work for both Canadian Mortgages and US Mortgages.

Apparently there are some key differences in the way that Canadian Mortgages are computed compared to US Mortgages. For instance, did you know that Canadian Mortgages have there interest compound semi-annually ( that is 2 times per year )? Whereas US Mortgages compound interest on a monthly basis?

Neither did I.

There are some other minor differences like the default term length of 25 years or 300 months, as opposed to 30 years in the US. But changing when the interest is compounded is what made finding the right formula tricky.

I finally came across an equation that let me solve for both Canadian Mortgage Monthly Payments and US Mortgage Monthly payments. Here is the skinny:

—— Input this data ——————————-
principal amount = E9 ( in dollars )
Amortization Period = d10 ( in years ie 6 mon = .5 )
Payments / year = D11 ( 12 = monthly, 52 = weekly )
Published Interest rate = D12 ( ie 9 % = 0.09 )
Times per year Int calculated = d13 ( CDN mortgage use 2
US mortgage use 12
all other loans use 12 )
—– Calculate the proper rate of interest ———–
e14 = Effective annual rate = EXP(D13*LN(1+(D12/D13)))-1
e15 = Interest rate per payment = (EXP(LN(E14+1)/(D10*D11))-1)*D10*D11
e17 = Payments = APMT(E9,E15/D11,D10*D11) ( both these functions are
= PMT (E9,E15/D11,D10*D11) ( identical,diff spreadsheet)
APMT( principal amount,interest rate per period,# periods )
( this is a standard function on any true commercial spreadsheet)
OR use the following if done using a calculator
= Payments = P*I/[1-(I+1)^-T]
= E9*(E15/D11)/(1-((E15/D11) +1)**(-1*D10*D11))
Total interest cost = E17*D10*D11-E9