Excel like a finance pro.
The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.
XNPV / XIRR
FinanceNPV and IRR for cash flows on real, irregular dates.
Difficulty
1What is it?
XNPV discounts a series of cash flows that occur on specific dates back to a present value at a given rate; XIRR finds the rate that makes that present value zero. Use these over NPV/IRR, which wrongly assume every period is exactly equal.
2What it looks like
XNPV(rate, values, dates)- rate
- The annual discount rate (e.g. 0.10 for 10%).
- values
- The cash flows — at least one negative and one positive.
- dates
- The date of each cash flow, same count as values.
3When you use it
- Value an investment or project with uneven timing.
- Compute a deal's IRR from dated cash flows (XIRR).
- Compare scenarios at a chosen discount rate.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Edit the dated cash flows (B2:B4) and the discount rate.
=XNPV(10%, B2:B4, A2:A4)-$5,785 NPV| A | B | C | |
|---|---|---|---|
| 1 | Date | Cash flow | Result |
| 2 | 1/1/2026 | -$5,785 NPV | |
| 3 | 1/1/2027 | ||
| 4 | 1/1/2028 |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#NUM!XIRR can't converge, or there's no sign change.Fix: Include at least one negative and one positive flow; give XIRR a guess.
#VALUE!Dates aren't real dates, or counts differ.Fix: Use real dates and match the number of values and dates.
6Better functions & alternatives
- NPV / IRR — Only valid when every period is the same length.
- PMT — For level payments on a loan rather than a valuation.
Want XNPV / XIRR already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Get my free report →