Results 1 to 2 of 2

20050208, 16:26 #1
 Join Date
 Feb 2005
 Posts
 1
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excel IRR function (2000 9.0.6926 SP3)
Does anyone know where I can find the longhand calculation used by the EXCEL IRR function... I have scenarios tha result in #NUM and need to see an actual result so would like to change the formula.

20050208, 17:31 #2
 Join Date
 Dec 2000
 Location
 Vancouver, Br. Columbia, Canada
 Posts
 268
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Excel IRR function (2000 9.0.6926 SP3)
Excel does not use a formula for IRR, in the sense that you can enter values and produce a result  the IRR is the discount rate that results in the NPV of all cash flows being equal to zero. Conceptually, the IRR calculation starts by calculating the NPV of the input cash flows (or just "the NPV" for convenience) at an arbitrary interest rate. If you have entered a guess for the IRR, it starts with that guess  if not it starts from a default value of 10%. It then calculates the NPV at a second rate  although MS doesn't specify, I suspect 0%. It interpolates (or extrapolates) from those two values to make a third estimate of the true IRR, and again calculates the NPV. According to Microsoft, it will continue for up to 20 iterations, or until the difference between successive estimates is less than 0.00001%. If it has achieved a result within that limit of accuracy, it returns that as the IRR  if not, it returns a "#NUM" error. In practice, I am sure the algorithm is more sophisticated than that, in order to obtain a more rapid convergence, and to detect local minima or maxima that do not represent a solution.
Extremely high or low calculated IRRs will be problematic  you might review the cash flows you are using for inputs to ensure that they are what you expected. If the cash flows are okay, you may have better luck either in manually estimating an IRR and using that for a guess, or using the Excel GoalSeek function. Set one cell to calculate NPV for your cash flows, with the discount rate in a separate cell. Click the goal seek function (Tools  Goal Seek) and set the cell with the NPV calculation as the target cell, set a target value of zero, and set the cell with the discount rate in it as the changing cell, then click on OKAY. Goal Seek seems to work better than the IRR calculation  obviously they use different algorithms. I suspect that the IRR algorithm contains either an implicit or explicit assumption that values will be in the range of 30% to +100% or so (and may rely on this assumption in dealing with the multiple "correct" results that can stem from sign changes in the cash flow arguments).
The attached s/sheet illustrates the use of a guess (in cell A13)  although the correct result is ~82% (values taken from the XL97 IRR help example) for the IRR after the first year the IRR calculation will only get there with a guess between ~70% and <100%. The goal seek (you can do it manually, or click the button) will arrive at the correct answer no matter the initial value in that cell.