+ Reply to Thread
Results 1 to 4 of 4

XIRR returns zero instead of a negative when ending value is less than total contributions

  1. #1
    Registered User
    Join Date
    09-18-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    Professional Plus 2013
    Posts
    4

    XIRR returns zero instead of a negative when ending value is less than total contributions

    Hi,

    I'm running a savings scenario where the ending value of my investment ends up being less than the total contributions based on monthly returns during a down market period, but the XIRR is returning a 0.00% instead of a negative number. In other periods, when the ending value is greater than the total contributions, the XIRR formula works properly. Can anybody explain/provide a formula that will return a negative XIRR in the attached scenario? Thanks so much!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,621

    Re: XIRR returns zero instead of a negative when ending value is less than total contribut

    Quote Originally Posted by kylc3 View Post
    XIRR is returning a 0.00% instead of a negative number.
    If you format C3 as Scientific, you will see that XIRR returns about 2.98E-09, not really zero.

    Although you might think that is close enough to zero, in my experience, that constant represents an error state in XIRR.

    In this case, I believe it is an alternative for the #NUM error.

    Thus, XIRR might require a "guess" in order to determine the IRR.

    And in fact, =XIRR(C6:C126,A6:A126,-6.5%) results in about -6.23%.

    I did not pick -6.5% out of thin air. Instead, I determined the inflection point by using the following tables.

    Please Login or Register  to view this content.
    FYI, I use SUMPRODUCT instead of XNVP because XNPV does not accept a negative discount rate, for no good reason. It's a defect, at least in Excel 2010 and earlier.

    PS.... Based on only the left-hand table, =XIRR(C6:C126,A6:A126,-5%) would have worked just as well. I drilled down one more level (right-hand table) because the NPV curve for varying discount rates seems to be very steep near the inflection point; and in my experience, often the first level on the left is not sufficient for a steep NPV curve.
    Last edited by joeu2004; 10-19-2017 at 08:32 PM. Reason: minor; PS

  3. #3
    Registered User
    Join Date
    09-18-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    Professional Plus 2013
    Posts
    4

    Re: XIRR returns zero instead of a negative when ending value is less than total contribut

    Thanks so much! One follow up question... I'm using this formula over rolling periods resulting in various outcomes, both positive and negative. Will this added parameter (-XX%) be safe to use and result in the correct XIRR for all of the different periods? Can I just pick a number like -5%?

    Or should I do something like, use an IF statement to say to use the XIRR w/o a parameter if the ending value for the period is higher than the total contributions, which results in a positive return, or use the XIRR w/ -XX% (possibly -5%?) parameter if the ending value for the period is lower than the total contributions, which results in a negative return.

    Thanks again for the help!

  4. #4
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,621

    Re: XIRR returns zero instead of a negative when ending value is less than total contribut

    Quote Originally Posted by kylc3 View Post
    Will this added parameter (-XX%) be safe to use and result in the correct XIRR for all of the different periods? Can I just pick a number like -5%?
    Well, you might get lucky. But in general, no.

    Quote Originally Posted by kylc3 View Post
    Or should I do something like, use an IF statement to say to use the XIRR w/o a parameter if the ending value for the period is higher than the total contributions, which results in a positive return, or use the XIRR w/ -XX% (possibly -5%?) parameter if the ending value for the period is lower than the total contributions, which results in a negative return.
    It's not that simple; and it has little to do with how the sum of the contributions compares with the ending value.

    First, the situations when XIRR will fail in some manner cannot be predicted.

    Second, even if they could be, it is difficult to predetermine the "guess" to use.

    It would be possible to write a VBA function that implements some form of the method that I demonstrated in my first response. In other words, determine the "guess" dynamically.

    However, there are two potential flaws with that.

    First, there can be multiple IRRs and even none. That is, in part, due to the inherent nature of the IRR -- the concept, not the Excel function. When there are multiple IRRs, one might seem "more appropriate" than the others. But that is very subjective.

    Second, there seems to be flaws in the Excel XIRR implementation. It sometimes produces numerical results which are not the IRR; that is, the returned rate does not cause the NPV to be close to zero. And it sometimes fails (returns #NUM, #DIV/0 or 2.98E-09) even when the "guess" is the actual IRR.

    My own VBA implementation of XIRR avoids the latter issue. But there is no workaround for the first issue.

+ 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. [SOLVED] Returning Results with Wildcard ending in Total within an Index Formula
    By ioswoody in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 06-21-2016, 09:43 PM
  2. XIRR Not Working Right - Returns 2.98 E-09
    By wm009 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-17-2013, 08:24 PM
  3. XIRR issue with non negative beginning month values
    By distressedasstXL in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2013, 10:26 PM
  4. Ending Negative Sign
    By edwardpestian in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-28-2006, 05:44 AM
  5. XIRR and negative IRRs
    By dave in Toronto in forum Excel General
    Replies: 1
    Last Post: 03-28-2006, 12:20 AM
  6. Subtract a group of cells from a total based on ending date
    By Nicholas Scarpinato in forum Excel General
    Replies: 0
    Last Post: 05-17-2005, 11:06 AM
  7. [SOLVED] Can't calculate the result if all cash flow are negative in XIRR
    By Ellis Yu in forum Excel General
    Replies: 0
    Last Post: 02-21-2005, 06:06 AM

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