SQL Server BinomialTree Function

Updated 2023-11-16 16:04:42.697000

Description

Use the table-valued function BinomialTree to return the option value, intrinsic value and underlying value for each node on a binomial tree for an American or European option.

Syntax

SELECT * FROM [westclintech].[wct].[BinomialTree](
  <@CallPut, nvarchar(4000),>
 ,<@AssetPrice, float,>
 ,<@StrikePrice, float,>
 ,<@TimeToMaturity, float,>
 ,<@RiskFreeRate, float,>
 ,<@DividendRate, float,>
 ,<@Volatility, float,>
 ,<@nSteps, int,>
 ,<@AmEur, 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. @Strike 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. @Time 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. @RiskFree 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, @Div should be the foreign risk-free interest rate. @Div 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.

@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.

Return Type

table

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).

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

If @DividendRate is NULL then @DividendRate is set to zero.

If @RiskFreeRate is NULL then @RiskFreeRate is set to zero.

Examples

Create the binomial tree for an American 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 10.

SELECT *
  FROM wct.BinomialTree('C', --@CallPut
                        99.5, --@AssetPrice
                        100, --@StrikePrice
                        datediff(d, '2012-09-04', '2012-12-15') / 365.0000, --@TimeToMaturity
                        .02, --@RiskFreeRate
                        .005, --@DividendRate
                        .20, --@Volatility
                        10, --@NumberOfSteps
                        'E' --@AmEur
    );

This produces the following result.

Since the purpose of the function is to show all the nodes it can return quite a number of rows. The number of row returned will be ((@NumberOfSteps + 2) / 2) * (@NumberOfSteps + 1).

In this example, we PIVOT the price values.

SELECT stepno,
       [0],
       [1],
       [2],
       [3],
       [4],
       [5],
       [6],
       [7],
       [8],
       [9],
       [10]
  FROM (   SELECT k.stepno,
                  k.node,
                  ROUND(k.price, 2) as price
             FROM wct.BinomialTree('C', --Call/Put
                                   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
                                   10, --Number of Steps
                                   'E') k ) d
  PIVOT (   SUM(price)
            FOR NODE IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) as P
 ORDER BY stepno;

This produces the following result.