XNPV Function in ONLYOFFICE Spreadsheet Editor: Syntax and Examples

With cash flows −1000 on January 1, 2026 and 1100 on January 1, 2027, =XNPV(0.10,B2:B3,A2:A3) is expected to return approximately 0. The first cash flow is at the valuation date and is not discounted; the later cash flow is discounted for 365/365=1 year.

ONLYOFFICE-specific limitation: the inspected 10.0.0.169 implementation rejects rates less than or equal to zero and requires dates in nondecreasing order. Do not import another spreadsheet application’s XNPV assumptions without checking these differences.

=XNPV(rate, values, dates)

All three arguments are required. rate is an annual decimal discount rate, values contains monetary cash flows and dates contains corresponding numeric dates. The first value is evaluated at the first date; later terms use (date−first_date)/365 as their exponent. There is no optional day-count basis or guess argument.

The official help asks for both negative payments and positive receipts. The source does not actually enforce mixed signs, so it can also calculate present values for one-sided numeric series. Mixed signs remain appropriate for the complete investment example here; do not describe the help’s condition as an implemented universal error guard.

1. Put =DATE(2026,1,1) in A2 and =DATE(2027,1,1) in A3.
2. Enter -1000 in B2 and 1100 in B3.
3. Enter =XNPV(0.10,B2:B3,A2:A3) in D2.
4. Keep A and B paired whenever sorting or editing the table.

The independent sum is −1000+1100/1.1=0. Unlike NPV, the first entry is not automatically shifted to the end of period 1. Including the initial outlay directly in XNPV is appropriate because its date fixes time zero.

The zero-rate error is a software limitation; mathematically the undiscounted cash-flow sum would be 100. Do not confuse a mathematical control with the function’s accepted domain.

Change the two dates to January 1, 2024 and January 1, 2025. There are 366 actual days between them. At 10%, the expected expression is −1000+1100/1.1^(366/365)≈−0.261089690439, not zero. The exponent denominator stays 365 even when the dates span leap day.

The implementation compares each raw numeric date with the preceding one and rejects a decrease. Sort the entire date/cash-flow table by date, keeping rows intact. Equal dates are allowed by the ordering check: their cash flows share the same time weight. For example,−100 and 110 on the same starting date have an expected present value 10 at a positive rate.

Date fractions are floored for discount exponents, but the sequence check occurs on the original numeric date values. Use whole-day DATE inputs rather than assuming descending time stamps on the same day will necessarily be accepted.

Unequal value/date lengths, negative or out-of-range dates, a decreasing date sequence, and nonpositive rate follow numeric-error checks in the source. Range inputs are expected to contain numbers; a text label, blank or Boolean inside the range does not follow NPV’s “skip and compress” behavior. Keep headers outside the supplied ranges and use explicit zero cash flows where appropriate.

The first-element type check and later conversion checks have distinct error paths, so this guide does not promise one universal error code for every malformed range. Use complete equal-length numeric columns as shown. A positive-only series can return a number in the implementation even though the help describes mixed signs; a numeric result does not validate whether the chosen series represents a complete investment.

To assess a root from XIRR, XNPV is a useful residual check only when that root is in XNPV’s accepted positive-rate domain. For a zero or negative candidate rate, evaluate the dated discount sum separately rather than assuming XNPV will accept it.

Checked October 18, 2026 against the official XNPV help and official SDKJS source tagged v10.0.0.169. Desktop Editors 10.0 was released October 13, 2026. The original introduction version for this individual function is not established; current help does not mark it retired or deprecated.

All results in this guide are expected values from documentation, source inspection and independent arithmetic, not application observations. Neither the application nor its calculation engine was used to test these examples. The tag matches the installed version/build designation but does not establish identical behavior in every older release, mobile client, server deployment or imported workbook. Examples use English names and comma argument separators; adapt separators to editor settings. These fictional calculations explain a function, not an investment or borrowing recommendation.

• DATE
• PV