OPT_BS Function (Gnumeric)
Calculate a generalized European option value in Gnumeric OPT_BS, with explicit carry, annual units and tested call/put controls.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
OPT_BS evaluates a generalized Black–Scholes European option model. It returns a theoretical price in the same monetary units as spot and strike. It does not retrieve a market quote, execute a trade, or establish that a particular asset follows the model’s assumptions. The cost-of-carry parameter is particularly important in Gnumeric because its default is zero.
Syntax and units
=OPT_BS(call_put_flag,spot,strike,time,rate,volatility,[cost_of_carry])
Use “c” for a call and “p” for a put. spot and strike must be positive for the ordinary lognormal model. time is the remaining time in years, rate is an annual continuously compounded decimal rate, and volatility is annualized standard deviation as a decimal. For 5% rate and 20% volatility, enter 0.05 and 0.2. Use strictly positive time and volatility in the examples here.
cost_of_carry is optional and defaults to 0, not to rate. In the usual no-dividend stock model, explicitly set it equal to rate. With a continuous dividend yield q, carry is rate minus q. The implementation applies the supplied numbers directly in exponentials and does not divide percent-looking inputs by 100.
Reproducible call, put and default comparison
These synthetic inputs use spot 100, strike 100, one year, rate 0.05 and volatility 0.2:
=OPT_BS("c",100,100,1,0.05,0.2,0.05)
=OPT_BS("p",100,100,1,0.05,0.2,0.05)
=OPT_BS("c",100,100,1,0.05,0.2)
The independent expected values are approximately 10.4505835722, 5.5735260223 and 7.5770821464. The third formula changes the model because the omitted carry becomes zero. It should not be treated as a shorter spelling of the first formula.
For a reusable row, put the seven arguments in A2:G2 and use =OPT_BS(A2,B2,C2,D2,E2,F2,G2). Keep the flag as text and label G1 Cost of carry. A sensitivity table can vary F2 while retaining the other assumptions, but it should identify the output as theoretical value rather than expected profit.
Independent validation and advanced checks
The expected values were calculated by numerically integrating the discounted payoff under a lognormal terminal-price distribution, rather than copying Gnumeric’s cumulative-normal expression. For the first two calls, put–call parity gives call minus put = 100 − 100×EXP(−0.05), approximately 4.8770575499. This is an additional structural check on the paired results.
For generalized carry b, parity instead uses spot×EXP((b−rate)×time) − strike×EXP(−rate×time). Substituting zero carry changes that forward-value term. This explains why a seemingly small omitted argument can produce a substantial valuation difference.
Errors and troubleshooting
The inspected callback converts a not-a-number result to #NUM!, including an unrecognized flag. Its numeric formula contains logarithms, square roots and division by volatility×sqrt(time). It does not separately implement every limiting case at zero time or zero volatility, nor comprehensively validate all economically nonsensical inputs. Avoid interpreting an output from an invalid domain as a reliable price.
If a workbook imported from elsewhere shows an unknown function, check Gnumeric’s derivatives-function support and export compatibility. These function names are marked Gnumeric-specific in the registration; an XLSX file extension alone does not make another application calculate them identically. Preserve assumptions and compare a known control after any conversion.
Evidence and compatibility
Gnumeric ssconvert 1.12.57 under C.UTF-8 recalculated the exact literal examples on October 17, 2026. The OptionControls fixture recorded F1 = 10.450583572185565; F2 = 5.573526022256971; F3 = 7.577082146427273. These agree with the independent expectations within 10^-7, including finite-difference sensitivity error. Cell-driven layouts are instructional equivalents, not separate GUI tests. Other boundary/error statements are source-derived. Official Gnumeric 1.12.57 implementation was retrieved October 17, 2026; independent calculations use discounted-payoff quadrature and the explicitly stated derivative or survival relationships. Original introduction and last functioning release are not established. No GUI screenshot or execution of another release is claimed. These derivatives functions are registered as Gnumeric-specific; verify recalculation after export rather than relying on cached values in another application.