XNPV in WPS Spreadsheet: Discount Cash Flows by Actual Dates
Calculate dated net present value with actual-day offsets over 365, preserve the initial flow and distinguish monetary value from a rate.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
XNPV returns a monetary net present value for cash flows that need not occur at equal intervals. The WPS formula uses actual day differences from the first date, divided by 365, including across a leap year. The first flow has a zero date offset and is included without discounting.
Syntax and date preparation
=XNPV(rate, values, dates)
All three arguments are required. Values and dates must correspond one-to-one. WPS requires mixed positive and negative flows and identifies the first date as the starting date. These examples put the earliest date first, with every subsequent date strictly later and in increasing order.
Use actual spreadsheet dates, preferably constructed with DATE rather than ambiguous date text. For example, enter =DATE(2025,1,1) in B2 and =DATE(2026,1,1) in B3. The ISO dates printed here describe calendar dates, not text to pass directly as arguments. No raw serial-number origin or historic 1900 boundary is needed for these modern-date controls.
Work through an original example
Enter −100 in A2 and 110 in A3, with the two dates above in B2:B3. Use =XNPV(0.1,A2:A3,B2:B3).
The dates are 365 days apart. The independent calculation is −100 + 110/(1.1)^(365/365) = 0. This is a net value in the cash flows’ monetary units, not a 10% return. The 10% is the discount-rate input.
Change only the second date to July 2, 2025, 182 days after the first. The receipt is discounted for 182/365 of a year and the independent value becomes approximately 4.8945791575. Receiving the same positive amount sooner increases its present value at a positive discount rate.
Date-sensitive controls
| Dates, in order | Flows | Rate | Independent value |
|---|---|---|---|
| 2025-01-01;2026-01-01 | −100;110 | 0.1 | 0 |
| 2024-01-01;2025-01-01 | −100;110 | 0.1 | about −0.0261089690 |
| 2025-01-01;2025-07-02 | −100;110 | 0.1 | about 4.8945791575 |
| 2025-01-01;2026-01-01 | −100;110 | 0.2 | −8.3333333333 |
| 2025-01-01;2026-01-01;2027-01-01 | −210;121;121 | 0.1 | 0 |
| 2025-01-01;2026-01-01 | −200;220 | 0.1 | 0 |
The leap-crossing interval has 366 days. Its exponent is 366/365, not 1 simply because the month and day repeat. The three-flow example uses offsets 0, 365, 730 and discounts each amount separately.
Reconcile initial value and timing
For flows −210, 121, 121, the first receipt has present value 110 and the second 100. Their sum 210 cancels the initial −210. Do not add that initial amount a second time outside XNPV. This differs from common use of NPV, where the first supplied periodic amount has a different timing convention.
Date and amount columns must stay paired during sorting. Positive discount rates reduce later positive receipts more strongly, but negative later flows also have their own discounting; a general mixed-flow total need not respond monotonically to every input change.
Documentation and boundaries
The vendor example uses −200000 on 2020-02-02 and receipts 60000, 100000, 150000 on 2021-08-08, 09, 10 at 15%, displaying 50718.13722. Its formula image explicitly supplies the actual-days/365 exponent. These are source observations, not executions of the original examples.
WPS says fractional date serials are truncated, dates before the start cause #NUM!, mismatched value/date counts cause #NUM!, and invalid or nonnumeric dates can cause #VALUE!. Original controls avoid those cases and all rate-boundary questions. No assumptions about other products’ acceptance of unsorted dates or all-positive flows are imported. A net value is a calculation under chosen assumptions, not an investment recommendation.
Availability and evidence scope
The official WPS lesson is dated 2019-06-13 and explicitly labels WPS Office for Windows Personal. Its numeric build is unspecified. This documents the function by that date; it does not establish first introduction, the last functioning build, other-platform parity or a retirement/deprecation history.
The complete lesson and its formula images and walkthrough were inspected on October 18, 2026. Original examples are documentation-derived independent calculations, not WPS or other spreadsheet-engine executions. These fictional financial calculations explain a function, not the suitability of a financial product.