XIRR #NUM error - help!
aa
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 Cone
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" <aam...@gmail.com>
wrote in message
news:cd3dca37-c0f6-4172...@p26g2000yqb.googlegroups.com...
aa
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.
On Oct 11, 4:31 pm, "Jim Cone" <james.cone...@comcast.netXXX> wrote:
> 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 USAhttp://www.mediafire.com/PrimitiveSoftware
> (free and commercial Excel programs)
>
> .
> .
> .
>
> "aa" <aam...@gmail.com>
> wrote in messagenews:cd3dca37-c0f6-4172...@p26g2000yqb.googlegroups.com...
Dave Peterson
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?
--
Dave Peterson
aa
>
> - Show quoted text -
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?
Ron Rosenfeld
<pete...@XSPAMverizon.net> wrote:
>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.
Dave,
In XL2007, I cannot replicate your result. I get a #NUM error.
However, if I change the sign on the initial entry; ie
B2: -178200 vice 178200
I will then get the same result as you.
Dave Peterson
1. (I reuse the same worksheet over and over while testing.)
Sorry.
I did get the #num error when I retested.
--
Dave Peterson
joeu2004
> 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).
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.
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.
joeu2004
> I cannot explain why Excel XIRR does not find that solution when
> I enter that exact number as a "guess"[!].
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.