The Financial Toolbox comes with most common financial derivatives formulas ready for use. It also provides dozens of complex derivatives formulas and an insertion tool that facilitates the usage of the toolbox. The list of functions to deal with financial derivatives can be seen below:
# | Function Name | Function Description |
---|---|---|
1 |
sOptions_BlackScholes |
Returns the European option price using the standard Black and Scholes formula. |
2 |
sOptions_Merton73 |
Returns the European option on stock indices price using the Merton (1973) formula. |
3 |
sOptions_Black76 |
Returns the European option on futures/forwards using the Black (1977) formula. |
4 |
sOptions_GarmanKolhagen |
Returns the European option on currency using the Garman and Kohlhagen (1983) formula. |
5 |
sOptions_GeneralizedBlackScholes |
Returns the European option price using the Generalized Black and Scholes formula. |
6 |
sOptions_GeneralizedBlackScholesDelta |
Returns the delta of a European option calculated with the Generalized Black and Scholes formula. |
7 |
sOptions_GeneralizedBlackScholesGamma |
Returns the gamma of a European option calculated with the Generalized Black and Scholes formula. |
8 |
sOptions_GeneralizedBlackScholesTheta |
Returns the theta of a European option calculated with the Generalized Black and Scholes formula. |
9 |
sOptions_GeneralizedBlackScholesVega |
Returns the vega of a European option calculated with the Generalized Black and Scholes formula. |
10 |
sOptions_GeneralizedBlackScholesRho |
Returns the rho of a European option calculated with the Generalized Black and Scholes formula. |
11 |
sOptions_GeneralizedBlackScholesCarry |
Returns the carry sensitivity of a European option calculated with the Generalized Black and Scholes formula. |
12 |
sOptions_French |
Returns the French (1984) adjusted Black and Scholes model for trading day volatility. |
13 |
sOptions_JumpDiffusion |
Returns the European option price using the Merton (1976) jump diffusion model. |
14 |
sOptions_MiltersenSchwartz |
Returns the European option price using the Miltersen Schwartz (1997) commodity option model. |
15 |
sOptions_RollGeskeWhaley |
Returns the price of an American call on stock with known dividends using the Roll-Geske-Whaley formula. |
16 |
sOptions_BAWAmericanApprox |
Returns the approximate price of an American option on stock using the Barone-Adesi and Whaley (1987) formula. |
17 |
sOptions_BSAmericanApprox |
Returns the approximate price of an American option on stock using the Bjerksund and Stensland (1993) formula. |
18 |
sOptions_Executive |
Returns the price of an executive stock option. |
19 |
sOptions_ForwardStartOption |
Returns the price of a forward start option. |
20 |
sOptions_TimeSwitchOption |
Returns the price of a time switch option (discrete). |
21 |
sOptions_SimpleChooser |
Returns the price of a simple chooser option. |
22 |
sOptions_ComplexChooser |
Returns the price of a complex chooser option. |
23 |
sOptions_OptionsOnOptions |
Returns the price of an option on option. |
24 |
sOptions_ExtendibleWriter |
Returns the price of a writer extendible option. |
25 |
sOptions_TwoAssetCorrelation |
Returns the price of an option on two correlated assets. |
26 |
sOptions_EuropeanExchangeOption |
Returns the price of a European option to exchange one asset for another. |
27 |
sOptions_AmericanExchangeOption |
Returns the price of an American option to exchange one asset for another. |
28 |
sOptions_ExchangeExchangeOption |
Returns the price of an exchange option on exchange option. |
29 |
sOptions_OptionsOnTheMaxMin |
Returns the price of an option on the maximum or minimum of two risky assets. |
30 |
sOptions_SpreadApproximation |
Returns the approximate price of a spread option. |
31 |
sOptions_FloatingStrikeLookback |
Returns the price of a lookback option with a floating strike. |
32 |
sOptions_FixedStrikeLookback |
Returns the price of a lookback option with a fixed strike. |
33 |
sOptions_PartialFloatLB |
Returns the price of lookback option with a partial-time floating strike. |
34 |
sOptions_PartialFixedLB |
Returns the price of lookback option with a partial-time fixed strike. |
35 |
sOptions_ExtremeSpreadOption |
Returns the price of an extreme spread option. |
36 |
sOptions_StandardBarrier |
Returns the price of a standard barrier option. |
37 |
sOptions_DoubleBarrier |
Returns the price of a double barrier option. |
38 |
sOptions_PartialTimeBarrier |
Returns the price of a partial-time single asset barrier option. |
39 |
sOptions_TwoAssetBarrier |
Returns the price of a barrier option on two assets. |
40 |
sOptions_PartialTimeTwoAssetBarrier |
Returns the price of a partial-time barrier option on two assets. |
41 |
sOptions_LookBarrier |
Returns the price of a look-barrier option. |
42 |
sOptions_DiscreteAdjustedBarrier |
Returns the price of a discrete barrier option with monitoring adjustment. |
43 |
sOptions_SoftBarrier |
Returns the price of a soft barrier option. |
44 |
sOptions_GapOption |
Returns the price of a gap option. |
45 |
sOptions_CashOrNothing |
Returns the price of a cash-or-nothing option. |
46 |
sOptions_TwoAssetCashOrNothing |
Returns the price of a cash-or-nothing option on two assets. |
47 |
sOptions_AssetOrNothing |
Returns the price of an asset-or-nothing option. |
48 |
sOptions_SuperShare |
Returns the price of a super-share option. |
49 |
sOptions_BinaryBarrier |
Returns the price of a binary barrier option. |
50 |
sOptions_GeometricAverageRateOption |
Returns the price of a geometric average rate option. |
51 |
sOptions_TurnbullWakemanAsian |
Returns the price of an arithmetic average rate option using the Turnbull-Wakeman formula. |
52 |
sOptions_LevyAsian |
Returns the price of an arithmetic average rate option. |
53 |
sOptions_ForeignEquityOptionInDomesticCurrency |
Returns the price of a foreign equity option struck in domestic currency. |
54 |
sOptions_Quanto |
Returns the price of a fixed exchange rate foreign equity option. |
55 |
sOptions_EquityLinkedFXO |
Returns the price of an equity-linked foreign exchange option. |
56 |
sOptions_TakeoverFXoption |
Returns the price of a takeover foreign exchange option. |
57 |
sOptions_Swaption |
Returns the price of a European swaption using Black (1976) formula. |
58 |
sOptions_VasicekBondOption |
Returns the price of an option on a zero coupon bond. |
59 |
sFutures_ForwardPrice |
Returns the price of a forward contract with no income on the asset. |
60 |
sFutures_CurrencyForwardPrice |
Returns the price of a currency forward contract using discrete interest rates. |
61 |
sFutures_ForwardPriceIncomePresentValue |
Returns the price of a forward contract with a known asset income. |
62 |
sFutures_ForwardPriceIncomeYield |
Returns the price of a forward contract with a known asset yield. |
63 |
sFutures_ForwardValuation |
Returns the valuation of an outstanding forward contract with no income on the asset. |
64 |
sFutures_ForwardValuationIncomeYield |
Returns the valuation of an outstanding forward contract with known yield. |
65 |
sFutures_ForwardValuationAgreedVsCurrent |
Returns the valuation of an outstanding forward contract comparing the agreed price and the current price. |
66 |
sFutures_ForwardValuationIncomePresentValue |
Returns the valuation of an outstanding forward contract with known income. |
67 |
sFutures_ForwardRateAgreementValuation |
Returns the price of a forward rate agreement (FRA). |
68 |
sFutures_EurodollarFutureProfit |
Returns the profit of a Eurodollar future. |
69 |
sFutures_FuturesPriceTBond |
Returns the approximate price of a U.S. Treasury Bond Futures. |
70 |
sFutures_FuturesValuationTBond |
Returns the approximate valuation of an outstanding U.S. Treasury Bond Futures. |
To illustrate the usage of the tool, let’s calculate the price of a standard European call option. This derivative can be found in the "Financial Instruments" group located in the Financial Toolbox tab. The insertion is quite straightforward, simply select the financial instrument in the list and click on the right arrow. After choosing the destination cell - an upper left cell to receive the results - a table will be inserted in the spreadsheet that contains all necessary parameters for calculation and the price of the derivative that we were looking for.
A |
B |
C |
D |
|
1 |
Stock options: Generalized Black and Scholes |
|
|
|
2 |
Call or Put? |
Call |
|
|
3 |
Asset price |
$ 100.00 |
|
|
4 |
Strike price |
$ 110.00 |
|
|
5 |
Time to maturity |
5.0 |
|
|
6 |
Risk-free rate |
5.0 % |
|
|
7 |
Cost of carryng |
2.0 % |
|
|
8 |
Volatility |
20.0 % |
|
|
9 |
Price |
$ 15.40 |
=sOptions_GeneralizedBlackScholes($B$2,$B$3,$B$4,$B$5,$B$6,$B$7,$B$8) |
|
10 |
Delta |
0.51 |
=sOptions_GeneralizedBlackScholesDelta($B$2,$B$3,$B$4,$B$5,$B$6,$B$7,$B$8) |
|
11 |
Gamma |
0.01 |
=sOptions_GeneralizedBlackScholesGamma($B$2,$B$3,$B$4,$B$5,$B$6,$B$7,$B$8) |
|
12 |
Theta |
-1.74 |
=sOptions_GeneralizedBlackScholesTheta($B$2,$B$3,$B$4,$B$5,$B$6,$B$7,$B$8) |
|
13 |
Vega |
74.71 |
=sOptions_GeneralizedBlackScholesVega($B$2,$B$3,$B$4,$B$5,$B$6,$B$7,$B$8) |
|
14 |
Rho |
178.03 |
=sOptions_GeneralizedBlackScholesRho($B$2,$B$3,$B$4,$B$5,$B$6,$B$7,$B$8) |
|
15 |
|
|
|
|
Of course, other SAFE TOOLBOXES® tools would be of great value for pricing and managing financial derivatives. For instance, you can use the Simulation Toolbox for pricing derivatives by Monte Carlo simulation, or you can use the sensitivity analysis to find the impact on price caused by changes in derivative parameters.