Thanks for the information. Now I can see why US banks use daily compounding,
because it allows them to advertise a lower APR. I'm amazed the regulations
don't require the same annualizing as XIRR does, because that's the only
effective way of comparing rates. Live and learn. I'm glad Reg Z doesn't apply
in Canada.

--
Regards,
Fred


wrote in message
ups.com...
Fred Smith wrote:
XIRR would be a more popular choice among knowledgeable users, because it
will
automatically calculate the annual APR.

I suppose you could say it depends on your goal.

If your goal is to compute a (US) RegZ-compliant APR, XIRR gives the
wrong answer. (Reg Z is called the Truth in Lending Act.)

The reason is that XIRR annualizes the rate by compounding the daily
rate over 365 days. In other words, it is a compounded or effective
annual rate. But Reg Z specifies that the APR is "the __nominal__
annual percentage rate determined by __multiplying__ the unit-period
rate by the number of unit-periods in a year".

(The unit-period is determined by the payment frequency -- monthly, in
the OP's case.)

For example, using the OP's loan specifications and assuming payments
on the 1st of each month starting in Oct 2006, XIRR computes 8.2736%.
IRR computes 7.9812%. The IRR number matches the APR computed by the
OP's mortgage software, which presumably computes the APR in accordance
with the laws of some jurisdiction, perhaps the US.

By the way, "nominalizing" the XIRR number results in 7.9755%. Since
that is within 0.125% of the RegZ-compliant APR, as generally required
by Reg Z, arguably you could use XIRR if you "nominalize" its result.
One way to do that is:

=12 * rate(12, 0, -1, 1+XIRR(...))

Seems to me like a lot of trouble to compute the wrong answer. ;-)