# How to calculate IRR in Excel

Give IRR the cash flows in order, starting with the negative investment: `=IRR(B2:B7)`.

**Excel:** `=IRR(B2:B7)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Year | Cash flow |  | IRR |
| 0 | -50000 |  |  |
| 1 | 12000 |  |  |
| 2 | 15000 |  |  |
| 3 | 18000 |  |  |
| 4 | 16000 |  |  |
| 5 | 14000 |  |  |

Formula in E1: `=IRR(B2:B7)`

Result: 14.77%

## How it works

IRR finds the discount rate at which the cash flows' net present value is exactly zero: the project's own rate of return. Here it is 14.77% a year, so the project beats any hurdle rate below that. The values must be in time order and evenly spaced, with the investment first as a negative.

## Other ways

- `=NPV(IRR(B2:B7),B3:B7)+B2`: A check: discounting at the IRR gives an NPV of zero. (Returns 0 (to within rounding).)

## Common mistakes

- IRR needs at least one negative and one positive value, or it returns #NUM!.
- Monthly cash flows give a monthly IRR. Annualize it: `=(1+IRR(B2:B37))^12-1`.
- Cash flows on irregular dates need XIRR, which takes a range of dates as well.

## Questions

**Why does IRR return #NUM!?**

Either all the values have the same sign, or the search didn't converge. Add a guess as the second argument: `=IRR(B2:B7,0.1)`.

**What is the difference between IRR and NPV?**

NPV uses a rate you choose and gives a dollar amount. IRR gives the rate at which NPV is zero.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/calculate-irr