Login     Register

        Contact Us     Search

XLeratorDB/financial-options Documentation

SQL Server proportional dividends binomial tree


ProportionalDividendsTree

Updated: 15 Dec 2013


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.
Syntax
SELECT * FROM [wctOptions].[wct].[ProportionalDividendsTree](
  <@CallPut, nvarchar(4000),>
 ,<@AmEur, nvarchar(4000),>
 ,<@AssetPrice, float,>
 ,<@StrikePrice, float,>
 ,<@TimeToMaturity, float,>
 ,<@RiskFreeRate, 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.
@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.
@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
)
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:

T
yield
0.071233
0.0022
0.320548
0.0022
0.569863
0.0024
0.819178
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 which has been reformatted for presentation purposes.

node
stepno
underlying
intrinsic
price
0
0
49.54158357
0.458416427
6.044383717
0
1
43.9621693
6.037830699
8.586948135
1
1
56.22173891
0
3.701868949
0
2
38.73867167
11.26132833
11.85549427
1
2
49.89
0.11
5.591728303
2
2
63.80264211
0
1.945059407
0
3
34.13581965
15.86418035
15.86418035
1
3
43.9621693
6.037830699
8.208220816
2
3
56.61713559
0
3.168670914
3
3
72.40574943
0
0.798655758
0
4
30.01369366
19.98630634
19.98630634
1
4
38.65344659
11.34655341
11.65646646
2
4
49.780242
0.219758
5.031497327
3
4
64.11000084
0
1.427490813
4
4
82.16889423
0
0.205524039
0
5
26.44752619
23.55247381
23.55247381
1
5
34.06072085
15.93927915
15.93927915
2
5
43.86545253
6.134547472
7.739805961
3
5
56.49257789
0
2.508447405
4
5
72.75455222
0
0.40898805
5
5
93.2484952
0
0.012565878
0
6
23.24915147
26.75084853
26.75084853
1
6
29.94166079
20.05833921
20.05833921
2
6
38.56067832
11.43932168
11.43932168
3
6
49.66076942
0.339230581
4.310499419
4
6
63.95613684
0
0.813057358
5
6
82.366574
0
0.025799205
6
6
105.8220625
0
0
0
7
20.48673347
29.51326653
29.51326653
1
7
26.38405213
23.61594787
23.61594787
2
7
33.97897512
16.02102488
16.02102488
3
7
43.76017544
6.239824558
7.182577479
4
7
56.3569957
0
1.614600829
5
7
72.57994129
0
0.052968757
6
7
93.47282999
0
0
7
7
120.0910415
0
0
0
8
18.05254049
31.94745951
31.94745951
1
8
23.24915147
26.75084853
26.75084853
2
8
29.94166079
20.05833921
20.05833921
3
8
38.56067832
11.43932168
11.43932168
4
8
49.66076942
0.339230581
3.202656866
5
8
63.95613684
0
0.108750997
6
8
82.366574
0
0
7
8
106.0766464
0
0
8
8
136.2840404
0
0
0
9
15.86939532
34.13060468
34.13060468
1
9
20.43756531
29.56243469
29.56243469
2
9
26.32073041
23.67926959
23.67926959
3
9
33.89742558
16.10257442
16.10257442
4
9
43.65515102
6.344848979
6.344848979
5
9
56.22173891
0
0.223278399
6
9
72.40574943
0
0
7
9
93.2484952
0
0
8
9
120.0910415
0
0
9
9
154.660493
0
0
0
10
13.98382529
36.01617471
36.01617471
1
10
18.0092144
31.9907856
31.9907856
2
10
23.1933535
26.8066465
26.8066465
3
10
29.8698008
20.1301992
20.1301992
4
10
38.46813269
11.53186731
11.53186731
5
10
49.54158357
0.458416427
0.458416427
6
10
63.80264211
0
0
7
10
82.16889423
0
0
8
10
105.8220625
0
0
9
10
136.2840404
0
0
10
10
175.5148146
0
0



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service