Practice free

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

Practice this in the app

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.

FormulaExcel showsCase
=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.

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 .