+ Reply to Thread
Results 1 to 6 of 6

XIRR near 0 when should be negative

  1. #1
    Registered User
    Join Date
    01-30-2020
    Location
    LV, NV
    MS-Off Ver
    10
    Posts
    1

    XIRR near 0 when should be negative

    I can't get a simple XIRR to work when the total cash outlaw is negative.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2301 (Windows 11 22H2 64-bit)
    Posts
    65,650

    Re: XIRR near 0 when should be negative

    Welcome to the forum.

    What is a cash outlaw? For some reason, I have the image of a bandit in my head ...

    How about a sample workbook? Instructions at the top of the page.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,482

    Re: XIRR near 0 when should be negative

    If you mean all your cashflows are negative, there can't be an IRR. IRR requires at least one sign change in a set of cashflows. If you mean the sum of nominal cashflows is negative, that actually means very little with respect to IRRs. Consider -1 at time 0 and +10 at time 1, the IRR is +900%. OTOH, +1 at time 0 and -10 at time 1 also has an IRR of +900%.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,914

    Re: XIRR near 0 when should be negative

    Details will definitely help.

    I am not knowledgeable about financial models and such, so I don't understand all of the mathematical and financial ins and outs of the XIRR() (and other financial functions). I know that in other discussions, we have talked about some numeric instabilities in Excel's XIRR() implementation. A value near 0 (something like 2.98E-9 seems to be the common value returned) is often the equivalent of an error value. User joeu2004 discusses this issue here: https://www.excelforum.com/excel-gen...ml#post5191942 I observed during the same discussion that other spreadsheets seem to have more stable implementations of XIRR() and don't return the same "error" value.

    Debugging steps I would take at this point:
    1) Check the actual XIRR() output and see if it is 2.98E-9 or similar to indicate an error condition.
    2) Check the result by calculating XNPV(). If the interest rate is correct, the XNPV() will be 0 or nearly 0. If the XNPV() is not 0, then you know the XIRR() returned an incorrect value. Note joeu2004's observation that Excel will not allow a negative interest rate in the XNPV() function, so you may need some adjustment (or use a different spreadsheet that is not limited in this way).
    3) Try different values for the "guess" parameter in the XIRR() function.
    4) Calculate XNPV() at different interest rates, then create a scatter chart for XNPV() vs. interest rate. Remember that XIRR() is determining the interest rate that makes XNPV()=0, so you can then look at the chart and determine approximately where XNPV() would be 0.

    That should give you some things to read and try while debugging your XIRR() function.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: XIRR near 0 when should be negative

    Please confirm your range of dates and particularly your values.

    If they are all negative or all positive then you won;t get a result. One of the numbers needs to have a different sign
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Registered User
    Join Date
    04-14-2020
    Location
    Henderson
    MS-Off Ver
    NV
    Posts
    1

    Re: XIRR near 0 when should be negative

    I had the same problem until I used a guess of -.5. Then it correctly calculated the negative IRR.
    C182

+ 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. XIRR near 0 when should be negative
    By traylore333 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-30-2020, 05:22 PM
  2. Replies: 3
    Last Post: 10-21-2017, 04:03 AM
  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. Replies: 1
    Last Post: 04-26-2012, 12:48 AM
  5. Replies: 1
    Last Post: 11-20-2008, 01:52 AM
  6. [SOLVED] XIRR and negative IRRs
    By dave in Toronto in forum Excel General
    Replies: 1
    Last Post: 03-28-2006, 12:20 AM
  7. 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