When should I use XNPV instead of NPV?

Use XNPV when cash flows arrive at uneven intervals (e.g., payments on Jan 15, Mar 20, Jul 5, and Dec 10). NPV assumes regular periods (monthly, quarterly, annual) and cannot handle sporadic dates. XNPV accounts for exact calendar days, making it essential for real-world scenarios like project milestones, insurance claims, or lease schedules.

What discount rate should I use?

Use your organization's cost of capital, weighted average cost of capital (WACC), or minimum required return. Conservative estimates use 10–15%; optimistic scenarios use 3–7%. Always test multiple rates to understand sensitivity. For grade weighting, a rate like 5–10% reflects that recent performance matters somewhat more than older work.

What happens if my dates are not sorted?

XNPV returns #NUM! error if any date is out of chronological order. The function requires ascending order: the earliest date first, then progressively later dates. Sort your data by date column before calling XNPV, or manually verify that date[1] < date[2] < date[3].

Can I use XNPV with negative values?

Yes. XNPV handles both inflows (positive) and outflows (negative). For example, an initial investment of −$10,000 on day 1, a return of +$6,000 on day 100, and +$5,500 on day 200 would be {−10000, 6000, 5500}, showing a net present value after accounting for the upfront cost and delayed returns.

XNPV function

Q: Can I use XNPV with negative values?

Yes. XNPV handles both inflows (positive) and outflows (negative). For example, an initial investment of −$10,000 on day 1, a return of +$6,000 on day 100, and +$5,500 on day 200 would be {−10000, 6000, 5500}, showing a net present value after accounting for the upfront cost and delayed returns.

XNPV returns the net present value of cash flows occurring at irregular intervals, discounting each flow by actual elapsed time.

=XNPV(rate, values, dates)

Generate a XNPV formula

Describe what you need. The generator will reach for XNPV where XNPV is the right tool, and tell you when it is not.

Arguments

How XNPV reads its arguments

Argument	Required	Description
rate	Required	The discount rate per year as a decimal; 0.1 means 10% annual. Must be greater than −1; any rate ≤ −1 produces #NUM!.
values	Required	An array of cash flows (positive or negative); must have the same length as dates, with typically the first flow on the earliest date.
dates	Required	An array of dates corresponding to each value; must be in strict chronological order (earliest first) or #NUM! is returned.

Returns

A number representing the net present value.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate semester grade NPV with 5% annual discount

Student	Subject	Assignment	Date	Score	Max Score
Alex	Mathematics	Quiz 1	2026-01-20	85	100
Alex	Mathematics	Midterm	2026-03-15	72	100
Alex	Mathematics	Project	2026-04-25	95	100
Alex	Mathematics	Final Exam	2026-05-30	88	100

=XNPV(0.05, {85, 72, 95, 88}, {DATE(2026,1,20), DATE(2026,3,15), DATE(2026,4,25), DATE(2026,5,30)})

Result: 336.48

XNPV discounts each score by the elapsed time from the first assignment. The Quiz 1 score (85) on Jan 20 is worth full value; the Midterm (72) is slightly discounted as 54 days have passed; the Project and Final are progressively discounted more. At a 5% annual rate over 130 days, the time-adjusted sum is 336.48 instead of 340.

2. Compare grades with higher discount rate (favors recent performance)

Student	Subject	Assignment	Date	Score	Max Score
Alex	Mathematics	Quiz 1	2026-01-20	85	100
Alex	Mathematics	Midterm	2026-03-15	72	100
Alex	Mathematics	Project	2026-04-25	95	100
Alex	Mathematics	Final Exam	2026-05-30	88	100

=XNPV(0.15, {85, 72, 95, 88}, {DATE(2026,1,20), DATE(2026,3,15), DATE(2026,4,25), DATE(2026,5,30)})

Result: 328.09

A 15% discount rate heavily devalues early assignments. The Jan 20 quiz (85) contributes only ~82.3 in present-value terms; the May 30 final (88) contributes more proportionally because it is recent. The result (328.09) is 8.39 points lower than the 5% example, reflecting that steeper discounting penalizes older assessments more severely.

3. Sum grades with no time discounting (rate = 0%)

