XNPV Function

Calculates net present value for cash flows on specific dates.

Category

Financial

Available in

All versions, Microsoft 365

Returns

A net present value

Taught hands-on in Financial Modeling · Financial Functions Toolkit module

Overview

XNPV discounts each cash flow by the exact number of days since the first one, using an annual rate. Unlike NPV, the first cash flow is today (not discounted) and the flows can be any distance apart.

It's the standard for deal valuations and real project schedules.

Syntax

=XNPV(rate, values, dates) Explain

Arguments

ArgumentRequired?What it does
rateYesThe annual discount rate.
valuesYesThe cash flows. The first is usually the investment (negative).
datesYesThe matching dates. The first date is the starting point.

Examples

Example 1 — Store opening on a real schedule

The same store, with cash arriving on actual dates.

B7fx=XNPV(8%,B2:B6,A2:A6)
AB
1DateCash flow
24/1/2026-$150,000
39/15/2026$40,000
46/30/2027$50,000
53/31/2028$60,000
612/15/2028$55,000
7XNPV$30,129.97

Higher than the NPV version because the cash comes in sooner than whole years apart. The 8% is annual — XNPV handles the days.

Common mistakes

  • #VALUE! or #NUM!. Values and dates must be the same size, the dates must be real dates, and none can be before the first date.
  • Using a monthly rate. XNPV always takes an annual rate.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.