Bond and treasury functions after a migration: the finance-desk checklist

Fixed-income sheets are the worst possible thing to migrate on faith. The formulas are short, the inputs are dates, and every answer is a plausible-looking number — there is no shape to a wrong bond price that tells you it is wrong. So we executed the whole bond and treasury family: 26 functions, 161 test cases, in LibreOffice Calc on four pinned builds and in Google Sheets by Drive import, all on 2026-08-31.

The headline is not close. In LibreOffice, 64 of the 161 cases matched the documented result. Of the 97 that did not, 83 are the same error-code substitution (a documented #NUM! arriving as #VALUE!), and the remaining 14 are real: two functions that compute nothing at all, one that ignores its own documented domain rule, and three that return a wrong number with no error. In Google Sheets, 127 of the 161 matched; 28 of the 34 misses are the four ODD* functions, which Google does not have.

The family at a glance

The Excel column throughout this guide is Microsoft’s documented behaviour as recorded in our test corpus — it is the yardstick, and we do not run Excel. The Google Sheets column is executed output from the dated Drive-import run of 2026-08-31. The LibreOffice column is Calc 25.8.7.3, and every row below returned the identical result on 24.2.0.3, 24.8.7.2 and 25.2.0.3.

Read the LibreOffice column carefully before drawing a conclusion from it. “Error codes only” means every value the function returns is right and only the failure codes differ; those functions are safe to migrate as long as nothing branches on the code. The five rows in bold are the ones that change money.

1. ODDFPRICE and ODDFYIELD compute nothing in LibreOffice

The odd-first-coupon pair is the one place in this family where LibreOffice does not disagree with Excel — it declines to answer at all.

The bond in the ODDFPRICE rows is Microsoft’s published example — settled 2008-11-11, maturing 2021-03-01, issued 2008-10-15, first coupon 2009-03-01, 7.85% coupon, 6.25% yield, redemption 100, semiannual, actual/actual — for which Microsoft prints “$ 113.60”. Our corpus does not take that figure on trust: it re-derives the price from the odd-short-first-coupon formula in exact rational day counts at 50 digits and gets 113.5977174740789…, which is 113.60 at Microsoft’s two printed decimals, and asserts the eight-decimal form so that a quasi-coupon schedule off by a single day would fail.

The reason nothing computes is in LibreOffice’s source, and it is unusually explicit. The argument validation in front of the two functions is real — negative rate, invalid frequency and date ordering are all checked — but every path that survives validation ends in the same exception, which is also why the error cases come back #VALUE! rather than the documented #NUM!. Our corpus probed this from several directions before recording it: the plain spelling parses (so this is not a storage-token problem — _xlfn.ODDFPRICE, COM.MICROSOFT.ODDFPRICE and ORG.OPENOFFICE.ODDFPRICE are all #NAME?), the same #VALUE! appears when LibreOffice parses the formula natively rather than reading it from OOXML, and five bases, three frequencies, settlement before and after the first coupon, dates as serials and as DATE() calls and as text, and a bond whose first period is deliberately regular all produce it. Not one input produced a number.

In Google Sheets the same four ODD* functions are simply absent: all 28 cases return #NAME?. That is the honest verdict rather than a defect — Google does not document these functions — but for a migration it has the same consequence.

2. TBILLPRICE does not enforce the one-year rule, and its permissiveness is not monotonic

Microsoft’s Remarks are unambiguous: “If settlement > maturity, or if maturity is more than one year after settlement, TBILLPRICE returns the #NUM! error value.” A treasury bill that matures more than a year out is not a treasury bill.

So the two siblings reject the out-of-range bill (with the wrong code, but they reject it) and TBILLPRICE quietly prices it. Our test file records the probe that establishes the shape of this: on LibreOffice 25.8.7.3, with the same settlement and maturities 365, 366, 370, 400, 500 and 730 days out, TBILLPRICE returned a price for every one of them — 90.975, 90.95, 90.85, 90.1, 87.65 and 81.975, a two-year bill priced without complaint — while TBILLEQ and TBILLYIELD rejected all six.

And the boundary behaves backwards. At 364 days — a maturity comfortably inside the documented range — the same probe found #VALUE!, while 365 days and beyond return a price. The one input near the boundary that TBILLPRICE rejects is the legal one.

The prices it invents do not follow the documented formula either. Microsoft’s TBILLPRICE is 100 × (1 - discount × DSM/360) with DSM the days from settlement to maturity, and the executed 98.45 for the documented 62-day bill confirms that LibreOffice uses the actual day count there (62 actual days give exactly 98.45; the 30/360 count of 61 would give 98.475). But 90.1 at a 400-day maturity implies DSM = 396, which is neither the actual count (400) nor the US 30/360 count (395). Whatever the out-of-range branch is computing, it is not the documented function.

3. INTRATE’s default basis disagrees with LibreOffice’s own day counter

This is the quietest defect in the family and the most likely one to be in your workbook, because it fires on the shortest correct formula: the one that leaves basis off.

The security is Microsoft’s own example: settlement 2008-02-15, maturity 2008-05-15, investment 1,000,000, redemption 1,014,420, and the page spells the underlying figure out in its Description column as “0.05768 or 5.77%”. Our corpus derives it rather than copying it. Microsoft states the formula in full — ((redemption - investment) / investment) × (B / DIM) — and on basis 2 (actual/360) DIM is the actual count 90 and B is 360, so the rate is 0.01442 × 4 = 721/12500 = 0.05768 exactly, a terminating decimal with no rounding anywhere.

The basis-0 rows are the point. Under US 30/360 the days from 2008-02-15 to 2008-05-15 are (5−2)×30 + (15−15) = 90 — the same 90 as the actual count, because the two dates share a day of the month and neither is the 31st. So basis 0 and basis 2 are forced to give the same answer for this security, and any engine that returns something else has a day-count bug and nothing else can explain it.

Work backwards through the documented formula and 0.01442 × 360/DIM = 0.0583280899 puts LibreOffice’s DIM at 89. It is losing one day. What makes this a self-inconsistency rather than a defensible reading of the standard is that the same engine already counts these two dates correctly elsewhere: our test file records that LibreOffice’s own DAYS360 returns 90 for this date pair on all four builds, and its YEARFRAC(…,0) returns 0.25. One 30/360 code path out of several disagrees with the others, and it is the one INTRATE uses by default. The error is 1.1% of the rate, on a function whose entire job is to produce a rate, with no error and no warning.

4. MDURATION and DURATION are wrong on actual/actual — and right on 30/360

Microsoft’s MDURATION example is a bond settled 2008-01-01, maturing 2016-01-01, 8% coupon, 9% yield, semiannual, basis 1, and it publishes 5.736. Our corpus recomputes it from the definition at 50 digits over the sixteen individual discounted cash flows — not from a closed-form annuity shortcut — giving a price of 94.382992475446753, a Macaulay duration of 5.9937749555451836 years and a modified duration of 5.7356698139188359. That is 5.736 at Microsoft’s three printed decimals: the published figure, reproduced.

The basis-0 row is what turns this from an opinion into a finding. In this example the settlement date falls exactly on a coupon date, so the only place the basis argument enters the calculation — the ratio of days from settlement to the next coupon over days in that coupon period — is 1 under both rulers: 180 of 180 under 30/360, 182 of 182 under actual/actual. The two bases cannot legitimately disagree here, and LibreOffice’s own basis-0 answer is exactly the derived value. So one of its two day-count paths is wrong, and the pair of cases says which one.

The same split appears in DURATION on the same bond, which is where the defect actually lives: our test file records LibreOffice returning a Macaulay duration of 5.991950138 on basis 1 against 5.9937749555 on basis 0 for the MDURATION bond, so the error is in the shared coupon-period code rather than in MDURATION’s final division by (1 + yld/frequency). The visible damage is 0.0017 years, about 0.03% — small enough to survive a review, large enough to move a hedge ratio.

5. ACCRINT’s eight-argument examples do not run

Microsoft’s ACCRINT page carries three worked examples. The seven-argument one matches everywhere. The two that pass the optional eighth argument (the calc_method flag, with a DATE() settlement) do not run in LibreOffice at all:

Both published figures re-derive cleanly on the documented 30/360 basis: 2008-03-05 to 2008-05-01 is 56 days, so 1000 × 0.1 × 56/360 = 15.555556, and 2008-04-05 to 2008-05-01 is 26 days, giving 7.2222222. Google Sheets returns both. We are deliberately not naming a cause: the two failing cases change two things at once relative to the passing one — the eighth argument and a DATE() settlement instead of a cell reference — and our corpus has no probe that isolates them. What is executed and reportable is that the two documented eight-argument forms return #VALUE! on all four builds.

6. The error codes: 83 substitutions out of 84

This is the bulk of the divergence and the easiest part to reason about. Across the 26 functions, 84 executed cases expect #NUM! — a negative rate, an out-of-range basis, settlement after maturity, a zero price, an invalid frequency. LibreOffice returns #VALUE! on 83 of them. The single exception is TBILLPRICE’s over-a-year case from section 2, which returns a price instead of any error at all.

Google Sheets returns the documented #NUM! on every one of the 84, in every function that it implements.

What this breaks is narrow but real. IFERROR, ISERROR and ISERR are unaffected, because they catch every code — if your desk wraps its pricing calls in IFERROR and shows a dash, nothing here changes behaviour. What stops firing is any branch that distinguishes kinds of failure: =IF(ERROR.TYPE(x)=6,"bad inputs",x) tests for #NUM!, code 6, and receives #VALUE!, code 3. That distinction is worth something on a bond desk — “this security is outside the model’s domain” is a different alert from “this cell is not a date” — and it is the distinction that does not survive. The pattern is not specific to fixed income; the corpus-wide count and the exceptions that run the other way are in every error code LibreOffice reports as #VALUE!.

One boundary is worth noting: LibreOffice is consistent about #VALUE! for a malformed date, which is what Microsoft documents for that case too. Every “not a date” case in the family matches in all three columns. It is only the out-of-domain cases that collapse onto the wrong code.

A note on the documentation itself

Two of the figures in this family are not reproducible from the vendor’s own inputs, and it is worth saying which so that nobody chases an engine for them.

DISC’s Microsoft example uses settlement 2018-07-01, maturity 2048-01-01, price 97.975, redemption 100 and basis 1, and prints the result 0.001038. That figure does not belong to those inputs. From the formula the page itself states — ((redemption - pr)/redemption) × (B/DSM) — the discount rate is 0.000686384169…, and the printed 0.001038 is reproducible only by moving the maturity back ten years to 2038-01-01. It is a stale figure left behind when the example’s dates were rolled forward. Our corpus asserts the derived value, and all four LibreOffice builds return 0.000686384169, matching to full double precision. Google Sheets returned 0.000686345065 on 2026-08-31 — close, but a genuinely different number, which puts a small actual/actual year-length difference between the two executed engines on the same bond.

That is one of a number of published figures in this corpus that do not reproduce from their own pages; the full set, with the derivations, is in when the documentation is wrong.

The finance-desk migration checklist

Grep for ODDFPRICE and ODDFYIELD first. These are the only two functions in the family that cannot be made to work in either target engine. If either appears anywhere in the workbook, that formula needs replacing before anything else is decided — with PRICE/YIELD if the first period turns out to be regular after all, or with an explicit cash-flow model built out of COUPNCD, COUPDAYS and discounting, or with a source outside the spreadsheet.

Then grep for every bare INTRATE( and RECEIVED( with four arguments. A missing basis is the highest-frequency, lowest-visibility exposure on this page: the formula looks right, the answer looks right, and it is off by about 1%. Adding an explicit basis does not fix it — explicit basis 0 gives the same wrong answer — so the fix is to state a basis LibreOffice gets right (bases 2 and 4 both reproduce the documented figure on our example) or to recompute the rate from the day count yourself.

Re-run every duration on basis 0 as a control. If a duration changes when you flip basis 1 to basis 0 on a bond whose settlement falls on a coupon date, you are looking at the defect in section 4, not at a modelling choice.

Audit the guards, not the prices. Search the neighbourhood of every bond function for ERROR.TYPE, ISNA(, IFNA( and ISERR(. Those are the formulas whose meaning changes under the 83 substitutions, and they are usually the ones driving an alert or a suppression rule.

Validate the treasury-bill tenor before the call. Because TBILLPRICE will price a two-year bill, the domain check has to live in your sheet rather than in the function. Something in the shape of =IF(maturity-settlement>365,"not a bill",TBILLPRICE(settlement,maturity,discount)) gives the same answer in every engine. That is a construction we are describing from the documented and executed behaviour above, not a case we ran — treat it as a suggested shape, not as a measured result.

Do not assume Google Sheets is the safe target either. It matched 127 of 161, which is much better than LibreOffice — but 28 of its 34 misses are the entire ODD* family being absent, and it is the engine that disagreed on DISC.

Honest limits

The Excel column throughout, including every published worked example, every documented #NUM! condition and every basis-table reading, is Microsoft’s documented behaviour as recorded in our test corpus. We do not run Excel, and no value in that column is a measurement. Where our corpus asserts a figure Microsoft also prints, the figure was independently re-derived first — in exact rational day counts and 50-digit arithmetic, from a clean-room implementation of the OpenFormula day-count procedures written for this corpus and used by no engine — and the derivation is what is asserted.

The Google Sheets column is executed output from a dated run, because Sheets has no version number to pin: a formula-only .xlsx carrying no cached results goes to Google Drive, is recalculated by Google’s engine, and comes back as .xlsx for readback, with a deterministic =1111+2222 canary in every sheet proving the recalculation happened. Every case in this guide ran on 2026-08-31. Sheets’ .xlsx export rounds floats to ten significant digits, which is why five full-precision rows in the family (in PRICE, PRICEDISC, PRICEMAT, RECEIVED and YIELDDISC) are counted as misses above even though the difference is in the readback rather than in the engine; the DISC disagreement is not one of those and is a real difference.

The LibreOffice column is executed output from Calc 25.8.7.3, and all 161 cases returned identical results on 24.2.0.3, 24.8.7.2 and 25.2.0.3. Those four builds are the only ones we tested. The source citation for the ODDFPRICE/ODDFYIELD stubs is a reading of LibreOffice’s published source, which explains the executed result but is not itself an executed result. The 364-day TBILLPRICE boundary and the multi-maturity price list in section 2, and the DAYS360, YEARFRAC and DURATION cross-checks in sections 3 and 4, come from probes recorded in the test files rather than from published corpus cases — they are reproducible the same way, but they are not rows in the tables above.

Scope. 161 cases across 26 functions is the whole fixed-income corpus, and it is built around each vendor’s documented examples plus their documented exclusions. Untested here: negative yields and negative prices beyond the documented error rows, bases 3 (actual/365) and 4 (European 30/360) on most functions, frequency 4, settlement dates inside a stub period other than the published ones, leap-day settlements, and the whole of AMORDEGRC/AMORLINC and the depreciation family, which are a separate story. See our methodology for how recalculation is proven rather than assumed, and the function pages for ODDFPRICE, TBILLPRICE, INTRATE and MDURATION for the per-engine support matrices.

Check before you migrate