Login     Register

        Contact Us     Search

XLeratorDB/financial-options Documentation

SQL Server non-recombining tree function for equity options


NonRecombiningTree

Updated: 31 Mar 2014


Use NonRecombiningTree to calculate the price and Greeks of an American or European option paying discrete dividends using a non-recombining binomial tree as suggested in Options, Futures, and Other Derivatives, 8th Edition, by John C. Hull. The NonRecombiningTree calculation creates a new binomial tree at every node where there is a dividend payment, potentially creating millions of nodes and should be used sparingly. Primarily, the NonRecombiningTree function should only be used for American Call options where the dividend is very large in relation to the price of the asset, as the recombining tree calculation for dividend-paying stocks tends to underestimate the theoretical value.
Syntax
SELECT [wctOptions].[wct].[NonRecombiningTree](
  <@CallPut, nvarchar(4000),>
 ,<@AmEur, nvarchar(4000),>
 ,<@AssetPrice, float,>
 ,<@StrikePrice, float,>
 ,<@TimeToMaturity, float,>
 ,<@RiskFreeRate, float,>
 ,<@DividendRate, float,>
 ,<@Dividend_RangeQuery, nvarchar(max),>
 ,<@Volatility, float,>
 ,<@NumberOfSteps, int,>
 ,<@RV, 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.
@AmEur
identifies the option as being American ('A') or European ('E'). @AmEur 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 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 annualized, continuously compounded zero-coupon dividend rate over the life of the option, used in addition to the discrete dividends. @DividendRate is an expression of type float or of a type that can be implicitly converted to float.
@Dividend_RangeQuery
a string containing an SQL statement which, when executed, provides the function with the times and amounts of the dividends to be used in the calculation. The results of the SQL must contain exactly two columns, the first being the time value, as a float or as a value that implicitly converts to float, and the second being the dividend amount as float, or as a value that implicitly converts to float. @Dividend_RangeQuery is an expression of type nvarchar or of a type that can be implicitly converted to nvarchar.
@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.
@NumberOfSteps
the number of steps in the binomial tree. @NumberOfSteps is an expression of type int or of a type that can be implicitly converted to int.
 
@RV
identifies the calculation to be performed. @RV is an expression of type nvarchar or of a type that can be implicitly converted to nvarchar. @RV is not case-sensitive. The following values are acceptable for @RV.

@RV
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
'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).
·         @NumberOfSteps must be greater than 1 (@NumberOfSteps > 1)
·         If @RV is NULL, then @RV is set to 'P'.
·         Negative time values returned by @Dividend_RangeQuery are ignored.
·         Time values returned by @Dividend_RangeQuery that are greater than @TimeToMaturity are ignored.
·         If @RiskFreeRate is NULL then @RiskFreeRate is set to zero.
·         If @DividendRate is NULL then @DividendRate is set to zero.
·         To get the implied volatility (given price), use NonRecombiningTreeIV.
·         Use the table-valued function NonRecombiningTreePriceNGreeks to calculate the price, delta, gamma, theta, vega, rho, and lambda in a single SELECT statement.
Example
Calculate the price for an American call option expiring in one year with asset price of 100, a strike price of 100 and a volatility of 30%. The risk-free rate is 2.75%. A single dividend of 50 will be paid in 10 months' time. The number of steps is 100.
SELECT wct.NonRecombiningTree(
       'C'        --Put/Call
      ,'A'        --American/European
      ,100        --Asset Price
      ,100        --Strike Price
      ,1          --Time-to-maturity
      ,.0275      --Risk-free rate
      ,NULL       --Dividend rate
      ,'SELECT 0.83333333333, 50'   --Discrete Dividends
      ,0.30       --Volatility
      ,100        --Number of Steps
      ,'P'        --Return Value
      ) as PRICE
This produces the following result.
                 PRICE
----------------------
      11.9443454101102

 

See Also

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service