XNPV
The net present value at a rate of cash flows on dates, each row of a table a value and its date.
Group: Financial. Compatible with DAX.
Syntax
XNPV ( <table>, <values>, <dates>, <rate> )| Parameter | Type | Description |
|---|---|---|
table | Table | The cash flows: one row for each payment or receipt. |
values | Expression evaluated for each row | The amount of each row: negative for money paid out, positive for money received. |
dates | Expression evaluated for each row | The date of each row's amount. |
rate | Number | The discount rate a year, as a decimal. |
Returns
Number. The value of all the flows on the earliest date.
Remarks
- Every amount is discounted to the earliest date of the table, in years of 365 days, whatever order the rows are in. The times of day are dropped.
- Rows whose amount is blank are left out. A row with an amount but no date is an error.
- A rate of −1 or less is an error. A table without any flow gives BLANK.
- At the rate
XIRRfinds, the result is 0.
Examples
The new line at 8 %
The value of the flows on 15 January 2024, the day of the investment.
EVALUATE
VAR Flows =
DATATABLE (
"Date", DATETIME, "Amount", DOUBLE,
{
{ "2024-01-15", -500000 },
{ "2024-09-30", 120000 },
{ "2025-06-30", 180000 },
{ "2026-01-31", 150000 },
{ "2026-10-31", 210000 }
}
)
RETURN
ROW ( "NPV at 8 %", XNPV ( Flows, [Amount], [Date], 0.08 ) )| NPV at 8 % |
|---|
| 72025.1730866632 |
Row order does not matter
The same flows listed backwards give the same value.
EVALUATE
VAR Flows =
DATATABLE (
"Date", DATETIME, "Amount", DOUBLE,
{ { "2026-10-31", 210000 }, { "2026-01-31", 150000 }, { "2025-06-30", 180000 },
{ "2024-09-30", 120000 }, { "2024-01-15", -500000 } }
)
RETURN
ROW ( "NPV at 8 %", XNPV ( Flows, [Amount], [Date], 0.08 ) )| NPV at 8 % |
|---|
| 72025.1730866632 |