Login     Register

        Contact Us     Search

XLeratorDB/financial-options Documentation

SQL Server functioun for price and Greeks for standard barrier options


StandardBarrierPriceNGreeks

Updated: 31 Oct 2013


Use the table-valued function StandardBarrierPriceNGreeks to calculate the price and Greeks of a European-style Knock-In or Knock-Out option. StandardBarrierPriceNGreeks valuations are based on the formulae published by Mark Rubinstein and Eric Reiner in 1991.
Syntax
SELECT * FROM [wctOptions].[wct].[StandardBarrierPriceNGreeks](
  <@CallPut, nvarchar(4000),>
 ,<@BarrierType, nvarchar(4000),>
 ,<@AssetPrice, float,>
 ,<@StrikePrice, float,>
 ,<@BarrierPrice, float,>
 ,<@Rebate, float,>
 ,<@TimeToMaturity, float,>
 ,<@RiskFreeRate, float,>
 ,<@DividendRate, float,>
 ,<@Volatility, float,>)
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.
Return Type
RETURNS TABLE (
      [Price] [float] NULL,
      [Delta] [float] NULL,
      [Gamma] [float] NULL,
      [Theta] [float] NULL,
      [Vega] [float] NULL,
      [Rho] [float] NULL,
      [Lambda] [float] NULL,
      [GammaP] [float] NULL,
      [DdeltaDtime] [float] NULL,
      [DdeltaDvol] [float] NULL,
      [DdeltaDvolDvol] [float] NULL,
      [DgammaDvol] [float] NULL,
      [DvegaDvol] [float] NULL,
      [VegaP] [float] NULL,
      [PhiRho2] [float] NULL,
      [RhoFuturesOption] [float] NULL,
      [CarrySensitivity] [float] NULL,
      [DgammaDspot] [float] NULL,
      [DeltaX] [float] NULL,
      [RiskNeutralDensity] [float] NULL,
      [DvommaDvol] [float] NULL,
      [DgammaDtime] [float] NULL,
      [DvegaDtime] [float] NULL,
      [ForwardPrice] [float] NULL,
      [ForwardPoints] [float] NULL
Unless otherwise specified, the columns returned by StandardBarrierPriceNGreeks are measuring the sensitivity of the theoretical value of the option.

Column
Description
Price
The theoretical value of the option.
Delta
The sensitivity to small changes in the asset price; the first derivative of the option with respect to price.
Gamma
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.
Theta
The sensitivity to small changes in time; the first derivative of the option with respect to time.
Vega
The sensitivity to small changes in volatility; the first derivative of the option with respect to volatility.
Rho
The sensitivity to small changes in the risk-free rate; the first derivative of the option with respect to the risk-free rate.
Lambda
Delta multiplied by the asset price divided by the theoretical value. If the theoretical value is zero, then lambda is set to zero.
GammaP
Gamma multiplied by asset price divided by strike price.
DdeltaDtime
The instantaneous change in delta over the passage of time; the second derivative, once to asset price and once to time.
DdeltaDvol
The sensitivity of delta with respect to volatility; the second derivative, once to asset price and once to volatility.
DdeltaDvolDvol
The second derivative of delta with respect to volatility; the third derivative, once to asset price and twice to volatility.
DgammaDvol
The rate of change in gamma with respect to changes in volatility; the third derivative, twice to asset price and once to volatility.
DvegaDvol
The rate of change to Vega as the volatility changes; the second derivative with respect to volatility.
VegaP
The percentage change in theoretical value for a 10 per cent change in volatility.
PhiRho2
The sensitivity to a change in the dividend yield (foreign interest rate for a currency option); the first derivative with respect to dividend yield.
RhoFuturesOption
The sensitivity to a change in the cost-of carry; the first derivative with respect to the cost-of-carry.
CarrySensitivity
-PhiRho2
DgammaDspot
The rate of change in gamma with respect to change in the asset price; the third derivative with respect to price.
DeltaX
The sensitivity to a change in the strike price; the first derivative with respect to strike price.
RiskNeutralDensity
The sensitivity of DeltaX; the second derivative with respect to strike price.
DvommaDvol
The sensitivity of DvegaDvol to changes in volatility; the third derivative, twice to asset price and once to volatility.
DgammaDtime
The sensitivity of Gamma to the passage of time; the third derivative, twice to asset price and once to time.
DvegaDtime
The sensitivity of Vega to the passage of time; the second derivative, once to volatility and once to time.
ForwardPrice
The value of the underlying asset at the expiration date of the option.
ForwardPoints
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.
·         To calculate a single return value use StandardBarrier.
Example
A down-and-in call
SELECT *
FROM wct.StandardBarrierPriceNGreeks(
      '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
      )

Here are the first few columns of the resultant table.




In this SELECT we un-pivot the columns returned by the function for ease of viewing the results.
SELECT n.*
FROM wct.StandardBarrierPriceNGreeks(
   '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
      )
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)
,('RhoFuturesOption',RhoFuturesOption)
,('CarrySenstivity',CarrySensitivity)
,('DgammaDspot',DgammaDspot)
,('DeltaX',DeltaX)
,('RiskNeutralDensity',RiskNeutralDensity)
,('DvommaDvol',DvommaDvol)
,('DgammaDtime',DgammaDtime)
,('DvegaDtime',DvegaDtime)
,('ForwardPrice',ForwardPrice)
,('ForwardPoints',ForwardPoints)
)n([Return Value], Value)

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


Use CROSS APPLY to with
StandardBarrierPriceNGreeks when there are multiple rows of input data.
SELECT n.rn
,k.*
FROM (VALUES
    (1,'C','DI',100,97,95,2,0.75,0.005,0.009,0.16)
   ,(2,'P','DI',100,97,95,2,0.75,0.005,0.009,0.16)
   ,(3,'C','DO',100,97,95,2,0.75,0.005,0.009,0.16)
   ,(4,'P','DO',100,97,95,2,0.75,0.005,0.009,0.16)
   ,(5,'C','UI',100,103,105,2,0.75,0.005,0.009,0.16)
   ,(6,'P','UI',100,103,105,2,0.75,0.005,0.009,0.16)
   ,(7,'C','UO',100,103,105,2,0.75,0.005,0.009,0.16)
   ,(8,'P','UO',100,103,105,2,0.75,0.005,0.009,0.16)
   )n(rn,z,bt,S,X,H,K,T,Rf,Rd,Vol)
CROSS APPLY wct.StandardBarrierPriceNGreeks(z,bt,S,X,H,K,T,Rf,Rd,Vol)k

Here are the first few columns of (re-formatted) data in the resultant table.

rn
Price
Delta
Gamma
Theta
Vega
Rho
Lambda
GammaP
1
3.0785
-0.1703
0.0203
-0.0073
0.2571
0.1310
-5.5304
0.0203
2
4.6988
-0.2909
0.0250
-0.0090
0.2975
-0.3016
-6.1919
0.0250
3
5.7811
0.7711
0.0074
-0.0017
0.0742
0.2617
13.3384
0.0074
4
1.4704
-0.1015
0.0027
-0.0010
0.0338
-0.0304
-6.9007
0.0027
5
4.6802
0.3178
0.0252
-0.0084
0.3057
0.2600
6.7906
0.0252
6
3.4500
0.2268
0.0246
-0.0083
0.2793
-0.1727
6.5750
0.0246
7
1.4028
0.1140
0.0030
-0.0009
0.0330
0.0259
8.1272
0.0030
8
5.9202
-0.7883
0.0037
-0.0021
0.0594
-0.3110
-13.3153
0.0037

 
 

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service