OPT_MILTERSEN_SCHWARTZ Function (Gnumeric)
Use Gnumeric Miltersen-Schwartz commodity-futures option pricing with fourteen explicit inputs, discount-factor units and verified model checks.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
OPT_MILTERSEN_SCHWARTZ computes a theoretical commodity-futures option value using spot, convenience-yield and interest-rate factors. The function requires fourteen arguments; it is not a simple futures-price formula with a single volatility. The examples are synthetic numerical demonstrations, not calibrated market recommendations.
Use “c” for call or “p” for put. Discount is the price of a unit zero-coupon payment at option maturity, such as 0.95; it is a discount factor rather than an annual interest rate. Futures and strike share price units. Option_time and futures_time use a consistent year basis with the futures maturity no earlier than the option maturity in these examples.
The next three arguments are model volatilities of spot commodity price, convenience yield and forward interest rate. The correlations are spot/convenience, spot/rate and convenience/rate in that order. The last two positive parameters are convenience-yield and interest-rate mean-reversion speeds in reciprocal-time units. All thirteen numeric arguments after side are required.
Syntax
OPT_MILTERSEN_SCHWARTZ(side, discount, futures, strike, option_time, futures_time, spot_vol, convenience_vol, rate_vol, rho_se, rho_sf, rho_ef, kappa_e, kappa_f)
Worked example and independent check
Put “c”, 0.95,100,100,0.5,1,0.2,0.1,0.05,0.2,0.1,0.15,1,1.5 into B2:B15. Enter =OPT_MILTERSEN_SCHWARTZ(B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15) in B17. The expected call value is approximately 5.32220197198631. Changing only side to “p” gives approximately 5.33868227293522.
The source constructs an integrated variance and a covariance adjustment before applying normal-CDF terms. An independent numerical integration of the three factor exposures, using Simpson quadrature, reproduces the example without executing Gnumeric’s analytic expression.
A simpler control sets both convenience and rate volatilities, and all correlations, to zero; uses option and futures times 1, spot volatility 0.2 and positive mean reversions 1. With discount 0.95 and futures=strike=100, the call value is approximately 7.56728908263551. This reduces to a discounted lognormal futures-option calculation and provides a second check.
| Native-test formula | Independently expected output |
|---|---|
=OPT_MILTERSEN_SCHWARTZ("c",0.95,100,100,0.5,1,0.2,0.1,0.05,0.2,0.1,0.15,1,1.5) |
5.3222019719863125 |
=OPT_MILTERSEN_SCHWARTZ("p",0.95,100,100,0.5,1,0.2,0.1,0.05,0.2,0.1,0.15,1,1.5) |
5.338682272935223 |
=OPT_MILTERSEN_SCHWARTZ("c",0.95,100,100,1,1,0.2,0,0,0,0,0,1,1) |
7.567289082635513 |
=OPT_MILTERSEN_SCHWARTZ("bad",0.95,100,100,0.5,1,0.2,0.1,0.05,0.2,0.1,0.15,1,1.5) |
#NUM! |
Advanced use
Keep a parameter table that distinguishes discount factors, annualized volatilities, correlations and mean-reversion speeds. Similar-looking decimal inputs have different units and cannot be exchanged. Changing the time unit requires consistent changes to the model parameters, not just one time cell.
The three correlations should jointly form a valid correlation matrix; checking each individually lies between -1 and 1 is not sufficient. Calibration and economic suitability remain separate tasks from reproducing a spreadsheet value. Retain the assumptions and original model inputs with any output.
Errors and limitations
The invalid side “bad” returns #NUM!. Use positive futures, strike, discount, option time and mean-reversion speeds, with nonnegative volatilities and a positive resulting integrated variance. The implementation divides by both mean reversions and by the variance’s square root; zero-parameter limiting cases are not generally supplied automatically.
The zero-volatility control above still keeps spot volatility and both mean-reversion speeds positive. It does not certify an all-zero-variance case. Extreme parameter combinations, invalid correlation matrices, expiry and calibration accuracy are outside the selected tests.
Version and verification
First introduction is not established; generic release notes about option pricers do not identify this function precisely. The guide is scoped to official Gnumeric 1.12.57 implementation and selected native checks. The selected formulas were evaluated in isolated, time-bounded Gnumeric 1.12.57 native engine runs under C.UTF-8 on 17 October 2026. Observed results agree with the independent calculations within numerical tolerance; original input workbooks, outputs and execution logs are retained. No GUI screenshot or newer-version equivalence is claimed. A retirement date and last functioning release are not established.