XNPV in Collabora Office: Discount Cash Flows on Actual Dates

=XNPV(Rate; Values; Dates)

All three arguments are required. Rate is the annual discount rate for the model; enter 8% or 0.08, not 8. Values contains the cash amounts. Dates contains their corresponding dates, in matching order and with the same number of entries.

Outflows use negative amounts and inflows positive amounts. The Help describes a schedule containing both signs, with the first date as the starting reference and later dates after it. Later rows need not be sorted, but each value must stay paired with its date.

Although the Help calls Rate an internal rate of return, XNPV takes a rate as input; it does not solve for one. A root-finding function such as XIRR addresses the rate at which the net present value becomes zero.

For each pair, the calculation is:

cash flow / (1 + Rate)^((payment date − first date)/365)

XNPV sums those discounted amounts. It has no Basis argument. The denominator is always 365, while the numerator counts actual serial-day differences, including leap days. Thus “ignoring leap years” in the Help means the year denominator does not become 366; it does not mean deleting 29 February from elapsed days.

The first flow has exponent zero and is included without discounting. Do not add it again after XNPV. A positive result means discounted inflows exceed discounted outflows under this chosen rate and sign convention, not that a project is guaranteed to succeed.

Enter =XNPV(E2;B2:B4;A2:A4) in E4. The independent target is 79.385204561842.

To audit it, put =A2-$A$2 in C2 and fill down: expected day offsets are 0, 182, and 365. Put =B2/(1+$E$2)^(C2/365) in D2 and fill down. =SUM(D2:D4) reproduces the independent XNPV target. Use real date cells and retain unrounded discounted amounts.

Setting E2 to zero removes discounting, giving 150. Swapping rows 3 and 4 while keeping each amount paired with its date leaves the mathematical answer unchanged. Do not move the first reference row when making that check.

For an outflow of 1000 on 2028-01-01 and receipt of 1100 on 2029-01-01 at 10%, the interval is 366 days. The independent value is:

-1000 + 1100/(1.10)^(366/365)

That is approximately −0.261089690439, rather than zero. The extra actual day changes the exponent while the year denominator stays 365. This distinction matters when comparing a calendar-year promise with this function’s fixed-day discount convention.

Use the mixed-sign, first-date-earliest schedule described by Collabora Help for portable workbooks. The pinned co-26.04 implementation, however, explicitly checks only equal flattened array lengths and at least two entries before summing and rejecting nonfinite results. It does not itself scan for both signs or reject a date earlier than the first.

Consequently, this guide does not claim that all-positive flows or earlier dates must produce an error in that source snapshot. They may produce a mathematical value outside the Help’s stated input contract. Treat that as a documentation/source distinction, not an invitation to depend on undocumented behavior across products or versions.

The source receives dates as integer day serials. Avoid date-time fractions, ambiguous date strings, blanks, text amounts, and range-shape coercions; conversion behavior at the interface has not been native-tested here.

Unequal entry counts and fewer than two entries are rejected in the pinned implementation. Use Rate greater than −100% for a real-valued discount factor with arbitrary dated exponents. At −100% or below, division by zero or invalid powers can occur; the source rejects nonfinite output, but it does not contain a universal simple Rate≤−100% guard. Do not rely on special cases that happen to yield a finite value.

There is no currency conversion, inflation adjustment, risk correction, or rate curve built in. Use one currency and consistent nominal/real assumptions. Exact localized error displays and extreme-rate numerical accuracy remain unverified in the application.

NPV uses periodic timing instead of this explicit date schedule. DURATION and MDURATION describe fixed-coupon bond timing and sensitivity under a different model.

Reviewed on 19 October 2026 against saved Collabora Office 26.04 Help retrieved on 17 October 2026 and the Collabora co-26.04 source snapshot at commit c1013fad0d38683630b48c01b6d5208b8d81181e. The repository is hosted under LibreOffice/core, but the evidence used here is the pinned Collabora branch snapshot, not a claim about another spreadsheet product. The Help URL marked latest can change. Fresh Help retrieval was unavailable during this review, so Help statements were checked against the saved first-party pages; the pinned implementation files were freshly retrieved and verified.

Examples are independently calculated expectations, not recorded Collabora output. Native Collabora and its GUI were unavailable for this review; no screenshots or application runs are claimed. The first release introducing this function and the last working binary version have not been established. Online, mobile, older releases, localized function names, and round-trip compatibility need separate verification. Formulas use English names and semicolon separators; use the separators configured in your installation.

• Collabora Help: XNPV — reviewed 19 October 2026.
• Pinned Collabora XNPV entry point and formula — reviewed 19 October 2026.