Mortgage Amortization - Canadian semi annual compounding

[George]

I am trying to find a formula to calculate an amortization schedule where the
interest is compounded semi-annually rather than monthly (that's how we do it
in the Great White North).

There are lots of amortization templates but they all seem to calculate
interest on a monthly compounding basis.

Any help would be appreciated.

 

[Fred Smith]

The way I answer this is to ask the question How much would I owe after one
year if I made no payments? On a $100 mortgage at 6%, the answer is:

=fv(6%/2,2,0,100) or $106.09

So a 6% Canadian mortgage (ie, compounded semi-annually) has an APR of
6.09%.

The next question is, what monthly interest rate will turn $100 into $106.09
in a year? The answer is:

=rate(12,0,100,-106.09) or 0.49%

To generalize, the formula to convert Canadian mortgage rates to monthly
rates is:

=rate(12,0,100,fv(i/2,2,0,100))

To generalize for any period it's:

=rate(nper,0,100,fv(i/2,2,0,100))

There are other solutions which don't use financial functions, or which use
effective and nominal rates, but I prefer this one because I get a better
idea of where the numbers are coming from.

Hope this helps,
Fred