Downloads
DAXFinancialAdvanced

XNPV

Returns the net present value of cash flows that fall on specific dates, which do not need to be evenly spaced.

Syntax
XNPV(<table>, <values>, <dates>, <rate>)

Returns: A number in the same currency as the cash flows. A positive value means the discounted inflows are larger than the outflows at that rate.

What it does

XNPV discounts a list of cash flows to a single value today. Each cash flow is discounted for the exact number of days from the first date, using a 365-day year. Unlike PV and NPV-style functions that assume equal periods, XNPV is built for real schedules where payments arrive on different dates, such as a capital project with irregular milestones, a sale with deferred payments or a loan with uneven draws. Finance teams use it for project appraisals, acquisition values and the valuation of contracts.

Syntax and arguments

Sign convention: an initial investment is a negative value. Later receipts are positive. Keep the rows in the table on their true dates, and the first row is usually the date of the investment.

Example

A company invests 10,000 on January 15, 2024. It expects to receive 4,000 on each anniversary for the next three years. The finance team wants the NPV of the project at an 8 percent discount rate.

Project NPV at 8% =
XNPV(
    'Project_Cash_Flows',
    'Project_Cash_Flows'[Amount],
    'Project_Cash_Flows'[CashFlowDate],
    0.08
)

The table holds four rows: -10,000 on January 15, 2024, and 4,000 on January 15 of 2025, 2026 and 2027. The result is 306.21, which is positive, so at 8 percent the project creates a small amount of value. The same cash flows and dates give 306.21 in Excel's XNPV, which is a useful check before you rely on the DAX measure.

Example: a second, different use

The discount rate is held in a parameter table, so the team can test several rates without changing the measure. SELECTEDVALUE reads the rate the user picked, with 8 percent as the default.

Project NPV at Selected Rate =
XNPV(
    'Project_Cash_Flows',
    'Project_Cash_Flows'[Amount],
    'Project_Cash_Flows'[CashFlowDate],
    SELECTEDVALUE('Discount_Rate'[Rate], 0.08)
)

At 10 percent on the same cash flows, the result is -55.19, which is negative. The project does not clear a 10 percent hurdle rate.

Things to watch

In business terms

XNPV supports capital budgeting, acquisition and deal valuation, the value of a contract with deferred payments, the appraisal of a lease or a royalty and the impairment test of a long-term receivable with a known payment schedule. It answers "is this worth more than it costs, in today's money?" for cash flows that do not come at regular intervals.

Related functions

All DAX functions · View as Markdown · DAX fundamentals