XIRR #Num! Error

[RAB1203]

Hi - any help would be much appreciated here. I keep getting a #NUM! error
on this XIRR function. Data is as follows:

$(118,000,000) 1/1/2010
$12,750,000 2/1/2010
$10,847,083 6/1/2010
$4,170,833 7/1/2010
$18,527,083 10/1/2010
$9,750,000 11/1/2010
$3,685,833 1/1/2011
$4,500,000 2/1/2011
$7,500,000 3/1/2011
$4,535,833 4/1/2011
$11,250,000 5/1/2011
$12,750,000 6/1/2011
$13,697,083 8/1/2011
$(1,166,667) 10/1/2011
$7,457,917 1/1/2012
$3,421,667 2/1/2002
$1,067,942 5/1/2012
$25,766,666 7/1/2012

It works if I include all except for the last inflow (gives me 9%). It
works if I lower the last inflow to $3 million (gives me 15%), but as soon as
a go to $4 million or higer, I get the error. I've tried a ton of different
guesses and nothing is helping. No one in my office can figure it out. Any
suggestions would be greatly appreciated!

Thanks

 

[Joe User]

I keep getting a #NUM! error on this XIRR function.
[....]
$3,421,667 2/1/2002

There is a typo in your data. I presume that date should be 2012, not 2002.

Ordinarily, I would assume the typo is just in the posting. But when I
correct the typo, XIRR returns a reasonable IRR of about 21.77%.

So I suspect that might be your problem. If not, post a response for
additional assistance. Please include the version of Excel that you are
using. (I am using Excel 2003.)


----- original message -----

 

[Squeaky]

Hi RAB1203,

Your 3rd entry from the bottom has an incorrect date. It reads 2/1/2002.
All dates must be later than the original date of 1/1/2010.
Change it to 2/2/2012 and all should be well.

Squeaky.

Click YES if this was helpful.