Login     Register

        Contact Us     Search

XLeratorDB/financial-options Documentation

SQL Server function to generate a binomial tree for equity options


BinomialDiscreteDividendsTree

Updated: 15 Dec 2013


Use the table-valued function BinomialDiscreteDividendsTree to return the option value, intrinsic value, underlying value, and present value of the dividend amounts for each node on a binomial tree for an American or European option paying discrete dividends.
Syntax
SELECT * FROM [wctOptions].[wct].[BinomialDiscreteDividendsTree](
  <@CallPut, nvarchar(4000),>
 ,<@AmEur, nvarchar(4000),>
 ,<@AssetPrice, float,>
 ,<@StrikePrice, float,>
 ,<@TimeToMaturity, float,>
 ,<@RiskFreeRate, float,>
 ,<@DividendRate, float,>
 ,<@Dividend_RangeQuery, nvarchar(max),>
 ,<@Volatility, float,>
 ,<@NumberOfSteps, int,>)
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.
Return Types
RETURNS TABLE (
      [node] [int] NULL,
      [stepno] [int] NULL,
      [underlying] [float] NULL,
      [intrinsic] [float] NULL,
      [price] [float] NULL,
      [PVdividends] [float] NULL
)
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.
·         If @DividendRate is NULL then @DividendRate 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:

T
amt
0.071233
0.11
0.320548
0.11
0.569863
0.12
0.819178
0.12

The number of steps is 10 and the time-to-maturity is 1 (year).
SELECT *
FROM wct.BinomialDiscreteDividendsTree(
      'P',        --@CallPut
      'A',        --@AmEur
      50,         --@AssetPrice
      50,         --@StrikePrice
      1,          --@TimeToMaturity
      0.10,       --@RiskFreeRate
      0,          --@DividendRate  
      'SELECT T, amt
      FROM (VALUES
            (0.071233,0.11),
            (0.320548,0.11),
            (0.569863,0.12),
            (0.819178,0.12)
            )n(T,amt)' --@Dividend_RangeQuery
      ,0.4              --@Volatility
      ,10               --@NumberOfSteps
      )          

This produces the following result which has been reformatted for presentation purposes.

node
stepno
underlying
intrinsic
price
PVdividends
0
0
50
0
6.012745681
0.439663671
0
1
44.00544108
5.994558916
8.538523152
0.333765472
1
1
56.57678575
0
3.685860644
0.333765472
0
2
38.81981374
11.18018626
11.78110746
0.337119871
1
2
49.8974562
0.1025438
5.56748544
0.337119871
2
2
64.16391291
0
1.936708336
0.337119871
0
3
34.2507646
15.7492354
15.7492354
0.340507982
1
3
44.01218359
5.987816406
8.171636845
0.340507982
2
3
56.58352826
0
3.155898956
0.340507982
3
3
72.77366484
0
0.794420441
0.340507982
0
4
30.11415998
19.88584002
19.88584002
0.233052691
1
4
38.71574656
11.28425344
11.601084
0.233052691
2
4
49.79338902
0.20661098
5.012393116
0.233052691
3
4
64.05984573
0
1.420598017
0.233052691
4
4
82.43304995
0
0.2037783
0.233052691
0
5
26.56608838
23.43391162
23.43391162
0.235394909
1
5
34.14565153
15.85434847
15.85434847
0.235394909
2
5
43.90707052
6.092929479
7.711940855
0.235394909
3
5
56.47841519
0
2.497448863
0.235394909
4
5
72.66855177
0
0.405934689
0.235394909
5
5
93.51918705
0
0.012051837
0.235394909
0
6
23.31953126
26.68046874
26.68046874
0.117398478
1
6
29.99850577
20.00149423
20.00149423
0.117398478
2
6
38.60009234
11.39990766
11.39990766
0.117398478
3
6
49.67773481
0.322265193
4.293266445
0.117398478
4
6
63.94419152
0
0.807878337
0.117398478
5
6
82.31739573
0
0.024743819
0.117398478
6
6
105.9795173
0
0
0.117398478
0
7
20.5638798
29.4361202
29.4361202
0.118578352
1
7
26.44927183
23.55072817
23.55072817
0.118578352
2
7
34.02883497
15.97116503
15.97116503
0.118578352
3
7
43.79025396
6.209746036
7.155866083
0.118578352
4
7
56.36159863
0
1.606205356
0.118578352
5
7
72.55173521
0
0.050801928
0.118578352
6
7
93.40237049
0
0
0.118578352
7
7
120.2550773
0
0
0.118578352
0
8
18.13580143
31.86419857
31.86419857
0.119770085
1
8
23.32190286
26.67809714
26.67809714
0.119770085
2
8
30.00087737
19.99912263
19.99912263
0.119770085
3
8
38.60246395
11.39753605
11.39753605
0.119770085
4
8
49.68010641
0.319893587
3.190014155
0.119770085
5
8
63.94656313
0
0.104302245
0.119770085
6
8
82.31976734
0
0
0.119770085
7
8
105.9818889
0
0
0.119770085
8
8
136.4553975
0
0
0.119770085
0
9
15.87540229
34.12459771
34.12459771
0
1
9
20.44530145
29.55469855
29.55469855
0
2
9
26.33069348
23.66930652
23.66930652
0
3
9
33.91025662
16.08974338
16.08974338
0
4
9
43.67167561
6.328324388
6.328324388
0
5
9
56.24302028
0
0.214144596
0
6
9
72.43315686
0
0
0
7
9
93.28379214
0
0
0
8
9
120.136499
0
0
0
9
9
154.7190359
0
0
0
0
10
13.98911852
36.01088148
36.01088148
0
1
10
18.01603134
31.98396866
31.98396866
0
2
10
23.20213278
26.79786722
26.79786722
0
3
10
29.88110729
20.11889271
20.11889271
0
4
10
38.48269386
11.51730614
11.51730614
0
5
10
49.56033633
0.439663671
0.439663671
0
6
10
63.82679304
0
0
0
7
10
82.19999725
0
0
0
8
10
105.8621188
0
0
0
9
10
136.3356274
0
0
0
10
10
175.5812515
0
0
0

 
 


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service