Call Spread Exit Calculator
Version 1.0 · Excel workbook · Black-Scholes-Merton
A calculator for capped call spreads that have moved in the money, built for the moment somebody asks whether to close one. Nine tabs, 318 live formulas, no macros, no protected sheets, nothing computed somewhere else and pasted in as a value. Every cell is auditable.
The assumption you must override
Every price in this workbook is a mid-market theoretical value. It assumes you can trade in and out at mid, which you cannot. There is no bid-offer, no brokerage, no exchange or clearing fee, no market impact and no financing spread anywhere in it.
That is deliberate, because it isolates the thing worth seeing: at mid, with proceeds reinvested at the risk-free rate, holding and selling are identical by construction. The workbook has a cell for the difference and it reads exactly zero. Everything that actually decides an exit sits on top of that — and the first of those is your execution cost, which is a blue input on the Exit Decision tab, defaulted to zero. Put your own number in it. A structure whose remaining return is 30 basis points does not survive a 40 basis point round trip.
What is in it
The position. Both legs priced under Black-Scholes-Merton with a continuous dividend yield, the spread value today, the maximum it can ever pay, intrinsic value and time value. Time value reads negative on a deep in-the-money spread, which is correct and is discounting rather than a mispricing — the workbook says so on the line rather than leaving you to wonder.
The backward measure beside the forward ones. Percent of maximum captured, computed the way most systems compute it, sitting directly above the gap to maximum value, that gap as a return on the capital still committed, the same figure annualised, and the excess over the risk-free rate. The point of the layout is that you can watch one move while the others do not.
An exit decision tab. Two questions kept apart, because they are not the same question. Liquidating — sell and do not replace the exposure — resolves into a wash at mid, then into three things: execution cost, funding differential, and whether you hold a view the market does not. Migrating — sell and re-establish the same structure elsewhere — drops the market view entirely and becomes a subtraction: round-trip cost against the benefit of moving. Each carries a break-even cost in basis points and a plain-language verdict.
A metric trap tab. Eight rows holding one position at one index level, varying only the entry premium and the days remaining. The first four are the same position with the same thirty days to run and four different entry prices: identical value, identical gap to maximum, identical annualised return, and percent-of-max readings of 90.9%, 86.4%, 72.7% and −9.0%. The last of those describes a position that is up 97.75% annualised on the money still committed.
A paired structure tab. A capped spread held alongside an uncapped call, across a range of index moves. Remaining-upside measures computed on the spread alone score the uncapped leg at zero, because a call with no cap has no maximum value to subtract from. The tab reports the ratio of what the pair actually delivered to what the spread-only measure said was left.
Six worked scenarios. From a genuine bond — deep in the money with a week to run, where the remaining return is discounting and nothing else — through to a position that is underwater and deeply out of the money, where the metric reads negative and the convexity is the whole position. Each row is independent and reprices from its own inputs, and each carries a written explanation of what the situation is and what the economics actually are.
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.
What it does not do
A single flat volatility, not a surface. European exercise, which is right for the index options these structures use and wrong for anything American. No dividends beyond a continuous yield, no borrow cost, no counterparty or collateral modelling, no tax. It prices a two-leg call spread and an optional uncapped call, and nothing path-dependent. It does not model your funding curve — the reinvestment rate is a single input, continuously compounded to match the risk-free rate.
Verification
Every output was checked against an independent Python implementation before release: the position tab, all eight metric-trap rows, all six scenarios, all ten paired-structure rows and every line of the exit decision tab. All 318 formulas recalculate without error.
One defect was found and fixed during that process, and it is the kind worth describing because it produced a plausible wrong answer rather than an obvious one. The exit decision tab compared holding against selling, but the two sides used different compounding conventions — the option value was carried forward continuously while the reinvested proceeds compounded annually. With identical rates and no transaction cost the comparison returned roughly two basis points in favour of holding, which looks like a result and is an artifact. Both sides are continuous now, and the cell reads exactly zero. That zero is the tab's entire argument, so it needed to be exact rather than close.
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.
