OPT_AMER_EXCHANGE Function (Gnumeric)
Calculate Gnumeric American asset-exchange option values with explicit quantities, carries, correlation and approximation limits.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
OPT_AMER_EXCHANGE models the right to exchange qty2 units of asset 2 for qty1 units of asset 1 with American exercise. The exercise value is based on qty1spot1 minus qty2spot2 when positive. Reversing the assets changes the modeled right.
All eleven numerical arguments are required. Spot prices use compatible currency units; quantities scale the corresponding assets. Time is a year fraction, and rate, carry rates and annualized volatilities are decimal fractions. Rho is the correlation input, ordinarily between -1 and 1. This is a synthetic model demonstration rather than an investment recommendation.
Syntax
OPT_AMER_EXCHANGE(spot1, spot2, qty1, qty2, time, rate, carry1, carry2, volatility1, volatility2, rho)
Worked example and independent check
Put the following values in B2:B12 in syntax order: 100,95,1,1,1,0.05,0.02,0.01,0.2,0.25,0.3. Enter =OPT_AMER_EXCHANGE(B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12) in B14. The expected value is approximately 13.2342881134304.
The relative volatility is SQRT(0.2^2+0.25^2-20.30.2*0.25), approximately 0.269258240356725. The code feeds the scaled prices 100 and 95 into the Bjerksund-Stensland call approximation with transformed rate 0.05-0.01=0.04 and carry 0.02-0.01=0.01. Independent evaluation of that reduction reproduces the result.
Changing both quantities from 1 to 2 doubles the expected value to approximately 26.4685762268608. This is a useful scaling check for the same underlying model, not a claim that every input change has a linear effect.
| Native-test formula | Independently expected output |
|---|---|
=OPT_AMER_EXCHANGE(100,95,1,1,1,0.05,0.02,0.01,0.2,0.25,0.3) |
13.234288113430388 |
=OPT_AMER_EXCHANGE(100,95,2,2,1,0.05,0.02,0.01,0.2,0.25,0.3) |
26.468576226860776 |
=OPT_AMER_EXCHANGE("bad",95,1,1,1,0.05,0.02,0.01,0.2,0.25,0.3) |
#VALUE! |
Advanced use
Keep the two carry rates independent and record their meaning alongside the asset prices. Correlation affects relative volatility, so merely looking at individual volatilities is insufficient. The same quantity-weighted spot values should be used consistently in any independent comparison.
Despite its generic theoretical-price label, the retained implementation uses the Bjerksund-Stensland approximation internally. Distinguish an approximation-based American calculation from an exact universal pricing result or the European exchange formula. These models have different exercise assumptions.
Errors and limitations
The incompatible first-price text “bad” returns #VALUE!. Validate positive prices, quantities, time and individual volatilities, a meaningful correlation and a strictly positive effective volatility for the examples described here. The code is not a complete economic input validator.
A perfectly offset volatility combination can be singular; no zero-effective-volatility limit is certified. Neither extreme rates nor expiry boundaries are established by the bounded checks. Preserve which asset is received and which is surrendered when diagnosing an unexpectedly large value.
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.