XNPV Function (Gnumeric)

=XNPV(rate,values,dates)

For numeric date serials dt, first date d0, and cash flows Ct, the source’s mathematical expression is XNPV(r) = Σ Ct/(1+r)^((dt-d0)/365). A constant 365-day denominator is used even when the calendar interval crosses a leap day. This is a source-derived implementation detail, not a claim that all spreadsheets use this convention. Gnumeric C implementation, lines 1492–1525.

The first cash flow is not shifted forward or discounted by a whole year when its date equals the base date; its exponent is zero. For conventional real-valued calculations with fractional exponents, use a discount rate greater than −100%.

Enter the cash flows in A2:A6 as numbers (not currency strings). In B2:B6, enter the indicated DATE formulas as actual date values and format them as dates. A negative value is an outgoing investment, a positive value is a receipt. These example cash flows and dates are illustrative, not investment advice.

In D2 enter 10% as the annual discount rate. Then:

=XNPV(D2,A2:A6,B2:B6)

Independently calculated expected result: 373.8358607868 (about 373.84 formatted to two decimals). The contributions are:

-1000/(1.1)^0 + 300/(1.1)^(59/365) + 350/(1.1)^(181/365) + 400/(1.1)^(365/365) + 450/(1.1)^(638/365).

The date offsets shown in the table were independently obtained from real calendar dates (not from the worksheet application). The result is positive under the assumed 10% annual rate; it is not a prediction that the flows will occur.

Contrast with periodic NPV: =A2+NPV(D2,A3:A6) returns approximately 169.8654463493 when the four future receipts are assumed to occur at the ends of four equally spaced annual periods. That is not the timeline in column B. This difference is expected and demonstrates why actual dates matter, not a cross-application bug.

Place annual hurdle rates 5%, 10%, 15% in D5:D7 and fill down:

=XNPV(D5,$A$2:$A$6,$B$2:$B$6)

This produces a date-aware NPV sensitivity table; all dates/values ranges are fixed while the rate varies. The same dataset has an independently calculated annualized XIRR of approximately 60.6557705%. If you enter:

=XNPV(XIRR(A2:A6,B2:B6),A2:A6,B2:B6)

Expected: approximately zero, subject to numerical solver tolerance. A rate close to 60.66% is a mathematical result of the short-dated example cash flows, not a promised or recommended yield.

• #NUM! for different usable collection sizes: the upstream gnumeric_xnpv explicitly checks independently collected payment and date counts and returns a numeric error if they differ (source). Use two equal-length ranges with a one-to-one entry in every row.
• IMPORTANT implementation limitation (blank pairing): the upstream code contains a FIXME that values and dates should be collected as pairs to preserve alignment when data is missing; it currently collects them separately. Therefore do not leave gaps in either column or rely on blanks being skipped in sync; validate each pair yourself. This caution is specific to Gnumeric’s inspected source (lines 1502–1514).
• Unexpected result: verify that cells in B are actual date values rather than imported text and that each amount belongs to its stated date; verify chronological sequence, including the first date as the intended present-value anchor.
• rate wrong units: this argument is an annual effective rate. Entering a periodic monthly rate into a dated annual-rate formula misstates the discounting. Use a clearly labelled annual rate.
• Invalid arithmetic: a rate at or below −100% can produce a division-by-zero/nonreal power with fractional exponents; do not assume an undocumented uniform error code for all such inputs.
• Duplicated/same-day transactions: the date-exponent formula allows the mathematical possibility of more than one flow on the same date; represent every amount/date pair explicitly, or aggregate them at that date after documenting the transformation.
• False type parameter note: the generated Gnumeric Finance manual includes a copied payment-type note directly under XNPV, but its documented synopsis and C implementation have only three arguments, no type. Do not add a fourth argument based on that stray note (manual; source).

NPV discounts equal periods, while this function discounts actual dates; XIRR finds the annual rate that zeros this dated cash-flow NPV; IRR solves the equally spaced counterpart; DATE constructs reproducible date cells.

This tutorial is for Gnumeric 1.12, checked against the official Finance manual and public function catalogue on 13 October 2026, with 1.12.62 the documented stable release announced in September 2026. The catalogue marks this named function Complete / Basic (registered implementation and a basic upstream test classification). Those are not proofs that this particular worksheet has been executed, nor a blanket guarantee of Excel, LibreOffice or another spreadsheet’s parsing, coercion or file-import behavior. The Finance manual identifies purpose and argument order. Implementation claims were independently checked against plugins/fn-financial/functions.c in the checksum-verified official Gnumeric 1.12.62 source archive on 13 October 2026. Source inspection is not native application execution.

Examples use commas as argument separators, a period for decimals, and percent input as shown in the English Gnumeric documentation. Gnumeric’s entry/display conventions may vary by locale; if your configured formula editor expects different separators, adapt the punctuation without reordering arguments. Verify any workbook imported from another program in Gnumeric before relying on financial decisions.

Verification boundary: All numerical results below were independently calculated outside Gnumeric; no native Gnumeric execution or screenshots were performed.

All pages and source claims below were checked on 2026-10-13.

1. Gnumeric 1.12 Finance function reference — specific section for XNPV, defining syntax, order, parameters and purpose.
2. Gnumeric official function catalogue — XNPV: Complete / Basic, an upstream registration/testing classification rather than live worksheet validation.
3. GNOME Gnumeric source, financial functions — implementation details from plugins/fn-financial/functions.c in the checksum-verified official 1.12.62 source archive.
4. Gnumeric 1.12.62 official release notice — current 1.12-series release context as of 2026-10-13.