Description
Use this function to calculate the internal rate of return for a series of cash flows occurring at regular intervals. Supported in Chains.
IRR (Internal Rate of Return) is widely used in financial analysis and capital budgeting to estimate the profitability of potential investments or projects. It represents the discount rate that makes the net present value (NPV) of all cash flows equal to zero.
Syntax
IRR(values, [guess])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
values |
Yes | A range of cells containing the cash flow values. | A number, a reference to a cell containing a number, or a formula which results in either of these. At least one value must be negative (cost) and one must be positive (income). |
guess |
No | A number that you guess is close to the IRR. | A number between -1 and 1, a reference to a cell containing a number between -1 and 1, or a formula which results in either of these. If omitted, 0.1 (10%) is used. |
Example
Sample data
| A | B | C | ||
|---|---|---|---|---|
| 1 | Rate | Term | Amount | |
| 2 | 0.06 | 10 | $200 | |
| 3 | 0.08 | 5 | $500 | |
| 4 | 0.05 | 20 | $1,000 | |
| 5 | 0.07 | 15 | $300 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Calculates the IRR for the cash flow identified by the supplied range. | =IRR(B2:B5) |
This formula examines the range Result 14.09% |
| Calculates the IRR for the cash flow identified by the supplied range using the supplied guess value. | =IRR(B2:B5, 0.05) |
This formula examines the range Result 14.09% |
| Calculates the IRR cash flow identified by the supplied values using the supplied array constant. | =IRR({-1000,500,600,700}) |
This formula examines the supplied values ( Result: 37.33% |
Notes
- IRR assumes:
- Cash flows occur at regular intervals (e.g., annually). For irregular intervals, use XIRR instead.
- The first cash flow (usually an investment) occurs at the beginning of the first period.
- The order of cash flows is important. Ensure they are in chronological order.
- IRR is calculated through iteration and may not converge in some cases.
- If IRR can't find a result after 20 iterations, it returns the #NUM! error.
- IRR may have multiple solutions or no solution, especially for non-conventional cash flows (where signs change more than once).
- IRR is sensitive to the timing of cash flows. Early cash flows have a greater impact on the result.
- IRR doesn't consider the size of the investment, so it may not be suitable for comparing projects of vastly different scales.
Tips
- Use IRR in conjunction with other financial metrics like NPV for a more comprehensive investment analysis.
- When comparing projects, ensure they have similar risk profiles and time horizons.
- If IRR returns an error, try using different initial guesses in the
guessargument. - For more complex scenarios with irregular cash flows, consider using XIRR.
- Always interpret IRR in the context of your company's required rate of return or cost of capital.