XIRR Error
 

I have the following:

1 Jan 2007 , -10000
1 Jan 2008 , 2000

I get XIRR as -80%. I understand that.

If I have the following:
1 Jan 2007 , -10000
1 Feb 2007, -10000
1 Jan 2008 , 2000

I get -91%. I understand this as well.

But, when I have the following:
1 Jan 2007 , -10000
1 Feb 2007, -10000
1 March 2007, -10000
1 Jan 2008 , 2000

I get 0%. Why?

XIRR Error
 

Hi,

If I supply a guess rate of -0.1, I get the answer as -94.8%. Have tested
it both in Excel 2007 and 2003.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Marc" wrote in message
...
I have the following:

1 Jan 2007 , -10000
1 Jan 2008 , 2000

I get XIRR as -80%. I understand that.

If I have the following:
1 Jan 2007 , -10000
1 Feb 2007, -10000
1 Jan 2008 , 2000

I get -91%. I understand this as well.

But, when I have the following:
1 Jan 2007 , -10000
1 Feb 2007, -10000
1 March 2007, -10000
1 Jan 2008 , 2000

I get 0%. Why?

XIRR Error
 

When you have results that are so far out of touch with reality, you need to
supply a reasonable guess. You have exactly the situation that the Guess
parameter was made for.

Regards,
Fred.

"Marc" wrote in message
...
I have the following:

1 Jan 2007 , -10000
1 Jan 2008 , 2000

I get XIRR as -80%. I understand that.

If I have the following:
1 Jan 2007 , -10000
1 Feb 2007, -10000
1 Jan 2008 , 2000

I get -91%. I understand this as well.

But, when I have the following:
1 Jan 2007 , -10000
1 Feb 2007, -10000
1 March 2007, -10000
1 Jan 2008 , 2000

I get 0%. Why?

XIRR Error
 

On Feb 25, 4:47*pm, "Fred Smith" wrote:
When you have results that are so far out of touch
with reality, you need to supply a reasonable guess.

Perhaps. But how is the hapless user supposed to know that the
results are "so far out of touch with reality"? Rhetorical question.
The answer: plug the XIRR result into the rate for XNPV.

But isn't that what XIRR should be doing, effectively? After all,
XIRR has an error return to indicate this very condition. From the
Help page: "If XIRR can't find a result that works after 100 tries,
the #NUM! error value is returned."

With Marc's example, XIRR returns about 2.9802E-09. With that rate,
XNPV returns -28000. I think that is sufficiently far from zero that
XIRR should recognize that the result does not "work" ;-).

I don't displute that the situation arises due to discontinuities.
But this is not an anomaly due to multiple IRRs.

IMHO, this is a simple defect in the XIRR implementation: failing to
recognize a non-solution. In Marc's last example, XIRR should have
returned #NUM! (or #DIV/0!).

XIRR Error
 

I agree that in this example, XIRR should have returned #NUM rather than 0.
Regardless, the fix is still the same -- supply a better guess.

However, I still think the example is academic, and not real world. It's
well known that providing silly numbers to XIRR can cause silly results. If
you can design a better convergence algorithm than Newton-Raphson, more
power to you. But in the real world, and I've done literally millions of
return calculations using real world data, Newton-Raphson works very
effectively.

Regards,
Fred.


"joeu2004" wrote in message
...
On Feb 25, 4:47 pm, "Fred Smith" wrote:
When you have results that are so far out of touch
with reality, you need to supply a reasonable guess.

Perhaps. But how is the hapless user supposed to know that the
results are "so far out of touch with reality"? Rhetorical question.
The answer: plug the XIRR result into the rate for XNPV.

But isn't that what XIRR should be doing, effectively? After all,
XIRR has an error return to indicate this very condition. From the
Help page: "If XIRR can't find a result that works after 100 tries,
the #NUM! error value is returned."

With Marc's example, XIRR returns about 2.9802E-09. With that rate,
XNPV returns -28000. I think that is sufficiently far from zero that
XIRR should recognize that the result does not "work" ;-).

I don't displute that the situation arises due to discontinuities.
But this is not an anomaly due to multiple IRRs.

IMHO, this is a simple defect in the XIRR implementation: failing to
recognize a non-solution. In Marc's last example, XIRR should have
returned #NUM! (or #DIV/0!).