Login     Register

        Contact Us     Search

XLeratorDB/financial-options Documentation

SQL Server function for pricing binary barries options


BinaryBarrierPayoutAtHit

Updated: 31 Oct 2013


Use BinaryBarrierPayoutAtHit to calculate the price or Greeks for binary barrier options having payoffs that are received the moment that the barrier is breached. BinaryBarrierPayoutAtHit 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 1–4:
(1)    down-and-in cash-(at-hit)-or-nothing;
(2)    up-and-in cash-(at-hit)-or-nothing;
(3)    down-and-in asset-(at-hit)-or-nothing; and
(4)    up-and-in asset-(at-hit)-or-nothing.
Syntax
SELECT [wctOptions].[wct].[BinaryBarrierPayoutAtHit](
  <@BarrierType, nvarchar(4000),>
 ,<@CashOrNothing, bit,>
 ,<@AssetPrice, float,>
 ,<@StrikePrice, float,>
 ,<@BarrierPrice, float,>
 ,<@Rebate, float,>
 ,<@TimeToMaturity, float,>
 ,<@RiskFreeRate, float,>
 ,<@DividendRate, float,>
 ,<@Volatility, float,>
 ,<@ReturnValue, nvarchar(4000),>)
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.
@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.
@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.
@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.
@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.
@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.
@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.
@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.
@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.
@ReturnValue
identifies the calculation to be performed. @ReturnValue is an expression of type nvarchar or of a type that can be implicitly converted to nvarchar. For a full description of the return values, see BinaryBarrierPayoutAtHitPriceNGreeks. @ReturnValue is not case-sensitive. The following values are acceptable for @ReturnValue

@ReturnValue
Returns
'P','PRICE'
Price
'D','DELTA'
Delta
'G','GAMMA'
Gamma
'T','THETA'
Theta
'V','VEGA'
Vega
'R','RHO'
Rho
'L','LAMBDA'
Lambda
'DDDV','VANNA','DVEGADSPOT','DDELTADVOL'
DdeltaDvol
'DVV','DDELTADVOLDVOL'
DdeltaDvolDvol
'DT','CHARM','DDELTADTIME'
DdeltaDtime
'GV','ZOMMA','DGAMMADVOL'
DgammaDvol
'GP','GAMMAP'
GammaP
'DVDV','VOMMA','VOLGA','DVEGADVOL'
DvegaDvol
'VP','VEGAP'
VegaP
'PR2','PHIRHO2'
PhiRho2
'S','SPEED','DGAMMADSPOT'
DgammaDspot
'DX','DELTAX'
Delta X
'GX','GAMMAX','DX','RND','RISKNEUTRALDENSITY'
Risk Neutral Density
'VVV','ULTIMA','DVOMMADVOL'
DvommaDvol
'VT','VETA','DVEGADTIME'
DvegaDtime
'GT','COLOR','DGAMMADTIME'
DgammaDtime
'FR','RHOFUTURESOPTIONS','FUTURESOPTIONSRHO'
Futures Options Rho
'B','CARRYSENSITIVITY'
Carry Sensitivity

Return Type
float
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.
Example
In this example we calculate the theoretical value (price) of a down-and-in cash-(at-hit)-or-nothing option.
SELECT wct.BinaryBarrierPayoutAtHit(
 'DI'       --BarrierType
,'True'     --CashOrNothing
,100       --AssetPrice
,100        --StrikePrice
,92         --BarrierPrice
,2          --Rebate
,.05        --TimeToMaturity
,.10        --RiskFreeRate
,.05        --DividendRate
,.20        --Volatility
,'P' --ReturnValue
) as Price
This produces the following result.
                 Price
----------------------
     0.116492416924189
This SQL returns delta for the same option.
SELECT wct.BinaryBarrierPayoutAtHit(
 'DI'       --BarrierType
,'True'     --CashOrNothing
,100        --AssetPrice
,100        --StrikePrice
,92         --BarrierPrice
,2          --Rebate
,.05        --TimeToMaturity
,.10        --RiskFreeRate
,.05        --DividendRate
,.20        --Volatility
,'D'        --ReturnValue
) as Delta
This produces the following result.
                 Delta
