Skip to content
Google Sheets

XNPV function in Google Sheets

Calculates the net present value of an investment based on a specified series of potentially irregularly spaced cash flows and a discount rate.

=XNPV(discount, cashflow_amounts, cashflow_dates)

XNPV syntax and parameters

Three arguments, three required.

  • discountnumberRequired

    The discount rate of the investment over one period.

  • cashflow_amountsrangeRequired

    A range of cells containing the income or payments associated with the investment.

  • cashflow_datesrangeRequired

    A range of cells with dates corresponding to the cash flows in cashflow_amounts.

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 Excel

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 Google Sheets gets this done.