Fixed vs Float Swap Model
Version 1.0 · Excel workbook · bootstrapped USD SOFR OIS curve
A working model of the swap most life insurers actually trade: fixed against a forward-looking Term SOFR floating leg, bilateral, uncleared. It builds a discount curve from thirty-two market quotes, generates both leg schedules, values the trade, and then prices the one thing a generic swap calculator leaves out — what it costs to hold the trade you need instead of the trade you could have cleared. Twelve tabs, 5,188 live formulas, no macros, no protected sheets, nothing computed somewhere else and pasted in as a value.
The one number it is built around
Term SOFR is not compounded SOFR. It is a forward-looking term rate, it trades at a basis to the overnight index, and it is not among the indices enumerated at 17 CFR 50.4(a) — which is why a swap that references it was never inside the clearing requirement in the first place.
That basis is the number. Enter it on the Swap Terms tab and the Cleared vs Uncleared tab reports three things: the trade marked with it, the same schedule marked without it, and the difference. Add your own margin, funding and fee assumptions and it goes further — the all-in comparison, and the basis level at which the two structures cost the same.
On the shipped trade — receive fixed 4.055% against 3-month Term SOFR, $100m, four years left to run — a fifteen basis point term basis is worth $567,104, which is fifteen basis points on the float leg's discounted annuity and nothing more mysterious than that. The workbook checks that identity on its own face.
What is in it
A bootstrapped curve. Thirty-two USD SOFR OIS par quotes become a sixty-five node discount curve. Each node is solved in order from the quotes above it — one formula, filled down, no iteration and no circular reference. The market quotes seventeen annual tenors and the bootstrap needs fifty; every filled year is labelled Interpolated in its own column, so you can see which points are data and which are inference. Par rates are not treated as zero rates anywhere.
Two independent leg schedules. The fixed and float legs are generated separately from their own frequencies and day counts. There is no fixed-to-float period ratio in this file, which is the construct that quietly marks every row a fixed payment when somebody picks a combination the ratio logic does not cover. Both legs are generated backwards from maturity, so the final period ends exactly on the maturity roll and any stub lands at the front. No row reads the row above it.
Dates that respect the calendar. Modified Following, tested against weekends and a 2024–2080 Federal Reserve holiday list together, so a payment landing on a weekday holiday rolls like any other. The shipped trade contains one: 13 October 2025 is Columbus Day, and the schedule rolls to the 14th.
Fixings that are required, not invented. Any float period whose accrual starts before the value date has already set. The model marks it Fixed, looks the rate up in the fixings table, and refuses to project one. If a fixing is missing the valuation says which. Every other period is projected as the forward over its own accrual window — start to end, not the next period's.
A valuation with eleven structural checks. Both legs end together and on the maturity roll; payment dates strictly increase; each accrual period starts where the last one ended; the schedule fits the rows built for it; the mark reconciles against spread times PV01. No principal exchange anywhere, because an interest rate swap does not have one.
The cleared-versus-uncleared comparison. Margin, funding spread, clearing fees and bilateral credit charges, each with a Source column beside it, because a number without a provenance is an assumption in name only. Underneath, the all-in difference across a strip of basis levels from zero to twenty-five basis points, and the breakeven.
A how-to-test tab. Every cell you may change, and nine numbered experiments with the answers you should get.
What it does not do
One curve for discounting and projection. No convexity adjustment, no turn-of-year effects, no cross-currency basis, no CSA discounting for a non-cash or non-USD collateral schedule. Vanilla fixed-versus-float only: no amortising or accreting notionals, no caps, floors or collars, no cancellables, no compounded-in-arrears float leg, no cross-currency swaps. Mid-market pricing throughout, so bid-offer is yours to add.
The comparison tab prices costs, not risks. A cleared SOFR swap leaves the difference between compounded SOFR and the Term SOFR your liability references sitting on your book. That is a real exposure and it appears on no line of the comparison. The tab says so in its own words.
Verification
Every output was checked against an independent Python implementation before release: all sixty-five curve nodes, both leg schedules, every accrual fraction, every projected forward, every discount factor and every present value — 419 cell-by-cell comparisons, none of them sharing code with the workbook. All 5,188 formulas recalculate without error. The bootstrap reprices all thirty-two quoted instruments to a worst residual of three hundredths of a billionth of a basis point.
Three defects were found and fixed on the way, and each is worth naming.
The one-year node was left off the annual grid. Every longer OIS swap pays its first coupon there, so leaving it out meant the eighteen-month and two-year discount factors came out above the one-year — a curve implying negative forwards. The workbook's own monotonicity test caught it before anything downstream did.
Fixing that exposed a second. The first annual node then had no previous coupon date to accrue from, so its accrual came out as zero and its discount factor as exactly 1.0. A discount factor of one at a one-year point is not a subtle error, but nothing on the page looked odd until the verification tab divided by it.
A verification formula that only worked in the wrong spreadsheet. The business-day test was written as NETWORKDAYS over a range inside SUMPRODUCT. LibreOffice — which the release harness runs — evaluates that happily. Excel does not accept array arguments there and returns #VALUE!. So the release check passed a formula Excel would never evaluate, which is worse than having no check at all, because it is the cell you stop looking at. It is now a per-node column, counted, and every other formula in the file was swept for the same pattern.
Corrections are recorded on the changelog tab inside the file, and the Read Me carries the version number — check it against the version at the top of this page to see whether your copy is current. The form asks for an email so I know who is using the workbook. There is no mailing list.
Goes with The Clearing Exemption You Don't Have.
