IRR ( cashFlowsList , guessRate )
IRR calculates Internal Rate of Return of a value list of cash flows (cashFlowsList) using an initial guessed rate of return (guessRate). The parameter guessRate defaults to .0001 if initially blank.
IRR calculates the Internal Rate of Return with the least recursion possible. It is fast – using the Newton-Raphson method for speed and precision. It calculates to a modifiable decimal precision – presently set at 1.0e-7. A list of 360 cash flows was processed through this function with no noticeable speed problems. The C source code from which this custom function was derived is included in the comments.
- cashFlowsList – Value list of cash flows.
- guessRate – Initial guessed rate of return.
- IRR ( “-500000¶200000¶300000¶200000”, .1 ) = .1882462096837047
- IRR ( List ( -2000, 600, 300, 500, 700, 400 ), .05 ) = .0793216878811758
- IRR ( List ( -5000, 10000, 0, 10000, 10000 ), .1 ) = 1.4633789720926647
DISCLAIMER: Using this IRR function, several calculations were compared against the results obtained on other IRR calculators and the results were identical. That does not guarantee a correct result in every operation of this custom function, so you assume this risk when you use it.
NOTICE: This is a recursive function. If you change the function name then also change it in the function code.
IRR code: Copy the selected code (click on Select All to highlight all the code) to the clipboard and paste it into a newly created Custom Function. Download code as a text file from here.