+ Reply to Thread
Results 1 to 10 of 10

XIRR returning #Value Error

  1. #1
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2013
    Posts
    346

    XIRR returning #Value Error

    Hi

    In the sample sheet, Column J3,J4 and J5 I am using XIRR which is just XIRR=(values, dates) with matching criteria. Not sure why it is giving #Value Error
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    07-13-2021
    Location
    California, USA
    MS-Off Ver
    2010
    Posts
    513

    Re: XIRR returning #Value Error

    Since you did not respond to my comments in a similar thread in another forum (click here, user joeu2004), I am relunctant to contribute here. So I'll try to make my comments brief.

    1. You get a #VALUE error because it appears that you did not array-enter the formula (press ctrl+shift+Enter instead of just Enter).

    So you have an implicit intersection operation, but the row where the formula is entered (row 3) is not within the referenced ranges (rows 11:56).

    Of course, that is not you intent, in the first place.


    2. After you correct #1, you get a #VALUE error because the formula references H40:H46 in a numeric expression, but those formulas return the null string ("").


    3. After you correct #1 and #2, your formula displays 5248.25% (absurd) because you multiply XIRR by 100, but format the result as Percentage.


    4. In J4:J5, the corrected formula displays 0%. The actual value is 2.98E-09 (rounded).

    That is misleading. You can use SUMPRODUCT (*) to calculate the NPV and confirm that 2.98E-09 is not a valid IRR because the NPV is not (nearly) zero.

    As I explained in my response in the other forum, 2.98E-09 should be treated as an error; interpret it the same as #NUM.

    XIRR (and XNPV?) does not tolerate empty cells (and zero) in the beginning of the cash flow series -- for no good reason, IMHO.

    (*) The SUMPRODUCT alternative to XNPV is as follows (row 4), based on the math formula in the XIRR help page:

    Please Login or Register  to view this content.
    It can be entered normally (just press Enter, as usual). The reference to E12 is a kludge; but it works for row 4 and 5.

    -----

    A word to the wise....

    1. Your method of calculating the individual CAGRs is debatable.

    You are calculating the rate of return of the sum of the current valuations from the date of the earliest investment.

    I believe the correct calculation is the TWR.


    2. Your method of calculating the overall average XIRR and CAGR is debatable.

    You are calculating the simple average of the individual averages. In general, "the average of partial averages does not equal the average of the whole".

    If your intent is to calculate portfolio rates of return, the usual calculation is the weighted average based on the investment ("allocation"), not current valuation.

    (But technically, that might not be mathematically correct, either.)


    3. Technically, your YEARFRAC formulas in column N are incorrect.

    You are calculating YEARFRAC(endDate, startDate).

    The correct usage is YEARFRAC(startDate, EndDate).

    You are lucky that YEARFRAC is so forgiving. Most functions would return an error.


    4. It is usually misleading to annualize sub-annual rates of return.

    For example, a 1% daily gain is a compounded annualized rate of return of 3678% (absurd). Even a simple annualized rate of return of 365% is misleading.

    That is why most brokerages report a simple rate of return over the period if the period is sub-annual.
    Last edited by curiouscat408; 03-04-2023 at 08:13 PM. Reason: Item #4

  3. #3
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2013
    Posts
    346

    Re: XIRR returning #Value Error

    Can you please suggest TWR formula re placing XIRR

    Definitely, your suggestion is more valuable
    Last edited by grcshekar; 03-05-2023 at 12:51 AM.

  4. #4
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: XIRR returning #Value Error

    "XIRR (and XNPV?) does not tolerate empty cells (and zero) in the beginning of the cash flow series -- for no good reason, IMHO."
    As a kind of reaction on that statement
    L3:L5 = IRR on an excel 365 (with Filter),sorry for your 2010
    J3:J5 = IRR on an older system with auxiliary cells starting in P
    PS. i have no financial skills
    Attached Files Attached Files
    Last edited by bsalv; 03-05-2023 at 01:23 AM.
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

  5. #5
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2013
    Posts
    346

    Re: XIRR returning #Value Error

    Both showing value error

  6. #6
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: XIRR returning #Value Error

    L3:L5 are errors because you don't have Excel 365.

    the formulas in P3:AV5, do they show errors ?
    Aggregate is a excel-function since 2010, so it should be okay with your version.

  7. #7
    Forum Contributor
    Join Date
    11-10-2019
    Location
    India
    MS-Off Ver
    2013
    Posts
    346

    Re: XIRR returning #Value Error

    Can you kindly replace my formula in Column J itself

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: XIRR returning #Value Error

    Welcome (back) to the forum.

    Thanks for your question. However, we have just found out that you have posted the same question elsewhere.... and haven't told us. Feel free to cross-post on other sites...

    But. Every forum has its rules. Please see Forum Rule #3 about cross-posting. So.

    Do not keep it a secret. We all are willing to give you our time, freely. However, some of us get very annoyed if we waste our time developing a solution for you, when you already have a nice solution elsewhere. All we ask is that you show us some respect and tell us:

    1) if you have cross-posted, and

    2) the URL of the cross posts.

    That way, those of us who don't want to waste their time can quickly check to see if you're already happy with another solution.

    (Note: this requirement is not optional. No help to be offered until the link(s) is/are provided.)

    Is it cross-posted anywhere else?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  9. #9
    Valued Forum Contributor
    Join Date
    07-13-2021
    Location
    California, USA
    MS-Off Ver
    2010
    Posts
    513

    Re: XIRR returning #Value Error

    Quote Originally Posted by Glenn Kennedy View Post
    (Note: this requirement is not optional. No help to be offered until the link(s) is/are provided.)
    My apologies. I did not see this until after I submitted -- and I invested to many early-morning hours to delete it.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: XIRR returning #Value Error

    Quote Originally Posted by curiouscat408 View Post
    My apologies. I did not see this until after I submitted -- and I invested to many early-morning hours to delete it.
    Your post will be reinstated once the OP has provided the x-post links
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 11
    Last Post: 05-04-2021, 02:40 AM
  2. XIRR formula error returning tiny decimal (2.98E-09)
    By jliyanage in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-06-2021, 12:00 AM
  3. Problem with XIRR function returning #NUM!
    By Myrna Larson in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 07:05 AM
  4. [SOLVED] Problem with XIRR function returning #NUM!
    By Francois_Provost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2005, 03:05 AM
  5. Problem with XIRR function returning #NUM!
    By Francois_Provost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2005, 12:05 AM
  6. Problem with XIRR function returning #NUM!
    By Francois_Provost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-05-2005, 11:05 PM
  7. [SOLVED] Problem with XIRR function returning #NUM!
    By Francois_Provost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-05-2005, 09:40 PM

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