IRR calculates the internal rate of return for a series of cash flows, showing the annual growth rate an investment is expected to generate. It helps you compare projects and decide whether an investment is worth pursuing.
=IRR(values, [guess])What does IRR do?
The IRR function in Excel calculates the internal rate of return for a series of cash flows that occur at regular intervals. In simple terms, it finds the annual percentage rate that makes the net present value of those cash flows equal to zero. This rate tells you how much an investment is expected to earn each year, considering the timing of money coming in and going out.
IRR matters because it lets you compare investments of different sizes and durations on a level playing field. Instead of guessing whether a project is worthwhile, you can check if its IRR is higher than your cost of capital or a target return. It is widely used in budgeting, capital planning, and personal finance.
Imagine you are deciding between two projects: one costs £10,000 now and returns £3,000 per year for five years; another costs £15,000 and returns £5,000 per year for four years. By calculating the IRR for each, you can quickly see which one offers a better annual return and make a more informed decision.
When to use IRR
- Evaluating whether to invest in a new project or equipment
- Comparing two or more investment opportunities with different cash flow patterns
- Assessing the profitability of a business expansion
- Calculating the return on a series of rental income payments
- Deciding between buying or leasing an asset
- Measuring the performance of a portfolio of investments
IRR syntax
=IRR(values, [guess])| Argument | Required? | What to enter |
|---|---|---|
values | Required | An array or range of cells containing the cash flows. The first cash flow is usually negative (the initial investment), and subsequent cash flows can be positive or negative. For example, {-1000, 300, 400, 500}. |
guess | Optional | A number you think is close to the IRR. If omitted, Excel uses 0.1 (10%). You only need to provide a guess if Excel returns a #NUM! error. For example, 0.2 for 20%. |
What it returns: The internal rate of return as a decimal number, which you can format as a percentage. For example, 0.12 means 12%.
IRR examples
1 Simple investment with equal annual returns
This example shows a £1,000 investment that returns £300 each year for four years. The formula =IRR(B2:B6) calculates the rate that makes the net present value of these cash flows zero. It returns approximately 0.0771, or 7.71% per year. This is the annual return you would earn on the investment.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | Cash Flow | |||
| 2 | 0 | -1000 | 0.07713847295 | ||
| 3 | 1 | 300 | |||
| 4 | 2 | 300 | |||
| 5 | 3 | 300 | |||
| 6 | 4 | 300 | |||
| 7 |
2 Uneven cash flows from a project
Here, an initial outlay of £5,000 is followed by increasing returns: £1,500, £2,000, £2,500, and £3,000 over four years. The formula =IRR(B2:B6) finds the rate that equates the present value of these inflows to the initial outflow. The result is about 0.2465, or 24.65%, indicating a strong annual return.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | Cash Flow | |||
| 2 | 0 | -5000 | 0.2488833566 | ||
| 3 | 1 | 1500 | |||
| 4 | 2 | 2000 | |||
| 5 | 3 | 2500 | |||
| 6 | 4 | 3000 | |||
| 7 |
Common IRR errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #NUM! | IRR cannot find a result after 20 iterations, often because the cash flows do not include at least one negative and one positive value, or the guess is far from the actual rate. | Ensure the range contains both negative and positive cash flows, and provide a guess close to the expected rate, such as 0.1 for 10%. |
| #VALUE! | The values argument contains text, logical values, or empty cells that cannot be interpreted as numbers. | Check that all cells in the range are numeric. Remove any text or special characters. |
| #DIV/0! | This can occur if the cash flows are all zero or if the initial investment is zero and there are no subsequent cash flows. | Verify that the cash flow series includes a non-zero initial investment and at least one non-zero subsequent cash flow. |
| Incorrect result (too high or too low) | Multiple IRRs can exist when cash flows change sign more than once. Excel returns the first one it finds based on the guess. | Use the guess argument to steer Excel toward the desired rate, or use XIRR for more control with dates. |
Tips for using IRR
- Always include the initial investment as a negative number in the first cell of the range.
- Format the result as a percentage to make it easier to interpret.
- If you get a #NUM! error, try adding a guess argument like 0.1 or 0.2.
- Use IRR to compare projects, but also consider the scale of investment and absolute returns.
- For cash flows with irregular dates, use the XIRR function instead.
- Remember that IRR assumes cash flows are reinvested at the IRR itself, which may not be realistic.
Frequently asked questions
What is a good IRR?
A good IRR depends on your cost of capital and risk. Generally, an IRR higher than your required rate of return (e.g., 10% or 15%) is considered good.
Why does IRR return a negative value?
A negative IRR means the investment is expected to lose money. This happens when the total cash inflows are less than the initial investment.
Can IRR be used for monthly cash flows?
Yes, but IRR assumes regular periods. If your cash flows are monthly, the result is a monthly rate. To annualize, use =(1+IRR(range))^12-1.
What is the difference between IRR and NPV?
IRR gives the break-even discount rate, while NPV calculates the value of cash flows at a specific discount rate. IRR is a percentage; NPV is a dollar amount.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. IRR reference