OPT_BS_GAMMA Function (Gnumeric)
Calculate option gamma in Gnumeric with explicit cost of carry, sensitivity units, native examples and domain limits.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
OPT_BS_GAMMA calculates the curvature of a European option’s theoretical price with respect to the underlying spot price. Gamma is the rate at which delta changes per unit change in spot. It is a model sensitivity, not the option price or a predicted trading profit. The calculations below use synthetic inputs to explain the spreadsheet function.
Syntax and units
OPT_BS_GAMMA(spot, strike, time, rate, volatility, [cost_of_carry])
Spot and strike use the same currency units. Time is a year fraction. Rate, volatility, and cost of carry are annualized numerical fractions: enter 0.05 for 5% and 0.2 for 20%, not 5 and 20. The callback uses these values directly without dividing by 100.
Cost of carry defaults to zero, not to the risk-free rate. For a modeled non-dividend-paying stock, explicitly choosing carry equal to rate is a different assumption from omitting it. Use positive spot, strike, time, and volatility for the nonsingular model described here.
There is no call/put flag because this model’s gamma is the same for a corresponding call and put. Inserting a side flag shifts every numerical argument into the wrong position.
Worked example
Use spot=100, strike=100, time=1, rate=0.05, volatility=0.2, and carry=0.05:
=OPT_BS_GAMMA(100,100,1,0.05,0.2,0.05)
The expected result is approximately 0.0187620173458469. This means a small one-unit spot change produces approximately 0.018762 change in delta locally, with the other assumptions held fixed. It does not mean the option price changes by that amount.
Omitting carry, =OPT_BS_GAMMA(100,100,1,0.05,0.2), gives approximately 0.0188796471645325. The difference illustrates why a missing optional argument must not silently stand for the stock assumption intended by the reader.
For a worksheet, put the six inputs in B2:B7 and use =OPT_BS_GAMMA(B2,B3,B4,B5,B6,B7). Label each input and its units. Percentage formatting can display 0.05 as 5% without changing the stored number.
Independent calculation and advanced use
The implementation forms d1=(LN(spot/strike)+(carry+volatility^2/2)*time)/(volatility*SQRT(time)) and computes EXP((carry-rate)*time)*phi(d1)/(spot*volatility*SQRT(time)), where phi is the standard normal density. For the explicit-carry example, d1=0.35. Evaluating the density independently reproduces the stated result.
In a local second-order price approximation, the gamma contribution is 0.5*gamma*(spot_change)^2; a separate delta term is also needed. This is a small-change approximation with fixed model assumptions, not a substitute for repricing after a large move. Gamma itself changes as spot, time, and volatility change.
Errors and limitations
=OPT_BS_GAMMA("bad",100,1,0.05,0.2,0.05) produces #VALUE! for an incompatible text argument. Zero time or volatility makes the displayed formula singular; this guide does not promise an expiry-limit result or a particular native error for those inputs. A numerical result does not validate the assumptions of the pricing model.
Check currency consistency, annualization, decimal rates, and the optional carry before comparing with another calculator. Gamma units depend on the spot-price unit; rescaling an asset price changes the interpretation of a “one-unit” move.
Version and verification
The guide is pinned to official Gnumeric 1.12.57 source checked on 17 October 2026. The first introduction and any last-working release are not established. The selected examples were recalculated in the actual 1.12.57 engine under C.UTF-8 on that date and compared with independent mathematical calculations. No GUI screenshot, extreme-domain certification, or newer-version equivalence is claimed.
Related functions
OPT_BS_DELTA · SQRT · EXP