Skip to content
Excel

XNPV function in Excel

Returns the net present value for a schedule of cash flows that is not necessarily periodic

=XNPV(rate, values, dates)

XNPV syntax and parameters

Three arguments, three required.

  • ratenumberRequired

    The discount rate to apply to the cash flows.

  • valuesarrayRequired

    A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive value and one negative value.

  • datesarrayRequired

    A schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order.

XNPV examples

Formulas you'll actually reuse.

  1. Present value of irregularly dated cash flows — dates in A, amounts in B, first row the outlay:

    =XNPV(8%, B2:B8, A2:A8)

  2. A go/no-go from the sign of the NPV:

    =IF(XNPV(8%, B2:B8, A2:A8) > 0, "Invest", "Pass")

    Result"Invest"

XNPV in Google Sheets

Same name — your formula ports as-is.

Try XNPV in the playground

Edit the example — nothing to install.

Preloaded with the XNPV formula from Example 1 — change anything and watch it respond.

Loading the editor…

XNPV errors

What they mean — and the fixes.

  • #NUM!

    A date is earlier than the first date — the first row must be the earliest cash flow.

Related functions

More ways Excel gets this done.