Syntax
XNPV(rate, values, dates)
| Parameter | Description |
|---|---|
| rate | Parameter of the XNPV function. |
| values | Parameter of the XNPV function. |
| dates | Parameter of the XNPV function. |
Examples
Real estate investment with irregular cash flows
Formula
=XNPV(0.10, {-250000, 5000, 5000, 280000}, {"2026-01-15","2026-07-01","2027-01-01","2028-06-30"})
$8,432.17. An investment property bought for $250K with two rent collections and a $280K sale has a positive NPV at 10%.
Venture capital return
Formula
=XNPV(0.15, {-100000, -50000, 500000}, {"2024-03-01","2025-06-15","2028-09-01"})
$143,891.30. A VC investment of $100K plus a $50K follow-on, with a $500K exit 4.5 years later, is very attractive at 15% discount.
Common Errors
#NUM!
The values and dates arrays have different lengths, or a date is earlier than the first date in the series.
#VALUE!
A date is not a valid date or a value is non-numeric.
Tips
First date = time zero
XNPV uses the earliest date as the base (no discounting applied). All other cash flows are discounted based on their distance from that date.
Dates don't need to be sorted
XNPV handles unsorted dates correctly, but keeping them in chronological order makes your spreadsheet easier to read and debug.
XNPV vs NPV
Use NPV for regular periodic cash flows (monthly, quarterly). Switch to XNPV when payment timing is irregular — it's more accurate because it accounts for exact day counts.
Try XNPV in Viztab
Import your data and use XNPV with 370+ other formulas. No signup required.
Open Viztab