Can XIRR start with a "0" in the first period

  • Thread starter Thread starter carlsondaniel
  • Start date Start date
C

carlsondaniel

I am having issues calculating XIRR. When my first cash flow is 0, I
get an error message, even though a regular IRR calc allows a 0 in the
first period. Is this just the way XIRR works or is there a way I can
get around it? Thanks for you help.
 
I am having issues calculating XIRR. When my first cash flow is 0, I
get an error message, even though a regular IRR calc allows a 0 in the
first period. Is this just the way XIRR works or is there a way I can
get around it?
Click to expand...

This appears to be a defect in the XIRR algorithm. As you noted, IRR
works just fine insofar as the presence of initial zero cash flows
should have no effect on the internal rate of return.

In my example cash flow model, XIRR resulted in an infinitesimally very
small internal rate of return, not an error. But that is still an
incorrect answer. And based on that result, I would not be surprised
that some cash flow models do result in an error. The error you saw
might be avoided by a "good" initial guess. But I mean "good" for the
incorrect XIRR algorithm, not "good" for the computation of the true
internal rate of return.

In my example, I set up the following column of cash flows: 0, 0,
-10000, 1000, 2000, 20000. In a parallel column, I set up the
following dates: 1/1/2001 for the first cash flow; =A1+365 copied down
for the subsequent cash flows.

IRR(A1:A6), IRR(A2:A6) and IRR(A3:A6) all correctly compute the same
internal rate of return, namely 34.8%. XIRR(A3:A6,B3:B6) correctly
computes the same internal rate of return. But XIRR(A1:A6,B1:B6) and
XIRR(A2:A6,B2:B6) incorrectly compute nearly 0%, namely 3E-09. (I am
using Office Excel 2003.)
 
I am also getting 0.00% for my XIRR. Is there a way around this?

Also for your dates try A1 = 1/1/2001, B2 = (=edate(a1,1) to calculate
your months.

Dan
 
I am also getting 0.00% for my XIRR. Is there a way around this?
Click to expand...

As I indicated, choosing an appropriate "guess" might work. But since
this is a flaw in the XIRR algorithm, I cannot say for sure that that
would work all the time.
Also for your dates try A1 = 1/1/2001, B2 = (=edate(a1,1) to calculate
your months.
Click to expand...

I purposely add 365 in order to be consistent with XIRR's sense of "a
year". I wanted the payments periods to be equal to be consistent with
IRR, even though XIRR is needed only when payment periods are unequal.
The point of my example was to demonstrate the XIRR flaw, not to
demonstrate how to use XIRR.
 
Errata....
As I indicated, choosing an appropriate "guess" might work.
Click to expand...

I'm sorry. You meant: is there a way to coerce XIRR to compute the
correct answer, namely 34.8% in my example?

I think the right answer is to ensure that the XIRR ranges (values and
dates) skip the initial zero cash flows. Off-hand, I cannot think of a
formula to accomplish that. But I am "sure" there is such a formula,
and I hope someone more clever will post it.
 
Back
Top