IRR
IRR('Node' [, Guess])
The IRR function calculates the internal rate of return (IRR), a metric of profitability for potential investments. Internal rate of return is a discount rate that sets the net present value (NPV) of all future cash flows of the investment equal to zero. An approximate solution is found using numerical iteration.
Use this function when you need to find the discount rate at which an investment’s cash flows break even (NPV = 0).
Parameters
- NodeNode referenceRequired
- Yearly cash flows. At least one of the cash flows must be negative and at least one positive.
- GuessNumberOptional
- A first guess at the rate. Default:
10%.
Example
IRR of a project with mixed cash flows
This example calculates the internal rate of return for a project with an initial investment and subsequent positive cash flows.
Input node: 'CF'
| Year | Value |
|---|---|
| 2025 | -10000 |
| 2026 | 1155 |
| 2027 | 5000 |
| 2028 | 7000 |
Our goal is to find a discount rate with which the discounted cash flows sum up to zero:
Formula: IRR('CF')
| Year | IRR Result |
|---|---|
| 2027 | -23% |
| 2028 | 12% |
The IRR function tells us the rate is 12%. The result for cash flows up to year 2027 is not interesting for us and can be filtered out with the FILTER function.