Cap Ladder Hedge Analyser
Version 1.0 · Excel workbook · Black-Scholes-Merton
A calculator for the decision most indexed annuity programs make once and never revisit: how many different cap rates to put inside a single hedge. Enter your own ladder — up to eight rungs, each with a cap and a weight — and the workbook prices what that structure costs you at expiry, on the path, and under each of the three ways desks size the hedge. Nine tabs, 579 live formulas, no macros, no protected sheets, nothing computed somewhere else and pasted in as a value.
The one number it is built around
Every mismatch in this workbook is driven by a single quantity: the dispersion of caps inside one hedge. Not the number of policies, not the number of rungs, not how aggressively you aggregate.
The fastest way to see it is to break the workbook. Set every rung to the same cap and watch the dispersion figure go to zero, the net gamma go to zero, all three terminal gaps go to zero, the twelve-month drift path go flat, and the three sizing conventions collapse onto the same hedge — with as many rungs and as much notional as you like. Then widen the caps by half a point and watch every one of those numbers come back.
That is the argument, and the workbook is built so you can falsify it in about thirty seconds.
What is in it
A payoff ladder. Both sides at the crediting date, across sixteen index outcomes from −20% to +30%, with each rung's weighted contribution shown separately. The liability carries a cap above and a 0% floor below, as an indexed annuity credit actually does. Three gap columns, one per sizing convention, and a worst-case row underneath. The notional-sized column is never negative, for any ladder you can enter — that is the concavity result, and you can spend a while trying to break it.
A gamma tab. Where gamma actually peaks — at S·exp((r−q+σ²⁄2)T), which sits above spot in most conditions and is why an at-the-money call spread is short gamma from the day you buy it. Each rung priced separately with its own delta, gamma and gamma sign-change level, then aggregated, then compared to a single hedge at the average cap. It ends on the net gamma and a plain-language verdict on which way your book will drift.
A dispersion gap tab. The same inequality applied twice: to the capped credit, where it tells you the hedge over-covers at expiry, and to gamma, where it tells you the hedge sheds delta faster on the path. Two mismatches, opposite signs, one cause. Underneath, a sensitivity block that holds your average cap fixed and widens the ladder step by step, so you can see the gap grow while the average stays put.
A sizing tab. Notional, delta and value sizing side by side. For each: the hedge size it implies, the delta gap at inception, the value gap at inception, and the worst terminal shortfall in both percentage and money terms. The rows are in different units and the tab says so rather than inviting a false comparison.
A drift path. Twelve months of a hedge sized to match delta at inception and then left alone. Monthly index move is an input, so you can run a rally, a selloff or a flat year and watch the sign of the drift follow it.
A how-to-test tab. Every cell you may change, listed by tab, and seven numbered experiments with the answers you should get.
What it does not do
One flat volatility, not a surface. European exercise. A single point-to-point crediting method with a cap and a zero floor — no participation rates, no averaging, no spread or margin designs, no multi-year or ratchet structures. No policyholder behaviour at all: no surrenders, no partial withdrawals, no new business mid-term. No fees, no borrow, no counterparty or collateral modelling, no tax. Every price is mid-market, so the true-up overlay it implies you should buy is priced as though you could buy it at mid, which you cannot.
Verification
Every output was checked against an independent Python implementation before release: the inputs tab, all eight rungs' deltas, gammas and sign-change levels, all three sizing conventions, all sixteen payoff rows in every sizing column, and all twelve months of the drift path. All 579 formulas recalculate without error. The workbook was then re-run under nine different cap ladders — uniform, two-rung, eight-rung, asymmetric weights, and several deliberately extreme — with the concavity result asserted on every one.
Two defects were found and fixed in that process, and both are worth describing because neither would have looked wrong on the page.
Gamma came out with the wrong sign. The formula computed exp(−d₁²⁄2), or so it read. In Excel, unary minus binds more tightly than exponentiation, so -d1^2 evaluates as (-d1)^2 and the expression silently became exp(+d₁²⁄2). The resulting gammas were plausible in magnitude and wrong in sign, which is the worst combination — nothing on the tab looked odd. It was caught by comparison against an implementation that does not share Excel's precedence rules.
The liability was missing its floor. The hedge payoff was floored at zero and the liability was not, so in a down market the workbook showed policyholders being credited a negative return. On a −20% index it reported a twenty-point gap that does not exist. That is a modelling error rather than a formula error, and it surfaced only because the uniform-ladder test asserts that every mismatch must be exactly zero — and one of them was not.
A third error was in the documentation rather than the model. The how-to-test tab originally claimed that dropping the risk-free rate below the dividend yield pushes the gamma peak below spot. It does not: at 20% volatility the peak sits above spot even with rates well under the yield, because the variance term dominates. The experiment now says what actually happens and why, which is a better lesson than the wrong one was.
Corrections are recorded on the changelog tab inside the file, and the Read Me tab 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 Your Hedge Is Worth More and Does Less.
