Finance formula
NPV / IRR in Excel and Google Sheets
Value future cash flows today (NPV) and find their rate of return (IRR).
Syntax
=NPV(rate, value1, ...) =IRR(values)- rate
- Discount rate per period.
- values
- Cash flows. The first one is usually negative (the investment).
Example
| Row | A | B |
|---|---|---|
| 1 | Year | Cash flow |
| 2 | 0 | -10,000 |
| 3 | 1 | 4,000 |
| 4 | 2 | 4,000 |
| 5 | 3 | 4,000 |
| 6 | IRR |
B6 =IRR(B2:B5)
Result: 9.7%
The return that makes these cash flows break even.
Ready-to-copy formulas
NPV with an upfront investment
=NPV(8%,B3:B5)+B2
Tips
- NPV assumes the first value is one period in the future, so add the upfront amount separately.
Need the whole spreadsheet?
Start from a free template with every formula already in place. Opening a template uses no credits.