# IRR function in Excel

> IRR calculates the internal rate of return: the discount rate at which a series of cash flows has a net present value of zero.

Source: https://spreadsheetpractice.com/functions/irr · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** IRR calculates the internal rate of return: the discount rate at which a series of cash flows has a net present value of zero.
- **Syntax:** `=IRR(values, [guess])`
- **Most common use:** `=IRR(B2:B6)` returns 17.2% (Project A's annual return)
- **Available in:** Every current version
- **Category:** [Financial functions](https://spreadsheetpractice.com/functions/category/finance)
- **Returns:** The rate of return per period, as a decimal. Format it as a percentage.
- **Google Sheets:** Works the same in Google Sheets.

## What it does

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.

## 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 was calculated by running the formula on the sample sheet.

| 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 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Year | Project A | Project B |  | Result |
| 2 | 0 | -$50,000 | -$50,000 |  | 17.2% |
| 3 | 1 | $15,000 | $5,000 |  | 13.5% |
| 4 | 2 | $18,000 | $10,000 |  | $0 |
| 5 | 3 | $20,000 | $20,000 |  |  |
| 6 | 4 | $22,000 | $40,000 |  |  |

## 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.

## Related functions

- [NPV](https://spreadsheetpractice.com/functions/npv): NPV discounts a series of future cash flows back to today at a given rate, so you can judge whether an investment beats your cost of capital.
- [RATE](https://spreadsheetpractice.com/functions/rate): RATE works out the interest rate per period from the number of payments, the payment and the amount borrowed or invested.
- [PV](https://spreadsheetpractice.com/functions/pv): PV calculates what a series of future payments, or a single future amount, is worth today at a given interest rate.

## 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.

## Practice

A graded IRR exercise ("Internal rate of return") is on [the IRR page](https://spreadsheetpractice.com/functions/irr). It is checked instantly, then re-checked on changed data.

Lessons that use IRR:

- [Lesson 41: NPV & IRR](https://spreadsheetpractice.com/learn/npv-irr) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
