NPV vs XNPV
NPV assumes one cash flow per evenly spaced period; XNPV uses each flow’s real date.
| NPV | XNPV | |
|---|---|---|
| Spacing between flows | Even (one period) | Any, by the dates |
| Initial investment | Outside the function, added separately | Inside the values range |
| Rate | Per period | Yearly |
| Its IRR | IRR | XIRR |
Syntax and example
NPV
=NPV(rate, value1, [value2], …)
=NPV(B1,B5:B8)+B4
Calculates the present value of a series of future cash flows at a discount rate.
XNPV
=XNPV(rate, values, dates)
=XNPV(E1,B2:B7,A2:A7)
Calculates the net present value of cash flows that arrive on irregular dates.
Which one to use?
For regular monthly or yearly flows, NPV. If payments land on scattered dates, XNPV and XIRR.
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice it
Solve the exercise on the sheet; it is checked instantly. See all lessons · Formula translator
XNPV and XIRR: cash flows on real dates
NPV and IRR assume one cash flow per period, all evenly spaced. When payments arrive on irregular dates, XNPV(rate, flows, dates) and XIRR(flows, dates) discount each one by the days since the first.
In E2, calculate the investment’s net present value with the rate in E1 and the real dates.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Cash flow | Discount rate | 12% | ||
| 2 | 01/15/2025 | -150,000.00 | NPV with dates | |||
| 3 | 03/01/2025 | 28,000.00 | IRR with dates | |||
| 4 | 06/20/2025 | 41,000.00 | ||||
| 5 | 08/05/2025 | 35,000.00 | ||||
| 6 | 11/30/2025 | 47,000.00 | ||||
| 7 | 02/10/2026 | 52,000.00 | ||||
| 8 |
Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.
Frequently asked questions
- What is the difference between NPV and XNPV?
- NPV assumes one cash flow per evenly spaced period; XNPV uses each flow’s real date.
- When should I use NPV or XNPV?
- For regular monthly or yearly flows, NPV. If payments land on scattered dates, XNPV and XIRR.