XNPV
Returns the net present value of cash flows that fall on specific dates, which do not need to be evenly spaced.
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
- table: the table that holds the cash flows and their dates, one row for each cash flow. Required.
- values: the column of cash flows. Money you pay out is negative, and money you receive is positive. Required.
- dates: the column of dates for the cash flows. Each date must be a real date, and each cash flow must have one. Required.
- rate: the annual discount rate, such as 0.08 for 8 percent. Required.
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
- Dates must be real dates, not text, and every cash flow needs a date. A blank date gives an error or a wrong value.
- The sign convention decides the answer. Reversing the signs gives the NPV of the other side of the deal.
- The rate is an annual rate, and the function uses a 365-day year. A monthly rate must be converted to an annual one first.
- Check the first row. In Excel, the discounting is measured from the first date in the list, so keep the investment row first and in date order.
- XNPV does not handle taxes, financing or terminal values. Those must be in the cash flows.
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.