XNPV Function

Excel Guides / Financial

Financial

Value a set of future cash flows in today's money, using the actual date of each one.

Updated 12 Oct 2026 Ā· 5 min read Ā· By Sky Dashboards

XNPV tells you what a set of future cash flows is worth in today's money, using the actual date of each one. You give it a discount rate, the cash flows and their dates, and it returns one figure. Positive means the flows earn more than that rate; negative means they fall short. It also fixes the two weak spots of the older NPV function: the assumption of even spacing, and its confusing treatment of the first cash flow.

XNPV syntax

=XNPV(rate, values, dates)

Argument Required? What it does
rate Required The annual discount rate, such as 8% or a cell that holds it.
values Required The cash flows. Payments out are negative and receipts are positive. The first value belongs to the starting date.
dates Required The date of each value. The first date is the starting point, and no other date may come before it.

All three arguments are required. XNPV is available in every current version of Excel.

How XNPV discounts each cash flow

Each value is divided by (1 + rate) ^ ((date - first_date) / 365), and the results are added up. The first value has an exponent of zero, so it is not discounted at all. A value 365 days later is discounted by one full year, and one 182 days later by roughly half a year.

The discounting always runs back to the first date in the list, not to today. To get today's value, make the first row today's date with a cash flow of 0.

Worked example: a new oven for a bakery

A bakery is weighing up a $9,000 deck oven bought on 1 March 2026. It expects the extra wholesale capacity to bring in the cash flows below, and it wants an 8% annual return on money tied up in equipment. Columns C and D do the discounting by hand so you can see it; G2 does it in one step.

G2fx=XNPV(G1, B2:B7, A2:A7)
A B C D E F G
1 Date Cash flow Years Discounted Rate 8%
2 01-Mar-26 ($9,000.00) 0.00 ($9,000.00) XNPV $1,400.47
3 30-Jun-26 $1,800.00 0.33 $1,754.66 Check $1,400.47
4 31-Oct-26 $2,200.00 0.67 $2,089.68
5 28-Feb-27 $2,400.00 1.00 $2,222.69
6 30-Jun-27 $2,300.00 1.33 $2,075.98
7 31-Dec-27 $2,600.00 1.84 $2,257.46

C2 is =(A2-$A$2)/365 and D2 is =B2/(1+$G$1)^C2, both filled down. G3 checks the total with =SUM(D2:D7).

The result, $1,400.47, is the value of the whole plan on 1 March 2026 after allowing for an 8% return. Positive means the flows more than cover that return. Without discounting, the flows net $2,300. Discounting takes about $900 off, because most of the money arrives a year or more after the purchase.

Try other rates and watch the value fall as the rate rises:

Discount rate XNPV
0% $2,300.00
5% $1,719.14
8% $1,400.47
12% $1,006.17
20% $307.73
30% ($428.25)

Tip

A one-variable data table builds this rate table for you and updates it whenever the cash flows change.

The NPV timing gotcha

The older =NPV(rate, value1, [value2], ...) assumes evenly spaced periods and that the first value arrives one period from now. It discounts every value it is given, including the first. If your first value is a purchase made today, NPV pushes it a full period into the future.

Take a simpler plan with yearly flows in B2:B6: $9,000 spent today, then $2,500, $3,000, $3,000 and $3,500 at the end of each of the next four years, discounted at 8%.

=NPV(8%, B2:B6)

Wrong: returns $778.64, because the $9,000 is treated as if it were paid a year from now.

=NPV(8%, B3:B6) + B2

Right: returns $840.93. NPV discounts the four future flows, and the cost today is added at full value.

The wrong answer is the right one divided by 1.08: one extra year of discounting on everything. XNPV avoids the issue because its first value always sits at time zero.

Watch out

NPV ignores dates entirely. If the gaps between cash flows are uneven, use XNPV.

XIRR answers the opposite question. Instead of valuing the flows at a rate you choose, it finds the rate at which XNPV is exactly zero. So the two always agree:

=XNPV(XIRR(B2:B7, A2:A7), B2:B7, A2:A7)

Returns $0.00 for the oven example, give or take a fraction of a cent. The XIRR is 23.96%.

That makes a handy check on your inputs. It also explains any XNPV result at a glance: for a plan that starts with a cost and then brings money in, a positive XNPV means the XIRR is above your rate, and a negative one means it is below. The oven's XNPV is positive at 8% and at 20%, and negative at 30%, which fits an XIRR of 23.96%.

To find the rate or cash flow that produces a particular XNPV rather than zero, let Goal Seek change the input cell for you.

Common XNPV mistakes

  • Using a monthly rate. The rate is always annual, even when the flows are monthly. Do not divide it by 12.
  • A positive purchase price. Costs must be negative, or XNPV counts them as income.
  • The start date is not first. The first row must hold the earliest date. Any earlier date returns #NUM!.
  • Ranges of different sizes. values and dates must cover the same number of cells, or XNPV returns #NUM!.
  • Dates stored as text. Text dates return #VALUE!. Real dates right-align in a cell by default; text does not.

Frequently asked questions

What is the difference between NPV and XNPV?

NPV assumes evenly spaced periods and discounts the first value by one period. XNPV uses the actual dates, treats the first value as time zero and always works with an annual rate.

Does XNPV use a 365-day year?

Yes. It divides the days between dates by 365, even across leap years.

Can I use a monthly rate in XNPV?

No. XNPV converts the days between flows into years, so it needs an annual rate.

Why does XNPV at the XIRR rate show a tiny number instead of zero?

XIRR stops searching once it is extremely close to the answer, so the rate is very slightly rounded. The leftover is a fraction of a cent and can be ignored.

Want the finished dashboard instead?

My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.

Browse templates ā†’

Related guides

All 32 Excel guides