NPV Function in Excel, Google Sheets, Airtable & Notion
NPV discounts a series of cash flows back to today at a given rate and adds them up. The first value is treated as arriving one period from now, so an up-front cost is usually added separately.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | NPV(rate, value1, [value2], ...) |
| Google Sheets | ✔ Built in | NPV(discount, cashflow1, [cashflow2, ...]) |
| Airtable | ≈ Workaround | cf1 / POWER(1 + rate, 1) + cf2 / POWER(1 + rate, 2) + ...No NPV function in Airtable; write the discounted sum out term by term, with one field per cash flow. |
| Notion | ≈ Workaround | cf1 / pow(1 + rate, 1) + cf2 / pow(1 + rate, 2) + ...No npv() in Notion; write out the discounted sum. |
| Excel | =NPV(8%, A2:A4) - 10000 | open excel.new ↗ |
|---|---|---|
| Google Sheets | =NPV(8%, A2:A4) - 10000 | open sheets.new ↗ |
| Airtable workaround | -10000 + 3000 / POWER(1 + 0.08, 1) + 4200 / POWER(1 + 0.08, 2) + 6800 / POWER(1 + 0.08, 3) | |
| Notion workaround | -10000 + 3000 / pow(1 + 0.08, 1) + 4200 / pow(1 + 0.08, 2) + 6800 / pow(1 + 0.08, 3) |
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 3000 4200 6800
NPV discounts the first listed cash flow by one full period. The initial investment happens today, so it is added after NPV rather than placed inside it.
Arguments
| Argument | What it means |
|---|---|
rate | Discount rate per period, e.g. 8% for yearly cash flows. |
value1, [value2], ... | Cash flows at the end of periods 1, 2, 3… Outflows negative, inflows positive. Must be evenly spaced in time. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=NPV(8%, 3000, 4200, 6800) - 10000 | 1776.66 | Positive: the project beats an 8% return. | |
=NPV(10%, 100, 100, 100) | 248.69 | Three payments of 100 discounted at 10%. | |
=NPV(8%, B3:B7) + B2 | NPV of the project | B2 holds the up-front cost (negative) at period 0. |
Examples use Excel / Google Sheets syntax unless noted.
How NPV works
NPV is the standard way to compare cash today with cash later. Each value is divided by (1 + rate)n, where n is its period number, and the results are added. A positive NPV means the cash flows are worth more than the rate you discounted at; a negative one means they are worth less. The rate and the spacing of the cash flows must match: yearly flows need a yearly rate, monthly flows a monthly rate (8%/12).
The classic mistake is putting the initial investment inside the function. Excel's NPV assumes the first value arrives at the end of period 1, so =NPV(8%, -10000, 3000, 4200, 6800) discounts the up-front cost by a year and gives the wrong answer. Keep period 0 outside: =NPV(8%, B3:B5) + B2. For irregular dates use XNPV in Excel or Google Sheets.
Airtable and Notion have no NPV function, but the calculation is just a sum of discounted terms, which the copy buttons write out explicitly. That works well for a fixed number of periods stored in separate fields. Use IRR to find the rate at which the NPV is exactly zero.
Frequently Asked Questions
- Should the initial investment go inside NPV?
- No. Add it separately:
=NPV(rate, future flows) + initial, with the initial investment as a negative number. - What discount rate should I use?
- The return you require or could get elsewhere for similar risk, per period. The function does not choose it for you, and the result is sensitive to it.