IRR function
IRR calculates the internal rate of return: the discount rate at which a series of cash flows has a net present value of zero. Use it to compare projects or investments by their annual return. The cash flows must include at least one negative value (the investment) and one positive value.
- Available in
- Every current version
- Category
- Financial functions
- Returns
- The rate of return per period, as a decimal. Format it as a percentage.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=IRR(values, [guess])
| Argument | Required? | What it is |
|---|---|---|
values | Required | The cash flows in time order, starting with the upfront investment as a negative number. |
guess | Optional | A starting estimate for the rate. Defaults to 10%; change it only if IRR fails to find an answer. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =IRR(B2:B6) | 17.2% | Project A's annual return |
| E3 | =IRR(C2:C6) | 13.5% | Project B: the same $75,000 back, but later, so a lower return |
| E4 | =NPV(IRR(B2:B6),B3:B6)+B2 | $0 | At the IRR, NPV comes out to zero |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Year | Project A | Project B | Result | |
| 0 | -$50,000 | -$50,000 | 17.2% | |
| 1 | $15,000 | $5,000 | 13.5% | |
| 2 | $18,000 | $10,000 | $0 | |
| 3 | $20,000 | $20,000 | ||
| 4 | $22,000 | $40,000 |
Practice IRR
From lesson 27, NPV & IRR. Graded instantly, then re-checked on changed data.
Internal rate of return
E5, calculate the project's IRR.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #NUM! | The cash flows are all positive or all negative, so no rate balances them. Enter the investment as a negative number. |
| #NUM! on long or unusual cash flows | IRR couldn't converge from its starting guess. Supply a guess near the expected return, such as 0.15. |
| Comparing IRRs on projects of different size | A higher IRR isn't always more money. Check NPV too; a larger project with a lower IRR can create more value. |
Tips
- For cash flows on irregular dates, IRR's assumption of evenly spaced periods doesn't hold. XIRR handles actual dates.
Questions
How do I calculate IRR in Excel?
List the cash flows in order with the investment as a negative number, then use =IRR(B2:B6). Format the result as a percentage.
What is the difference between IRR and NPV?
NPV tells you the value added in money at a chosen rate. IRR tells you the rate at which the value added is zero.
Why does IRR return #NUM!?
Either there is no sign change in the cash flows, or the calculation didn't converge. Check the signs, then try adding a guess.
Lessons that use IRR
- Lesson 27: NPV & IRR (Pro)
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.