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.
XNPV syntax & arguments
Syntax
=XNPV(rate, values, dates)
-
1
rate
RequiredThe 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
values
RequiredA 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
dates
RequiredThe 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
valuesrather 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-1to find the effective annual rate. If 6% is already an effective annual rate, enter6%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
ratereturns#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.
Advanced XNPV practice problems
Use XNPV alongside other Excel functions in realistic, less-prescriptive challenges.
No advanced XNPV problems are currently available.