#VALUE error with XIRR

[bdyer30]

The XIRR function returns #VALUE with the following data (no guess). Excel
help says that #VALUE is returned when dates are not valid, but when I use
more simple cash flows with the same dates, I get a valid result. I have
troubleshooted this a fair amount and am at a loss. Any suggestions would be
much appreciated.

Here are the data:

6/30/2004 (€ 100,000)
9/30/2004 (€ 2,102,139)
12/31/2004 (€ 112,500)
3/31/2005 (€ 112,500)
6/30/2005 (€ 112,500)
9/30/2005 (€ 112,500)
12/31/2005 (€ 112,500)
3/31/2006 € 387,500
6/30/2006 (€ 112,500)
9/30/2006 (€ 112,500)
12/31/2006 (€ 112,500)
3/31/2007 (€ 112,500)
6/30/2007 (€ 112,500)
9/30/2007 (€ 112,500)
12/31/2007 (€ 112,500)
3/31/2008 (€ 112,500)
6/30/2008 (€ 112,500)
9/30/2008 € 2,607,420
12/31/2008 (€ 112,500)
3/31/2009 (€ 112,500)
6/30/2009 (€ 112,500)
9/30/2009 € 2,834,940
12/31/2009 (€ 46,260)
3/31/2010 € 3,753,428
6/30/2010 € 3,756,240
9/30/2010 € 3,759,053
12/31/2010 € 6,971,788
3/31/2011 € 4,276,859
6/30/2011 € 5,581,281
9/30/2011 € 5,586,469
12/31/2011 € 5,591,658
3/31/2012 € 2,556,534
6/30/2012 € 2,558,909
9/30/2012 € 2,561,285
12/31/2012 € 2,563,661
3/31/2013 € 2,566,037

 

[Ron Rosenfeld]

I get 61.62% using your data.

The XIRR function returns #VALUE with the following data (no guess). Excel
help says that #VALUE is returned when dates are not valid, but when I use
more simple cash flows with the same dates, I get a valid result. I have
troubleshooted this a fair amount and am at a loss. Any suggestions would be
much appreciated.

Here are the data:

6/30/2004 (¤ 100,000)
9/30/2004 (¤ 2,102,139)
12/31/2004 (¤ 112,500)
3/31/2005 (¤ 112,500)
6/30/2005 (¤ 112,500)
9/30/2005 (¤ 112,500)
12/31/2005 (¤ 112,500)
3/31/2006 ¤ 387,500
6/30/2006 (¤ 112,500)
9/30/2006 (¤ 112,500)
12/31/2006 (¤ 112,500)
3/31/2007 (¤ 112,500)
6/30/2007 (¤ 112,500)
9/30/2007 (¤ 112,500)
12/31/2007 (¤ 112,500)
3/31/2008 (¤ 112,500)
6/30/2008 (¤ 112,500)
9/30/2008 ¤ 2,607,420
12/31/2008 (¤ 112,500)
3/31/2009 (¤ 112,500)
6/30/2009 (¤ 112,500)
9/30/2009 ¤ 2,834,940
12/31/2009 (¤ 46,260)
3/31/2010 ¤ 3,753,428
6/30/2010 ¤ 3,756,240
9/30/2010 ¤ 3,759,053
12/31/2010 ¤ 6,971,788
3/31/2011 ¤ 4,276,859
6/30/2011 ¤ 5,581,281
9/30/2011 ¤ 5,586,469
12/31/2011 ¤ 5,591,658
3/31/2012 ¤ 2,556,534
6/30/2012 ¤ 2,558,909
9/30/2012 ¤ 2,561,285
12/31/2012 ¤ 2,563,661
3/31/2013 ¤ 2,566,037

--ron

 

[bdyer30]

Thanks for taking a look.

I am getting a valid result in the Formula argument box, but for some reason
it is outputting "#VALUE" in the cell. Any idea why this might happen?

Thanks

 

[bdyer30]

For example, with the data below, the "formula result" in the formula box is
15.14%, but the cell still says "#VALUE." Any ideas?

Thanks,

New data:

