Summary

The Excel XNPV function returns net present value using exact calendar dates for each cash flow. Microsoft documents XNPV as the appropriate choice when the cash flows are not evenly spaced, which makes it the date-accurate counterpart to periodic NPV.

XNPV is therefore the correct function for milestone payments, staged investments, irregular receipts, and other real schedules that do not follow equal month-end or year-end spacing. It discounts each cash flow by the exact day distance from the first date in the series.

The result is a value amount, not a return rate. If the worksheet also needs a dated return percentage, XIRR is the companion function.

Syntax

=XNPV(rate, values, dates)

rate is the annual discount rate, values is the cash-flow range or array, and dates is the aligned set of actual dates for those cash flows. Microsoft notes that the rate must be greater than -1, and the values and dates arrays must be the same length.

XNPV uses the first date in the series as the base date and discounts all later values relative to that starting point using a 365-day year. That day-based treatment is what makes the result different from periodic NPV.

Arguments

• rate - Annual discount rate as a decimal.
• values - Cash-flow amounts aligned with each date in the series.
• dates - Actual dates corresponding to each cash-flow amount.

To keep the result reliable, make sure the values and dates are aligned row by row, the dates are real Excel dates, and no later date appears before the first one in the series.

XNPV vs Related Functions

XNPV and NPV answer similar valuation questions, but they assume different timing structures.

Use XNPV with XIRR for date-driven models. Use NPV with IRR when the cash-flow spacing is periodic and equal by design.

Using the XNPV Function

XNPV is especially useful when a project does not follow clean monthly or yearly timing. Instead of forcing the worksheet into artificial equal periods, the function discounts each flow based on the actual number of days from the first cash-flow date.

That makes XNPV more realistic for staged funding, milestone billing, investment draws, and irregular repayments. In those cases, using periodic NPV can distort the valuation because it assumes timing symmetry that the cash-flow schedule does not have.

• Use XNPV for real dated schedules, not for evenly spaced periodic models.
• Keep the discount rate in an assumption cell when scenario analysis is needed.
• Pair the result with XIRR when stakeholders want both value and return perspectives.

Example 1 - Irregular NPV Check

This example uses one initial outflow and three later inflows with uneven spacing. Because the dates are irregular, XNPV gives a more accurate valuation than NPV would under an equal-period assumption.

=XNPV(0.1,B1:B4,A1:A4)

Example 2 - Comparing Rate Sensitivity

Changing only the discount rate while keeping the same dated cash flows is a clean way to measure sensitivity. Lower required return raises present value, while higher required return lowers it.

=XNPV(0.08,B1:B4,A1:A4)

Example 3 - Single-Inflow Precision

A two-date case makes the day-count effect easier to isolate. The difference between January 1 and June 1 is part of the valuation itself, so the function reflects timing with more precision than a rough half-year assumption.

=XNPV(0.1,{-1000,1500},{"2026-01-01","2026-06-01"})

Example 4 - Dynamic Investment Impact

Referencing the rate from a cell makes XNPV easier to use in scenario analysis. The cash-flow calendar remains fixed while the required return can be tested under base, optimistic, or conservative assumptions.

=XNPV(G1,B1:B4,A1:A4)

Conclusion Recap

• Summary: XNPV discounts cash flows using their actual calendar dates.
• Syntax: =XNPV(rate,values,dates).
• Core setup: Keep values and dates aligned, use real dates, and use a valid annual discount rate.
• Best use: Irregular cash-flow schedules, dated valuation, and scenario-driven discount-rate analysis.