# SQL server function for pricing binary barrier options

BinaryBarrierOnly

Updated: 31 Oct 2013

Use BinaryBarrierOnly to calculate the price or Greeks for binary barrier options having payoffs that are received only at expiration. Down-and-in and up-and-in options have positive payoffs if the barrier is breached at some time before expiration. Down-and-out and up-and-out options have positive payoffs if the barrier is not breached before expiration.
BinaryBarrierOnly 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 5–12:
(5)    down-and-in cash-(at-expiry)-or-nothing;
(6)    up-and-in cash-(at-expiry)-or-nothing;
(7)    down-and-in asset-(at-expiry)-or-nothing;
(8)    up-and-in asset-(at-expiry)-or-nothing;
(9)    down-and-out cash-or-nothing;
(10)   up-and-out cash-or-nothing;
(11)   down-and-out asset-or-nothing; and
(12)   up-and-out asset-or-nothing.
Syntax
SELECT [wctOptions].[wct].[BinaryBarrierOnly](
<@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 BinaryBarrierOnlyPriceNGreeks. @ReturnValue is not case-sensitive. The following values are acceptable for @ReturnValue

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.
·         Use BinaryBarrierOnlyPriceNGreeks to calculate the price and all the Greeks.
Example
In this example we calculate the theoretical value (price) of a down-and-in cash-(at-expiry)-or-nothing option.
SELECT wct.BinaryBarrierOnly(
'DI'          --BarrierType
,'True'        --CashOrNothing
,100           --AssetPrice
,100           --StrikePrice
,92            --BarrierPrice
,2             --Rebate
,.50           --TimeToMaturity
,.10           --RiskFreeRate
,.05           --DividendRate
,.20           --Volatility
,'P'           --ReturnValue
) as Price
This produces the following result.
Price
----------------------
0.990650335835426
This SQL returns delta for the same option.
SELECT wct.BinaryBarrierOnly(
'DI'          --BarrierType
,'True'        --CashOrNothing
,100           --AssetPrice
,100           --StrikePrice
,92            --BarrierPrice
,2             --Rebate
,.50           --TimeToMaturity
,.10           --RiskFreeRate
,.05           --DividendRate
,.20           --Volatility
,'D'           --ReturnValue
) as 'Delta'
This produces the following result.
Delta
----------------------
-0.0921821992078131
This SELECT statement reproduces the table for cash-or-nothing where Call/Put = Both from the Binary Options paper by Mark Rubenstein and Eric Reiner, July 31, 1991.
SELECT *
FROM (
SELECT 'OUT' as [OUT/IN]
,n.T
,n2.H
,wct.BinaryBarrierOnly(CASE WHEN n2.H <= 100 THEN 'DO' ELSE 'UO' END,'True',100,100,n2.H,2,n.T,.10,.05,.20,'P') as Price
FROM (VALUES (.05),(.50),(.95))n(T)
CROSS APPLY(VALUES (92),(96),(100),(104),(108))n2(H)
UNION ALL
SELECT 'IN'
,n.T
,n2.H
,wct.BinaryBarrierOnly(CASE WHEN n2.H <= 100 THEN 'DI' ELSE 'UI' END,'True',100,100,n2.H,2,n.T,.10,.05,.20,'P') as Price
FROM (VALUES (.05),(.50),(.95))n(T)
CROSS APPLY(VALUES (92),(96),(100),(104),(108))n2(H)
) D
PIVOT(SUM(Price) for T in([.05],[.50],[.95])) as P
ORDER BY 1
Here are the results of this query, reformatted for ease of viewing.

 OUT/IN H 0.05 0.5 0.95 IN 92 0.116333 0.99065 1.13927 IN 96 0.697207 1.42435 1.46893 IN 100 1.99002 1.90246 1.81875 IN 104 0.779568 1.52952 1.57191 IN 108 0.179699 1.17943 1.3315 OUT 92 1.87369 0.911809 0.679479 OUT 96 1.29282 0.478108 0.349812 OUT 100 0 0 0 OUT 104 1.21046 0.372937 0.246837 OUT 108 1.81033 0.723031 0.487251

This SELECT statement reproduces the table for asset-or-nothing where Call/Put = Both from the Binary Options paper by Mark Rubenstein and Eric Reiner, July 31, 1991.
SELECT *
FROM (
SELECT 'OUT' as [OUT/IN]
,n.T
,n2.H
,wct.BinaryBarrierOnly(CASE WHEN n2.H <= 100 THEN 'DO' ELSE 'UO' END,'False',100,100,n2.H,n2.H,n.T,.10,.05,.20,'P') as Price
FROM (VALUES (.05),(.50),(.95))n(T)
CROSS APPLY(VALUES (92),(96),(100),(104),(108))n2(H)
UNION ALL
SELECT 'IN'
,n.T
,n2.H
,wct.BinaryBarrierOnly(CASE WHEN n2.H <= 100 THEN 'DI' ELSE 'UI'    END,'False',100,100,n2.H,n2.H,n.T,.10,.05,.20,'P') as Price
FROM (VALUES (.05),(.50),(.95))n(T)
CROSS APPLY(VALUES (92),(96),(100),(104),(108))n2(H)
) D
PIVOT(SUM(Price) for T in([.05],[.50],[.95])) as P
ORDER BY 1
Here are the results of this query, reformatted for ease of viewing.

 OUT/IN H 0.05 0.5 0.95 IN 92 5.35498 46.3014 54.2277 IN 96 33.5081 69.7471 73.4112 IN 100 99.7503 97.531 95.361 IN 104 40.59 81.1526 85.1258 IN 108 9.71098 64.7464 74.4635 OUT 92 94.3953 51.2296 41.1333 OUT 96 66.2422 27.7839 21.9498 OUT 100 0 0 0 OUT 104 59.1603 16.3783 10.2352 OUT 108 90.0393 32.7846 20.8976