OPT_ON_OPTIONS Function (Gnumeric)
Understand Gnumeric OPT_ON_OPTIONS strike order, compound-option flags, bounded test evidence and the observed numerical discrepancy.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
OPT_ON_OPTIONS evaluates an option whose underlying asset is itself an option. There are two exercise dates and two strikes. Confusing the payment to acquire the underlying option with that option’s own strike changes the contract substantially; the inspected help and implementation disagree about these strike labels.
Executable syntax and flags
=OPT_ON_OPTIONS(type_flag,spot,underlying_strike,compound_strike,time1,time2,rate,cost_of_carry,volatility)
All nine arguments are required. The exact lowercase flags are “cc” for a call on a call, “pc” for a put on a call, “cp” for a call on a put and “pp” for a put on a put. time1 is the compound option’s expiry and time2 is the underlying option’s expiry, in years with annualized inputs. Use 0<time1<time2 in the ordinary tested domain.
In 1.12.57 the third argument is used as the underlying option’s strike and the fourth as the compound exercise price. The help’s strike1/strike2 descriptions assign these roles the other way around. The executable formula and critical-value solver establish the order used above, and the independent payoff control tests that interpretation.
rate and carry are decimal annual rates; volatility is annualized decimal standard deviation of the ultimate underlying asset. spot, both strikes, time and volatility must be chosen in a meaningful positive domain. This function does not infer either contract’s terms from a security identifier.
A reproducible call on a call
=OPT_ON_OPTIONS("cc",100,100,10,0.5,1,0.05,0.05,0.2)
This synthetic contract expires in half a year and permits paying 10 to acquire a call with strike 100 that expires in one year. The spot is 100, annual rate and carry are 5%, and volatility is 20%. An independent conditional-payoff integration gives approximately 4.0214515895. The one isolated native run returned 4.021452964284395, about 0.00000137483 above the independent integral (relative difference about 0.0000342%). This is an observed numerical discrepancy, not exact agreement. The source uses an approximate bivariate-normal calculation and a critical-price residual threshold of 0.0001; those are possible contributors, but this test does not isolate their individual contributions. The comparison meets an explicit absolute tolerance of 0.000002 for this fixture only; no general accuracy bound or convergence guarantee is claimed.
For a cell-driven model, put the nine arguments in A2:I2. Label C1 Underlying strike, D1 Compound strike, E1 Compound expiry years and F1 Underlying expiry years, then use =OPT_ON_OPTIONS(A2,B2,C2,D2,E2,F2,G2,H2,I2). These labels are more reliable than ambiguous Strike1/Strike2 headings.
Independent validation
At time1, the value being acquired is the remaining half-year European call value. The compound payoff is the greater of that value minus 10 and zero. The independent calculation prices that remaining call through discounted lognormal-payoff quadrature, then integrates the compound payoff over spot at time1 and discounts to today.
The spot at which acquiring the underlying call just breaks even is approximately 104.7205037207 in this control. That critical spot is not a strike you type into the worksheet. It is an internally solved exercise boundary, which explains the extra numerical work compared with a single vanilla option.
Advanced interpretation and numerical limits
The compound premium is paid for the right to make a later decision; it is not simply the underlying option price minus the compound strike today. Timing and the possibility of declining exercise matter. Similarly, exchanging time1 and time2 does not represent the same contract in another order.
The source’s critical-value Newton iteration has no explicit iteration-count cap, and equal expiries can feed a zero remaining lifetime into an inner option formula. The native probe is therefore isolated and timeout-bounded for the ordinary case above. Do not infer that malformed flags, extreme strikes or reversed/equal times are safe just because this case completes.
The implementation checks unrecognized type text only after attempting a critical-value calculation. This guide does not recommend deliberately executing invalid flags as an error test. Preserve exact lowercase flags and validate the input domain before using the function in a large recalculating model.
Verification scope and sources
On October 17, 2026, isolated Gnumeric ssconvert 1.12.57 runs recalculated the literal controls, returning 4.021452964284395. Each case used its own workbook and output cell F1; the instructional cell layouts are not separate GUI tests. Exact input and output files were retained. Other boundaries are source-derived unless expressly tested. Official 1.12.57 source was retrieved October 17, 2026. Original introduction and last functioning release are not established; no GUI screenshot or execution of another release is claimed. The stated input domain and test scope should travel with any compatibility comparison.