All versions
Financial
The Excel XNPV function returns the net present value of cash flows that occur on irregular, actual dates (unlike NPV's equal periods).
Quick answer:
=XNPV(8%, B2:B6, A2:A6) // NPV with real dates
Syntax
=XNPV(rate, values, dates)
| Argument | Description | |
|---|---|---|
rate | Required | The annual discount rate. |
values | Required | A range of cash flows (first is usually the negative investment). |
dates | Required | A matching range of dates for each cash flow. |
How to use it
XNPV returns the net present value of cash flows that occur on irregular, actual dates (unlike NPV's equal periods).
=XNPV(8%, values, dates) // date-accurate NPV
Try it: interactive demo
Live demo
This is the formula pattern XNPV uses — copy it into Excel with your own numbers.
Result: computed in Excel
Practice workbook
Download the free XNPV practice workbook
Every example on this page, ready to open in Excel — plus practice challenges with answers on a separate tab. No sign-up required.
Frequently asked questions
XNPV vs NPV?
XNPV uses the actual date of each cash flow, so timing is exact; NPV assumes equal periods.
Do the dates need sorting?
The first date is the start; others can be in any order but must pair with their values.
Which Excel versions support it?
All modern versions.
Why might it return #NUM! or #VALUE!?
Out-of-range arguments (e.g. negative rate or settlement after maturity) give #NUM!; non-numeric inputs give #VALUE!.
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class