BinomialTree
Updated: 15 Dec 2013
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 [wctOptions].[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.
@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.
@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 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).
· @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
Calculate the price 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 which has been reformatted for presentation purposes.
|
node
|
stepno
|
underlying
|
intrinsic
|
price
|
|
0
|
0
|
99.5
|
0
|
4.103311265
|
|
0
|
1
|
96.22834929
|
0
|
2.392745577
|
|
1
|
1
|
102.882883
|
2.882882987
|
5.83283543
|
|
0
|
2
|
93.06427344
|
0
|
1.181236385
|
|
1
|
2
|
99.5
|
0
|
3.617105639
|
|
2
|
2
|
106.38078
|
6.380780018
|
8.073703831
|
|
0
|
3
|
90.00423528
|
0
|
0.444198037
|
|
1
|
3
|
96.22834929
|
0
|
1.925784612
|
|
2
|
3
|
102.882883
|
2.882882987
|
5.326677766
|
|
3
|
3
|
109.9976014
|
9.997601434
|
10.85284203
|
|
0
|
4
|
87.04481397
|
0
|
0.096901257
|
|
1
|
4
|
93.06427344
|
0
|
0.794907303
|
|
2
|
4
|
99.5
|
0
|
3.068312001
|
|
3
|
4
|
106.38078
|
6.380780018
|
7.609971526
|
|
4
|
4
|
113.7373905
|
13.73739053
|
14.13510512
|
|
0
|
5
|
84.18270113
|
0
|
0
|
|
1
|
5
|
90.00423528
|
0
|
0.194724294
|
|
2
|
5
|
96.22834929
|
0
|
1.401018234
|
|
3
|
5
|
102.882883
|
2.882882987
|
4.753039094
|
|
4
|
5
|
109.9976014
|
9.997601434
|
10.49941749
|
|
5
|
5
|
117.604328
|
17.60432805
|
17.81716633
|
|
0
|
6
|
81.41469717
|
0
|
0
|
|
1
|
6
|
87.04481397
|
0
|
0
|
|
2
|
6
|
93.06427344
|
0
|
0.391300916
|
|
3
|
6
|
99.5
|
0
|
2.420779915
|
|
4
|
6
|
106.38078
|
6.380780018
|
7.110202129
|
|
5
|
6
|
113.7373905
|
13.73739053
|
13.92885641
|
|
6
|
6
|
121.6027369
|
21.6027369
|
21.75810355
|
|
0
|
7
|
78.73770771
|
0
|
0
|
|
1
|
7
|
84.18270113
|
0
|
0
|
|
2
|
7
|
90.00423528
|
0
|
0
|
|
3
|
7
|
96.22834929
|
0
|
0.786324108
|
|
4
|
7
|
102.882883
|
2.882882987
|
4.07166649
|
|
5
|
7
|
109.9976014
|
9.997601434
|
10.18221376
|
|
6
|
7
|
117.604328
|
17.60432805
|
17.72257196
|
|
7
|
7
|
125.7370869
|
25.73708695
|
25.8519225
|
|
0
|
8
|
76.1487401
|
0
|
0
|
|
1
|
8
|
81.41469717
|
0
|
0
|
|
2
|
8
|
87.04481397
|
0
|
0
|
|
3
|
8
|
93.06427344
|
0
|
0
|
|
4
|
8
|
99.5
|
0
|
1.58012818
|
|
5
|
8
|
106.38078
|
6.380780018
|
6.588679916
|
|
6
|
8
|
113.7373905
|
13.73739053
|
13.81732919
|
|
7
|
8
|
121.6027369
|
21.6027369
|
21.68047788
|
|
8
|
8
|
130.012
|
30.01200003
|
30.08739136
|
|
0
|
9
|
73.6449001
|
0
|
0
|
|
1
|
9
|
78.73770771
|
0
|
0
|
|
2
|
9
|
84.18270113
|
0
|
0
|
|
3
|
9
|
90.00423528
|
0
|
0
|
|
4
|
9
|
96.22834929
|
0
|
0
|
|
5
|
9
|
102.882883
|
2.882882987
|
3.175287438
|
|
6
|
9
|
109.9976014
|
9.997601434
|
10.03810777
|
|
7
|
9
|
117.604328
|
17.60432805
|
17.6437716
|
|
8
|
9
|
125.7370869
|
25.73708695
|
25.77539422
|
|
9
|
9
|
134.4322551
|
34.43225514
|
34.46934756
|
|
0
|
10
|
71.22338864
|
0
|
0
|
|
1
|
10
|
76.1487401
|
0
|
0
|
|
2
|
10
|
81.41469717
|
0
|
0
|
|
3
|
10
|
87.04481397
|
0
|
0
|
|
4
|
10
|
93.06427344
|
0
|
0
|
|
5
|
10
|
99.5
|
0
|
0
|
|
6
|
10
|
106.38078
|
6.380780018
|
6.380780018
|
|
7
|
10
|
113.7373905
|
13.73739053
|
13.73739053
|
|
8
|
10
|
121.6027369
|
21.6027369
|
21.6027369
|
|
9
|
10
|
130.012
|
30.01200003
|
30.01200003
|
|
10
|
10
|
139.0027937
|
39.00279372
|
39.00279372
|
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
This produces the following result which has been reformatted for ease of viewing.
|
stepno
|
0
|
1
|
2
|
3
|
4
|
5
|
6
|
7
|
8
|
9
|
10
|
|
0
|
4.1
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
|
1
|
2.39
|
5.83
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
|
2
|
1.18
|
3.62
|
8.07
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
|
3
|
0.44
|
1.93
|
5.33
|
10.85
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
|
4
|
0.1
|
0.79
|
3.07
|
7.61
|
14.14
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
|
5
|
0
|
0.19
|
1.4
|
4.75
|
10.5
|
17.82
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
|
6
|
0
|
0
|
0.39
|
2.42
|
7.11
|
13.93
|
21.76
|
NULL
|
NULL
|
NULL
|
NULL
|
|
7
|
0
|
0
|
0
|
0.79
|
4.07
|
10.18
|
17.72
|
25.85
|
NULL
|
NULL
|
NULL
|
|
8
|
0
|
0
|
0
|
0
|
1.58
|
6.59
|
13.82
|
21.68
|
30.09
|
NULL
|
NULL
|
|
9
|
0
|
0
|
0
|
0
|
0
|
3.18
|
10.04
|
17.64
|
25.78
|
34.47
|
NULL
|
|
10
|
0
|
0
|
0
|
0
|
0
|
0
|
6.38
|
13.74
|
21.6
|
30.01
|
39
|