Financial

XNPV Function in Excel

Returns the net present value for a schedule of cash flows with specific dates.

Syntax

  • =XNPV(rate, values, dates)

Arguments

  • rate (required): Discount rate
  • values (required): Cash flow values
  • dates (required): Dates for each cash flow

Examples

  • =XNPV(10%, A1:A5, B1:B5) - NPV with dates - Result: Present value

Excel XNPV function documentation

  • XNPV computes net present value for cash flows occurring on specific irregular dates.
  • Syntax: =XNPV(rate, values, dates).
  • Use XNPV when monthly or ad hoc payment timing makes standard [NPV](/functions/npv/) assumptions too rough.
  • Related metrics: [IRR](/functions/irr/), [XIRR](/functions/xirr/), [PV](/functions/pv/), [Financial functions](/categories/financial/).

XNPV arguments and date requirements

  • rate is annual discount rate and should match your valuation framework.
  • values and dates arrays must be equal length and aligned row-by-row.
  • At least one negative and one positive flow is typical for investment appraisal.
  • dates must be valid Excel dates; text dates can break calculations.

Step-by-step: irregular cash-flow valuation

  • Step 1 — Put cash flows in B2:B20 and exact dates in C2:C20.
  • Step 2 — Enter discount rate in E1.
  • Step 3 — Compute =XNPV(E1, B2:B20, C2:C20).
  • Step 4 — Test sensitivity by varying E1 across best/base/worst cases.
  • Step 5 — Calculate [XIRR](/functions/xirr/) on the same schedule for rate comparison.

XNPV vs NPV vs PV

  • XNPV discounts each cash flow using actual day count timing.
  • NPV assumes equal spacing between periods.
  • PV is best for standard annuity or lump-sum structures.
  • If dates are irregular, XNPV generally produces more decision-useful values than periodic NPV.

Worked examples to copy

  • Basic dated valuation: =XNPV(0.1, B2:B8, C2:C8).
  • Hardcoded example: =XNPV(0.12, {-10000,2500,3000,4200,4500}, {DATE(2026,1,1),DATE(2026,4,30),DATE(2026,9,15),DATE(2027,2,1),DATE(2027,12,31)}).
  • Compare periodic estimate: =NPV(0.1, B3:B8)+B2.
  • Decision flag: =IF(XNPV(rate,flows,dates)>0,"Value creation","Value destruction").

People also ask

  • Why does XNPV differ from NPV? — Uneven timing materially changes discounting.
  • Can XNPV use monthly rate? — Prefer annual rate with true dates.
  • XNPV #NUM? — Often invalid dates or misaligned ranges.
  • Do I still need IRR? — Yes, pair XNPV (value) with [IRR](/functions/irr/) or XIRR (rate).

Common errors

  • First date should be earliest

Use cases

  • Irregular cash flows
  • Investment valuation
  • Project analysis

Frequently asked questions

  • What is the difference between NPV and XNPV? NPV assumes equal periods between cash flows. XNPV uses actual dates, making it accurate for irregular cash flows. Use XNPV when payments don't occur at regular intervals - it calculates exact time between each cash flow.
  • How do I set up XNPV correctly? Values and dates must be in the same order. First value is typically the initial investment (negative). Dates should be actual dates, not text. Rate is annual. Example: =XNPV(10%, {-1000,200,300,400,500}, {date1,date2,date3,date4,date5}).
  • Why is my XNPV different from NPV? XNPV accounts for exact timing. If cash flows are monthly but not exactly 30 days apart, XNPV gives more accurate results. NPV assumes exactly equal periods. For annual cash flows on exact anniversaries, results should be similar.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use XNPV

  • Irregular cash flows — common Financial scenario for XNPV.
  • Investment valuation — common Financial scenario for XNPV.
  • Project analysis — common Financial scenario for XNPV.

XNPV in the Financial category

  • Browse all Financial functions at /categories/financial/ for related formulas.
  • XNPV syntax: =XNPV(rate, values, dates)
  • rate (required): Discount rate
  • values (required): Cash flow values
  • dates (required): Dates for each cash flow
  • Confirm XNPV arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm XNPV arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • NPV (/functions/npv/): Calculates the net present value of an investment based on a discount rate and future cash flows.
  • XIRR (/functions/xirr/): Returns the internal rate of return for a schedule of cash flows with specific dates.
  • PV (/functions/pv/): Returns the present value of an investment - the total amount that future payments are worth now.

Errors to watch for

  • First date should be earliest — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter XNPV on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

XNPV worked examples to copy

  • =XNPV(10%, A1:A5, B1:B5) — NPV with dates. Expected result: Present value.

XNPV reference summary for crawlers and offline review

  • XNPV belongs to the Financial category in Excel. Returns the net present value for a schedule of cash flows with specific dates.
  • Full syntax: =XNPV(rate, values, dates). Open /functions/xnpv/ for parameters, FAQs, and related pages.
  • Common mistakes: First date should be earliest
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.