Spreadsheet formulas explained, side by side

IRR Function in Excel, Google Sheets, Airtable & Notion

IRR returns the discount rate at which the net present value of a series of cash flows is exactly zero: the investment's effective return per period.

Syntax in each app

AppSupportSyntax & notes
Excel✔ Built inIRR(values, [guess])
Google Sheets✔ Built inIRR(cashflow_amounts, [rate_guess])
Airtable✘ Not available—
Airtable has no IRR and formulas cannot iterate. Calculate IRR in Excel or Google Sheets (or a script) and store the result in a number field.
Notion✘ Not available—
Notion has no irr() and no practical way to solve for a rate. Calculate it elsewhere and store it as a number property.
Try IRR with your own values
Start with the investment as a negative number
Result (as Excel & Google Sheets return it)
0.1634 16.34% when the cell is formatted as a percentage
Your formula, ready to paste
Excel=IRR(A2:A5) open excel.new ↗
Google Sheets=IRR(A2:A5) open sheets.new ↗
Airtable✘ No equivalent. Airtable has no IRR and formulas cannot iterate. Calculate IRR in Excel or Google Sheets (or a script) and store the result in a number field.
Notion✘ No equivalent. Notion has no irr() and no practical way to solve for a rate. Calculate it elsewhere and store it as a number property.

Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.

Cash flow
-10000
3000
4200
6800

The result is a decimal rate per period: 0.1634 means 16.34%. Excel formats it as a percentage automatically.

Arguments

ArgumentWhat it means
valuesA range or array of cash flows in time order, starting with period 0. Needs at least one negative and one positive value.
[guess] optionalStarting estimate for the iteration, 10% by default. Try another guess if IRR returns #NUM!.

Examples

FormulaResultWhyCopy
=IRR({-10000, 3000, 4200, 6800})0.1634 (16.34%)Invest 10,000, receive three yearly payments.
=IRR({-1000, 1100})0.1 (10%)Put in 1,000, get 1,100 back a period later.
=IRR({100, 200})#NUM!No negative cash flow, so there is no rate that makes NPV zero.

Examples use Excel / Google Sheets syntax unless noted.

How IRR works

IRR is the rate that makes NPV equal to zero. If a project's IRR is higher than your required return, its NPV at that required return is positive. Unlike NPV, the first value in IRR is period 0 (today), so the initial investment goes inside the range: =IRR(B2:B6) with B2 holding the negative up-front cost.

The result is per period. Monthly cash flows give a monthly IRR; convert it with (1 + irr)^12 − 1 for an effective annual rate. Cash flows must be evenly spaced; for real dates use XIRR. IRR needs at least one negative and one positive value, and when the signs change more than once (invest, earn, invest again) there can be more than one valid answer. Excel returns the one nearest the guess.

Like RATE, IRR is found by iteration, which Airtable and Notion formulas cannot do. Work it out in a spreadsheet and store it, or compare options with an explicit NPV at a fixed rate instead, which both apps can calculate.

Frequently Asked Questions

Why does IRR return #NUM!?
The cash flows need both a negative and a positive value. If they have them, try a different guess such as =IRR(B2:B6, -10%).
What is the difference between IRR and XIRR?
IRR assumes the cash flows are one period apart. XIRR takes a matching list of dates and returns an annual rate for irregular timing.

Part of the Financial functions. Results in the tester follow Excel and Google Sheets behaviour; always double-check important work in your own file.