Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: IRR - #DIV/0 Error

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Seattle, WA
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I usually encounter this error if the IRR is so low that the IRR is not worth calculating (investment loss). However, I have $2.4 million of investment with $5.9 million of return. I keep getting a #DIV/0 error until I try numerous "Guesses." I can get it to work with a -.10% guess.

    I understand that Excel will attempt 20 passes at calculating the IRR, then return an error if it cannot solve for the IRR. Is there a way to get Excel to try more than 20 attempts?

    I don't think changing "Iterations" in the Calculation tab will increase the number of attempts Excel will try before giving up on the IRR calculation. Any ideas?

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-22 13:59, Corey wrote:
    I usually encounter this error if the IRR is so low that the IRR is not worth calculating (investment loss). However, I have $2.4 million of investment with $5.9 million of return. I keep getting a #DIV/0 error until I try numerous "Guesses." I can get it to work with a -.10% guess.

    I understand that Excel will attempt 20 passes at calculating the IRR, then return an error if it cannot solve for the IRR. Is there a way to get Excel to try more than 20 attempts?

    I don't think changing "Iterations" in the Calculation tab will increase the number of attempts Excel will try before giving up on the IRR calculation. Any ideas?
    Hi Corey,

    Please tell us exactly what you did.

    cell A1 holds -2.4
    cell A2 holds 5.9
    =IRR(A1:A2,.1) returns 145.83%

    If I add 7 zeros between the two numbers
    =IRR({-2.4,0,0,0,0,0,0,0,5.9},0.1)
    returns 11.9%

    I have read that if you use -0.9 as a guess, IRR will always converge to a value, whether the retrun is + or -.

    Also, try using XIRR if you can, at least to verify the annualized IRR result.

    Bye,
    Jay

    P.S. By the way, how did the explanation work for you (from a thread from awhile ago), where you had to explain to your boss annualizing the IRR result to get the XIRR value?

    [ This Message was edited by: Jay Petrulis on 2002-04-22 15:07 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Seattle, WA
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Jay. I'll give that a try. Thanks also for the posting last month. Been holding off on using XIRR because it is an add-in, worried future users might not understand why an error occured if they don't have that particular add-in installed. Once I define who the users are, maybe I'll start using XIRR and make sure all futute user have the add-in installed. Thanks.

  4. #4
    New Member
    Join Date
    Jan 2010
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IRR - #DIV/0 Error

    Jay,

    I want to thank you as well. I had a similar problem to Corey's and your suggestion to use '-0.9' as the guess solved the problem.

    Great site. Will use it again in the future.

    Regards,
    Aaron

  5. #5
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: IRR - #DIV/0 Error

    This post was useful to me too - didn't realise a small IRR could produce a #DIV/0! error.
    Richard Schollar

    Using xl2013

  6. #6
    New Member
    Join Date
    May 2013
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IRR - #DIV/0 Error

    Quote Originally Posted by Corey View Post
    I usually encounter this error if the IRR is so low that the IRR is not worth calculating (investment loss). However, I have $2.4 million of investment with $5.9 million of return. I keep getting a #DIV/0 error until I try numerous "Guesses." I can get it to work with a -.10% guess.

    I understand that Excel will attempt 20 passes at calculating the IRR, then return an error if it cannot solve for the IRR. Is there a way to get Excel to try more than 20 attempts?

    I don't think changing "Iterations" in the Calculation tab will increase the number of attempts Excel will try before giving up on the IRR calculation. Any ideas?

    That was very helpful...

Some videos you may like

User Tag List

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
  •