IRR function
IRR returns the discount rate per period that makes a series of evenly spaced cash flows break even in present-value terms. Analysts use it to compare projects and investments.
Example
Read it as a sentence: find the yearly return rate that makes the cash flows in N1:N5 break even, which gives 15%.
Excel shows 15%
N
1
-1000
2
300
3
400
4
500
5
200
Data
Syntax
IRR(values, [guess])
Arguments in [square brackets] are optional.
-
values(required) - Series of cash flows with at least one negative and one positive.
-
guess(optional) - Starting estimate for the rate; 0.1 if omitted.
Returns: Internal rate of return per period.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=IRR({-100,60,60}) | 13% | Array constant |
=IRR(Data!N1:N5) | 15% | Cash flows |
=IRR(Data!N1:N5,0.5) | 15% | With guess |
=IRR({0,0,0}) | #NUM! | Zero NPV everywhere |
=IRR(-100) | #NUM! | Single value |
=IRR(Data!O1:O3) | #NUM! | All positive |
=IRR(Data!B1:B8) | #NUM! | Mixed range |
=IRR(Data!C1:C5) | #VALUE! | Error in range |
The Data sheet these formulas read
A cell that starts with = holds that formula.
B
C
N
O
1
10
1
-1000
100
2
20
2
300
200
3
TRUE
=NA()
400
300
4
4
500
5
abc
5
200
6
30
7
FALSE
8
40
Data
Watch out for
- Cash flows must include both an outflow and an inflow or you get #NUM!.
- Multiple sign changes can give several valid answers; try different guesses.
- Assumes equal spacing; use XIRR for dated flows.
Where it works
- Excel
- IRR is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .