Login     Register

        Contact Us     Search

XLeratorDB/financial-options Documentation

SQL Server Binomial Tree function for European option pricing


BinomialEuro

Updated: 04 Sep 2012


Use BinomialEuro to calculate the price, delta, gamma, theta, vega, rho, or lambda of a European option using the Binomial Tree option pricing formula.
Syntax
SELECT [wctOptions].[wct].[BinomialEuro] (
  <@CallPut, nvarchar(4000),>
 ,<@AssetPrice, float,>
 ,<@StrikePrice, float,>
 ,<@TimeToMaturity, float,>
 ,<@RiskFreeRate, float,>
 ,<@DividendRate, float,>
 ,<@Volatility, float,>
 ,<@NSteps, int,>
 ,<@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.
@nSteps
the number of steps in the binomial tree. @nSteps is an expression of type int or of a type that can be implicitly converted to int.
 
@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. @ReturnValue is not case-sensitive. The following values are acceptable for @ReturnValue.

@ReturnValue
Returns
'P', 'PRICE'
Price
'D', 'DELTA'
Delta
'G', 'GAMMA'
Gamma
'V', 'VEGA'
Vega
'T', 'THETA'
Theta
'R', 'RHO'
Rho
'L', 'LAMBDA'
Lambda

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).
·         @nSteps must be greater than 1 (@nSteps > 1).
·         If @ReturnValue is NULL, then @ReturnValue is set to 'P'.
·         If @DividendRate is NULL an error will be returned
·         If @RiskFreeRate is NULL an error will be returned
·         For pricing American options, use BjerksundStensland or BinomialAmerican.
·         To use the Black-Scholes-Merton method for European options, use BlackScholesMerton.
·         To get the implied volatility (given price), use BinomialEuroIV.
·         To calculate the price and all the Greeks, use BinomialPriceNGreeks.
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%. The number of steps is 100.
SELECT cast(wct.BinomialEuro(
      '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
      ,100        --Number of Steps
      ,NULL       --Return Value
      ) as money) as Price
This produces the following result.
                Price
---------------------
               4.1547
 
To calculate the delta for the same option:
SELECT cast(wct.BinomialEuro(
      '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
      ,100        --Number of Steps
      ,'D'        --Return Value
      ) as money) as Delta
 
This produces the following result.
                Delta
---------------------
               0.5172
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.BinomialEuro(
      '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
      ,100        --Number of Steps
      ,'P'        --Return Value
      ) as money) as [Call Price]
,cast(wct.BinomialEuro(
      '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
      ,100        --Number of Steps
      ,'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.6582                4.7384
                99.00                3.9064                4.4873
                99.50                4.1547                4.2362
               100.00                4.4029                3.9852
               100.50                4.6907                3.7737
               101.00                4.9785                3.5621
 


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service