Real deals don't pay on neat anniversaries. A fund calls capital in March and returns it in October; a contract pays at signing and then every few months. XNPV and XIRR take a date for every cash flow:
=XNPV(rate, values, dates) and =XIRR(values, dates)
Each value is discounted by (1 + rate)^(days / 365), where days count from the first date in the list. That first cash flow isn't discounted, so unlike NPV, the year 0 amount goes inside the range. The rate is always annual, even when the cash flows are monthly.
XIRR is the annual rate that makes XNPV zero. Private equity funds and deal teams report returns this way because money goes in and out on irregular dates.
Two checks before you trust the answer: the dates must be real dates, not text, and the values need at least one negative and one positive.
A B
1 1/15/2026 -10,000
2 7/1/2026 2,500
3 3/31/2027 4,000
4 12/15/2027 6,000
=XNPV(8%,B1:B4,A1:A4) 1,236.86
=XIRR(B1:B4,A1:A4) 17.7%
The first cash flow isn't discounted; each later one is discounted by its days from 1/15/2026.