SQL Server BjerksundStensland Function

Updated 2023-11-16 16:17:05.757000

Description

Use BjerksundStensland to calculate the price or Greeks of an American-style option using the Bjerksund and Stensland 2002 option pricing formula.

Syntax

SELECT [westclintech].[wct].[BjerksundStensland] (
  <@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 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 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 BjerksundStenslandPriceNGreeks. @ReturnValue is not case-sensitive. The following values are acceptable for @ReturnValue:

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 then @RiskFreeRate = 0.

For pricing European options, use BLACKSCHOLESMERTON or BINOMIALEURO.

To use the binomial method for American options, use BINOMIALAMERICAN.

To get the implied volatility (given price), use BJERKSUNDSTENSLANDIV.

To calculate the price and all the Greeks, use BJERKSUNDSTENSLANDPRICENGREEKS.

Examples

Calculate the price for a put 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 ROUND(wct.BjerksundStensland('P', --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
                                    NULL --Return Value
             ),
             4) as Price;

This produces the following result.

To calculate the delta for the same option:

SELECT ROUND(wct.BjerksundStensland('P', --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
             ),
             4) as Delta;

This produces the following result.

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.BjerksundStensland('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.BjerksundStensland('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.

In this example we show all of the return values for an option (which can be more easily done using BjerksundStenslandPriceNGreeks.

SELECT n.rv as [Description],
       wct.BjerksundStensland('P', --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.