NPV vs PV: which discounting function do you need?
Both discount future money to today. PV handles the special case of IDENTICAL payments at regular intervals (annuities, loans); NPV takes a list of DIFFERENT cash flows. The trap everyone hits: NPV discounts its very first argument by one full period — it assumes your first flow happens a period from now, not today.
The differences at a glance
| NPV | PV | XNPV | |
|---|---|---|---|
| Cash flows | A list — each can differ | One constant payment (plus optional lump sum) | A list, each with its own DATE |
| Timing assumption | First flow = END of period 1 (discounted once!) | Ordinary annuity (or type=1 for due) | Explicit — dates say everything |
| Include today's outlay | ADD it outside: =NPV(r,flows)+initial (initial is negative) | It's the result — PV is the willing-to-pay-today number | Just date it today |
| Irregular spacing | No — assumes equal periods | No | Yes — its whole purpose |
| Compatibility | Universal | Universal | Universal (all verified by execution) |
Which should you use?
- PV — Valuing streams of equal payments: what a loan, lease, annuity, or bond coupon leg is worth today.
- NPV — Project appraisal with varying yearly flows — remembering the initial investment is added OUTSIDE the function, undiscounted.
- XNPV — Real-world dated cash flows (deals close mid-month, payments cluster) — the assumptions disappear because every flow carries its date.
Compatibility (from executed tests)
All three execute identically in every version of all three apps we test — including the NPV first-flow discounting convention (verified: NPV(10%,100,200,300)=481.59, where 100 is divided by 1.1 once) and XNPV returning 0 when discounting at the flows' own IRR.
Example formulas
| Project NPV with initial outlay today | =NPV(10%,C2:C6)+B2 |
| Value of $1,000/year for 5 years | =PV(10%,5,-1000) |
| Dated flows | =XNPV(10%,B2:B6,A2:A6) |
Full per-version details on each function page: NPV · PV · XNPV.