How to calculate NPV in Excel
Net Present Value discounts future cash flows back to today's money, so you can judge whether an investment actually pays. Excel has NPV and XNPV built in — here's how to use them correctly, and the one-click alternative.
When NPV is the right tool
Any time money arrives in different years and you need a single, comparable figure for the decision.
Capital decisions
Compare projects or kit purchases on a like-for-like basis.
Investment cases
Turn a multi-year cash forecast into one go/no-go number.
Pricing & deals
Value a contract whose payments land over several years.
How to calculate it in Excel
A column of cash flows, a discount rate, the NPV function — and one rule about where the Year-0 outlay goes.
List the cash flows
One row per period. Outflows negative, inflows positive. Keep the periods evenly spaced.
Pick a discount rate
Your cost of capital or required return, e.g. 10% per year in a single cell.
Apply NPV to future flows
=NPV(rate, year1:year5) — important: NPV assumes the first value is one period away, so it covers Year 1 onward only.
Add the Year-0 outlay
=NPV(rate, year1:year5) + year0. Add the initial investment (a negative number) outside the function.
The classic NPV mistake is including the Year-0 investment inside the NPV() range — Excel then discounts it by a year it shouldn't. Keep the upfront cost outside the function and add it on. If your cash flows land on irregular dates, use XNPV(rate, values, dates) instead, which discounts by actual calendar days.
The faster way — skip the setup
NPV is easy to get subtly wrong, and a board case needs IRR and payback alongside it. DataHub Pro returns all three.
Easy to get subtly wrong
- Remember NPV starts at period 1
- Handle the Year-0 outlay separately
- Switch to XNPV for irregular dates
- Rebuild for every scenario
NPV, IRR and payback together
- Upload the cash flows — NPV, IRR and payback computed
- Year-0 handled correctly
- Test discount-rate scenarios instantly
- Straight into a board-ready report
FAQ
What is the NPV formula in Excel?
Use =NPV(rate, future_cash_flows) for the future periods, then add the Year-0 cash flow outside the function: =NPV(rate, C2:C6) + C1. NPV assumes the first value is one period in the future.
What is the difference between NPV and XNPV?
NPV assumes cash flows are evenly spaced one period apart. XNPV takes an extra dates argument and discounts each flow by the actual number of calendar days, which is more accurate for irregular timing.
Why is my Excel NPV wrong?
The usual cause is including the initial Year-0 investment inside the NPV() range, so Excel over-discounts it. Keep the upfront cost out of the function and add it on afterwards.
Keep exploring
More analysis you can run on your own data — no sign-up needed to read.
Value the investment without the gotchas
Upload your cash-flow forecast and DataHub Pro returns NPV, IRR and payback — Year-0 handled correctly. Free to try, no card.
Start free →