Results 1 to 2 of 2
2005-02-08, 16:26 #1
- Join Date
- Feb 2005
- Thanked 0 Times in 0 Posts
Excel IRR function (2000 9.0.6926 SP-3)
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.
2005-02-08, 17:31 #2
- Join Date
- Dec 2000
- Vancouver, Br. Columbia, Canada
- Thanked 1 Time in 1 Post
Re: Excel IRR function (2000 9.0.6926 SP-3)
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.