Login     Register

        Contact Us     Search

XLeratorDB/financial-options Documentation

SQL Server function for price and Greeks for standard barrier options


StandardBarrier

Updated: 31 Oct 2013


Use StandardBarrier to calculate the price or Greeks of a European-style Knock-In or Knock-Out option. StandardBarrier valuations are based on the formulae published by Mark Rubinstein and Eric Reiner in 1991.
Syntax
SELECT [wctOptions].[wct].[StandardBarrier](
  <@CallPut, nvarchar(4000),>
 ,<@BarrierType, nvarchar(4000),>
 ,<@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.
@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 StandardBarrierPriceNGreeks. @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.
·         To calculate the price and all the Greeks use StandardBarrierPriceNGreeks.
Example
A down-and-in call

 

SELECT cast(wct.StandardBarrier(
      'C'         --PutCall
      ,'DI'       --BarrierType
      ,100        --Asset Price
      ,90         --Strike Price
      ,97         --Barrier
      ,2          --Rebate
      ,0.5        --Time-to-expiry
      ,.10        --Risk Free Rate
      ,.05        --Dividend Rate
      ,.20        --Volatility
      ,'P'        --Return Value
      ) as money) as Price
This produces the following result.
                Price
---------------------
               8.5951
 


This SELECT statement reproduces the table for a down-and-in call included in the original paper on Barrier Options by Rubinstein and Reiner, though the numerical values are different.
SELECT *
FROM (
      SELECT n.t
      ,n3.Barrier
      ,n2.Strike
      ,cast(wct.StandardBarrier(
            'C'         --PutCall
            ,'DI'       --BarrierType
            ,100        --Asset Price
            ,n2.Strike --Strike Price
            ,n3.Barrier --Barrier
            ,2                --Rebate
            ,n.t        --Time-to-expiry
            ,.10        --Risk Free Rate
            ,.05        --Dividend Rate
            ,.20        --Volatility
            ,'P'        --Return Value
            ) as money) as Price
      FROM (VALUES (0.5),(0.75),(1.00),(1.25),(1.50))n(t)
      CROSS APPLY(VALUES (90),(100),(110))n2(Strike)
      CROSS APPLY(VALUES (97),(100),(103))n3(Barrier)
      ) D
PIVOT(SUM(Price) for t in([0.5],[0.75],[1.00],[1.25],[1.50])) as P
ORDER BY 1,2

This produces the following result.




We can reproduce the table in its entirety with the following SQL.
SELECT *
FROM (
      SELECT n.t
      ,n5.Z
      ,n4.BarrierType
      ,n3.Barrier
      ,n2.Strike
      ,cast(wct.StandardBarrier(
             n5.Z            
            ,n4.BarrierType
            ,100       
            ,n2.Strike 
            ,n3.Barrier
            ,2               
            ,n.t       
            ,.10       
            ,.05       
            ,.20       
            ,'P'       
            ) as money) as Price
      FROM (VALUES (0.5),(0.75),(1.00),(1.25),(1.50))n(t)
      CROSS APPLY(VALUES (90),(100),(110))n2(Strike)
      CROSS APPLY(VALUES (97),(100),(103))n3(Barrier)
      CROSS APPLY(VALUES ('UI'),('DI'),('UO'),('DO'))n4(BarrierType)
      CROSS APPLY(VALUES ('C'),('P'))n5(Z)
      ) D
PIVOT(SUM(Price) for t in([0.5],[0.75],[1.00],[1.25],[1.50])) as P
ORDER BY 1,2

Here are the results of this query, reformatted for ease of viewing.

Z
BarrierType
Barrier
Strike
0.5
0.75
1
1.25
1.5
C
DI
97
90
8.5951
10.0461
11.3066
12.4324
13.4536
C
DI
97
100
3.9519
5.3719
6.6460
7.8070
8.8761
C
DI
97
110
1.6224
2.6220
3.6449
4.6461
5.6113
C
DI
100
90
13.1652
14.6055
15.8850
17.0379
18.0873
C
DI
100
100
6.7186
8.4494
9.9409
11.2685
12.4716
C
DI
100
110
2.8347
4.3556
5.7455
7.0265
8.2156
C
DI
103
90
13.1652
14.6055
15.8850
17.0379
18.0873
C
DI
103
100
6.7186
8.4494
9.9409
11.2685
12.4716
C
DI
103
110
2.8347
4.3556
5.7455
7.0265
8.2156
C
DO
97
90
6.5391
6.5202
6.5324
6.5534
6.5761
C
DO
97
100
4.7357
5.0383
5.2488
5.4093
5.5379
C
DO
97
110
3.1813
3.6944
4.0545
4.3283
4.5467
C
DO
100
90
2.0000
2.0000
2.0000
2.0000
2.0000
C
DO
100
100
2.0000
2.0000
2.0000
2.0000
2.0000
C
DO
100
110
2.0000
2.0000
2.0000
2.0000
2.0000
C
DO
103
90
2.0000
2.0000
2.0000
2.0000
2.0000
C
DO
103
100
2.0000
2.0000
2.0000
2.0000
2.0000
C
DO
103
110
2.0000
2.0000
2.0000
2.0000
2.0000
C
UI
97
90
13.1652
14.6055
15.8850
17.0379
18.0873
C
UI
97
100
6.7186
8.4494
9.9409
11.2685
12.4716
C
UI
97
110
2.8347
4.3556
5.7455
7.0265
8.2156
C
UI
100
90
13.1652
14.6055
15.8850
17.0379
18.0873
C
UI
100
100
6.7186
8.4494
9.9409
11.2685
12.4716
C
UI
100
110
2.8347
4.3556
5.7455
7.0265
8.2156
C
UI
103
90
13.1937
14.6826
15.9732
17.1263
18.1724
C
UI
103
100
6.9961
8.6648
10.1186
11.4202
12.6039
C
UI
103
110
3.1155
4.5728
5.9244
7.1789
8.3485
C
UO
97
90
2.0000
2.0000
2.0000
2.0000
2.0000
C
UO
97
100
2.0000
2.0000
2.0000
2.0000
2.0000
C
UO
97
110
2.0000
2.0000
2.0000
2.0000
2.0000
C
UO
100
90
2.0000
2.0000
2.0000
2.0000
2.0000
C
UO
100
100
2.0000
2.0000
2.0000
2.0000
2.0000
C
UO
100
110
2.0000
2.0000
2.0000
2.0000
2.0000
C
UO
103
90
1.9443
1.8895
1.8736
1.8692
1.8690
C
UO
103
100
1.6954
1.7512
1.7840
1.8060
1.8218
C
UO
103
110
1.6920
1.7494
1.7829
1.8052
1.8212
P
DI
97
90
1.6063
2.0797
2.4544
2.7503
2.9845
P
DI
97
100
4.6684
5.1991
5.5574
5.8050
5.9752
P
DI
97
110
10.0441
10.2427
10.3199
10.3241
10.2785
P
DI
100
90
1.2449
1.7829
2.1974
2.5213
2.7767
P
DI
100
100
4.3106
4.9043
5.3017
5.5769
5.7680
P
DI
100
110
9.9390
10.0879
10.1547
10.1598
10.1191
P
DI
103
90
1.2449
1.7829
2.1974
2.5213
2.7767
P
DI
103
100
4.3106
4.9043
5.3017
5.5769
5.7680
P
DI
103
110
9.9390
10.0879
10.1547
10.1598
10.1191
P
DO
97
90
1.6076
1.6641
1.6970
1.7189
1.7347
P
DO
97
100
1.6112
1.6660
1.6982
1.7198
1.7353
P
DO
97
110
1.8639
1.8060
1.7887
1.7837
1.7830
P
DO
100
90
2.0000
2.0000
2.0000
2.0000
2.0000
P
DO
100
100
2.0000
2.0000
2.0000
2.0000
2.0000
P
DO
100
110
2.0000
2.0000
2.0000
2.0000
2.0000
P
DO
103
90
2.0000
2.0000
2.0000
2.0000
2.0000
P
DO
103
100
2.0000
2.0000
2.0000
2.0000
2.0000
P
DO
103
110
2.0000
2.0000
2.0000
2.0000
2.0000
P
UI
97
90
1.2449
1.7829
2.1974
2.5213
2.7767
P
UI
97
100
4.3106
4.9043
5.3017
5.5769
5.7680
P
UI
97
110
9.9390
10.0879
10.1547
10.1598
10.1191
P
UI
100
90
1.2449
1.7829
2.1974
2.5213
2.7767
P
UI
100
100
4.3106
4.9043
5.3017
5.5769
5.7680
P
UI
100
110
9.9390
10.0879
10.1547
10.1598
10.1191
P
UI
103
90
0.8615
1.2201
1.5475
1.8292
2.0671
P
UI
103
100
2.7722
3.3936
3.8471
4.1857
4.4412
P
UI
103
110
7.0001
7.4930
7.8069
8.0072
8.1283
P
UO
97
90
2.0000
2.0000
2.0000
2.0000
2.0000
P
UO
97
100
2.0000
2.0000
2.0000
2.0000
2.0000
P
UO
97
110
2.0000
2.0000
2.0000
2.0000
2.0000
P
UO
100
90
2.0000
2.0000
2.0000
2.0000
2.0000
P
UO
100
100
2.0000
2.0000
2.0000
2.0000
2.0000
P
UO
100
110
2.0000
2.0000
2.0000
2.0000
2.0000
P
UO
103
90
2.3562
2.5295
2.6117
2.6497
2.6637
P
UO
103
100
3.5112
3.4773
3.4164
3.3488
3.2809
P
UO
103
110
4.9117
4.5616
4.3095
4.1103
3.9448



To calculate the delta for the first option:
SELECT wct.StandardBarrier(
      'C'         --PutCall
      ,'DI'       --BarrierType
      ,100        --Asset Price
      ,90         --Strike Price
      ,97         --Barrier
      ,2          --Rebate
      ,0.5        --Time-to-expiry
      ,.10        --Risk Free Rate
      ,.05        --Dividend Rate
      ,.20        --Volatility
      ,'D'        --Return Value
      ) as Delta

This produces the following result.
                 Delta
----------------------
    -0.649025843078022


In this example we show all of the return values for an option (which can be more easily done using
StandardBarrierPriceNGreeks).
SELECT n.rv as Description
,wct.StandardBarrier('C','DI',100,90,97,2,0.5,.10,.05,.20,n.rv) as Value
FROM (
      SELECT 'Price' UNION ALL
      SELECT 'Delta' UNION ALL
      SELECT 'Gamma' UNION ALL
      SELECT 'GammaX' UNION ALL
      SELECT 'Theta' UNION ALL
      SELECT 'Vega' UNION ALL
      SELECT 'Rho' UNION ALL
      SELECT 'RhoFuturesOption' UNION ALL
      SELECT 'Lambda' UNION ALL
      SELECT 'DdeltaDvol' UNION ALL
      SELECT 'DdeltaDvolDvol' UNION ALL
      SELECT 'DdeltaDtime' UNION ALL
      SELECT 'DgammaDvol' UNION ALL
      SELECT 'GammaP' UNION ALL
      SELECT 'DvegaDvol' UNION ALL
      SELECT 'VegaP' UNION ALL
      SELECT 'PhiRho2' UNION ALL
      SELECT 'CarrySensitivity' UNION ALL
      SELECT 'DgammaDspot' UNION ALL
      SELECT 'DeltaX' UNION ALL
      SELECT 'DvommaDvol' UNION ALL
      SELECT 'DvegaDtime' UNION ALL
      SELECT 'DgammaDtime')n(rv)

This produces the following result.
Description                       Value
---------------- ----------------------
Price                  8.59507043337689
Delta                -0.649025843078022
Gamma                 0.052092730129516
GammaX               0.0207577954824956
Theta               -0.0173153378580135
Vega                  0.258665904443234
Rho                   0.156984606562283
RhoFuturesOption    -0.0379626818828527
Lambda                -7.55114048347627
DdeltaDvol           0.0135552546964846
DdeltaDvolDvol       -0.650285444002208
DdeltaDtime        0.000306086723699144
DgammaDvol         -0.00416152983184759
GammaP                0.052092730129516
DvegaDvol          -0.00199687541169169
VegaP                 0.517331808886468
PhiRho2              -0.199959958365525
CarrySensitivity      0.199959958365525
DgammaDspot            12980.5178819264
DeltaX               -0.582783834275347
DvommaDvol         0.000564868690799969
DvegaDtime          -0.0699760427380625
DgammaDtime       -5.04370755206702E-05


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service