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
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | IRR(values, [guess]) |
| Google Sheets | ✔ Built in | IRR(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. |
| 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
| Argument | What it means |
|---|---|
values | A range or array of cash flows in time order, starting with period 0. Needs at least one negative and one positive value. |
[guess] optional | Starting estimate for the iteration, 10% by default. Try another guess if IRR returns #NUM!. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=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.