# Thread: IRR calculation (2002 - SP3)

1. ## 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. ## Re: IRR calculation (2002 - SP3)

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

3. ## 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. ## Re: IRR calculation (2002 - SP3)

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

5. ## 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. ## 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. ## 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. ## Re: IRR calculation (2002 - SP3)

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

Thanks very much for your help.

9. ## 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
•