SQL Server BinaryBarrierAndStrike Function

Updated 2023-11-14 20:54:14.777000

Description

Use the scalar function BinaryBarrierAndStrike to calculate the price or Greeks 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.

Syntax

SELECT [westclintech].[wct].[BinaryBarrierAndStrike](
  <@CallPut, nvarchar(4000),>
 ,<@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

@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.

@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 BinaryBarrierAndStrikePriceNGreeks. @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 (@Rebate >= 0).

If @Rebate is NULL, then @Rebate = 0.

@BarrierPrice assumes continuous monitoring.

To convert a non-continuous @BarrierPrice use the AdjustedBarrier function.

Use BINARYBARRIERANDSTRIKEPRICENGREEKS to calculate the price and all the Greeks in a simple table-valued function.

Examples

In this example we calculate the theoretical value (price) of a down-and-in cash-or-nothing call option.

SELECT wct.BinaryBarrierAndStrike(   'C',    --CallPut
                                     '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.

This SQL returns delta for the same option.

SELECT wct.BinaryBarrierAndStrike(   'C',    --CallPut
                                     '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.

This SELECT statement reproduces the table for down-and-out and up-and-out cash-or-nothing call and put options from the Binary Options paper by Mark Rubenstein and Eric Reiner, July 31, 1991 .

SELECT *
FROM
(
    SELECT n3.z,
           n.T,
           n2.H,
           cast(wct.BinaryBarrierAndStrike(   n3.z,
                                              CASE
                                                  WHEN n2.H <= 100 THEN
                                                      'DO'
                                                  ELSE
                                                      'UO'
                                              END,
                                              'True',
                                              100,
                                              100,
                                              n2.H,
                                              2,
                                              n.T,
                                              .10,
                                              .05,
                                              .20,
                                              'P'
                                          ) as money) as Price
    FROM
    (
        VALUES
            (.05),
            (.50),
            (.95)
    ) n (T)
        CROSS APPLY
    (
        VALUES
            (92),
            (96),
            (100),
            (104),
            (108)
    ) n2 (H)
        CROSS APPLY
    (
        VALUES
            ('P'),
            ('C')
    ) n3 (z)
) D
PIVOT
(
    SUM(Price)
    for T in ([.05], [.50], [.95])
) as P
ORDER BY 1;

Here are the results of this query.

This SELECT statement reproduces the table for down-and-in and up-and-in cash-or-nothing call and put options from the Binary Options paper by Mark Rubenstein and Eric Reiner, July 31, 1991 .

SELECT *
FROM (
SELECT n3.z
,n.T
,n2.H
,CAST(wct.BinaryBarrierAndStrike(n3.z,CASE WHEN n2.H <= 100 THEN 'DI' ELSE 'UI' END,'True',100,100,n2.H,2,n.T,.10,.05,.20,'P') as money) as Price
FROM (VALUES (.05),(.50),(.95))n(T)
CROSS APPLY(VALUES (92),(96),(100),(104),(108))n2(H)
CROSS APPLY(VALUES ('P'),('C'))n3(z)
      ) D
PIVOT(SUM(Price) for T in([.05],[.50],[.95])) as P
ORDER BY 1;

Here are the results of this query.

This SELECT statement reproduces the table for down-and-out and up-and-out asset-or-nothing call and put options from the Binary Options paper by Mark Rubenstein and Eric Reiner, July 31, 1991.

SELECT *
FROM
(
    SELECT n3.z,
           n.T,
           n2.H,
           CAST(wct.BinaryBarrierAndStrike(   n3.z,
                                              CASE
                                                  WHEN n2.H <= 100 THEN
                                                      'DO'
                                                  ELSE
                                                      'UO'
                                              END,
                                              'False',
                                              100,
                                              100,
                                              n2.H,
                                              n2.H,
                                              n.T,
                                              .10,
                                              .05,
                                              .20,
                                              'P'
                                          ) as money) as Price
    FROM
    (
        VALUES
            (.05),
            (.50),
            (.95)
    ) n (T)
        CROSS APPLY
    (
        VALUES
            (92),
            (96),
            (100),
            (104),
            (108)
    ) n2 (H)
        CROSS APPLY
    (
        VALUES
            ('P'),
            ('C')
    ) n3 (z)
) D
PIVOT
(
    SUM(Price)
    for T in ([.05], [.50], [.95])
) as P
ORDER BY 1;

This produce the following result.