Student	Subject	Assignment	Date	Score	Max Score
Alex	Mathematics	Quiz 1	2026-01-20	85	100
Alex	Mathematics	Midterm	2026-03-15	72	100
Alex	Mathematics	Project	2026-04-25	95	100
Alex	Mathematics	Final Exam	2026-05-30	88	100

=XNPV(0, {85, 72, 95, 88}, {DATE(2026,1,20), DATE(2026,3,15), DATE(2026,4,25), DATE(2026,5,30)})

Result: 340

When rate is 0%, XNPV returns the simple sum: 85 + 72 + 95 + 88 = 340. No time discounting is applied; all scores are equally weighted regardless of when they occurred. This isolates the discounting effect: comparing 340 (rate=0) to 336.48 (rate=5%) to 328.09 (rate=15%) shows how discount rate transforms the same score data into different present values.

Common errors

Which XNPV error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The values and dates arrays have unequal lengths, or one contains non-numeric/non-date data (e.g., text, blanks, or mixed types).	Ensure both arrays have exactly the same number of elements with no gaps. For example, =XNPV(0.05, A2:A5, B2:B6) fails (values is 4 cells, dates is 5); change to =XNPV(0.05, A2:A5, B2:B5). Also check that all dates are actual date values, not text.
#NUM!	Dates are not in strict ascending chronological order. XNPV requires each date to be later than the previous one; if date[i+1] < date[i], the calculation breaks.	Sort your data by the dates column from earliest to latest. Verify the sequence visually: 2026-01-20 → 2026-03-15 → 2026-04-25 → 2026-05-30 (each later than the last). If data is mixed, copy to a new range, apply sort, and run XNPV there.
#NUM!	The discount rate is ≤ −1 (e.g., −1.5, −1.0, or −100%). This creates division by zero or negative denominators in the NPV formula, making the result undefined.	Use a rate greater than −1. Typical rates range from −0.50 (−50%) to 1.0 (+100%). If you need a negative rate (declining value), use −0.10 or −0.25, not ≤ −1.0.

Tips and when to use something else

Use XNPV for **irregular payment dates**; if payments are monthly or quarterly (regular intervals), use NPV instead—it is simpler and faster.
The first date serves as your reference point (day 0 for discounting). Place your earliest transaction first and set the first value as your baseline (often an initial investment or payment).
Discount rate has outsized impact on long time horizons. Test rates of 5%, 10%, and 15% side-by-side to see how assumptions shape your conclusion.
XNPV is not limited to finance: you can weight irregular events (grades, milestones, test scores) by time using a discount rate that reflects how much you value recency.

Related functions

NPVNPV returns the net present value of a series of cash flows discounted to their present value today, helping you decide if an investment creates value.XIRRXIRR returns the annualized internal rate of return for cash flows at irregular date intervals, letting you evaluate project or investment profitability over time.IRRReturns the internal rate of return for a series of regular periodic cash flows, measuring annualized investment profitability.PVReturns the present value of an investment based on a series of regular payments and a constant interest rate.FVFV returns the future value of a series of regular cash flows at a constant interest rate, modeling compound growth over time.

Frequently asked questions

When should I use XNPV instead of NPV?: Use XNPV when cash flows arrive at uneven intervals (e.g., payments on Jan 15, Mar 20, Jul 5, and Dec 10). NPV assumes regular periods (monthly, quarterly, annual) and cannot handle sporadic dates. XNPV accounts for exact calendar days, making it essential for real-world scenarios like project milestones, insurance claims, or lease schedules.
What discount rate should I use?: Use your organization's cost of capital, weighted average cost of capital (WACC), or minimum required return. Conservative estimates use 10–15%; optimistic scenarios use 3–7%. Always test multiple rates to understand sensitivity. For grade weighting, a rate like 5–10% reflects that recent performance matters somewhat more than older work.
What happens if my dates are not sorted?: XNPV returns #NUM! error if any date is out of chronological order. The function requires ascending order: the earliest date first, then progressively later dates. Sort your data by date column before calling XNPV, or manually verify that date[1] < date[2] < date[3].
Can I use XNPV with negative values?: Yes. XNPV handles both inflows (positive) and outflows (negative). For example, an initial investment of −$10,000 on day 1, a return of +$6,000 on day 100, and +$5,500 on day 200 would be {−10000, 6000, 5500}, showing a net present value after accounting for the upfront cost and delayed returns.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17