"DRB" wrote:
If I want to calculate a mortgage payment with Canadian
semi-annual compounding I can't use the built in functions.

I believe you can, if you know how. I am not familiar with
Canadian mortgages, but based on one Canadian mortgage
calculator [1], I believe the following would work using normal
worksheet functions.

Example: 25yr loan of $130,000 at 4.89%. The monthly
payment ($747.97) can be computed as follows:

=PMT(RATE(6,,-1,1+4.89%/2),25*12,-130000)

Explanation: Apparently, the monthly interest rate is the
compounded rate that equals the semi-annual rate, defined
as the annual rate divided by two.

I cannot say with impunity that is correct because I have
found ambiguous explanations of Canadian mortgage rates.

But I ass-u-me that a Canadian lender's calculator does the
right thing. I also note that the above formula is equivalent
to another formula offered by www.exceltip.com [2].


-----
[1] http://www.canequity.com/mortgage-calculator

[2]
http://www.exceltip.com/st/Calculati...ents/1129.html
Note that (4.89%/2 + 1)^(2/12) - 1 is mathematically the
same as RATE(6,,-1,1+4.89%/2), and the numerical results
are the same to 12 significant digits in this example.