Quick Answer

XNPV Function

Calculates the net present value for a schedule of cash flows that is not necessarily periodic.

Syntax

XNPV

(rate, values, dates)

Parameters

Parameter	Description	Required
rate	Discount rate to apply to the cash flows.	Required
values	Series of cash flows corresponding to the dates.	Required
dates	Series of dates corresponding to the cash flows.	Required

Basic Example

NPV for irregular cash flows

=XNPV(0.1, B2:B5, A2:A5)

ResultNet present value

A2:A5 contains dates and B2:B5 contains the cash flows, discounted at 10%.

Advanced Examples

Example 1: Include initial investment

Project evaluation

Initial investment is included in the values array on the first date

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

Result: Project NPV

Unlike NPV, XNPV includes the initial investment in the values array because it is tied to a date.

How XNPV Works

XNPV discounts each cash flow to the first date using the actual number of days between dates. The discount rate is annualized.

1

Enter rate

Type the rate

2

Enter values

Type the values

3

Enter dates

Type the dates

4

Calculate

Press Enter to get the result.

Important Notes & Limitations

Dates must be valid and the values array must match the dates array in length.
The initial investment is included in the values array, not added separately.
Uses a 365-day year for annualization.

Common Errors & Fixes

#VALUE! errorDates and values arrays have different lengths or dates are invalid.

Fix: Ensure arrays match and dates are valid.

Wrong NPVInitial investment was added outside the formula.

Fix: Include the initial investment in the values array with its date.

Download Practice File

Practice XNPV with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the XNPV function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Functions

ACCRINT

Calculates accrued interest for a security that pays periodic interest.

ACCRINTM

Calculates accrued interest for a security that pays interest at maturity.

AMORDEGRC

Calculates depreciation for a specific period using the French declining balance method.

AMORLINC

Calculates depreciation for a specific period using the French straight-line method.

COUPDAYBS

Calculates the number of days from the beginning of the coupon period to the settlement date.

COUPDAYS

Calculates the number of days in the coupon period that contains the settlement date.

Frequently Asked Questions

What is the difference between NPV and XNPV?

NPV assumes equal periods. XNPV uses actual dates for irregular intervals.

Do I add the initial investment separately in XNPV?

No, include it in the values array with its date.