I had posted this yesterday and did not recieve a
response - I included prior questions for reference...

What I'm trying to do is
use the most accurate formula to calculate the return for
a series of quarterly payments that are being compounded
annually. Why do I get such vastly different numbers when
i calculate the IRR and annualize it vs. just the regular
xirr? Does the XIRR compound in every period?

Also does the IRR and XIRR formula give the effective or
nominal result? Thank you very much.

-----Original Message-----
Suppose IRR returned a (quarterly) rate of 2%. When you
try to multiply by
four to get the annual rate, you get 8%, which would turn
$100 into $108 in
a year.

But if you invest $100 at 2% quarterly, you get more than
$108 after a year,
because of the compounding effect. You actually get 100*
(1.02)^4 or $108.24.
Therefore the effective annual interest rate is 8.24%.

This, by the way, should be the same as XIRR, which
automatically calculates
the annual interest rate. It won't be exact, because IRR
would assume all
deposits are made 91.25 days apart, which of course
couldn't be the case
with XIRR. However, XIRR and IRR should be within 5 bps
of each other. If
not, then your dates are probably out.

There are several ways to convert from a nominal (eg,
quarterly) to an
effective rate (eg, annual). Harlan gave you one. The
EFFECT function is
another. I like to use the FV function because it helps
me think through
"how much money would I have after a year if I invested a
dollar at this
rate?"

--
Regards,
Fred
Please reply to newsgroup, not e-mail


wrote in message
...

-----Original Message-----
"Jen" wrote...
I would like to know which function is more accurate
to
use when calculating the IRR for quarterly cash
flows. I
know XIRR is to be used for irregular cash flow
periods,
but I also heard that it is more accurate. When I
compare
the quarterly cash flow result calculated using IRR
and
multiplied by 4 to annualize the result, I get a
different
number (by about 100 basis points) than if I use the
XIRR.
Also, to make sure, both IRR and XIRR compound on an
annual basis correct?

XIRR must use some approximations in order to deal with
dates, so if your
cashflows are all quarterly, you'd be better off using
IRR to calculate the
quarterly IRR, then converting the result to an annual
IRR.

--
To top-post is human, to bottom-post and snip is
sublime.
.

Is there a more accurate way of annualizing the
quarterly
IRR other than multiplying by 4? Thank you.


.

..