ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   #VALUE error with XIRR (https://www.excelbanter.com/excel-discussion-misc-queries/67038-value-error-xirr.html)

bdyer30
#VALUE error with XIRR
 
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
#VALUE error with XIRR
 
I get 61.62% using your data.



On Tue, 24 Jan 2006 04:49:02 -0800, "bdyer30"
wrote:

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
#VALUE error with XIRR
 
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

"Ron Rosenfeld" wrote:

I get 61.62% using your data.



On Tue, 24 Jan 2006 04:49:02 -0800, "bdyer30"
wrote:

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
#VALUE error with XIRR
 
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


"bdyer30" wrote:

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

"Ron Rosenfeld" wrote:

I get 61.62% using your data.



On Tue, 24 Jan 2006 04:49:02 -0800, "bdyer30"
wrote:

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

Ron Rosenfeld
#VALUE error with XIRR
 
On Tue, 24 Jan 2006 07:16:02 -0800, "bdyer30"
wrote:

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
#VALUE error with XIRR
 
Good ideas, I'll check those out and let you know. Thanks again for all the
help...I really need to figure this out.

"Ron Rosenfeld" wrote:

On Tue, 24 Jan 2006 07:16:02 -0800, "bdyer30"
wrote:

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
#VALUE error with XIRR
 
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" wrote:

On Tue, 24 Jan 2006 07:16:02 -0800, "bdyer30"
wrote:

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

Ron Rosenfeld
#VALUE error with XIRR
 
On Tue, 24 Jan 2006 08:01:02 -0800, "bdyer30"
wrote:

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


All times are GMT +1. The time now is 07:16 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com