XIRR in WPS Spreadsheet: Annualized Roots for Dated Cash Flows

=XIRR(values, dates, [guess])

Values and dates are corresponding ranges. WPS requires at least one positive and one negative cash flow and documents omitted guess as 0.1. The original controls supply an explicit 0.1 guess, an initial negative flow followed by positive receipts, and increasing modern dates with the earliest first.

The lesson’s date description says dates may be in any order, while its errors prohibit dates earlier than the starting date. This guide avoids that ambiguity by ordering every example chronologically. Construct dates with DATE; do not depend on locale-sensitive date strings or historic serial origins.

Enter −100 and 110 in A2:A3. Put =DATE(2025,1,1) in B2 and =DATE(2026,1,1) in B3. Use =XIRR(A2:A3,B2:B3,0.1).

There are 365 days between the flows. The mathematical root is 0.1, since −100 + 110/(1.1) = 0. The expected rate is an independently verified root; it is not a recorded WPS solver result.

For two flows with outlay magnitude C, receipt R and positive day difference d, the independent root is (R/C)^(365/d) − 1. This follows by rearranging the documented zero-value equation.

The first three pairs all gain 10% between their actual dates. Their annualized rates differ because the durations differ. The 366-day example has an exponent 365/366 in the closed-form root, while the 182-day example has 365/182. Neither is a promise that the same short-term return can be repeated.

For a candidate r, compute the sum of each cash flow divided by (1+r)^(days_from_first/365). The residual should be near zero at a valid numerical root, allowing for cash-flow scale and rounding. In the three-flow example, −210+121/1.1+121/1.21 equals zero exactly in rational arithmetic.

Scaling all cash flows by two leaves a mathematical root unchanged, although it scales any residual. Thus a fixed absolute monetary residual threshold may not be appropriate for every dataset. The formula establishes a zero-value rate; choosing which financial assumptions are useful remains a separate decision.

The WPS lesson describes iteration from guess, a precision statement of 0.000001%, and #NUM! after 100 unsuccessful iterations. These are historical documented limits, not a universal convergence or accuracy guarantee. Its displayed example has several sign changes and a result 3.752779705; that example does not establish that every such timeline has one unique root.

An explicit guess is a starting value, not a cash flow or an assumed reinvestment rate. Multiple sign changes can yield multiple mathematical roots. A successful result, a different root from another guess, or an error should be interpreted with a separately checked equation. No original example here predicts how WPS selects among multiple roots.

The lesson documents #NUM! for missing opposite signs or mismatched range counts and #VALUE! for invalid dates. Original controls avoid these errors, time fractions and ordering ambiguities. See NPV for periodic net-value analysis.

The official WPS lesson is dated 2019-06-03 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. Solver examples identify mathematical roots and do not certify application convergence or displayed precision. These fictional financial calculations explain a function, not the suitability of a financial product.