BinaryBarrierAndStrikePriceNGreeks
Updated 2023-11-14 19:57:49.760000
Syntax
SELECT * FROM [westclintech].[wct].[BinaryBarrierAndStrikePriceNGreeks](
<@CallPut, nvarchar(4000),>
,<@BarrierType, nvarchar(4000),>
,<@CashOrNothing, bit,>
,<@AssetPrice, float,>
,<@StrikePrice, float,>
,<@BarrierPrice, float,>
,<@Rebate, float,>
,<@TimeToMaturity, float,>
,<@RiskFreeRate, float,>
,<@DividendRate, float,>
,<@Volatility, float,>)
Description
Use the table-valued function BinaryBarrierAndStrikePriceNGreeks to calculate the price and other derivatives for binary barrier options having payoffs that are received only at expiration. Unlike options handled by the BINARYBARRIERONLY and the BINARYBARRIERPAYOUTATHIT functions, these options also include a designation as a put or a call in addition to the characteristics of the barrier.
BinaryBarrierAndStrike valuations are based on the formulae published by Mark Rubinstein and Eric Reiner in 1991. In their July 31, 1991 paper Binary Options, they enumerated 28 different types of binary barrier options. This function deals with options numbers 13–28.
The first group of options have positive payoffs requiring not only that the barrier be breached, but also that the underlying asset finish above the strike price.
(13) down-and-in cash-or-nothing call;
(14) up-and-in cash-or-nothing call;
(15) down-and-in asset-or-nothing call; and
(16) up-and-in asset-or-nothing call.
The next group have positive payoffs requiring not only that the barrier be breached, but also that the underlying asset finish below the strike price.
(17) down-and-in cash-or-nothing put;
(18) up-and-in cash-or-nothing put;
(19) down-and-in asset-or-nothing put; and
(20) up-and-in asset-or-nothing put.
The third group of options have positive payoffs requiring that not only that the barrier not be breached, but also that the underlying asset finish above the strike price.
(21) down-and-out cash-or-nothing call;
(22) up-and-out cash-or-nothing call;
(23) down-and-out asset-or-nothing call; and
(24) up-and-out asset-or-nothing call.
The final group of options have positive payoffs requiring not only that the barrier not be breached, but also that the underlying asset finish below the strike price.
(25) down-and-out cash-or-nothing put;
(26) up-and-out cash-or-nothing put;
(27) down-and-out asset-or-nothing put; and
(28) up-and-out asset-or-nothing put.
Arguments
@BarrierType
identifies the type of barrier as 'UI' (Up-and-In), 'UO' (Up-and-Out), 'DI' (Down-and-In), or 'DO' (Down-and-out). @BarrierType must be of a type nvarchar or of a type that implicitly converts to nvarchar.
@CashOrNothing
identifies the option as either a cash-or-nothing ('True') or asset-or-nothing ('False') binary barrier. @CashOrNothing is an expression of type bit or of a type that can be implicitly converted to bit.
@DividendRate
the continuously compounded zero coupon dividend rate over the life of the option. For currency options, @DividendRate should be the foreign risk-free zero coupon rate. @DividendRate is an expression of type float or of a type that can be implicitly converted to float.
@RiskFreeRate
the continuously compounded zero coupon risk-free rate over the life of the option. @RiskFreeRate is an expression of type float or of a type that can be implicitly converted to float.
@BarrierPrice
For a knock-in option, @BarrierPrice is the value at which the option comes into existence if the @AssetPrice crosses the barrier. For a knock-out option, @BarrierPrice is the value at which the option is extinguished if the @AssetPrice crosses the barrier. @BarrierPrice must be of a type float or of a type that implicitly converts to float.
@TimeToMaturity
the time to expiration of the option, expressed in years. @TimeToMaturity is an expression of type float or of a type that can be implicitly converted to float.
@Rebate
An amount paid to the buyer of the option in the event that the barrier is never breached. @Rebate must be of a type float or of a type that implicitly converts to float.
@CallPut
identifies the option as being a call ('C') or a put ('P'). @CallPut is an expression of type nvarchar or of a type that can be implicitly converted to nvarchar.
@StrikePrice
the exercise price of the option. @StrikePrice is an expression of type float or of a type that can be implicitly converted to float.
@Volatility
the volatility of the relative price change of the underlying asset. @Volatility is an expression of type float or of a type that can be implicitly converted to float.
@AssetPrice
the price of the underlying asset. @AssetPrice is an expression of type float or of a type that can be implicitly converted to float.
Return Type
table
{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "046ecf15-2f45-4eb5-900f-3210c576892a", "colName": "Price", "colDatatype": "float", "colDesc": "The theoretical value of the option."}, {"id": "1bd71790-fdf3-4827-8d92-0b8c73f64bb1", "colName": "Delta", "colDatatype": "float", "colDesc": "The sensitivity to small changes in the asset price; the first derivative of the option with respect to price."}, {"id": "9cfeff50-b630-4bd0-8415-844c8b6a417c", "colName": "Gamma", "colDatatype": "float", "colDesc": "The rate of change in Delta with respect to small changes in the asset price; the second derivative of the option with respect to price."}, {"id": "f885f308-a6d8-4aa9-bbdf-b5b918156d3c", "colName": "Theta", "colDatatype": "float", "colDesc": "The sensitivity to small changes in time; the first derivative of the option with respect to time."}, {"id": "d24a3295-606d-4e8c-bfa6-570a755f2a67", "colName": "Vega", "colDatatype": "float", "colDesc": "The sensitivity to small changes in volatility; the first derivative of the option with respect to volatility."}, {"id": "1e435c77-4c1b-4787-ba3e-e3f1d6f035cd", "colName": "Rho", "colDatatype": "float", "colDesc": "The sensitivity to small changes in the risk-free rate; the first derivative of the option with respect to the risk-free rate."}, {"id": "d8505af1-64a3-4212-9709-11577d6cad39", "colName": "Lambda", "colDatatype": "float", "colDesc": "Delta multiplied by the asset price divided by the theoretical value. If the theoretical value is zero, then lambda is set to zero."}, {"id": "1685e519-00fb-40b7-9f23-8bb54e6498df", "colName": "GammaP", "colDatatype": "float", "colDesc": "Gamma multiplied by asset price divided by strike price."}, {"id": "94474110-6db8-45c8-8f66-6db52681e555", "colName": "DdeltaDtime", "colDatatype": "float", "colDesc": "The instantaneous change in delta over the passage of time; the second derivative, once to asset price and once to time."}, {"id": "2da7febf-d55e-46f8-bcfb-7280b3be5bd6", "colName": "DdeltaDvol", "colDatatype": "float", "colDesc": "The sensitivity of delta with respect to volatility; the second derivative, once to asset price and once to volatility."}, {"id": "553438d2-7224-4dd5-aa45-376cedff8281", "colName": "DdeltaDvolDvol", "colDatatype": "float", "colDesc": "The second derivative of delta with respect to volatility; the third derivative, once to asset price and twice to volatility."}, {"id": "b3147397-8ac9-4343-91b9-1f37bca6c5fe", "colName": "DgammaDvol", "colDatatype": "float", "colDesc": "The rate of change in gamma with respect to changes in volatility; the third derivative, twice to asset price and once to volatility."}, {"id": "ad52a442-9909-4b25-95ae-2e17c2b353a8", "colName": "DvegaDvol", "colDatatype": "float", "colDesc": "The rate of change to Vega as the volatility changes; the second derivative with respect to volatility."}, {"id": "ba7c00cb-2633-4174-8877-e858f85d15dd", "colName": "VegaP", "colDatatype": "float", "colDesc": "The percentage change in theoretical value for a 10 per cent change in volatility."}, {"id": "69949fa1-5384-44bc-9be2-6e190fe942f7", "colName": "PhiRho2", "colDatatype": "float", "colDesc": "The sensitivity to a change in the dividend yield (foreign interest rate for a currency option); the first derivative with respect to dividend yield."}, {"id": "07d0ad96-86cb-4bff-b4c9-18f995d3b7db", "colName": "DgammaDspot", "colDatatype": "float", "colDesc": "The rate of change in gamma with respect to change in the asset price; the third derivative with respect to price."}, {"id": "034f5190-1c0e-4bab-8a8b-52ed8b131a3a", "colName": "DeltaX", "colDatatype": "float", "colDesc": "The sensitivity to a change in the strike price; the first derivative with respect to strike price."}, {"id": "8091becf-66ca-47ea-91a2-d43ba1e456c9", "colName": "RiskNeutralDensity", "colDatatype": "float", "colDesc": "The sensitivity of DeltaX; the second derivative with respect to strike price."}, {"id": "4b69e68a-f431-4bfc-a5ed-a0b911cedca6", "colName": "DvommaDvol", "colDatatype": "float", "colDesc": "The sensitivity of DvegaDvol to changes in volatility; the third derivative, twice to asset price and once to volatility."}, {"id": "20bb10bd-b96c-4a97-aa75-0dcaab761d22", "colName": "DgammaDtime", "colDatatype": "float", "colDesc": "The sensitivity of Gamma to the passage of time; the third derivative, twice to asset price and once to time."}, {"id": "ee24ea5c-67bd-422f-968c-4780c41d9c9d", "colName": "DvegaDtime", "colDatatype": "float", "colDesc": "The sensitivity of Vega to the passage of time; the second derivative, once to volatility and once to time."}, {"id": "d2ccce44-ba57-4834-8668-e23179ce8358", "colName": "FuturesOptionsRho", "colDatatype": "float", "colDesc": "The sensitivity to change in risk-free rate assuming a cost-of-carry of zero. CarrySensitivity -PhiRho2 ForwardPrice The value of the underlying asset at the expiration date of the option. ForwardPoints The difference between the ForwardPrice and the asset price."}, {"id": "98919c47-fd13-4311-9690-7d3cb9903f7b", "colName": "CarrySensitivity", "colDatatype": "float", "colDesc": "-PhiRho2"}, {"id": "419edc9a-07ea-49a2-af97-ecbe4685d5ba", "colName": "ForwardPrice", "colDatatype": "float", "colDesc": "The value of the underlying asset at the expiration date of the option."}, {"id": "b28386d5-d9e2-42d2-b288-b222c3e929f7", "colName": "ForwardPoints", "colDatatype": "float", "colDesc": "The difference between the ForwardPrice and the asset price."}]}
Remarks
@Volatility must be greater than zero (@Volatility > 0).
@TimeToMaturity must be greater than zero (@TimeToMaturity > 0).
@AssetPrice must be greater than zero (@AssetPrice > 0).
@StrikePrice must be greater than zero (@StrikePrice > 0).
If @ReturnValue is NULL, then @ReturnValue is set to 'P'.
If @DividendRate is NULL then @DividendRate = 0.
If @RiskFreeRate is NULL @RiskFreeRate = 0.
@BarrierPrice must be greater than zero (@BarrierPrice > 0).
@Rebate must be greater than or equal to zero (@Rebate >= 0).
If @Rebate is NULL, then @Rebate = 0.
@BarrierPrice assumes continuous monitoring.
To convert a non-continuous @BarrierPrice use the ADJUSTEDBARRIER function.
Examples
In this example we calculate the price and Greeks of a down-and-in cash-or-nothing call option.
SELECT *
FROM wct.BinaryBarrierAndStrikePriceNGreeks( 'C', --CallPut
'DI', --BarrierType
'True', --CashOrNothing
100, --AssetPrice
100, --StrikePrice
92, --BarrierPrice
2, --Rebate
.50, --TimeToMaturity
.10, --RiskFreeRate
.05, --DividendRate
.20 --Volatility
);
This produces the following result
{"columns":[{"field":"Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Delta","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Gamma","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Theta","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Vega","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Rho","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Lambda","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"GammaP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DdeltaDtime","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DdeltaDvol","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DdeltaDvolDvol","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DgammaDvol","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DvegaDvol","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"VegaP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PhiRho2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DgammaDspot","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DeltaX","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"RiskNeutralDensity","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DvommaDvol","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DgammaDtime","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DvegaDtime","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"FuturesOptionsRho","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CarrySensitivity","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ForwardPrice","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ForwardPoints","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Price":"0.237728502921379","Delta":"-0.0301930668994421","Gamma":"0.00317472714783662","Theta":"-0.00126385444988394","Vega":"0.0224979325084196","Rho":"0.00221383454113017","Lambda":"0","GammaP":"0.00317472714783662","DdeltaDtime":"3.92842398641455E-05","DdeltaDvol":"-0.000297989646846908","DdeltaDvolDvol":"0.000243830305564352","DgammaDvol":"-0.000272046290356043","DvegaDvol":"-0.000919069887417301","VegaP":"0.0449958650168392","PhiRho2":"-0.00340247700403307","DgammaDspot":"-0.000222105348002266","DeltaX":"-0.0266271393553619","RiskNeutralDensity":"0.00228677077274142","DvommaDvol":"-9.85342348596707E-05","DgammaDtime":"0.000287089395939542","DvegaDtime":"-0.00141711661241278","FuturesOptionsRho":"-0.00118864251510281","CarrySensitivity":"0.00340247700403307","ForwardPrice":"102.531512052443","ForwardPoints":"2.53151205244289"}]}
In this SELECT we un-pivot the columns returned by the function for ease of viewing the results.
SELECT n.*
FROM wct.BinaryBarrierAndStrikePriceNGreeks( 'C', --CallPut
'DI', --BarrierType
'True', --CashOrNothing
100, --AssetPrice
100, --StrikePrice
92, --BarrierPrice
2, --Rebate
.50, --TimeToMaturity
.10, --RiskFreeRate
.05, --DividendRate
.20 --Volatility
)
CROSS APPLY
(
VALUES
('Price', Price),
('Delta', Delta),
('Gamma', Gamma),
('Theta', Theta),
('Vega', Vega),
('Rho', Rho),
('Lambda', Lambda),
('GammaP', GammaP),
('DdeltaDtime', DdeltaDtime),
('DdeltaDvol', DdeltaDvol),
('DdeltaDvolDvol', DdeltaDvolDvol),
('DgammaDvol', DgammaDvol),
('DvegaDvol', DvegaDvol),
('VegaP', VegaP),
('PhiRho2', PhiRho2),
('DgammaDspot', DgammaDspot),
('DeltaX', DeltaX),
('RiskNeutralDensity', RiskNeutralDensity),
('DvommaDvol', DvommaDvol),
('DgammaDtime', DgammaDtime),
('DvegaDtime', DvegaDtime),
('FuturesOptionsRho', FuturesOptionsRho),
('CarrySensitivity', CarrySensitivity),
('ForwardPrice', ForwardPrice),
('ForwardPoints', ForwardPoints)
) n ([Return Value], Value);
This produces the following result.
{"columns":[{"field":"Return Value"},{"field":"Value","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Return Value":"Price","Value":"0.23772850292138"},{"Return Value":"Delta","Value":"-0.0301930668993033"},{"Return Value":"Gamma","Value":"0.00317471327004881"},{"Return Value":"Theta","Value":"-0.00126385444988403"},{"Return Value":"Vega","Value":"0.022497932508421"},{"Return Value":"Rho","Value":"0.00221383454113433"},{"Return Value":"Lambda","Value":"0"},{"Return Value":"GammaP","Value":"0.00317471327004881"},{"Return Value":"DdeltaDtime","Value":"3.92842493694796E-05"},{"Return Value":"DdeltaDvol","Value":"-0.000297989639908014"},{"Return Value":"DdeltaDvolDvol","Value":"0.00024383030556574"},{"Return Value":"DgammaDvol","Value":"-0.000272046290494821"},{"Return Value":"DvegaDvol","Value":"-0.000919069889360191"},{"Return Value":"VegaP","Value":"0.0449958650168419"},{"Return Value":"PhiRho2","Value":"-0.00340247700403862"},{"Return Value":"DgammaDspot","Value":"-0.000222105278613327"},{"Return Value":"DeltaX","Value":"-0.0266271393552231"},{"Return Value":"RiskNeutralDensity","Value":"0.00228675689495361"},{"Return Value":"DvommaDvol","Value":"-9.85342348596985E-05"},{"Return Value":"DgammaDtime","Value":"0.000287089396129649"},{"Return Value":"DvegaDtime","Value":"-0.00141711661051171"},{"Return Value":"FuturesOptionsRho","Value":"-0.00118864251510142"},{"Return Value":"CarrySensitivity","Value":"0.00340247700403862"},{"Return Value":"ForwardPrice","Value":"102.531512052443"},{"Return Value":"ForwardPoints","Value":"2.53151205244289"}]}
Use CROSS APPLY when there are many input rows.
SELECT n.bt,
n.K,
k.Price,
K.Delta,
K.Gamma,
K.Theta,
K.Vega,
K.Rho
FROM
(
VALUES
(1, 'C', 'DI', 'True', 100, 100, 92, 2, .50, .10, .05, .20),
(2, 'C', 'DI', 'True', 100, 100, 96, 2, .50, .10, .05, .20),
(3, 'C', 'DI', 'True', 100, 100, 100, 2, .50, .10, .05, .20),
(4, 'C', 'UI', 'True', 100, 100, 104, 2, .50, .10, .05, .20),
(5, 'C', 'UI', 'True', 100, 100, 108, 2, .50, .10, .05, .20),
(6, 'C', 'DO', 'True', 100, 100, 92, 2, .50, .10, .05, .20),
(7, 'C', 'DO', 'True', 100, 100, 96, 2, .50, .10, .05, .20),
(8, 'C', 'DO', 'True', 100, 100, 100, 2, .50, .10, .05, .20),
(9, 'C', 'UO', 'True', 100, 100, 104, 2, .50, .10, .05, .20),
(10, 'C', 'UO', 'True', 100, 100, 108, 2, .50, .10, .05, .20)
) n (rn, z, bt, cash, S, X, K, H, T, Rf, Rd, Vol)
CROSS APPLY wct.BinaryBarrierAndStrikePriceNGreeks(z, bt, cash, S, X, K, H, T,
Rf, Rd, Vol) k;
This produces the following results.
{"columns":[{"field":"bt"},{"field":"K","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Delta","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Gamma","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Theta","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Vega","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Rho","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"bt":"DI","K":"92","Price":"0.237728502921379","Delta":"-0.0301930668994421","Gamma":"0.00317472714783662","Theta":"-0.00126385444988394","Vega":"0.0224979325084196","Rho":"0.00221383454113017"},{"bt":"DI","K":"96","Price":"0.570360462148656","Delta":"-0.053730085422532","Gamma":"0.00352623485966319","Theta":"-0.00104406518983036","Vega":"0.0163564527330795","Rho":"0.00809391083174682"},{"bt":"DI","K":"100","Price":"1.03157979931309","Delta":"-0.0688400387482968","Gamma":"0.00212116990638833","Theta":"6.29847691193675E-05","Vega":"-0.00933911140782451","Rho":"0.0215252713884784"},{"bt":"UI","K":"104","Price":"1.0162240188112","Delta":"0.0570994198167973","Gamma":"-0.000804645239327328","Theta":"-6.39956083692272E-05","Vega":"-0.00711727178888122","Rho":"0.0216088218552235"},{"bt":"UI","K":"108","Price":"0.931418742753932","Delta":"0.0634616424943779","Gamma":"0.000115618625784464","Theta":"-0.000682278415296889","Vega":"0.00382553946788877","Rho":"0.0217358251535238"},{"bt":"DO","K":"92","Price":"0.793851296391708","Delta":"0.0835594086595615","Gamma":"-0.00410856904053958","Theta":"0.00132683921900334","Vega":"-0.0318370439162441","Rho":"0.0193114368473468"},{"bt":"DO","K":"96","Price":"0.461219337164432","Delta":"0.107096427182651","Gamma":"-0.0044600878545964","Theta":"0.00110704995894972","Vega":"-0.025695564140904","Rho":"0.0134313605567316"},{"bt":"DO","K":"100","Price":"0","Delta":"0.122206380508416","Gamma":"-0.00305502290132154","Theta":"0","Vega":"0","Rho":"0"},{"bt":"UO","K":"104","Price":"0.0153557805018901","Delta":"-0.003733078056678","Gamma":"-0.000129207755605876","Theta":"0.000126980377488484","Vega":"-0.00222183961894329","Rho":"-8.35504667562326E-05"},{"bt":"UO","K":"108","Price":"0.100161056559155","Delta":"-0.0100953007342586","Gamma":"-0.00104947162071767","Theta":"0.000745263184416256","Vega":"-0.0131646508757133","Rho":"-0.000210553765034316"}]}