Real cash flows rarely arrive in tidy annual steps. A private-equity fund draws capital on one date, calls more eight months later, distributes on assorted dates, and exits years after that. NPV and IRR assume equally spaced periods. Feed them irregularly dated flows as if they were annual and they mis-time every one without a word of warning. The date-aware pair solves this:
XNPV(rate, values, dates) XIRR(values, dates, [guess])How do XNPV and XIRR work?
Both take an explicit dates array alongside the values. XNPV discounts each flow by the actual fraction of a year between its date and the first date, using a 365-day-year (ACT/365) convention:
XNPV = Σ cashflow_i / (1 + rate) ^ ((date_i − date_0) / 365)Two structural safety features fall out of this. The first cash flow is anchored at t = 0 and isn't discounted, so unlike NPV, XNPV handles an initial outlay correctly with no "outside the function" trick. And irregular gaps come out exact. A flow 18 months out is discounted by 1.5 years' worth instead of being forced into a "year 1 or year 2" bucket. XIRR is the rate that makes XNPV equal zero, the true annualised internal rate of return for a dated stream.
A small illustration. Invest ₹50,000 on 1 Jan 2024 and receive ₹20,000 on each of 1 Jul 2024, 1 Jan 2025 and 1 Jul 2025. =XNPV(0.10, values, dates) = ₹4,586.76, a positive NPV at a 10% hurdle. =XIRR(values, dates) = 20.34%, the annualised return that respects the actual half-year spacing. Force this through IRR as if the flows were annual and you'd get a different, wrong number, because IRR would pretend each ₹20,000 arrived a full year apart.
Advertisement
Why does XIRR return an error, and how do you fix it?
The XIRR guess. XIRR solves iteratively, starting from a default guess of 10%. Most streams converge instantly. A stream with several sign changes, or a very high or negative return, can fail with #NUM!. So supply a sensible guess as the third argument. =XIRR(values, dates, 0.2) nudges the solver toward a root around 20%. If it still fails, the cash-flow signs are usually the real problem, since a valid IRR needs at least one negative and one positive flow.
Put dates in a real date-formatted column. Text that only looks like a date won't work, and you can check with =ISNUMBER(A2), which should return TRUE because Excel stores dates as serial numbers. Both functions live under Formulas ▸ Financial. Keep the values and dates as two columns of one Table so the arrays stay aligned when you insert a distribution.