+ Reply to Thread
Results 1 to 8 of 8

#VALUE error with XIRR

  1. #1
    bdyer30
    Guest

    #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


  2. #2
    Ron Rosenfeld
    Guest

    Re: #VALUE error with XIRR

    I get 61.62% using your data.



    On Tue, 24 Jan 2006 04:49:02 -0800, "bdyer30"
    <[email protected]> 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

  3. #3
    bdyer30
    Guest

    Re: #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"
    > <[email protected]> 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
    >


  4. #4
    bdyer30
    Guest

    Re: #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"
    > > <[email protected]> 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
    > >


  5. #5
    Ron Rosenfeld
    Guest

    Re: #VALUE error with XIRR

    On Tue, 24 Jan 2006 07:16:02 -0800, "bdyer30"
    <[email protected]> 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

  6. #6
    bdyer30
    Guest

    Re: #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"
    > <[email protected]> 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
    >


  7. #7
    bdyer30
    Guest

    Re: #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"
    > <[email protected]> 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
    >


  8. #8
    Ron Rosenfeld
    Guest

    Re: #VALUE error with XIRR

    On Tue, 24 Jan 2006 08:01:02 -0800, "bdyer30"
    <[email protected]> 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1