Buffered Outcome Calculator
Version 1.1 · Excel workbook · Black-Scholes-Merton
A calculator for defined-outcome (buffered) ETFs, built for people who need to see the arithmetic rather than trust a fact sheet. Eight tabs, 396 live formulas, no macros, no protected sheets, nothing computed somewhere else and pasted in as a value. Every cell is auditable.
What is in it
The contract. The four options a buffered fund actually holds — a call struck at zero, a short call at the cap, a long put at the start level, a short put at the buffer floor — priced at the reset date across the full range of index outcomes. A replication check compares them against the published payoff and reads "exact", or tells you the model is wrong.
The mark. What the fund is worth today, beside what it would pay if today were the reset date, for any index level and any point in the outcome period. The gap between those two columns is the thing that generates the complaints, and it converges to zero on a schedule you can watch by changing one cell.
A cap solver. Enter a buffer and the workbook finds the cap that structure can fund, interpolating across a sixty-row funding table. Change volatility, rates, dividend yield or tenor and the cap moves. A skew approximation prices the short call at an offset to the put volatility, and follows the Inputs tab automatically rather than sitting on a hardcoded number.
An entry tab. How much of the advertised buffer a mid-period buyer actually owns. Enter your own purchase levels and read the NAV you would pay, your break-even index, and your return if the index finishes at the buffer floor.
A How To Test tab. Every cell you are allowed to change, listed by tab, and seven numbered experiments with the answers you should get. Change one input, read one cell, compare. If your number matches, you changed the right thing.
What it does not do
It does not model the wrapper — no fund fees, no bid-offer, no creation and redemption mechanics, no tracking error. It assumes a single flat volatility rather than a full surface, with a two-volatility skew approximation as the only concession. It does not price the accelerated, dual-directional or floor variants, which have different payoff geometry. And it is European-exercise throughout, which is right for the index options these funds hold and wrong for anything else.
Verification
Every output was checked against an independent Python implementation before release. The four-option replication matches the published payoff to twelve decimal places at every index level tested, and eight input sets were run through both implementations and compared cell by cell.
Two defects were found and fixed during that process, both of the dangerous kind — wrong answers that looked reasonable. The cap solver took an absolute call volatility, so changing volatility on the Inputs tab left the calls priced at a hardcoded 20% and returned a confidently wrong cap with nothing flagging it. And the entry tab reported the buffer a buyer owns as 0% for a day-one holder, because the default cap is not struck at exactly fair value and the break-even inversion took the wrong branch at that knife edge. The first is fixed. The second was replaced with a measure that is unambiguous at every entry point, and the behaviour is documented rather than hidden.
Version 1.1 adds the How To Test tab. No calculation changed from 1.0 — the two files were compared formula by formula to confirm it.
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 The Buffer Is a Promise About One Day.
