I'm trying to build a spreadsheet that will calculate the PMT, FV CUMINT,
CUMPRINC etc etc for a range of interest rates, PV's and NPER's. I can do for
Variable Rates which are compounded monthly (for obvious reasons) but with
Fixed Rates (here in Canada) the quoted annual rate must be compounded semi
annually (and not monthly as in the US). I cannot determine how to do this
and thought there may be a quick solution. I was not aware of the two
functions you pointed out and will have a close look at them. Thanks.

"Niek Otten" wrote:

You'll have to establish an effective rate per month, taking into account semi-annual payments.
There have been many discussions here about the way one should do that. If your aim is to check or predict the calculations of
your mortgage company you'll have to get their calculation rules.
In the meantime you could experiment with the EFFECT() and NOMINAL() functions.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Iain" wrote in message ...
| Excel provides functions to calculate a variety of numbers for mortgages
| (when they have a monthly compounding interest rate) but I cannot find
| functions to generate those numbers when the quoted annual rate is compounded
| on a semi annual basis.