ProportionalDividendsTree

Updated 2023-11-16 23:10:41.363000

Syntax

SELECT * FROM [westclintech].[wct].[ProportionalDividendsTree](
  <@CallPut, nvarchar(4000),>
 ,<@AmEur, nvarchar(4000),>
 ,<@AssetPrice, float,>
 ,<@StrikePrice, float,>
 ,<@TimeToMaturity, float,>
 ,<@RiskFreeRate, float,>
 ,<@Dividend_RangeQuery, nvarchar(max),>
 ,<@Volatility, float,>
 ,<@NumberOfSteps, int,>)

Description

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

Arguments

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

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

@Dividend_RangeQuery

a string containing an SQL statement which, when executed, provides the function with the times and proportions 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.

@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. @StrikePrice 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": "b1846cba-bb00-4e36-a8c6-cec2d6e4f820", "colName": "node", "colDatatype": "int", "colDesc": "The node within the step"}, {"id": "7ad2f844-7ae9-4507-9b3d-5fcaeb107484", "colName": "stepno", "colDatatype": "int", "colDesc": "The step number"}, {"id": "5edb7243-9244-4a2e-a082-8809216a74f8", "colName": "underlying", "colDatatype": "int", "colDesc": "the underlying value of the undelying asset at this step and node in the tree"}, {"id": "8f396496-d97e-4f23-86f5-dac62a8d983f", "colName": "intrinsic", "colDatatype": "float", "colDesc": "the intrinsic value of the option at this step and node in the tree"}, {"id": "8c10427c-c173-4953-9017-616ea688f9aa", "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).

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

@NumberOfSteps must be greater than 1 (@NumberOfSteps > 1)

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.

Examples

In this example we have an American put where the underlying price is 50, the exercise price is 50, the risk-free rate is 0.10, the volatility is 0.4 and dividends are expected according to the following schedule:

{"columns":[{"field":"T","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"yield","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"T":"0.071233","yield":"0.0022"},{"T":"0.320548","yield":"0.0022"},{"T":"0.569863","yield":"0.0024"},{"T":"0.819178","yield":"0.0024"}]}

The number of steps is 10 and the time-to-maturity is 1 (year).

