Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > #VALUE error with XIRR
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
bdyer30
 
Posts: n/a
Default #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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default #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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
bdyer30
 
Posts: n/a
Default #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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
bdyer30
 
Posts: n/a
Default #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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default #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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
bdyer30
 
Posts: n/a
Default #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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
bdyer30
 
Posts: n/a
Default #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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default #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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
irr, xirr, npv frustrations Zachary Chan Excel Worksheet Functions 4 October 25th 05 04:41 AM
IRR, XIRR and NPV - a very frustrating problem zacharychan Excel Discussion (Misc queries) 1 October 24th 05 09:01 AM
XIRR function Daniel Bonallack Excel Worksheet Functions 2 June 20th 05 09:00 PM
Problem with XIRR function returning #NUM! Francois_Provost Excel Worksheet Functions 3 June 7th 05 01:05 AM
XIRR and IRR Dan Excel Worksheet Functions 2 May 13th 05 03:20 AM


All times are GMT +1. The time now is 10:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ｩ2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement