INTRATE Function (LibreOffice Calc)

=INTRATE(Settlement; Maturity; Investment; Redemption [; Basis])

The official Calc Help [S1] and OpenDocument 1.4 OpenFormula §6.12.22 [S3] agree on the four required parameters and one optional day-count basis, which defaults to 0 when omitted. The English Calc function name and OpenFormula function name are both INTRATE. Semicolons are the argument separators in the documented/tested English Calc setup; your localized installation may display other separator and decimal conventions. Use real date values, preferably DATE(year;month;day), instead of ambiguous typed date strings.

Day-count basis is part of the financial definition, not a formatting option [S1, S3].

Important: the numerator and year fraction vary when you change Basis. Selecting a different basis can legitimately change the quoted rate for exactly the same two payments. Do not choose a basis merely because its reported percentage looks better; use the convention specified in the security’s terms. For a plain 90-day, mid-month investment, bases 0/2/4 all produce the same quarter year, but that does not make them interchangeable for every date span.

OpenFormula specifies [S3]:

Annualized simple return = ((Redemption − Investment) / Investment)
                           ÷ YEARFRAC(Settlement; Maturity; Basis)

YEARFRAC is the fraction of a year according to the selected day-count basis. The investment return (Redemption − Investment) / Investment is not automatically compounded. For a holding period lasting several years, INTRATE divides its total fractional gain by a year fraction; an annual compound-growth calculation would instead raise the wealth ratio to the power 1 / years and subtract 1. These quantities differ increasingly as the time span and rate grow.

Example: an investment doubles from 1,000 to 2,000 over two 365-day years on an Actual/365 basis. Its simple annualized return is 100% / 2 = 50%, whereas the effective compound rate is SQRT(2)-1, about 41.42%. This illustration describes two mathematical conventions rather than two interchangeable investment quotations. If you need the return on irregular dated multiple cash flows, see the separate XIRR guide; do not try to make INTRATE model deposits or withdrawals [I1].

In a blank Calc worksheet enter the following values. Date formulas ensure genuine numeric dates, and using currency-format cells does not change the arithmetic.

Expected: B8 = 0.20 (20%); B9 = 0.25; B10 = 0.20; B11 = 50. The companion native worksheet explicitly ran the related guarded interest formula (B19), with result 0.20 [N2]. The expected financial result was additionally derived using Python Decimal and the independently calculated 90 calendar days, while the YEARFRAC check is a separate Calc formula, not independent day-count evidence [I1, N1].

Changing B5 to 950 produces -0.20 (a −20% simple annualized loss) for those dates and basis; this was also observed natively. Changing B5 to 1000 returns 0 (no gain), not a missing-value error [N1]. Format B8 and B10 as percent to display percentage rather than a decimal fraction. If your sheet reports 0.2 as 20, check its number format before interpreting a rate.

LibreOffice documents [S1]:

=INTRATE(DATE(1990;1;15);DATE(2002;5;5);1000000;2000000;3)

Native Calc 25.2.3.2 result: 0.0812374805252615, or 8.12374805252615% (approximately the 8.12% quoted in Help). For this Actual/365 basis the day count is 4,493 days, making the time fraction 4493/365 ≈ 12.309589 and the total gain 100%. The independent Decimal benchmark matched the native result within recorded tolerance [S1, I1, N1]. The unusually long maturity span demonstrates why a simple annualized yield need not be the same as a compounded long-term return.

Use the same January 15–April 15, 2026 investment (1000 paid; 1050 redeemed), with different bases. The following values were calculated independently and checked in Calc 25.2.3.2 [I1, N1].

The basis-1 formula for this example sits within a single non-leap year, so no multi-year Actual/Actual interpretation is necessary. Do not extrapolate this hand-computed fraction to periods spanning multiple calendar years: use the official convention or Calc’s own YEARFRAC with the required basis [S3].

Compare two quoted securities consistently. With separate investment and maturity inputs for each security, use the same contractual day-count convention for both before comparing simple annualized yields. A higher INTRATE is not automatically a better buy if the instruments differ in credit risk, liquidity, taxes, early-redemption terms or timing of interim cash flows; this function models only a single purchase and a final payout.

Build a safer worksheet. Suppose B2:B6 contain the five inputs from the worked example. This validation formula explicitly rejects reversed dates, nonpositive investment amounts, out-of-range numeric bases, and fractional basis entries:

=IF(AND(B3>B2;B4>0;B6>=0;B6<=4;B6=INT(B6));INTRATE(B2;B3;B4;B5;B6);NA())

The guard is a recommended spreadsheet check, not a claim that every test in the underlying function reports identical errors. If any dates/amounts are text, also confirm they are valid numeric cell values rather than date-like strings. A security that promises a guaranteed positive redemption can use an additional B5>0 guard as a separate contractual validation. Adjust it if the security structure allows negative outcomes; the function itself is a calculation, not a product suitability validator.

Compare direct calculation with a formula:

=IFERROR(INTRATE(B2;B3;B4;B5;B6);NA())

This catches calculation errors but does not make an invalid financial transaction valid; always perform domain checks first. For an optional/blank basis, either omit the last argument or explicitly use 0. The sample uses an explicit basis to reduce accidental comparison across conventions.

Diagnostic caution: native errors and fractional-basis coercion are version-specific observations, not general requirements of every Calc implementation. The official basis requirements should determine user-facing advice. The native test suite preserved all raw results and the actual workbook; supplemental worksheet checks confirm the guarded valid-case result [N2]. Neither establishes cross-platform consistency [N1, N2].

• LibreOffice Calc: The function is explicitly documented in current English online Help [S1]. Native execution was checked only on Linux with Calc 25.2.3.2, including the official Help example and 90-day basis comparisons [N1]. This is not a test of Calc 26.8 or another OS.
• OpenDocument/OpenFormula: The function is specified as INTRATE in OpenDocument 1.4, §6.12.22, with Basis=0 as the documented default [S3]. The standard’s presence does not independently prove every older LibreOffice release supports identical edge behavior.
• Other spreadsheet products and XLSX: Function-name similarity is not a portable-behavior guarantee. Date serials, day-count boundaries, fractional-basis coercions, return formats, and unsupported variants may differ. No Excel, Google Sheets, Gnumeric or other product was natively tested for this guide. Recalculate sample cases after importing into a different application.
• Locale: English names, semicolons and period decimals are shown as tested. Localized function names and separators can change; do not rewrite the function to Excel syntax merely because an imported example uses commas [S1].

Related functions include YEARFRAC, RECEIVED, DISC, PRICEDISC, YIELDDISC, and XIRR. YEARFRAC supplies the year fraction, RECEIVED computes maturity proceeds from an investment and a discount rate, and XIRR is for dated streams of cash flows. DISC, PRICEDISC, and YIELDDISC cover related discounted-security quotations but are not aliases for INTRATE.