How to calculate IRR in Excel
Formula (Excel and Google Sheets)
=IRR(B2:B7)Give IRR the cash flows in order, starting with the negative investment: =IRR(B2:B7).
- Result on the example
- 14.77% in
E1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
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.
=IRR(B2:B7)Result0.1477166506
B2:B76 rows × 1 col: -$50,000, $12,000, $15,000, $18,000, …A range: 6 cells in one column.
IRR(B2:B7)0.1477166506IRR: Internal rate of return.- values
B2:B76 rows × 1 col: -$50,000, $12,000, $15,000, $18,000, …
- values
In Google Sheets
The same formula works in Google Sheets: =IRR(B2:B7). We ran it in Google Sheets mode on the same data and it returns 14.77%.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =NPV(IRR(B2:B7),B3:B7)+B2 | 0.00%Different result | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate IRR
E1, calculate the internal rate of return of the cash flows in B2:B7, where year 0 is the investment.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.