----------------------
    -0.059603371860284
This SELECT statement reproduces the table for cash-(at-hit)-or-nothing options from the Binary Options paper by Mark Rubenstein and Eric Reiner, July 31, 1991.
SELECT *
FROM (
      SELECT n.T
      ,n2.H
      ,Cast(wct.BinaryBarrierPayoutAtHit(
       CASE
            WHEN n2.H <= 100 THEN 'DI'
            ELSE 'UI'
       END --BarrierType
      ,'True'--CashOrNothing
      ,100 --AssetPrice
      ,100 --StrikePrice
      ,n2.H --BarrierPrice
      ,2    --Rebate
      ,n.--TimeToMaturity
      ,.10 --RiskFreeRate
      ,.05 --DividendRate
      ,.20 --Volatility
      ,'P' --ReturnValue
      ) as money) as Price
      FROM (VALUES (.05),(.16),(.28),(.39),(.50),(.61),(.72),(.84),(.95))n(T)
      CROSS APPLY(VALUES (92),(96),(100),(104),(108))n2(H)
      ) D
PIVOT(SUM(Price) for T in([.05],[.16],[.28],[.39],[.50],[.61],[.72],[.84],[.95])) as P
ORDER BY 1
Here are the results of this query, reformatted for ease of viewing.

H
0.05
0.16
0.28
0.39
0.5
0.61
0.72
0.84
0.95
92
0.1165
0.5532
0.7981
0.9314
1.0227
1.0899
1.1418
1.1866
1.2200
96
0.6990
1.1761
1.3463
1.4287
1.4824
1.5207
1.5496
1.5743
1.5925
100
2.0000
2.0000
2.0000
2.0000
2.0000
2.0000
2.0000
2.0000
2.0000
104
0.7816
1.2780
1.4530
1.5375
1.5924
1.6316
1.6612
1.6864
1.7050
108
0.1800
0.7056
0.9767
1.1211
1.2190
1.2905
1.3456
1.3930
1.4283

This SELECT statement reproduces the table for asset-(at-hit)-or-nothing options from the Binary Options paper by Mark Rubenstein and Eric Reiner, July 31, 1991.
SELECT *
FROM (
      SELECT n.T
      ,n2.H
      ,Cast(wct.BinaryBarrierPayoutAtHit(
       CASE
            WHEN n2.H <= 100 THEN 'DI'
            ELSE 'UI'
       END --BarrierType
      ,'False'--CashOrNothing
      ,100 --AssetPrice
      ,100 --StrikePrice
      ,n2.H --BarrierPrice
      ,n2.H --Rebate
      ,n.--TimeToMaturity
      ,.10 --RiskFreeRate
      ,.05 --DividendRate
      ,.20 --Volatility
      ,'P' --ReturnValue
      ) as money) as Price
      FROM (VALUES (.05),(.16),(.28),(.39),(.50),(.61),(.72),(.84),(.95))n(T)
      CROSS APPLY(VALUES (92),(96),(100),(104),(108))n2(H)
      ) D
PIVOT(SUM(Price) for T in([.05],[.16],[.28],[.39],[.50],[.61],[.72],[.84],[.95])) as P
ORDER BY 1
Here are the results of this query, reformatted for ease of viewing.

H
0.05
0.16
0.28
0.39
0.5
0.61
0.72
0.84
0.95
92
5.3587
25.4474
36.7118
42.8451
47.0464
50.1365
52.5215
54.5823
56.1197
96
33.5505
56.4534
64.6226
68.5798
71.1553
72.9929
74.3831
75.5666
76.4399
100
100.00
100.00
100.00
100.00
100.00
100.00
100.00
100.00
100.00
104
40.6425
66.4543
75.5535
79.9486
82.8055
84.8424
86.3826
87.6934
88.6603
108
9.7182
38.1003
52.7429
60.5374
65.8239
69.6891
72.6609
75.2212
77.1272

 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service