The IRR Excel function is a finance function which can calculatee the internal rate of return (IRR) of a series of periodic cash flows. The function assumes that the cash flows occurs at regular intervals. In this guide, we’re going to show you how to use the IRR Excel function and also go over some tips and error handling methods.
Supported versions
- All Excel versions
Syntax
Arguments
values |
A series of cash flows. Cash flows must contain at least one positive value and one negative value. Payments are expressed as negative values and incomes as positive. |
[guess] |
Optional. Your estimation for an expected internal rate of return. Default value is 0.1 (10%). |
Examples
Note that we’ve used named ranges in this example to make the formulas easier to read. This is not required.
Example 1
When the [guess] argument is omitted, Excel assumes it as 0.1 and starts iterations from this value. The IRR calculates iterations until achieving an answer within 0.00001% accuracy.
Example 2
Tips
- The IRR Excel function assumes that cash flows occur at regular intervals. Use the XIRR function instead for non-periodic cash flows and higher precision.
- The rate of return is calculated by IRR is the interest rate corresponding to a 0 (zero) net present value (NPV). Using the return of the IRR in the NPV function returns 1.79E-09, which is equal to 0 (zero) within the accuracy of the IRR Excel. Sample formula: =NPV(IRR(cash_flow,guess),cash_flow)
- This function ignores text, logical values, or empty cells.
- Values should be in chronological order.
- VBA also has an IRR function which uses the same syntax.
Issues
#NUM!
- If the function cannot find a result within 0.00001% accuracy, or in 20 iterations, you will get a #NUM! error. Try changing the [guess] value.