Results 1 to 5 of 5

Thread: XIRR returns 0.00% instead of a negative percentage
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default XIRR returns 0.00% instead of a negative percentage

    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 percentage. 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!

    XIRR Issue.xlsx in link: https://ufile.io/ctp8d

  2. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,430
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: XIRR returns 0.00% instead of a negative percentage

    Your XIRR is actually returning a very small value, approximately 2.98E-0.09 which is really an error value. XIRR basically is throwing in the towel as it can't cope with the calculation.

    Enter the optional third argument, guess, as say -10% and the XIRR will calculate correctly --> approx -6.23% p.a.

  3. #3
    New Member
    Join Date
    Jun 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: XIRR returns 0.00% instead of a negative percentage

    Quote Originally Posted by StephenCrump View Post
    Your XIRR is actually returning a very small value, approximately 2.98E-0.09 which is really an error value. XIRR basically is throwing in the towel as it can't cope with the calculation.

    Enter the optional third argument, guess, as say -10% and the XIRR will calculate correctly --> approx -6.23% p.a.
    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 (-10%) be safe to use and result in the correct XIRR for all of the different periods? 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/ -10% 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
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,430
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: XIRR returns 0.00% instead of a negative percentage

    I never use XIRR myself, so I can't answer definitively.

    In the example you provided, it only takes a very small negative guess, e.g. -0.000001 to send XIRR off looking in the right direction. I think you'll find the converse is also the case, a small negative guess will flummox XIRR if the return is actually positive.

    So what you say makes sense to me, and I suggest you just try it out and see if it works.

    You may also have problems with XIRR if your cashflows flip between positive and negative, as potentially then there will be multiple solutions and you may get strange results depending on the initial guess.

    A quick Google of the limitations of XIRR might be informative.

  5. #5
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,358
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: XIRR returns 0.00% instead of a negative percentage

    Crossposted to https://www.excelforum.com/excel-for...ributions.html. See responses there.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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