6/30/2004 (€ 100,000)
9/30/2004 (€ 112,500)
12/31/2004 (€ 112,500)
3/31/2005 (€ 112,500)
6/30/2005 (€ 112,500)
9/30/2005 (€ 112,500)
12/31/2005 (€ 112,500)
3/31/2006 (€ 112,500)
6/30/2006 (€ 112,500)
9/30/2006 (€ 112,500)
12/31/2006 (€ 112,500)
3/31/2007 (€ 112,500)
6/30/2007 (€ 112,500)
9/30/2007 (€ 112,500)
12/31/2007 (€ 112,500)
3/31/2008 (€ 112,500)
6/30/2008 (€ 112,500)
9/30/2008 (€ 112,500)
12/31/2008 (€ 112,500)
3/31/2009 (€ 112,500)
6/30/2009 (€ 112,500)
9/30/2009 (€ 46,260)
12/31/2009 (€ 46,260)
3/31/2010 (€ 43,447)
6/30/2010 (€ 40,635)
9/30/2010 (€ 37,822)
12/31/2010 (€ 32,634)
3/31/2011 (€ 27,445)
6/30/2011 (€ 22,257)
9/30/2011 (€ 17,068)
12/31/2011 (€ 11,880)
3/31/2012 (€ 9,504)
6/30/2012 (€ 7,128)
9/30/2012 (€ 4,752)
12/31/2012 (€ 2,376)
3/31/2013 € 0
12/31/2004 (€ 25,000)
12/31/2005 (€ 25,000)
12/31/2006 (€ 25,000)
12/31/2007 (€ 25,000)
12/31/2008 (€ 25,000)
12/31/2009 (€ 25,000)
12/31/2010 (€ 25,000)
12/31/2011 (€ 25,000)
12/31/2012 (€ 25,000)
9/30/2004 (€ 289,639)
9/30/2004 (€ 950,000)
9/30/2004 (€ 750,000)
9/30/2005 (€ 750,000)
3/31/2006 (€ 750,000)
6/30/2006 (€ 750,000)
9/30/2006 (€ 750,000)
12/31/2006 (€ 750,000)
3/31/2007 (€ 750,000)
6/30/2007 (€ 750,000)
9/30/2007 (€ 750,000)
12/31/2007 (€ 750,000)
12/31/2006 (€ 633,589)
3/31/2007 (€ 633,589)
6/30/2007 (€ 633,589)
9/30/2007 (€ 633,589)
12/31/2007 (€ 633,589)
3/31/2008 (€ 633,589)
6/30/2008 (€ 633,589)
9/30/2008 (€ 633,589)
12/31/2008 (€ 633,589)
3/31/2009 (€ 633,589)
9/30/2004 € 0
12/31/2004 € 0
3/31/2005 € 0
6/30/2005 € 0
9/30/2005 € 0
12/31/2005 € 0
3/31/2006 € 500,000
6/30/2006 € 0
9/30/2006 € 0
12/31/2006 € 0
3/31/2007 € 0
6/30/2007 € 0
9/30/2007 € 0
12/31/2007 € 0
3/31/2008 € 0
6/30/2008 € 0
9/30/2008 € 2,719,920
12/31/2008 € 0
3/31/2009 € 0
6/30/2009 € 0
9/30/2009 € 2,881,200
12/31/2009 € 0
3/31/2010 € 1,831,055
6/30/2010 € 1,831,055
9/30/2010 € 1,831,055
12/31/2010 € 3,144,866
3/31/2011 € 3,144,866
6/30/2011 € 115,984
9/30/2011 € 0
12/31/2011 € 0
3/31/2012 € 0
6/30/2012 € 0
9/30/2012 € 0
12/31/2012 € 0
3/31/2013 € 0
9/30/2004 € 0
12/31/2004 € 0
3/31/2005 € 0
6/30/2005 € 0
9/30/2005 € 0
12/31/2005 € 0
3/31/2006 € 0
6/30/2006 € 0
9/30/2006 € 0
12/31/2006 € 0
3/31/2007 € 0
6/30/2007 € 0
9/30/2007 € 0
12/31/2007 € 0
3/31/2008 € 0
6/30/2008 € 0
9/30/2008 € 0
12/31/2008 € 0
3/31/2009 € 0
6/30/2009 € 0
9/30/2009 € 0
12/31/2009 € 0
3/31/2010 € 0
6/30/2010 € 0
9/30/2010 € 0
12/31/2010 € 0
3/31/2011 € 0
6/30/2011 € 3,028,882
9/30/2011 € 3,144,866
12/31/2011 € 2,016,253
3/31/2012 € 360,000
6/30/2012 € 360,000
9/30/2012 € 360,000
12/31/2012 € 360,000
3/31/2013 € 360,000
9/30/2004 € 0
12/31/2004 € 0
3/31/2005 € 0
6/30/2005 € 0
9/30/2005 € 0
12/31/2005 € 0
3/31/2006 € 0
6/30/2006 € 0
9/30/2006 € 0
12/31/2006 € 0
3/31/2007 € 0
6/30/2007 € 0
9/30/2007 € 0
12/31/2007 € 0
3/31/2008 € 0
6/30/2008 € 0
9/30/2008 € 0
12/31/2008 € 0
3/31/2009 € 0
6/30/2009 € 0
9/30/2009 € 0
12/31/2009 € 0
3/31/2010 € 0
6/30/2010 € 0
9/30/2010 € 0
12/31/2010 € 0
3/31/2011 € 0
6/30/2011 € 0
9/30/2011 € 0
12/31/2011 € 0
3/31/2012 € 0
6/30/2012 € 646,988
9/30/2012 € 691,049
12/31/2012 € 691,049
3/31/2013 € 691,049

 

[Ron Rosenfeld]

For example, with the data below, the "formula result" in the formula box is
15.14%, but the cell still says "#VALUE." Any ideas?

Again, it calculates properly for me.

My guess is that there is something wrong with your data; and that it is not
exactly the same as you present it in your post, or not being properly
interpreted by your OS.

Is the data the result of formula? Is it being pasted in from an HTML document
or web site?

If so, it may be that one or more of the data points has a non-printing
character that cannot be properly interpreted by the XIRR function.

Could there be a problem with proper interpretation of the dates? I note that
the dates are in US format while your cash data is in Euros. If your regional
settings are not also in US format, then some of the "dates" may look OK, but
may really be text or invalid.


--ron

 

[bdyer30]

Good ideas, I'll check those out and let you know. Thanks again for all the
help...I really need to figure this out.

 

[bdyer30]

You're right, because when I paste values on all the data it turns out OK.

Any idea what might be wrong with the data? I need it to autoupdate from
other sheets.

 

[Ron Rosenfeld]

You're right, because when I paste values on all the data it turns out OK.

Any idea what might be wrong with the data? I need it to autoupdate from
other sheets.

Is the data the result of formula?

Is it being pasted in from an HTML document or web site?

If so, it may be that one or more of the data points has a non-printing
character that cannot be properly interpreted by the XIRR function.

Could there be a problem with proper interpretation of the dates? I note that
the dates are in US format while your cash data is in Euros. If your regional
settings are not also in US format, then some of the "dates" may look OK, but
may really be text or invalid.

--ron