SQL Server BlackScholesMertonPriceNGreeks Function

Updated 2023-11-16 16:47:08.450000

Description

Use the table-valued function BlackScholesMertonPriceNGreeks to calculate the price and other derivatives of a European option using the Black-Scholes-Merton option pricing formula.

Syntax

SELECT *
FROM [westclintech].[wct].[BlackScholesMertonPriceNGreeks] (
  <@CallPut, nvarchar(4000),>
 ,<@AssetPrice, float,>
 ,<@StrikePrice, 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.

@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 continuously compounded risk-free zero coupon 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 dividend zero-coupon 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.

Return Type

table

Remarks

@TimeToMaturity must be greater than zero (@TimeToMaturity > 0).

@AssetPrice must be greater than zero (@AssetPrice > 0).

@StrikePrice must be greater than zero (@StrikePrice > 0).

@Volatility must be greater than zero (@Volatility > 0).

If @DividendRate is NULL then @DividendRate = 0.

If @RiskFreeRate is NULL then @RiskFreeRate = 0.

For American-style options see BJERKSUNDSTENSLANDPRICENGREEKS.

Examples

Calculate the price and Greeks 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 *
FROM wct.BlackScholesMertonPriceNGreeks(   'C',                                                --Put/Call
                                           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
                                       ) k;

Here is the resultant table.

In this SELECT we unpivot the columns returned by the function for ease of viewing the results.

SELECT n.*
FROM wct.BlackScholesMertonPriceNGreeks(   'C',                                                --Put/Call
                                           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
                                       ) k
    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),
        ('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.

In the following SELECT, we will populate a derived table with some option information and then calculate the price and select Greeks for options contained therein. For demonstration purposes we have made lots of simplifying assumptions about the US and contra interest rates and the implied volatility. This just demonstrates the structure of the TSQL statement.

SET NOCOUNT ON;
/*Create a temporary table to store data*/
CREATE TABLE #opt
(
    z char(1),
    s money,
    ex datetime,
    vol money
);
/*Put data in the table*/
INSERT INTO #opt
VALUES
('C', 98, '2012-09-22', 0.23);
INSERT INTO #opt
VALUES
('P', 99, '2012-09-22', 0.15);
INSERT INTO #opt
VALUES
('C', 100, '2012-09-22', 0.15);
INSERT INTO #opt
VALUES
('C', 101, '2012-09-22', 0.21);
INSERT INTO #opt
VALUES
('P', 102, '2012-09-22', 0.19);
INSERT INTO #opt
VALUES
('P', 98, '2012-10-20', 0.18);
INSERT INTO #opt
VALUES
('P', 99, '2012-10-20', 0.24);
INSERT INTO #opt
VALUES
('C', 100, '2012-10-20', 0.21);
INSERT INTO #opt
VALUES
('P', 101, '2012-10-20', 0.25);
INSERT INTO #opt
VALUES
('P', 102, '2012-10-20', 0.25);
INSERT INTO #opt
VALUES
('P', 98, '2012-11-17', 0.17);
INSERT INTO #opt
VALUES
('C', 99, '2012-11-17', 0.24);
INSERT INTO #opt
VALUES
('P', 100, '2012-11-17', 0.17);
INSERT INTO #opt
VALUES
('P', 101, '2012-11-17', 0.25);
INSERT INTO #opt
VALUES
('P', 102, '2012-11-17', 0.22);
INSERT INTO #opt
VALUES
('C', 98, '2012-12-22', 0.21);
INSERT INTO #opt
VALUES
('C', 99, '2012-12-22', 0.19);
INSERT INTO #opt
VALUES
('C', 100, '2012-12-22', 0.21);
INSERT INTO #opt
VALUES
('C', 101, '2012-12-22', 0.15);
INSERT INTO #opt
VALUES
('P', 102, '2012-12-22', 0.22);
INSERT INTO #opt
VALUES
('C', 98, '2013-03-16', 0.24);
INSERT INTO #opt
VALUES
('C', 99, '2013-03-16', 0.17);
INSERT INTO #opt
VALUES
('P', 100, '2013-03-16', 0.2);
INSERT INTO #opt
VALUES
('C', 101, '2013-03-16', 0.19);
INSERT INTO #opt
VALUES
('C', 102, '2013-03-16', 0.21);
INSERT INTO #opt
VALUES
('P', 98, '2013-03-22', 0.16);
INSERT INTO #opt
VALUES
('P', 99, '2013-03-22', 0.25);
INSERT INTO #opt
VALUES
('P', 100, '2013-03-22', 0.23);
INSERT INTO #opt
VALUES
('C', 101, '2013-03-22', 0.22);
INSERT INTO #opt
VALUES
('P', 102, '2013-03-22', 0.16);
/*Select data*/
SELECT O.z,
       O.s as Strike,
       O.ex as exDate,
       O.vol as Volatility,
       Cast(k.Price as money) as Price,
       Cast(k.Delta as money) as Delta,
       Cast(k.Gamma as money) as Gamma,
       Cast(k.Theta as money) as Theta,
       Cast(k.Vega as money) as Vega,
       Cast(k.Rho as money) as Rho,
       Cast(k.Lambda as money) as Lambda
FROM #OPT O
    CROSS APPLY wct.BlackScholesMertonPriceNGreeks(   O.z,                                        --Put/Call
                                                      96.76,                                      --Asset Price
                                                      O.s,                                        --Strike Price
                                                      datediff(d, '2012-09-04', O.ex) / 365.0000, --Time-to-expiry
                                                      0.0569,                                     --Risk Free Rate
                                                      0.00284,                                    --Dividend Rate
                                                      O.vol                                       --Volatility
                                                  ) k;
/*Clean up table*/
DROP TABLE #opt;

This produces the following result.

In this example, we translate some of the Greek values into monetary values in order to anticipate how changes in volatility, underlying, and time might affect the profit (or loss) on a long call position with a notional value of 10,000,000, which was bought at 0.50.

DECLARE @notional as float;
DECLARE @position as float;
SET @position = .50;
SET @notional = 10000000;

SELECT n.S as Spot,
       CAST((k.Price - @position) * @notional / 100 as money) as [P/L],
       CAST(k.Delta * @notional / 100 as money) as Delta,
       CAST(k.Gamma * @notional / 100 as money) as Gamma,
       CAST(k.Vega * @notional / 100 as money) as Vega
FROM
(
    SELECT 122
    UNION ALL
    SELECT 123.50
    UNION ALL
    SELECT 124
    UNION ALL
    SELECT 124.50
    UNION ALL
    SELECT 125
    UNION ALL
    SELECT 125.5
    UNION ALL
    SELECT 126
    UNION ALL
    SELECT 126.50
    UNION ALL
    SELECT 127
) n(s)
    CROSS APPLY wct.BlackScholesMertonPriceNGreeks(   'C',
                                                      n.s,                                   --Asset price    
                                                      125,                                   --Strike   
                                                      Cast(7 as float) / cast(365 as float), --Time
                                                      0.000335699,                           --Continuously Compounded EURIBOR
                                                      0.001869966,                           --Continuously Compounded LIBOR
                                                      .09                                    --Volatility
                                                  ) k
ORDER BY 1 DESC;

This produces the following result.