Excel XNPV Function

XNPV discounts cash flows on specific dates and adds their present values as of the first date.

Use it to compare an initial investment with later receipts and expenses when the dates may be irregularly spaced.

1
Set the annual discount rate Use a constant annual discount rate, even when the cash flows occur monthly or on irregular dates.
2
Pair every cash flow with its date Include the initial investment and later receipts or expenses. Money paid out is negative and money received is positive. Keep each amount aligned with its date.
3
Calculate their value on the first date XNPV discounts each later amount by the days since the first date and adds the signed present values. The first cash flow is included without discounting.

XNPV syntax & arguments

Syntax

=XNPV(rate, values, dates)
Required Optional
  1. 1

    rate

    Required

    The positive annual discount rate to apply to the dated cash flows. Use an effective annual rate even when cash flows occur monthly or on irregular dates.

  2. 2

    values

    Required

    A range or array of cash flows, including the initial investment. Each amount must match the date at the same position in dates. Money paid out is negative and money received is positive.

  3. 3

    dates

    Required

    The Excel dates corresponding to the cash flows in values, with the same number of entries. Put the earliest date first. Dates do not need to be equally spaced.

Example

=XNPV(10%,B3:B5,C3:C5)

Return the net present value of the cash flows in B3:B5 using their corresponding dates in C3:C5 and an annual discount rate of 10%.

XNPV caveats

  • What positive and negative amounts mean

    Positive cash flows are money you receive and negative cash flows are money you pay out. XNPV adds their signed present values. Unlike PV, it does not reverse the sign to return a balancing cash flow.

  • Include the initial cash flow in the values

    The first cash flow is already at the valuation date, so it is not discounted. Include the initial investment in values rather than adding it separately as you would with NPV. Adding it again would count it twice.

  • Every amount needs a matching date

    The first value belongs to the first date, the second value to the second date, and so on. Ranges with different numbers of entries return #NUM!. When sorting the data, move amounts and dates together so each pair stays intact.

  • The first date must be the earliest

    XNPV values the cash flows as of the first date. Later entries do not have to be sorted, but any date earlier than the first returns #NUM!. Keeping the pairs in chronological order makes the timeline easier to check.

  • Use Excel dates, not text labels

    Supply cells containing Excel dates or create dates with DATE, such as =DATE(2027,1,1). An invalid date returns #VALUE!. XNPV ignores the time of day because date serial numbers are truncated to whole days.

  • Use an annual rate on a 365-day basis

    XNPV discounts using the actual number of days divided by 365, including across leap years. Use an effective annual rate rather than a rate per payment interval. Do not divide an annual rate by 12 for monthly dates.

    For a nominal annual rate of 6% compounded monthly, use (1+6%/12)^12-1 to find the effective annual rate. If 6% is already an effective annual rate, enter 6% directly.

  • The result is an amount, not a return rate

    XNPV returns a monetary amount as of the first date. With the initial investment included, a positive result means the discounted receipts exceed the discounted expenses at the chosen rate. A negative result means the expenses exceed the receipts after discounting.

  • The discount rate must be positive

    A zero or negative rate returns #NUM!. For a zero discount rate, use SUM to add the signed cash flows without discounting them. XIRR can return a negative rate, even though XNPV cannot use that rate.

Finding the break-even annual return? Use XIRR with the same cash flows and dates to find the annualized rate that makes their net present value zero.

Intro XNPV practice problem

Solve the intro problem directly here, or open it on its own page.

Open full problem

Advanced XNPV practice problems

Use XNPV alongside other Excel functions in realistic, less-prescriptive challenges.

No advanced XNPV problems are currently available.