Hi Christina!
I've looked at the workbook and there are a few problems. But there
are solutions:
XIRR like IRR has a third argument. It a guess rate. In the absence of
this argument it uses a default rate of 10%. That usually works but in
those two cases you have negative returns. If you insert in the
"rogue" formulas -0.9, you'll find that they will both resolve.
In fact, to my mind this is a bug. If I use the IRR on these cash
flows the function returns an error message #DIV/0! Whilst I never
liked that error message, at least it is an error message and not a
(potentially) rational answer. The 0 return is potentially rational
and in my view this is a bug.
You'll find that the guess rate of -0.9 will almost always produce an
answer irrespective of whether the return is positive or negative.
But there are still problems! The classic test of the XIRR is that
XNPV using the XIRR return as a discount rate should, by definition,
return 0. But with your cash flows the answer I get is #NUM! I perform
my test by going back to basics and discounting each cash flow at the
daily equivalent of the calculated XIRR for the number of days from
the initial flow. The sum of those discounted flows approaches 0.
All that before the second cup of coffee!
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
