NPV vs XNPV in Excel: which one to use

The difference in one table

Both functions answer the same question: what is a series of future cash flows worth today at a required rate of return? The difference is how they measure time.

NPV knows nothing about dates. It counts positions: the first value in the range is period 1, the second is period 2 and so on, and every value is discounted by (1 + rate)^period. The period is whatever the rate is: a year with an annual rate, a month with a monthly rate.

XNPV reads a date for every cash flow. The first date is the starting point (time 0), and every other cash flow is discounted by the number of days since that date divided by 365. The rate is always an annual rate, whatever the spacing of the cash flows.

So if your cash flows fall on the same day every year, the two functions give practically the same answer (apart from tiny leap-year differences, because XNPV always divides by 365). As soon as payments arrive mid-year, monthly, or on irregular dates, the results drift apart, and XNPV is the one that matches the real timing.

NPV syntax and the year-0 trap

The syntax is =NPV(rate, value1, [value2], …), usually with a range: =NPV(E11,B3:B7). The crucial detail is that the first value is discounted by one full period. NPV treats the range as starting one period from now, not today.

An initial investment, however, happens today and must not be discounted. That is why the standard pattern keeps it outside the function: =NPV(E11,B3:B7)+B2. In the example this gives $6,375.

If you put the investment inside the range, =NPV(E11,B2:B7), Excel pushes every cash flow one year later. The result is $5,848, which is exactly $6,375 ÷ 1.09: the whole project has been discounted one year too far. This is the most common NPV error in spreadsheets, and it does not produce an error message, just a wrong number.

XNPV syntax and how it discounts, step by step

The syntax is =XNPV(rate, values, dates), for example =XNPV(E11,B2:B7,A2:A7). Values and dates must be ranges of the same size, the dates must be real dates (not text), and the first date should be the start of the project, because all time is measured from it. The investment on the first date is included in the range and is not discounted.

You can rebuild XNPV by hand, which is the best way to understand it. For the first inflow on 31 July 2026:

1. Days since the start: 197. In years: =(A3-$A$2)/365 = 0.540.
2. Discount factor: =1/(1+$E$11)^C3 = 0.9546.
3. Present value: =B3*D3 = $18,000 × 0.9546 = $17,182.

Fill the three formulas down and sum the present values, and you get $18,937, the same as XNPV. The one-cell version of the same calculation is =SUMPRODUCT(B2:B7/(1+E11)^C2:C7). If the three numbers agree, you know the dates and signs are set up correctly.

Why the results differ by $12,562 here

NPV assumes the five inflows arrive at the end of years 1, 2, 3, 4 and 5. In reality the last one arrives after 3.46 years, and the first after only half a year. Money that arrives earlier is discounted less, so the real present value is much higher. The gap of $12,562 is not a rounding issue: it is the value of receiving the cash about one and a half years sooner than NPV assumes.

The opposite can happen too. If a project's payments arrive later than the one-per-row pattern implies, for example a first payment 18 months after the investment, NPV overstates the value. Either way the fix is the same: when the dates are known, use them.

Worked example: one project, NPV vs XNPV

Dates, cash flows and the discount rate are inputs (yellow). Columns C to E rebuild XNPV by hand; the block below compares the functions.

Worked example: one project, NPV vs XNPV

	A	B	C	D	E
1	Date	Cash flow ($)	Years from start	Discount factor	Present value ($)
2	15 Jan 2026	-120,000	0.000	1.0000	-120,000
3	31 Jul 2026	18,000	0.540	0.9546	17,182
4	31 Mar 2027	30,000	1.205	0.9013	27,040
5	20 Dec 2027	35,000	1.929	0.8469	29,640
6	30 Sep 2028	40,000	2.710	0.7918	31,670
7	30 Jun 2029	45,000	3.458	0.7423	33,405
8	Total	48,000			18,937
9
10	Measure				Result
11	Discount rate (annual)				9.0%
12	XNPV on the actual dates				18,937
13	Same result by hand with SUMPRODUCT				18,937
14	NPV, one year per row, first cash flow outside				6,375
15	NPV with the first cash flow inside (wrong)				5,848
16	Gap: XNPV minus NPV				12,562
17	Years from first to last cash flow				3.46
18	Years NPV assumes (one per row after the first)				5

Fictional project and teaching numbers only, not investment advice. Real decisions also depend on risk, taxes and the choice of discount rate.

Monthly and quarterly cash flows with NPV

NPV works fine for evenly spaced flows that are not annual, as long as the rate matches the period. For monthly cash flows, convert the annual rate into an equivalent monthly rate with =(1+annual)^(1/12)-1, which is 0.72% for 9%, and use that inside NPV. Dividing the annual rate by 12 (0.75%) is a nominal convention and discounts slightly more. Whichever you choose, state it next to the result.

XNPV needs none of this: give it the monthly dates and the annual rate, and it does the conversion itself.

When to use NPV and when to use XNPV

Use NPV for planning models with one column per year or per month, where the timing is a modelling convention anyway, such as a five-year business plan or a DCF model. Keep the year-0 amount outside the function.

Use XNPV when the cash flows have real dates: a deal that closes mid-year, a property with irregular rent and sale dates, a project with milestone payments, or historical transactions. It is also the natural partner of XIRR: the XIRR is the rate at which the XNPV is zero, just as the IRR is the rate at which NPV is zero. The XIRR vs IRR guide shows the same timing effect on the return, and the IRR guide explains how to check a return with NPV.

Common mistakes

• Putting the initial investment inside NPV. =NPV(rate,B2:B7) discounts the year-0 amount by one period and shifts every cash flow a year later. Add it outside: =NPV(rate,B3:B7)+B2.
• Giving XNPV a monthly rate. XNPV always expects an annual rate, even when the cash flows are monthly. A monthly rate makes the result far too high.
• Using an annual rate in NPV with monthly rows. NPV treats each row as one period of the rate. With monthly rows, convert the rate first.
• Dates stored as text. Imported dates such as "31.07.2026" in a text cell make XNPV return an error. Convert them to real dates first.
• Wrong signs. Money going out must be negative and money coming in positive in both functions. All-positive values give a meaningless present value.
• A start date that is not first. XNPV measures time from the first date in the range. Put the investment date in the first row.