XIRR #NUM error - help!

  • Thread starter Thread starter aa
  • Start date Start date
A

aa

Why does XIRR return #NUM for this data set? any help is appreciated.
using excel 2003

17-Aug-10 178200
18-Aug-10 -5329800.11
18-Aug-10 184500
18-Aug-10 300000
18-Aug-10 -4881000.14
18-Aug-10 -4562099.88
20-Aug-10 -5273100
27-Aug-10 48600
02-Sep-10 551590.53
03-Sep-10 -9920400
13-Sep-10 106400
01-Oct-10 -274500
01-Oct-10 -638399.99
01-Oct-10 10247999.57
01-Oct-10 5557500
01-Oct-10 -353999.98
01-Oct-10 -189899.99
01-Oct-10 4732200.16
01-Oct-10 5154000.09
 
From the Excel (97) help file...

The #NUM! error value occurs when a problem occurs with a number in a formula or function.
Possible cause:
Suggested action:

Using an unacceptable argument in a function that requires a numeric argument.
Make sure the arguments used in the function are the correct type of arguments.

Using a worksheet function that iterates, such as IRR or RATE, and the function cannot find a result.
Use a different starting value for the worksheet function.

Entering a formula that produces a number that is too large or too small to be represented in Microsoft Excel.
Change the formula so that its result is between -1*10^303 and +1*10^307

--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial Excel programs)

..
..
..

"aa" <[email protected]>
wrote in message
Why does XIRR return #NUM for this data set? any help is appreciated.
using excel 2003

17-Aug-10 178200
18-Aug-10 -5329800.11
18-Aug-10 184500
18-Aug-10 300000
18-Aug-10 -4881000.14
18-Aug-10 -4562099.88
20-Aug-10 -5273100
27-Aug-10 48600
02-Sep-10 551590.53
03-Sep-10 -9920400
13-Sep-10 106400
01-Oct-10 -274500
01-Oct-10 -638399.99
01-Oct-10 10247999.57
01-Oct-10 5557500
01-Oct-10 -353999.98
01-Oct-10 -189899.99
01-Oct-10 4732200.16
01-Oct-10 5154000.09
 
Jim -

I understand that Excel is trying to iterate and find a possible
solution and it cannot find it. However I know that a solution exists
with the above dataset (colleague ran the same numbers using
Mathematica). In fact when the cashflows for the 17th and 18th are
summed up and represented as one cashflow then excel returns the
correct answer.
 
I put your data in A1:B20
and I used this formula in D1:
=XIRR(B1:B20,A1:A20)

And it returned: 2.98023E-09

I used xl2003 in my test.

What was your formula and what your initial guess?
 
I put your data in A1:B20
and I used this formula in D1:
=XIRR(B1:B20,A1:A20)

And it returned:  2.98023E-09

I used xl2003 in my test.

What was your formula and what your initial guess?
Click to expand...

Dave -

This is surprising. I'm using Excel 2003 as well. There was no initial
guess in my formula. And my result is #NUM!. One thing to note: The
row number in your formula is 20. There's nineteen rows of data. Can
you make sure your the data used in the formula is exactly as it
appears at the beginning of this thread?
 
It was a mistake. I must have pasted in A2:B20 and not noticed what was in row
1. (I reuse the same worksheet over and over while testing.)

Sorry.

I did get the #num error when I retested.
 
I understand that Excel is trying to iterate and find a possible
solution and it cannot find it. However I know that a solution exists
with the above dataset (colleague ran the same numbers using
Mathematica).
Click to expand...

What result does Mathematica give?

I stumbled across one saddle point (there might be others) between
-79.294% and -79.295%. Using a binary search algorithm, I determined
that about -79.29402041327662% causes the sum of the discounted cash
flows to be about zero [1].

I cannot explain why Excel XIRR does not find that solution when I
enter that exact number as a "guess"[!]. I suspect the internal
algorithm fails to try the "guess" initially.

But it does not surprise me that Excel XIRR fails without a "guess"
that is close enough. It simply means that Excel XIRR did not find a
suitable solution within the parametric limitations of the internal
algorithm. See the XIRR help page. Also see the wiki page for the
Internal Rate of Return for other mathematical considerations.

In fact when the cashflows for the 17th and 18th are summed
up and represented as one cashflow then excel returns the
correct answer.
Click to expand...

That does not work for me, using Excel 2003, even when I net all of
the cash flows on the same dates.


-----
Endnotes

[1] Re: "about -79.29402041327662% causes the sum of the discounted to
be __about__ zero". The sum is exactly zero when I compute the
individual discounted cash flows and sum them. It is about 9.3E-10
when I use an equivalent SUMPRODUCT expression. I cannot explain the
difference; usually they agree exactly. But 9E-10 is close enough to
zero. Note that the exact binary representation of
-79.29402041327662% must be entered using VBA
CDbl("-0.7929402041327662"). The Excel constant -79.2940204132766%
results in a SUMPRODUCT of about -4.66E-9.
 
I cannot explain why Excel XIRR does not find that solution when
I enter that exact number as a "guess"[!].
Click to expand...

My own Newton-Raphson implementation does find a solution when the
"guess" is between -48% and -99% inclusive. Any "guess" outside that
range results in results in 1+r going negative before an adequate
solution can be found (i.e. NPV < 0.005), so I return an error.

Excel XIRR returns an error even with an adequate "guess". There is
no good reason for that, although I don't know any specifics about how
Excel XIRR beyond what is documented in http://support.microsoft.com/kb/90728.

Note: I am using Excel 2003. I believe XIRR was "improved" in Excel
2010. YMMV.
 
Back
Top