XNPV
XNPV(rate, values, dates) discounts cash flows that land on irregular dates, each by its own day count. Worked examples and how it differs from NPV. Matches Excel.
Syntax
XNPV(rate, values, dates)| Argument | Required | Description |
|---|---|---|
rate | Required | The annual discount rate. |
values | Required | The cash flows. The first is usually the investment, as a negative number. |
dates | Required | The date of each cash flow, one per value. None may be earlier than the first. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=XNPV(0.09,A2:A6,B2:B6) | 2086.65 | Microsoft's example — five flows over fifteen months at 9%. |
=XNPV(XIRR(A2:A6,B2:B6),A2:A6,B2:B6) | 0.00 | At the XIRR the net present value is zero, by definition. |
=XNPV(0.09,A2:A6,B2:B5) | #NUM! | Values and dates must pair up one to one. |
NPV with real dates
NPV assumes the cash flows are exactly one period apart.
Real projects are not that tidy — a deposit on signing, payments on invoice,
a final settlement months later. XNPV discounts each flow by the actual
number of days since the first, as a fraction of a 365-day year.
The first flow is not discounted
Unlike NPV, which treats its first value as a period away, XNPV measures
every date from the first one, so the first flow counts at full value. The
initial investment belongs inside the range.
Order and dates
The dates do not have to be sorted, but none may fall before the first one, which is the date everything is discounted to. Dates are whole days; a time of day is ignored.
Excel compatibility
Matches Excel, including discounting by (date - first date) / 365 and #NUM! for mismatched lengths or a date before the first.