Login     Register

        Contact Us     Search

XLeratorDB/financial-options Documentation

SQL Server binomal tree function for option pricing


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

 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service