Results 1 to 9 of 9
Thread: IRR calculation (2002  SP3)

20050224, 15:45 #1
 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?

20050224, 16:16 #2
 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.

20050224, 17:06 #3
 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.

20050224, 17:23 #4
 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.

20050224, 19:19 #5
 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

20050224, 21:07 #6
 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.

20050225, 05:08 #7
 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....

20050225, 13:15 #8
 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.

20050225, 23:10 #9
 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.