Login     Register

        Contact Us     Search

XLeratorDB/financial-options Documentation

SQL Server Black Scholes Merton function for European option pricing


BlackScholesMerton

Updated: 31 Oct 2013


Use BlackScholesMerton to calculate the price or Greeks of a European option using the Black-Scholes-Merton option pricing formula. The price and Greeks are calculated in closed form.
Syntax
SELECT [wctOptions].[wct].[BlackScholesMerton] (
  <@CallPut, nvarchar(4000),>
 ,<@AssetPrice, float,>
 ,<@StrikePrice, 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.
@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.
@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 annualized, continuously compounded risk-free rate of return 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 annualized, continuously compounded dividend rate over the life of the option. For currency options, @DividendRate should be the foreign risk-free interest 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 BlackScholesMertonPriceNGreeks. @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
'RND','RISKNEUTRALDENSITY'
Risk Neutral Density
'VVV','ULTIMA','DVOMMADVOL'
DvommaDvol
'VT','VETA','DVEGADTIME'
DvegaDtime
'GT','COLOR','DGAMMADTIME'
DgammaDtime

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.
·         For pricing American options, use BjerksundStensland or BinomialAmerican.
·         To use the Binomial Tree method for European options, use BinomialEuro.
·         To get the implied volatility (given price), use BlackScholesMertonIV.
·         To calculate the price and all the Greeks, use BlackScholesMertonPriceNGreeks.
Example
Calculate the price for a call option on 2012-09-04, expiring on 2012-12-15, with a current asset price of 99.5, a strike price of 100 and a volatility of 20%. The risk free rate is 2% and the dividend rate is 0.5%.
SELECT cast(wct.BlackScholesMerton(
      'C'         --PutCall
      ,99.5       --Asset Price
      ,100        --Strike Price
      ,datediff(d,'2012-09-04','2012-12-15') / 365.0000     --Time-to-expiry
      ,.02        --Risk Free Rate
      ,.005       --Dividend Rate
      ,.20        --Volatility
      ,'P'        --Return Value
      ) as money) as Price

This produces the following result.
                Price
---------------------
                 4.15
 


To calculate the delta for the same option:
SELECT cast(wct.BlackScholesMerton(
      'C'         --PutCall
      ,99.5       --Asset Price
      ,100        --Strike Price
      ,datediff(d,'2012-09-04','2012-12-15') / 365.0000     --Time-to-expiry
      ,.02        --Risk Free Rate
      ,.005       --Dividend Rate
      ,.20        --Volatility
      ,'D'        --Return Value
      ) as money) as Delta

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


In this example we calculate the Call and Put prices for the same option with a series of different asset prices.
SELECT cast(n.S as money) as [Underlying]
,cast(wct.BlackScholesMerton(
      'C'         --PutCall
      ,n.s        --Asset Price
      ,100        --Strike Price
      ,datediff(d,'2012-09-04','2012-12-15') / 365.0000     --Time-to-expiry
      ,.02        --Risk Free Rate
      ,.005       --Dividend Rate
      ,.20        --Volatility
      ,'P'        --Return Value
      ) as money) as [Call Price]
,cast(wct.BlackScholesMerton(
      'P'         --PutCall
      ,n.s        --Asset Price
      ,100        --Strike Price
      ,datediff(d,'2012-09-04','2012-12-15') / 365.0000     --Time-to-expiry
      ,.02        --Risk Free Rate
      ,.005       --Dividend Rate
      ,.20        --Volatility
      ,'P'        --Return Value
      ) as money) as [Put Price]
FROM (
      SELECT 98.5 UNION ALL
      SELECT 99.0 UNION ALL
      SELECT 99.5 UNION ALL
      SELECT 100 UNION ALL
      SELECT 100.5 UNION ALL
      SELECT 101
      ) n(S)

This produces the following result.
           Underlying            Call Price             Put Price
--------------------- --------------------- ---------------------
                98.50                3.6518                4.7319
                99.00                3.8961                 4.477
                99.50                  4.15                4.2316
               100.00                4.4134                3.9957
               100.50                4.6861                3.7691
               101.00                4.9681                3.5518
 


In this example we show all of the return values for an option (which can be more easily done using
BlackScholesMertonPriceNGreeks).
SELECT n.rv as [Description]
,wct.BlackScholesMerton(
      'C'               --PutCall
      ,99.5       --Asset Price
      ,100        --Strike Price
      ,datediff(d,'2012-09-04','2012-12-15') / 365.0000     --Time-to-expiry
      ,.02        --Risk Free Rate
      ,.005       --Dividend Rate
      ,.20        --Volatility
      ,n.rv       --Return Value
      ) as [Value]
FROM (
      SELECT 'Price' UNION ALL
      SELECT 'Delta' UNION ALL
      SELECT 'Gamma' UNION ALL
      SELECT 'Theta' UNION ALL
      SELECT 'Vega' UNION ALL
      SELECT 'Rho' 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 'DgammaDspot' UNION ALL
      SELECT 'DeltaX' UNION ALL
      SELECT 'RiskNeutralDensity' UNION ALL
      SELECT 'DvommaDvol' UNION ALL
      SELECT 'DvegaDtime' UNION ALL
      SELECT 'DgammaDtime')n(rv)

This produces the following result.
Description                         Value
------------------ ----------------------
Price                    4.15002801072982
Delta                   0.517263007462764
Gamma                  0.0378316108959817
Theta                  -0.022410582448993
Vega                    0.209333286210361
Rho                     0.132230118433953
Lambda                   12.4017643036327
DdeltaDvol            0.00120640336138375
DdeltaDvolDvol      -1.56126607450338E-05
DdeltaDtime         -0.000265884058435201
DgammaDvol           -0.00189675260903188
GammaP                0.0376424528415018
DvegaDvol           -2.86185329331919E-05
VegaP                   0.418666572420722
PhiRho2                -0.143827456785512
DgammaDspot         -0.000542407991020922
DeltaX                 -0.473176412318152
RiskNeutralDensity     0.0374542405772943
DvommaDvol          -1.55316913764812E-06
DvegaDtime             -0.101680055475941
DgammaDtime          0.000187137587406527
 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service