Excel NPV Function (Discounted Cash Flows) – Examples & Practice
Practice the Excel NPV function online with an interactive grid, instant feedback, and clear formula help.
Instruction
Calculate net present value of cash flows in B3:B6 using a 10% discount rate.
Formula Syntax
=NPV(rate, value1, [value2], ...)
- rate: Discount rate for one period (must match the period length of cash flows).
- value1: First future cash flow (period 1).
- [value2], ...: Additional future cash flows in order.
What it does
NPV calculates the net present value of future cash flows using a periodic discount rate. Modelers typically add the initial investment (period 0) outside the NPV call.
Excel NPV Function Examples
Simple project cash flows
=NPV(0.1, B3:B6)+B2
Discounts B3:B6 at 10% per period and adds B2 as the usually-negative upfront investment (common pattern).
Rate from a cell
=NPV(E1, B3:B6)+B2
Keeps the discount rate visible for scenario tables.
Horizontal cash flows
=NPV(0.08, C2:F2)+C1
Works when periods are laid out across columns.
cash-flow-projection.xlsx
| A | B | |
|---|---|---|
| 1 | Period | Cash Flow |
| 2 | Initial | -1000 |
| 3 | Year 1 | 300 |
| 4 | Year 2 | 350 |
| 5 | Year 3 | 400 |
| 6 | Year 4 | 450 |
| 7 | Output |
Input Formula
Need Help?
Tips
- Document whether flows are monthly, quarterly, or annual.
- Pair with data validation on rates.
- Move to **XNPV** when dates are irregular.
NPV Function Use Cases
- Capital budgeting and project comparison
- Lease vs buy sketches
- Teaching time value of money
- Sensitivity tables with Data Table feature
- Quick sanity checks before detailed models
Common mistakes - NPV function not working
- Forgetting to add the period-0 investment explicitly
- Mixing annual rates with monthly cash flows
- Treating NPV as IRR (use IRR or XIRR for rates of return)
- Including non-numeric cells in the flow range
- Using XNPV expectations on NPV without date logic
FAQ
Does NPV include period 0 cash flow?
Excel NPV assumes the first cash flow is one period in the future. Add period-0 investment separately outside NPV or adjust the model.
NPV vs XNPV?
XNPV uses actual dates for irregular timing; NPV assumes equal spacing between flows.
Is the rate per period?
Yes. A 10% annual rate with monthly flows needs a monthly-consistent rate.
Can NPV take uneven cash flows?
Yes, as a series of values; spacing is assumed uniform unless you use XNPV.
Why is my NPV sensitive to rate?
Present value is highly convex in the discount rate—always document assumptions.
Comparison
| Function | Timing |
|---|---|
| NPV | Evenly spaced periods |
| XNPV | Date-based |
| PV | Often single stream or annuity |
Example
=NPV(0.1, B3:B6)+B2
Advanced examples
Scenario grid
Link rate and cash-flow ranges to a two-way data table to show how NPV moves with growth and WACC assumptions.
After-tax flows
NPV itself is neutral—ensure your cash-flow series is consistently after-tax or before-tax per policy.
Related functions
SUM answers undiscounted totals—compare side by side with NPV so stakeholders see both cash magnitude and present value.
When dates are irregular, Excel’s XNPV / XIRR family (see in-product help) extends the same economic idea beyond equal periods.