English Español Deutsch 日本語 Français Português Русский Italiano Nederlands Polski

Quick Navigation

XNPV Function

Excel 2007+

Summary

The XNPV function calculates the net present value for cash flows occurring on irregular dates, using actual days between payments based on a 365-day year. Unlike standard NPV which assumes periodic intervals, XNPV handles non-periodic cash flows with precise date-based discounting.

Syntax

XNPV(rate, values, dates)

Parameters

Parameter Type Required Description
rate Number Yes The annual discount rate to apply to future cash flows
values Number array Yes Cash flow amounts where first value is initial investment (typically negative)
dates Date array Yes Payment dates matching each cash flow value

Using the XNPV Function

XNPV is essential for financial analysis when cash flows don't follow regular monthly or annual patterns. It calculates NPV by discounting each cash flow based on the exact number of days from the first payment date, providing more accurate valuation for irregular investment schedules.

Common XNPV Examples

Investment NPV Analysis

=XNPV(0.09, A2:A6, B2:B6)

Calculates NPV for $10,000 investment with irregular returns at 9% discount rate, returns $2,086.65

Project Cash Flow Valuation

=XNPV(0.12, C10:C15, D10:D15)

Evaluates project profitability with uneven cash inflows/outflows

Real Estate Investment

=XNPV(0.08, F5:F12, G5:G12)

NPV for property with sporadic rental income and expenses

Frequently Asked Questions

XNPV uses actual dates and day counts for irregular cash flows, while NPV assumes equal periods (typically monthly or annual).

No, dates can be in any order as long as they correspond to the values and are after the first date.

Excel's serial date system with 365-day year basis. Dates are truncated to integers.

Common Errors and Solutions

#VALUE!

Cause: Non-numeric values in rate, values, or dates

Solution: Ensure all inputs contain valid numbers/dates

#NUM!

Cause: Dates precede first date or mismatched array sizes

Solution: Verify dates are chronological from first date and arrays match

#VALUE!

Cause: Invalid date values

Solution: Use proper Excel date formats

Notes

  • First value typically negative (initial investment)
  • Must include both positive and negative cash flows
  • Dates stored as serial numbers (Jan 1, 1900 = 1)
  • Uses actual/365 day count convention
  • More precise than standard NPV for irregular schedules

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in: Excel 2003 and earlier

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+