OPT_SPREAD_APPROX Function (Gnumeric)
Calculate a synthetic spread-option control with Gnumeric OPT_SPREAD_APPROX, including correlation, effective volatility and model limits.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
OPT_SPREAD_APPROX evaluates a European option on the spread between two futures prices. It combines their volatilities and correlation in an effective-volatility approximation. The word approximation is part of the function’s purpose: a matching result for a simple special case does not establish exact pricing for every nonzero-strike spread.
Syntax and units
=OPT_SPREAD_APPROX(call_put_flag,fut_price1,fut_price2,strike,time,rate,volatility1,volatility2,rho)
All nine arguments are required. Use “c” for a call or “p” for a put. The call payoff orientation is the positive part of first futures price minus second futures price minus strike. time is years, rate is an annual decimal discount rate, and both volatilities are annualized decimals. rho is the price correlation and should be between −1 and 1.
Prices and strike must be expressed on a consistent contract/unit basis. The interface has no separate quantity arguments, so unequal contract sizes require a carefully defined normalized model before calling the function. A price quoted per tonne cannot be mixed directly with a second price quoted per barrel merely because both are numbers.
Reproducible zero-strike control
=OPT_SPREAD_APPROX("c",100,100,0,1,0.05,0.2,0.2,0)
The independently expected result is approximately 10.6978034882. Both futures are 100, their volatilities are 20%, correlation is zero, strike is zero and the horizon is one year with a 5% discount rate.
At zero strike, this reduces to an exchange-style option with effective volatility sqrt(0.2^2+0.2^2), approximately 0.2828427125. Independent discounted-lognormal-payoff quadrature gives the stated value. This is a useful exact special-case control for the approximation’s implementation; it does not measure approximation error when the strike is nonzero.
For a worksheet, place the nine arguments in A2:I2 and use =OPT_SPREAD_APPROX(A2,B2,C2,D2,E2,F2,G2,H2,I2). Label I1 Correlation, avoiding confusion with the discount rate. Retain the ordering of the two futures so the call payoff keeps its intended sign.
Advanced use: correlation and effective volatility
The implementation weights the second volatility by second futures price divided by second futures price plus strike. It then forms an effective variance from the two weighted variances and their covariance. Increasing correlation tends to reduce the variability of a matching long-first/short-second spread under otherwise consistent inputs.
Correlation is not a percentage volatility and does not take values such as 20 for 20%. Use a value such as 0.5 for moderate positive correlation. A historical correlation estimate is an assumption, not something the function obtains from the two price cells.
For a scenario table, hold the futures prices and volatilities fixed while changing rho within a justified range. Reprice each scenario rather than assuming the first sensitivity remains linear. Keep the result labeled as a model premium rather than a guaranteed spread profit.
Errors, singularities and compatibility
The denominator second futures price plus strike appears in both normalization and effective volatility. It must be positive for the ordinary lognormal setup here and must not be zero. The source does not comprehensively validate all economic domains before performing division, logarithms and square roots. A correlation outside its valid range can make the variance invalid.
The tested case uses positive prices, time and volatilities with zero correlation, avoiding zero-effective-volatility boundaries. Those boundaries, negative prices, extreme correlations and nonzero-strike approximation accuracy are not certified by this one native control. Error behavior outside the tested domain is source-derived rather than a promise of a particular displayed error for every invalid input.
These are synthetic contract values only. The function neither retrieves futures quotes nor checks contract specifications, settlement rules or transaction costs. Match those definitions separately before interpreting any real-world spread model.
Verification scope and sources
On October 17, 2026, isolated Gnumeric ssconvert 1.12.57 runs recalculated the literal controls, returning 10.697803488174523. 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.