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