BinomialTree

Updated 2023-11-16 16:04:42.697000

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

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.

Arguments

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

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

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

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

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

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

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

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

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

Return Type

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "0e7bbf85-1ce1-4546-99e2-b26834c3f0a4", "colName": "node", "colDatatype": "int", "colDesc": "The node within the step"}, {"id": "e44c4b93-06fe-42cc-b456-a281937e67da", "colName": "stepno", "colDatatype": "int", "colDesc": "The step number"}, {"id": "20b2339e-b331-4174-ab36-d522364787ba", "colName": "underlying", "colDatatype": "int", "colDesc": "the underlying value of the undelying asset at this step and node in the tree"}, {"id": "bdaa6be3-7d21-4927-8daa-75c945c243c2", "colName": "intrinsic", "colDatatype": "float", "colDesc": "the intrinsic value of the option at this step and node in the tree"}, {"id": "a8a0a5d9-1373-415e-ab9a-ab0c1b272abe", "colName": "price", "colDatatype": "float", "colDesc": "the theoretical value of the option at this step and node in the tree"}]}

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.

{"columns":[{"field":"node","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"stepno","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"underlying","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"intrinsic","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"node":"0","stepno":"0","underlying":"99.5","intrinsic":"0","price":"4.10331126546237"},{"node":"0","stepno":"1","underlying":"96.2283492892819","intrinsic":"0","price":"2.39274557660086"},{"node":"1","stepno":"1","underlying":"102.882882987402","intrinsic":"2.88288298740162","price":"5.83283543020053"},{"node":"0","stepno":"2","underlying":"93.0642734365833","intrinsic":"0","price":"1.18123638510604"},{"node":"1","stepno":"2","underlying":"99.5","intrinsic":"0","price":"3.61710563855476"},{"node":"2","stepno":"2","underlying":"106.380780018084","intrinsic":"6.38078001808418","price":"8.07370383125967"},{"node":"0","stepno":"3","underlying":"90.0042352825002","intrinsic":"0","price":"0.444198036800946"},{"node":"1","stepno":"3","underlying":"96.2283492892819","intrinsic":"0","price":"1.92578461228176"},{"node":"2","stepno":"3","underlying":"102.882882987402","intrinsic":"2.88288298740162","price":"5.326677765603"},{"node":"3","stepno":"3","underlying":"109.997601434262","intrinsic":"9.99760143426199","price":"10.8528420254086"},{"node":"0","stepno":"4","underlying":"87.0448139726546","intrinsic":"0","price":"0.0969012568653358"},{"node":"1","stepno":"4","underlying":"93.0642734365833","intrinsic":"0","price":"0.794907303249054"},{"node":"2","stepno":"4","underlying":"99.5","intrinsic":"0","price":"3.06831200084976"},{"node":"3","stepno":"4","underlying":"106.380780018084","intrinsic":"6.38078001808418","price":"7.60997152588158"},{"node":"4","stepno":"4","underlying":"113.737390525186","intrinsic":"13.7373905251861","price":"14.1351051153859"},{"node":"0","stepno":"5","underlying":"84.1827011334791","intrinsic":"0","price":"0"},{"node":"1","stepno":"5","underlying":"90.0042352825002","intrinsic":"0","price":"0.194724293659481"},{"node":"2","stepno":"5","underlying":"96.2283492892819","intrinsic":"0","price":"1.40101823393864"},{"node":"3","stepno":"5","underlying":"102.882882987402","intrinsic":"2.88288298740162","price":"4.75303909409562"},{"node":"4","stepno":"5","underlying":"109.997601434262","intrinsic":"9.99760143426199","price":"10.4994174902749"},{"node":"5","stepno":"5","underlying":"117.604328047187","intrinsic":"17.6043280471872","price":"17.8171663328575"},{"node":"0","stepno":"6","underlying":"81.4146971737452","intrinsic":"0","price":"0"},{"node":"1","stepno":"6","underlying":"87.0448139726546","intrinsic":"0","price":"0"},{"node":"2","stepno":"6","underlying":"93.0642734365833","intrinsic":"0","price":"0.391300915672107"},{"node":"3","stepno":"6","underlying":"99.5","intrinsic":"0","price":"2.42077991522551"},{"node":"4","stepno":"6","underlying":"106.380780018084","intrinsic":"6.38078001808418","price":"7.11020212856249"},{"node":"5","stepno":"6","underlying":"113.737390525186","intrinsic":"13.7373905251861","price":"13.9288564115091"},{"node":"6","stepno":"6","underlying":"121.602736897395","intrinsic":"21.6027368973946","price":"21.7581035454753"},{"node":"0","stepno":"7","underlying":"78.7377077077012","intrinsic":"0","price":"0"},{"node":"1","stepno":"7","underlying":"84.1827011334791","intrinsic":"0","price":"0"},{"node":"2","stepno":"7","underlying":"90.0042352825002","intrinsic":"0","price":"0"},{"node":"3","stepno":"7","underlying":"96.2283492892819","intrinsic":"0","price":"0.786324108452473"},{"node":"4","stepno":"7","underlying":"102.882882987402","intrinsic":"2.88288298740162","price":"4.07166648962555"},{"node":"5","stepno":"7","underlying":"109.997601434262","intrinsic":"9.99760143426199","price":"10.18221376055"},{"node":"6","stepno":"7","underlying":"117.604328047187","intrinsic":"17.6043280471872","price":"17.7225719623691"},{"node":"7","stepno":"7","underlying":"125.737086946356","intrinsic":"25.7370869463561","price":"25.8519225017229"},{"node":"0","stepno":"8","underlying":"76.1487400958196","intrinsic":"0","price":"0"},{"node":"1","stepno":"8","underlying":"81.4146971737452","intrinsic":"0","price":"0"},{"node":"2","stepno":"8","underlying":"87.0448139726546","intrinsic":"0","price":"0"},{"node":"3","stepno":"8","underlying":"93.0642734365833","intrinsic":"0","price":"0"},{"node":"4","stepno":"8","underlying":"99.5","intrinsic":"0","price":"1.5801281795407"},{"node":"5","stepno":"8","underlying":"106.380780018084","intrinsic":"6.38078001808418","price":"6.5886799160665"},{"node":"6","stepno":"8","underlying":"113.737390525186","intrinsic":"13.7373905251861","price":"13.8173291874142"},{"node":"7","stepno":"8","underlying":"121.602736897395","intrinsic":"21.6027368973946","price":"21.6804778795424"},{"node":"8","stepno":"8","underlying":"130.012000034962","intrinsic":"30.0120000349618","price":"30.0873913596105"},{"node":"0","stepno":"9","underlying":"73.6449000992892","intrinsic":"0","price":"0"},{"node":"1","stepno":"9","underlying":"78.7377077077012","intrinsic":"0","price":"0"},{"node":"2","stepno":"9","underlying":"84.1827011334791","intrinsic":"0","price":"0"},{"node":"3","stepno":"9","underlying":"90.0042352825002","intrinsic":"0","price":"0"},{"node":"4","stepno":"9","underlying":"96.2283492892819","intrinsic":"0","price":"0"},{"node":"5","stepno":"9","underlying":"102.882882987402","intrinsic":"2.88288298740162","price":"3.17528743801645"},{"node":"6","stepno":"9","underlying":"109.997601434262","intrinsic":"9.99760143426199","price":"10.0381077745853"},{"node":"7","stepno":"9","underlying":"117.604328047187","intrinsic":"17.6043280471872","price":"17.6437716040886"},{"node":"8","stepno":"9","underlying":"125.737086946356","intrinsic":"25.7370869463561","price":"25.7753942245699"},{"node":"9","stepno":"9","underlying":"134.432255141257","intrinsic":"34.4322551412567","price":"34.4693475630574"},{"node":"0","stepno":"10","underlying":"71.2233886445092","intrinsic":"0","price":"0"},{"node":"1","stepno":"10","underlying":"76.1487400958196","intrinsic":"0","price":"0"},{"node":"2","stepno":"10","underlying":"81.4146971737452","intrinsic":"0","price":"0"},{"node":"3","stepno":"10","underlying":"87.0448139726546","intrinsic":"0","price":"0"},{"node":"4","stepno":"10","underlying":"93.0642734365833","intrinsic":"0","price":"0"},{"node":"5","stepno":"10","underlying":"99.5","intrinsic":"0","price":"0"},{"node":"6","stepno":"10","underlying":"106.380780018084","intrinsic":"6.38078001808418","price":"6.38078001808418"},{"node":"7","stepno":"10","underlying":"113.737390525186","intrinsic":"13.7373905251861","price":"13.7373905251861"},{"node":"8","stepno":"10","underlying":"121.602736897395","intrinsic":"21.6027368973946","price":"21.6027368973946"},{"node":"9","stepno":"10","underlying":"130.012000034962","intrinsic":"30.0120000349618","price":"30.0120000349618"},{"node":"10","stepno":"10","underlying":"139.002793722919","intrinsic":"39.0027937229189","price":"39.0027937229189"}]}

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;