BinomialDiscreteDividendsPriceNGreeks
Updated: 15 Dec 2013
Use the table-valued function BinomialDiscreteDividendsPriceNGreeks to calculate the price, delta, gamma, theta, vega, rho, and lambda of American and European options paying discrete dividends using a Cox Ross Rubinstein Binomial as described in Options, Futures, and Other Derivatives, 8th Edition, by John C. Hull.
Price, delta, gamma, and theta are calculated directly from the binomial tree. Vega and rho are calculated using a first-order forward finite difference.
Syntax
SELECT * FROM [wctOptions].[wct].[BinomialDiscreteDividendsPriceNGreeks](
<@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 Type
RETURNS TABLE (
[Price] [float] NULL,
[Delta] [float] NULL,
[Gamma] [float] NULL,
[Theta] [float] NULL,
[Vega] [float] NULL,
[Rho] [float] NULL,
[Lambda] [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)
· If @RV is NULL, then @RV is set to 'P'.
· 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.
· To improve performance, the vega calculation in BinomialDiscreteDividendsPriceNGreeks is different than the vega calculation in BinomialDiscreteDividends.
· To improve performance, the rho calculation in BinomialDiscreteDividendsPriceNGreeks is different than the rho calculation in BinomialDiscreteDividends.
· To calculate the price and Greeks using proportional dividends (where the dividend is specified with a date and a percentage) use ProportionalDividends.
Example
Calculate the price and Greeks for an American call option expiring in one year with asset price of 478, a strike price of 500 and a volatility of 30%. The risk-free rate is 2.75%. Dividends will be paid quarterly in the following amounts.
|
T
|
Div
|
|
0.25
|
4.00
|
|
0.50
|
4.10
|
|
0.75
|
4.20
|
|
1.00
|
4.30
|
The number of steps is 100.
SELECT *
FROM wct.BinomialDiscreteDividendsPriceNGreeks(
'C' --Put/Call
,'A' --American/European
,478 --Asset Price
,500 --Strike Price
,1 --Time-to-maturity
,.0275 --Risk-free rate
,NULL --Dividend rate
,'SELECT *
FROM (VALUES
(0.25,4.00),
(0.50,4.10),
(0.75,4.20),
(1.00,4.30)
)n(T,D)' --Discrete Dividends
,0.30 --Volatility
,100 --Number of Steps
)
This produces the following result.
|
Price
|
Delta
|
Gamma
|
Theta
|
Vega
|
Rho
|
Lambda
|
|
46.44208
|
0.501614
|
0.00291
|
-0.09046
|
1.814434
|
1.798961
|
5.162807
|
In this example, we calculate different option values by changing the volatility. We use the SeriesFloat function to generate volatilities between .20 and .40. Note the use of CROSS APPLY with a table-valued function.
SELECT n.SeriesValue as volatility
,k.*
FROM wct.SeriesFloat(0.20,0.40,.01,NULL,NULL)n
CROSS APPLY wct.BinomialDiscreteDividendsPriceNGreeks(
'C' --Put/Call
,'A' --American/European
,478 --Asset Price
,500 --Strike Price
,1 --Time-to-maturity
,.0275 --Risk-free rate
,NULL --Dividend rate
,'SELECT *
FROM (VALUES
(0.25,4.00),
(0.50,4.10),
(0.75,4.20),
(1.00,4.30)
)n(T,D)' --Discrete Dividends
,n.SeriesValue --Volatility
,100 --Number of Steps
)k
This produces the following result.
|
volatility
|
Price
|
Delta
|
Gamma
|
Theta
|
Vega
|
Rho
|
Lambda
|
|
0.2
|
28.06186
|
0.452841
|
0.00435
|
-0.06445
|
1.863724
|
1.740252
|
7.713601
|
|
0.21
|
29.92558
|
0.459023
|
0.004144
|
-0.06709
|
1.856414
|
1.75228
|
7.331948
|
|
0.22
|
31.782
|
0.464808
|
0.003958
|
-0.06971
|
1.849899
|
1.762432
|
6.9907
|
|
0.23
|
33.6319
|
0.47025
|
0.003787
|
-0.07233
|
1.84404
|
1.770957
|
6.683521
|
|
0.24
|
35.47594
|
0.475391
|
0.003631
|
-0.07494
|
1.838724
|
1.778061
|
6.405379
|
|
0.25
|
37.31466
|
0.480267
|
0.003487
|
-0.07755
|
1.833862
|
1.783918
|
6.152213
|
|
0.26
|
39.14852
|
0.48491
|
0.003354
|
-0.08014
|
1.829378
|
1.788674
|
5.920707
|
|
0.27
|
40.9779
|
0.489345
|
0.003231
|
-0.08273
|
1.825214
|
1.792454
|
5.708124
|
|
0.28
|
42.80311
|
0.493595
|
0.003116
|
-0.08532
|
1.821319
|
1.795367
|
5.51218
|
|
0.29
|
44.62443
|
0.497679
|
0.00301
|
-0.08789
|
1.817652
|
1.797524
|
5.330951
|
|
0.3
|
46.44208
|
0.501614
|
0.00291
|
-0.09046
|
1.814434
|
1.798961
|
5.162807
|
|
0.31
|
48.25652
|
0.505417
|
0.002817
|
-0.09302
|
1.827765
|
1.77411
|
5.006358
|
|
0.32
|
50.08428
|
0.509137
|
0.002728
|
-0.09555
|
1.842927
|
1.767381
|
4.859162
|
|
0.33
|
51.92721
|
0.512788
|
0.002644
|
-0.09805
|
1.839826
|
1.785384
|
4.720313
|
|
0.34
|
53.76704
|
0.516336
|
0.002565
|
-0.10054
|
1.836951
|
1.800129
|
4.590334
|
|
0.35
|
55.60399
|
0.519791
|
0.00249
|
-0.10302
|
1.834004
|
1.798885
|
4.46839
|
|
0.36
|
57.43799
|
0.52316
|
0.002419
|
-0.1055
|
1.831125
|
1.797251
|
4.353748
|
|
0.37
|
59.26912
|
0.526449
|
0.002353
|
-0.10796
|
1.828303
|
1.795259
|
4.245763
|
|
0.38
|
61.09742
|
0.529664
|
0.002289
|
-0.11043
|
1.825529
|
1.792937
|
4.143867
|
|
0.39
|
62.92295
|
0.532812
|
0.002229
|
-0.11288
|
1.827948
|
1.790312
|
4.047553
|
|
0.4
|
64.7509
|
0.535902
|
0.002172
|
-0.11532
|
1.857408
|
1.797634
|
3.956103
|
In this example we look at how to use dates in the function, using the YEARFRAC and CALCDATE functions from XLeratorDB/financial. We will use the GETDATE() function as the start date for the calculations. The option expires on November 27th, 2014 with dividends payable on February 6th, May 6th, August 6th, and November 6th, 2014.
SELECT *
FROM wct.BinomialDiscreteDividendsPriceNGreeks(
'C'
,'A'
,478
,500
,wct.YEARFRAC(GETDATE(),'2014-11-27',NULL)
,.0275
,NULL
,'SELECT *
FROM (VALUES
(wct.YEARFRAC(GETDATE(),wct.CALCDATE(2014,2,6),NULL),4.00),
(wct.YEARFRAC(GETDATE(),wct.CALCDATE(2014,5,6),NULL),4.10),
(wct.YEARFRAC(GETDATE(),wct.CALCDATE(2014,8,6),NULL),4.20),
(wct.YEARFRAC(GETDATE(),wct.CALCDATE(2014,11,6),NULL),4.30)
)n(T,D)'
,0.30
,100
)
This produces the following result on 2013-12-11. Your results will be different.
|
Price
|
Delta
|
Gamma
|
Theta
|
Vega
|
Rho
|
Lambda
|
|
44.58955
|
0.494331
|
0.002979
|
-0.09213
|
1.787774
|
1.673378
|
5.299227
|
In this example, we use @date_start as a datetime variable to establish the starting point for calculating the time-to-expiry and @date_start_string as a string variable to be used in @Dividend_RangeQuery.
DECLARE @date_start as datetime = cast('2013-12-11' as datetime)
DECLARE @date_start_string as varchar(max) = '''' + convert(varchar,@date_start,112) + ''''
SELECT *
FROM wct.BinomialDiscreteDividendsPriceNGreeks(
'C'
,'A'
,478
,500
,wct.YEARFRAC(@date_start,'2014-11-27',NULL)
,.0275
,NULL
,'SELECT *
FROM (VALUES
(wct.YEARFRAC(' + @date_start_string + ',wct.CALCDATE(2014,2,6),NULL),4.00),
(wct.YEARFRAC(' + @date_start_string + ',wct.CALCDATE(2014,5,6),NULL),4.10),
(wct.YEARFRAC(' + @date_start_string + ',wct.CALCDATE(2014,8,6),NULL),4.20),
(wct.YEARFRAC(' + @date_start_string + ',wct.CALCDATE(2014,11,6),NULL),4.30)
)n(T,D)'
,0.30
,100
)
This produces the follwing result.
|
Price
|
Delta
|
Gamma
|
Theta
|
Vega
|
Rho
|
Lambda
|
|
44.58955
|
0.494331
|
0.002979
|
-0.09213
|
1.787774
|
1.673378
|
5.299227
|
In this example we put some equity options trades into a table, with a separate table containing the closing price and volatilities for each ticker and a third table containing the dividend information. This example shows one way to combine the information from multiples tables to calculate the price and Greeks of the option. This example holds the risk-free rate constant, which you wouldn't want to do in practice and we will add a discount factor table and calculate the interpolated risk-free rate in another example. Once again, note the use of CROSS APPLY.
/*Put dividend information into a table*/
SELECT *
INTO #div
FROM (VALUES
('ABC','2013-12-15',4)
,('ABC','2014-03-15',4.05)
,('ABC','2014-06-15',4.1)
,('ABC','2014-09-15',4.15)
,('ABC','2014-12-15',4.2)
,('DEF','2014-01-17',1)
,('DEF','2014-04-17',1.05)
,('DEF','2014-07-17',1.1)
,('DEF','2014-10-17',1.15)
,('DEF','2015-01-17',1.2)
,('GHI','2014-02-17',1.25)
,('GHI','2014-05-17',1.3)
,('GHI','2014-08-17',1.35)
,('GHI','2014-11-17',1.4)
,('GHI','2015-02-17',1.45)
)d(ticker,date_div,amt_div)
/*Put price information into a table*/
SELECT *
INTO #prices
FROM (VALUES
('ABC',495,0.35)
,('DEF',125,0.3)
,('GHI',62.5,0.25)
)p(ticker,price,vol)
/*put the options into a table*/
SELECT *
INTO #options
FROM (VALUES
(1,'ABC','2014-08-09',470,'C')
,(2,'ABC','2014-08-20',480,'P')
,(3,'ABC','2014-11-10',490,'P')
,(4,'ABC','2014-09-14',500,'C')
,(5,'DEF','2014-11-14',100,'C')
,(6,'DEF','2014-12-12',110,'C')
,(7,'DEF','2014-08-11',120,'P')
,(8,'DEF','2014-08-26',130,'C')
,(9,'GHI','2014-08-04',50,'C')
,(10,'GHI','2014-12-28',55,'C')
,(11,'GHI','2014-10-09',60,'P')
,(12,'GHI','2014-11-14',65,'C')
)o(rn,ticker,expiry,strike,z)
/*Establish the start date for calculation purposes*/
DECLARE @date_start as datetime = cast('2013-12-11' as datetime)
DECLARE @date_start_string as varchar(max) = '''' + convert(varchar,@date_start,112) + ''''
SELECT A.rn
,A.ticker
,A.z
,A.expiry
,B.vol
,k.*
FROM #options A
INNER JOIN #prices B
ON A.ticker = B.ticker
CROSS APPLY wct.BinomialDiscreteDividendsPriceNGreeks(
A.z
,'A'
,B.price
,A.strike
,wct.YEARFRAC(@date_start,A.expiry,NULL)
,.0275
,NULL
,'SELECT wct.YEARFRAC(' + @date_start_string + ',date_div,NULL),amt_div FROM #div WHERE ticker = ' + '''' + CAST(A.ticker as varchar(max)) + ''''
,B.vol
,100
)k
DROP TABLE #div
DROP TABLE #prices
DROP TABLE #options
This produces the following result, which has been reformatted for presentation purposes.
|
rn
|
ticker
|
z
|
expiry
|
vol
|
Price
|
Delta
|
Gamma
|
Theta
|
Vega
|
Rho
|
Lambda
|
|
1
|
ABC
|
C
|
2014-08-09
|
0.35
|
65.3064
|
0.623
|
0.0028
|
-0.1277
|
1.4915
|
1.4064
|
4.722
|
|
2
|
ABC
|
P
|
2014-08-20
|
0.35
|
49.9372
|
-0.4139
|
0.0028
|
-0.0915
|
1.5582
|
-1.4954
|
-4.1023
|
|
3
|
ABC
|
P
|
2014-11-10
|
0.35
|
63.8201
|
-0.4355
|
0.0025
|
-0.075
|
1.8001
|
-2.2561
|
-3.3781
|
|
4
|
ABC
|
C
|
2014-09-14
|
0.35
|
55.7506
|
0.5429
|
0.0027
|
-0.1215
|
1.6732
|
1.5067
|
4.8204
|
|
5
|
DEF
|
C
|
2014-11-14
|
0.3
|
28.1058
|
0.8307
|
0.0078
|
-0.0195
|
0.2876
|
0.5043
|
3.6945
|
|
6
|
DEF
|
C
|
2014-12-12
|
0.3
|
21.8328
|
0.7194
|
0.0096
|
-0.0222
|
0.4097
|
0.5386
|
4.1188
|
|
7
|
DEF
|
P
|
2014-08-11
|
0.3
|
9.8515
|
-0.3999
|
0.013
|
-0.0194
|
0.384
|
-0.3686
|
-5.0737
|
|
8
|
DEF
|
C
|
2014-08-26
|
0.3
|
10.0405
|
0.4849
|
0.0132
|
-0.0278
|
0.4041
|
0.3197
|
6.0371
|
|
9
|
GHI
|
C
|
2014-08-04
|
0.25
|
12.9255
|
0.9476
|
0.0138
|
-0.0076
|
0.0437
|
0.1015
|
4.5822
|
|
10
|
GHI
|
C
|
2014-12-28
|
0.25
|
8.9648
|
0.7776
|
0.0313
|
-0.0115
|
0.1615
|
0.1795
|
5.4213
|
|
11
|
GHI
|
P
|
2014-10-09
|
0.25
|
5.3983
|
-0.4612
|
0.0301
|
-0.0064
|
0.212
|
-0.2462
|
-5.3401
|
|
12
|
GHI
|
C
|
2014-11-14
|
0.25
|
3.8996
|
0.4402
|
0.0304
|
-0.0106
|
0.2132
|
0.165
|
7.0556
|
For large volumes of data having many options for a single ticker, it will be more efficient to calculate the time values of the dividends outside of the BinomialDiscreteDividendPriceNGreeks function and simply use the pre-calculated values in @Dividend_RangeQuery.
/*Put dividend information into a table*/
SELECT *
INTO #div
FROM (VALUES
('ABC','2013-12-15',4)
,('ABC','2014-03-15',4.05)
,('ABC','2014-06-15',4.1)
,('ABC','2014-09-15',4.15)
,('ABC','2014-12-15',4.2)
,('DEF','2014-01-17',1)
,('DEF','2014-04-17',1.05)
,('DEF','2014-07-17',1.1)
,('DEF','2014-10-17',1.15)
,('DEF','2015-01-17',1.2)
,('GHI','2014-02-17',1.25)
,('GHI','2014-05-17',1.3)
,('GHI','2014-08-17',1.35)
,('GHI','2014-11-17',1.4)
,('GHI','2015-02-17',1.45)
)d(ticker,date_div,amt_div)
/*Put price information into a table*/
SELECT *
INTO #prices
FROM (VALUES
('ABC',495,0.35)
,('DEF',125,0.3)
,('GHI',62.5,0.25)
)p(ticker,price,vol)
/*put the options into a table*/
SELECT *
INTO #options
FROM (VALUES
(1,'ABC','2014-08-09',470,'C')
,(2,'ABC','2014-08-20',480,'P')
,(3,'ABC','2014-11-10',490,'P')
,(4,'ABC','2014-09-14',500,'C')
,(5,'DEF','2014-11-14',100,'C')
,(6,'DEF','2014-12-12',110,'C')
,(7,'DEF','2014-08-11',120,'P')
,(8,'DEF','2014-08-26',130,'C')
,(9,'GHI','2014-08-04',50,'C')
,(10,'GHI','2014-12-28',55,'C')
,(11,'GHI','2014-10-09',60,'P')
,(12,'GHI','2014-11-14',65,'C')
)o(rn,ticker,expiry,strike,z)
/*Establish the start date for calculation purposes*/
DECLARE @date_start as datetime = cast('2013-12-11' as datetime)
/*Put the #div values into another table with date converted to a
fraction of a year*/
SELECT ticker
,wct.YEARFRAC(@date_start,date_div,NULL) as T
,amt_div as D
INTO #tdiv
FROM #div
SELECT A.rn
,A.ticker
,A.z
,A.expiry
,B.vol
,k.*
FROM #options A
INNER JOIN #prices B
ON A.ticker = B.ticker
CROSS APPLY wct.BinomialDiscreteDividendsPriceNGreeks(
A.z
,'A'
,B.price
,A.strike
,wct.YEARFRAC(@date_start,A.expiry,NULL)
,.0275
,NULL
,'SELECT T,D FROM #tdiv WHERE ticker = ' + '''' + CAST(A.ticker as varchar(max)) + ''''
,B.vol
,100
)k
DROP TABLE #div
DROP TABLE #prices
DROP TABLE #options
DROP TABLE #tdiv
This produces the following result, which has been reformatted for presentation purposes.
|
rn
|
ticker
|
z
|
expiry
|
vol
|
Price
|
Delta
|
Gamma
|
Theta
|
Vega
|
Rho
|
Lambda
|
|
1
|
ABC
|
C
|
2014-08-09
|
0.35
|
65.3064
|
0.623
|
0.0028
|
-0.1277
|
1.4915
|
1.4064
|
4.722
|
|
2
|
ABC
|
P
|
2014-08-20
|
0.35
|
49.9372
|
-0.4139
|
0.0028
|
-0.0915
|
1.5582
|
-1.4954
|
-4.1023
|
|
3
|
ABC
|
P
|
2014-11-10
|
0.35
|
63.8201
|
-0.4355
|
0.0025
|
-0.075
|
1.8001
|
-2.2561
|
-3.3781
|
|
4
|
ABC
|
C
|
2014-09-14
|
0.35
|
55.7506
|
0.5429
|
0.0027
|
-0.1215
|
1.6732
|
1.5067
|
4.8204
|
|
5
|
DEF
|
C
|
2014-11-14
|
0.3
|
28.1058
|
0.8307
|
0.0078
|
-0.0195
|
0.2876
|
0.5043
|
3.6945
|
|
6
|
DEF
|
C
|
2014-12-12
|
0.3
|
21.8328
|
0.7194
|
0.0096
|
-0.0222
|
0.4097
|
0.5386
|
4.1188
|
|
7
|
DEF
|
P
|
2014-08-11
|
0.3
|
9.8515
|
-0.3999
|
0.013
|
-0.0194
|
0.384
|
-0.3686
|
-5.0737
|
|
8
|
DEF
|
C
|
2014-08-26
|
0.3
|
10.0405
|
0.4849
|
0.0132
|
-0.0278
|
0.4041
|
0.3197
|
6.0371
|
|
9
|
GHI
|
C
|
2014-08-04
|
0.25
|
12.9255
|
0.9476
|
0.0138
|
-0.0076
|
0.0437
|
0.1015
|
4.5822
|
|
10
|
GHI
|
C
|
2014-12-28
|
0.25
|
8.9648
|
0.7776
|
0.0313
|
-0.0115
|
0.1615
|
0.1795
|
5.4213
|
|
11
|
GHI
|
P
|
2014-10-09
|
0.25
|
5.3983
|
-0.4612
|
0.0301
|
-0.0064
|
0.212
|
-0.2462
|
-5.3401
|
|
12
|
GHI
|
C
|
2014-11-14
|
0.25
|
3.8996
|
0.4402
|
0.0304
|
-0.0106
|
0.2132
|
0.165
|
7.0556
|
In this example, we will use the same inputs as above, with the exception that we will have a table of discount factors and use the DFINTERP function to convert the discount factors into risk-free rates to be used in the function.
/*Put dividend information into a table*/
SELECT *
INTO #div
FROM (VALUES
('ABC','2013-12-15',4)
,('ABC','2014-03-15',4.05)
,('ABC','2014-06-15',4.1)
,('ABC','2014-09-15',4.15)
,('ABC','2014-12-15',4.2)
,('DEF','2014-01-17',1)
,('DEF','2014-04-17',1.05)
,('DEF','2014-07-17',1.1)
,('DEF','2014-10-17',1.15)
,('DEF','2015-01-17',1.2)
,('GHI','2014-02-17',1.25)
,('GHI','2014-05-17',1.3)
,('GHI','2014-08-17',1.35)
,('GHI','2014-11-17',1.4)
,('GHI','2015-02-17',1.45)
)d(ticker,date_div,amt_div)
/*Put price information into a table*/
SELECT *
INTO #prices
FROM (VALUES
('ABC',495,0.35)
,('DEF',125,0.3)
,('GHI',62.5,0.25)
)p(ticker,price,vol)
/*put the options into a table*/
SELECT *
INTO #options
FROM (VALUES
(1,'ABC','2014-08-09',470,'C')
,(2,'ABC','2014-08-20',480,'P')
,(3,'ABC','2014-11-10',490,'P')
,(4,'ABC','2014-09-14',500,'C')
,(5,'DEF','2014-11-14',100,'C')
,(6,'DEF','2014-12-12',110,'C')
,(7,'DEF','2014-08-11',120,'P')
,(8,'DEF','2014-08-26',130,'C')
,(9,'GHI','2014-08-04',50,'C')
,(10,'GHI','2014-12-28',55,'C')
,(11,'GHI','2014-10-09',60,'P')
,(12,'GHI','2014-11-14',65,'C')
)o(rn,ticker,expiry,strike,z)
/*Establish the start date for calculation purposes*/
DECLARE @date_start as datetime = cast('2013-12-11' as datetime)
/*Put the #div values into another table with date converted to a
fraction of a year*/
SELECT ticker
,wct.YEARFRAC(@date_start,date_div,NULL) as T
,amt_div as D
INTO #tdiv
FROM #div
/*Put the discount factors into a table*/
SELECT wct.TENOR2DATE(tenor,@date_start,NULL,'') as date_df
,df
INTO #df
FROM (
SELECT 'ON',0.999995555575309 UNION ALL
SELECT 'TN',0.999991111170370 UNION ALL
SELECT '1W',0.999956112706425 UNION ALL
SELECT '2W',0.999916450742048 UNION ALL
SELECT '1M',0.999804481000583 UNION ALL
SELECT '2M',0.999574621744643 UNION ALL
SELECT '3M',0.999241679910437 UNION ALL
SELECT '6M',0.998800609148515 UNION ALL
SELECT '9M',0.998022836090921 UNION ALL
SELECT '1Y',0.997197057207847 UNION ALL
SELECT '2Y',0.996311568695976
)n(tenor, df)
/*Calculate the risk-free rate for each expiry date in the portfolio*/
SELECT expiry
,wct.DFINTERP(date_df,df,expiry,@date_start,'CC') as Rf
INTO #rates
FROM #df, #options
GROUP BY expiry
SELECT A.rn
,A.ticker
,A.z
,A.expiry
,B.vol
,k.*
FROM #options A
INNER JOIN #prices B
ON A.ticker = B.ticker
INNER JOIN #rates C
ON A.expiry = C.expiry
CROSS APPLY wct.BinomialDiscreteDividendsPriceNGreeks(
A.z
,'A'
,B.price
,A.strike
,wct.YEARFRAC(@date_start,A.expiry,NULL)
,C.rf
,NULL
,'SELECT T,D FROM #tdiv WHERE ticker = ' + '''' + CAST(A.ticker as varchar(max)) + ''''
,B.vol
,100
)k
DROP TABLE #div
DROP TABLE #prices
DROP TABLE #options
DROP TABLE #tdiv
DROP TABLE #df
DROP TABLE #rates
This produces the following result, which has been reformatted for presentation purposes.
|
rn
|
ticker
|
z
|
expiry
|
vol
|
Price
|
Delta
|
Gamma
|
Theta
|
Vega
|
Rho
|
Lambda
|
|
1
|
ABC
|
C
|
2014-08-09
|
0.35
|
61.9164
|
0.6073
|
0.0029
|
-0.1159
|
1.4992
|
1.2933
|
4.8548
|
|
2
|
ABC
|
P
|
2014-08-20
|
0.35
|
53.8339
|
-0.4322
|
0.0028
|
-0.1084
|
1.5746
|
-1.646
|
-3.9742
|
|
3
|
ABC
|
P
|
2014-11-10
|
0.35
|
69.6191
|
-0.4584
|
0.0025
|
-0.0935
|
1.8148
|
-2.4399
|
-3.2594
|
|
4
|
ABC
|
C
|
2014-09-14
|
0.35
|
52.1462
|
0.5225
|
0.0028
|
-0.1099
|
1.6776
|
1.4062
|
4.9602
|
|
5
|
DEF
|
C
|
2014-11-14
|
0.3
|
27.0332
|
0.8368
|
0.0095
|
-0.0176
|
0.266
|
0.3694
|
3.8691
|
|
6
|
DEF
|
C
|
2014-12-12
|
0.3
|
20.5917
|
0.7074
|
0.0105
|
-0.0194
|
0.4119
|
0.4817
|
4.294
|
|
7
|
DEF
|
P
|
2014-08-11
|
0.3
|
10.8057
|
-0.4241
|
0.0132
|
-0.0237
|
0.3894
|
-0.3961
|
-4.9063
|
|
8
|
DEF
|
C
|
2014-08-26
|
0.3
|
9.2736
|
0.4623
|
0.0133
|
-0.0248
|
0.4015
|
0.2978
|
6.231
|
|
9
|
GHI
|
C
|
2014-08-04
|
0.25
|
12.6717
|
0.9533
|
0.0132
|
-0.0044
|
0.0457
|
0.1018
|
4.7021
|
|
10
|
GHI
|
C
|
2014-12-28
|
0.25
|
8.5559
|
0.7878
|
0.034
|
-0.0098
|
0.1358
|
0.1398
|
5.7549
|
|
11
|
GHI
|
P
|
2014-10-09
|
0.25
|
6.0389
|
-0.4925
|
0.03
|
-0.0086
|
0.2132
|
-0.271
|
-5.0973
|
|
12
|
GHI
|
C
|
2014-11-14
|
0.25
|
3.5027
|
0.416
|
0.0317
|
-0.0095
|
0.2083
|
0.1521
|
7.4237
|