Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IRR calculation (2002 - SP3)

    Excel seems to be unable to calculate the IRR (internal rate of return) in a particular case. Please take a look at the attached sheet. Can you see what the problem is?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: IRR calculation (2002 - SP3)

    Try adding a "guess"
    From the HELP
    <blockquote><hr>
    Guess is a number that you guess is close to the result of IRR.

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IRR calculation (2002 - SP3)

    Welcome to the Lounge

    The MS knowledge base has an article about IRR producing this error, XL: IRR Returns #DIV/0! Instead of #NUM! If No IRR Exists, though I do not use the IRR function so I am not sure if it applies in your case.

  4. #4
    New Lounger
    Join Date
    Feb 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IRR calculation (2002 - SP3)

    Thanks very much ! Putting a guess value did the trick.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IRR calculation (2002 - SP3)

    If you have really odd cash flows, you may find that the required guess to allow the IRR function to converge varies too much between different assumed input values. If so, it may be worthwhile using "GoalSeek" to calculate a discount rate that yields a zero NPV - the goalseek algorithm sems to be better than the IRR algorithm. See
    this post

  6. #6
    New Lounger
    Join Date
    Feb 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IRR calculation (2002 - SP3)

    in my case, the GoalSeek coud not find the right rate for NPV=0. (see my Excel sheet). It could be useful otherwise though. Thanks.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IRR calculation (2002 - SP3)

    <hr>in my case, the GoalSeek coud not find the right rate for NPV=0. (see my Excel sheet). <hr>

    Sure it can - see attached. Click the "Run Goal Seek" button - it will converge on 0.572498...% It's actually kind of neat to see the IRR 'click in' as the goal seek value gets close enough that the IRR algorithm can reach a solution. My guess is that with the values shown the IRR cannot quite come to a solution within the 20 iterations MS allows for it if it starts with the default guess of 10%.

    Incidentally, I confirmed the rate on my financial calculator, and it does not quite match the rate you indicated in the cell note, although it does match the value that IRR calculates when given a sufficiently close guess. I suspect a slightly different implementation of NPV calculation....

  8. #8
    New Lounger
    Join Date
    Feb 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IRR calculation (2002 - SP3)

    My IRR calculation was wrong and I was not using GoalSeek correctly.

    Thanks very much for your help.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IRR calculation (2002 - SP3)

    I'm glad to have been of assistance - it is just a very small payback of what I have learned from some of the real experts around here.

Posting Permissions

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