SELECT *
FROM wct.ProportionalDividendsTree(
                                      'P',  --@CallPut
                                      'A',  --@AmEur
                                      50,   --@AssetPrice
                                      50,   --@StrikePrice
                                      1,    --@TimeToMaturity
                                      0.10, --@RiskFreeRate
                                      'SELECT T, amt
      FROM (VALUES
            (0.071233,0.0022),
            (0.320548,0.0022),
            (0.569863,0.0024),
            (0.819178,0.0024)
            )n(T,amt)'                ,
                                            --@Dividend_RangeQuery
                                      0.4,  --@Volatility
                                      10    --@NumberOfSteps
                                  );

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":"49.5415835725939","intrinsic":"0.458416427406078","price":"6.04438371660606"},{"node":"0","stepno":"1","underlying":"43.9621693006402","intrinsic":"6.03783069935982","price":"8.58694813530436"},{"node":"1","stepno":"1","underlying":"56.2217389122497","intrinsic":"0","price":"3.70186894937291"},{"node":"0","stepno":"2","underlying":"38.7386716700371","intrinsic":"11.2613283299629","price":"11.8554942693136"},{"node":"1","stepno":"2","underlying":"49.89","intrinsic":"0.109999999999999","price":"5.59172830308186"},{"node":"2","stepno":"2","underlying":"63.8026421114595","intrinsic":"0","price":"1.94505940656737"},{"node":"0","stepno":"3","underlying":"34.1358196520361","intrinsic":"15.8641803479639","price":"15.8641803479639"},{"node":"1","stepno":"3","underlying":"43.9621693006402","intrinsic":"6.03783069935984","price":"8.20822081647817"},{"node":"2","stepno":"3","underlying":"56.6171355871594","intrinsic":"0","price":"3.16867091414881"},{"node":"3","stepno":"3","underlying":"72.4057494336241","intrinsic":"0","price":"0.798655758390852"},{"node":"0","stepno":"4","underlying":"30.0136936551787","intrinsic":"19.9863063448213","price":"19.9863063448213"},{"node":"1","stepno":"4","underlying":"38.653446592363","intrinsic":"11.346553407637","price":"11.6564664643264"},{"node":"2","stepno":"4","underlying":"49.780242","intrinsic":"0.21975800000002","price":"5.03149732651719"},{"node":"3","stepno":"4","underlying":"64.1100008418958","intrinsic":"0","price":"1.42749081279149"},{"node":"4","stepno":"4","underlying":"82.1688942267665","intrinsic":"0","price":"0.205524039232251"},{"node":"0","stepno":"5","underlying":"26.4475261937566","intrinsic":"23.5524738062434","price":"23.5524738062434"},{"node":"1","stepno":"5","underlying":"34.0607208488017","intrinsic":"15.9392791511983","price":"15.9392791511983"},{"node":"2","stepno":"5","underlying":"43.8654525281788","intrinsic":"6.13454747182123","price":"7.73980596087292"},{"node":"3","stepno":"5","underlying":"56.4925778888676","intrinsic":"0","price":"2.50844740521884"},{"node":"4","stepno":"5","underlying":"72.75455221805","intrinsic":"0","price":"0.408988049974795"},{"node":"5","stepno":"5","underlying":"93.248495199114","intrinsic":"0","price":"0.0125658784852865"},{"node":"0","stepno":"6","underlying":"23.2491514655276","intrinsic":"26.7508485344724","price":"26.7508485344724"},{"node":"1","stepno":"6","underlying":"29.9416607904062","intrinsic":"20.0583392095938","price":"20.0583392095938"},{"node":"2","stepno":"6","underlying":"38.5606783205413","intrinsic":"11.4393216794587","price":"11.4393216794587"},{"node":"3","stepno":"6","underlying":"49.6607694192","intrinsic":"0.339230580799999","price":"4.31049941864966"},{"node":"4","stepno":"6","underlying":"63.9561368398752","intrinsic":"0","price":"0.81305735807369"},{"node":"5","stepno":"6","underlying":"82.3665740043771","intrinsic":"0","price":"0.0257992047364512"},{"node":"6","stepno":"6","underlying":"105.822062457142","intrinsic":"0","price":"0"},{"node":"0","stepno":"7","underlying":"20.4867334701092","intrinsic":"29.5132665298908","price":"29.5132665298908"},{"node":"1","stepno":"7","underlying":"26.3840521308915","intrinsic":"23.6159478691085","price":"23.6159478691085"},{"node":"2","stepno":"7","underlying":"33.9789751187645","intrinsic":"16.0210248812355","price":"16.0210248812355"},{"node":"3","stepno":"7","underlying":"43.7601754421111","intrinsic":"6.23982455788887","price":"7.18257747901773"},{"node":"4","stepno":"7","underlying":"56.3569957019343","intrinsic":"0","price":"1.61460082884405"},{"node":"5","stepno":"7","underlying":"72.5799412927266","intrinsic":"0","price":"0.0529687570839301"},{"node":"6","stepno":"7","underlying":"93.4728299910926","intrinsic":"0","price":"0"},{"node":"7","stepno":"7","underlying":"120.091041456181","intrinsic":"0","price":"0"},{"node":"0","stepno":"8","underlying":"18.0525404936868","intrinsic":"31.9474595063132","price":"31.9474595063132"},{"node":"1","stepno":"8","underlying":"23.2491514655276","intrinsic":"26.7508485344724","price":"26.7508485344724"},{"node":"2","stepno":"8","underlying":"29.9416607904062","intrinsic":"20.0583392095938","price":"20.0583392095938"},{"node":"3","stepno":"8","underlying":"38.5606783205413","intrinsic":"11.4393216794587","price":"11.4393216794587"},{"node":"4","stepno":"8","underlying":"49.6607694192","intrinsic":"0.339230580800006","price":"3.20265686589266"},{"node":"5","stepno":"8","underlying":"63.9561368398752","intrinsic":"0","price":"0.108750996617051"},{"node":"6","stepno":"8","underlying":"82.366574004377","intrinsic":"0","price":"0"},{"node":"7","stepno":"8","underlying":"106.076646408523","intrinsic":"0","price":"0"},{"node":"8","stepno":"8","underlying":"136.284040427496","intrinsic":"0","price":"0"},{"node":"0","stepno":"9","underlying":"15.8693953151041","intrinsic":"34.1306046848959","price":"34.1306046848959"},{"node":"1","stepno":"9","underlying":"20.437565309781","intrinsic":"29.562434690219","price":"29.562434690219"},{"node":"2","stepno":"9","underlying":"26.3207304057774","intrinsic":"23.6792695942226","price":"23.6792695942226"},{"node":"3","stepno":"9","underlying":"33.8974255784795","intrinsic":"16.1025744215205","price":"16.1025744215205"},{"node":"4","stepno":"9","underlying":"43.6551510210501","intrinsic":"6.34484897894992","price":"6.34484897894992"},{"node":"5","stepno":"9","underlying":"56.2217389122497","intrinsic":"0","price":"0.223278398744791"},{"node":"6","stepno":"9","underlying":"72.4057494336241","intrinsic":"0","price":"0"},{"node":"7","stepno":"9","underlying":"93.248495199114","intrinsic":"0","price":"0"},{"node":"8","stepno":"9","underlying":"120.091041456181","intrinsic":"0","price":"0"},{"node":"9","stepno":"9","underlying":"154.660492989566","intrinsic":"0","price":"0"},{"node":"0","stepno":"10","underlying":"13.9838252864581","intrinsic":"36.0161747135419","price":"36.0161747135419"},{"node":"1","stepno":"10","underlying":"18.0092143965019","intrinsic":"31.9907856034981","price":"31.9907856034981"},{"node":"2","stepno":"10","underlying":"23.1933535020103","intrinsic":"26.8066464979897","price":"26.8066464979897"},{"node":"3","stepno":"10","underlying":"29.8698008045093","intrinsic":"20.1301991954907","price":"20.1301991954907"},{"node":"4","stepno":"10","underlying":"38.468132692572","intrinsic":"11.531867307428","price":"11.531867307428"},{"node":"5","stepno":"10","underlying":"49.5415835725939","intrinsic":"0.458416427406071","price":"0.458416427406071"},{"node":"6","stepno":"10","underlying":"63.8026421114595","intrinsic":"0","price":"0"},{"node":"7","stepno":"10","underlying":"82.1688942267666","intrinsic":"0","price":"0"},{"node":"8","stepno":"10","underlying":"105.822062457142","intrinsic":"0","price":"0"},{"node":"9","stepno":"10","underlying":"136.284040427496","intrinsic":"0","price":"0"},{"node":"10","stepno":"10","underlying":"175.514814623515","intrinsic":"0","price":"0"}]}