XNPV / XIRR
NPV and IRR's versions for cash flows that don't arrive on neat, evenly-spaced periods: each one paired with its own actual date.
Plain NPV and IRR assume every cash flow is exactly one period apart, which real investments almost never deliver. XNPV and XIRR use each flow's actual calendar date to discount it by the real number of days elapsed, which matters whenever payments land irregularly: a deal that closes mid-quarter, an investment with an unscheduled early payout. The rate XNPV takes, and the one XIRR returns, are always annualized, regardless of how far apart the actual dates happen to be.
Syntax
| Argument | Description |
|---|---|
| rate | XNPV: Required. The annual discount rate. |
| values | Required for both. The cash flows, including the initial outflow as a negative number. |
| dates | Required for both. The actual calendar date of each cash flow, one per value, same order. |
| guess | XIRR: Optional. A starting estimate for the solver. Rarely needed. |
A real example: valuing cash flows that land on odd dates
A $10,000 investment with two returns landing on irregular dates rather than tidy year-end marks:
Because each cash flow carries its own real date, XNPV discounts the April payment differently from the November one, reflecting the actual number of days each sat uncollected, something plain NPV has no way to account for.
Worked examples
| A | B | |
|---|---|---|
| 1 | 2026-01-01 | -10000 |
| 2 | 2026-04-15 | 3000 |
| 3 | 2026-11-01 | 9000 |
Common mistakes
- Using plain NPV/IRR on cash flows that aren't actually evenly spaced. The result silently assumes equal spacing even when the real dates say otherwise.
- Listing dates and values in a different order from each other. Each value has to line up with its own date in the same position.
- Forgetting the rate XNPV takes, and the one XIRR returns, is always an annual rate, not a per-period rate scaled to however far apart the flows happen to be.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice XNPV / XIRR →