Formuladrill

Financial Updated November 2026

XNPV in Excel

XNPV(rate, values, dates) returns the net present value of cash flows that fall on specific dates. Each amount is discounted by the exact number of days since the first date, at a yearly rate. Unlike NPV, the first cash flow is not discounted, so the initial investment goes inside the range.

Syntax

XNPV(rate, values, dates)
PartWhat it is
rateThe yearly discount rate.
valuesThe cash flows. Money paid out is negative, money received is positive.
datesThe date of each cash flow, in a range of the same size. The first date is the starting point.

Examples

=XNPV(0.1, B2:B6, A2:A6)

Result: The value today of the cash flows in B2:B6 on the dates in A2:A6, at 10% a year

=XNPV(B1, B4:B9, A4:A9)

Result: The same with the rate in a cell

Keep the rate in an input cell so it can be changed without editing the formula.

=XNPV(0.1, B2:B6, A2:A6)>0

Result: TRUE when the project earns more than 10% a year

=NPV(0.1, B3:B6)+B2

Result: The equivalent with NPV when the cash flows are exactly one year apart

NPV discounts its first value by one period, so the investment at time 0 is added outside.

Common mistakes

  • #NUM! means values and dates have different sizes, or a date is earlier than the first one.
  • #VALUE! means a date is stored as text.
  • Adding the initial investment outside XNPV, as you would with NPV. With XNPV it belongs inside the range, on the first date.
  • Passing a monthly rate. XNPV always takes a yearly rate and works in days over 365.

Practice XNPV

These drills check your answer on live data, free, no account needed.

  • NPV on irregular datesThe cash flows fall on irregular dates, so NPV, which assumes equal periods, does not fit. Calculate the net present value at the yearly rate in E1 with XNPV, in E2.
  • Is the project worth it?The project costs 50,000 today (year 0) and returns cash in years 1 to 3. Calculate the net present value in B6 using the discount rate in B1.
  • An NPV discounted one year too manyThis sheet decides whether to invest: the project costs 40,000 today (year 0) and returns cash in years 1 to 3. Nadia worked the NPV out by hand and got a higher number than the sheet shows. One formula is wrong. Find it and fix it.

Questions

What is the difference between NPV and XNPV?

NPV assumes equal periods and discounts the first value by one full period. XNPV uses the actual dates and does not discount the first value.

Does XNPV include the initial investment?

Yes. Put it in the first row as a negative number on the start date. It is not discounted.

What is XIRR?

The rate at which XNPV is zero: the internal rate of return for cash flows on irregular dates.

Related

  • NPVHow the Excel NPV function discounts future cash flows at a rate, why the initial investment is added outside the function, with a worked example and XNPV.
  • IRRHow the Excel IRR function finds the rate at which cash flows net to zero, with a worked example, the cash flow layout it needs, the #NUM! fix and XIRR.
  • PMTHow the Excel PMT function calculates the payment on a loan or the deposit for a savings goal, with mortgage and car loan examples and the monthly rate rule.
  • RATEHow RATE works in Excel: find the interest rate per period from the number of payments, the payment and the amount, then turn it into a yearly rate.

Previous: IPMT and PPMT