Login     Register

        Contact Us     Search

XLeratorDB/financial-options Documentation

SQL Server function options values change matrix


OptionMatrix

Updated: 04 Sep 2012


Use the table-valued function OptionMatrix to generate a result set of return values by varying two inputs into the calculated value. For example, you could generate a result set that shows how a change in the underlying and a change in the volatility affect the price.
Syntax
SELECT *
FROM [wctOptions].[wct].[OptionMatrix] (
  <@CallPut, nvarchar(4000),>
 ,<@AssetPrice, float,>
 ,<@StrikePrice, float,>
 ,<@TimeToMaturity, float,>
 ,<@RiskFreeRate, float,>
 ,<@DividendRate, float,>
 ,<@Volatility, float,>
 ,<@ReturnValue, nvarchar(4000),>
 ,<@AmEur, nvarchar(4000),>
 ,<@Row, nvarchar(4000),>
 ,<@RowStep, float,>
 ,<@RowNumSteps, int,>
 ,<@Col, nvarchar(4000),>
 ,<@ColStep, float,>
 ,<@ColNumSteps, 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.
@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 risk-free rate of return 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 dividend rate over the life of the option. For currency options, @DividendRate should be the foreign risk-free interest rate. @DividendRate 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.
@ReturnValue
identifies the calculation to be performed. @ReturnValue is an expression of type nvarchar or of a type that can be implicitly converted to nvarchar. @ReturnValue is not case-sensitive. The following values are acceptable for @ReturnValue.
@ReturnValue
Returns
'P', 'PRICE'
Price
'D', 'DELTA'
Delta
'G', 'GAMMA'
Gamma
'V', 'VEGA'
Vega
'T', 'THETA'
Theta
'R', 'RHO'
Rho
'L', 'LAMBDA'
Lambda
@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.
@Row
Identifies the variable which is changing with each row. @Row is an expression of type nvarchar or of a type that can be implicitly converted to nvarchar. The following values may be passed into @Row:
'S', 'U', 'ASSETP', 'UNDERLYING'
'X', 'K', 'STRIKE'
'T', 'TIME'
'R', 'RF', 'RISKFREE'
'D', 'DIV', 'DIVIDEND'
'V', 'VOL', 'VOLATILITY', 'SIGMA'
@RowStep
Identifies the value by which the intial row value is incremented and/or decremented. In the case of time ('T') the row values are only decremented and the step value is assumed to be expressed in days. @RowStep is an expression of type float or of a type that can be implicitly converted to float.
@RowNumSteps
Identifies the number of times that the initial row value is incremented and/or decremented. @RowNumSteps is an expression of type int or of a type that can be implicitly converted to int.
@Col
Identifies the variable which is changing with each column. @Col is an expression of type nvarchar or of a type that can be implicitly converted to nvarchar. The following values may be passed into @Col:
'S', 'U', 'ASSETP', 'UNDERLYING'
'X', 'K', 'STRIKE'
'T', 'TIME'
'R', 'RF', 'RISKFREE'
'D', 'DIV', 'DIVIDEND'
'V', 'VOL', 'VOLATILITY', 'SIGMA'
@ColStep
Identifies the value by which the intial column value is incremented and/or decremented. In the case of time ('T') the row values are only decremented and the step value is assumed to be expressed in days. @ColStep is an expression of type float or of a type that can be implicitly converted to float.
@ColNumSteps
Identifies the number of times that the initial column value is incremented and/or decremented. @ColNumSteps is an expression of type int or of a type that can be implicitly converted to int.
Return Types
RETURNS TABLE (
      [idx_row] [int] NULL,
      [idx_col] [int] NULL,
      [row] [float] NULL,
      [col] [float] NULL,
      [val] [float] NULL
)

Column
Column Description
idx_row
The row index into a zero-based 2-dimensional array
idx_col
The column index into a zero-based 2-dimensional array
row
The value of the row in @Row units
col
The value of the column in @Col unit
val
The return value, calculated using row and col

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).
·         If @ReturnValue is NULL, then @ReturnValue is set to 'P'.
·         If @DividendRate is NULL an error will be returned.
·         If @RiskFreeRate is NULL an error will be returned.
·         @RowNumSteps must be greater than zero.
·         @ColNumSteps must be greater than zero.
·         European options are calculated using Black-Scholes-Merton.
·         American options are calculated using Bjerksund & Stensland 2002.
·         For results automatically formatted into a ‘matrix’ format, use the sp_OptionMatrix stored procedure.
·         @Row cannot be the same as @Col.
·         For matrix P&L calculations use OptionPLMatrix.
Examples
In this example, we are going to calculate how the changes in the underlying and volatility will affect the price of a Call option where the underlying is valued at 105, the strike price is 100, the option expires on 2013-06-21 and today’s date is 2012-09-04. The continuously compounded risk free rate is 2% and the continuously compounded dividend rate is 1.25%. The volatility is 20%. We have put the initial values of the option into variables simply to make the SQL easier to read.
DECLARE @rv as char(1)
DECLARE @s as float
DECLARE @x as float
DECLARE @t as float
DECLARE @r as float
DECLARE @d as float
DECLARE @v as float
DECLARE @z as char(1)
SET @z = 'C'      --Call/Put
SET @s = 105      --Underlying
SET @x = 100      --Strike
SET @t = datediff(d,'2012-09-04','2013-06-21')/cast(365 as float) --Time
SET @r = .02      --RiskFree
SET @d = .0125    --Dividend
SET @v = .20      --Volatility
SET @rv = 'P'     --ReturnValue
Now we will invoke the table-valued function, specifying that we want the rows to move the underlying 3 steps in increments of 0.5 and the columns to move the volatility in 2 steps in increments of 0.01. This means that we will calculate new price values where the underlying prices are 103.5, 104.0, 104.5, 105.0, 105.5, 106, and 106.5 and where the volatilities are .18, .19, .20, .21, and .22.
SELECT *
FROM wct.OptionMatrix(@z,@s,@x,@t,@r,@d,@v,@rv,'E','UNDERLYING',0.5,3,'VOL',.01,2)
This produces the following result.
    idx_row     idx_col                    row                    col                    val
----------- ----------- ---------------------- ---------------------- ----------------------
          0           0                  103.5                   0.18       8.65125863951278
          0           1                  103.5                   0.19       8.99666600913994
          0           2                  103.5                    0.2       9.34297950448698
          0           3                  103.5                   0.21       9.69004597092759
          0           4                  103.5                   0.22       10.0377383101028
          1           0                    104                   0.18       8.96593117681508
          1           1                    104                   0.19       9.30955542553718
          1           2                    104                    0.2       9.65434254265256
          1           3                    104                   0.21       10.0001040359328
          1           4                    104                   0.22        10.346683598625
          2           0                  104.5                   0.18       9.28614653939416
          2           1                  104.5                   0.19       9.62771541070827
          2           2                  104.5                    0.2       9.97072809922398
          2           3                  104.5                   0.21       10.3149578200949
          2           4                  104.5                   0.22       10.6602165370413
          3           0                    105                   0.18        9.6118164453604
          3           1                    105                   0.19       9.95106673110088
          3           2                    105                    0.2       10.2920645067008
          3           3                    105                   0.21        10.634542047474
          3           4                    105                   0.22       10.9782772994258
          4           0                  105.5                   0.18       9.94284967021544
          4           1                  105.5                   0.19       10.2795276440593
          4           2                  105.5                    0.2       10.6182779491688
          4           3                  105.5                   0.21        10.958789595374
          4           4                  105.5                   0.22       11.3008044666979
          5           0                    106                   0.18        10.279152305361
          5           1                    106                   0.19       10.6130141082259
          5           2                    106                    0.2       10.9492926355384
          5           3                    106                   0.21       11.2876316384658
          5           4                    106                   0.22       11.6277351476435
          6           0                  106.5                   0.18       10.6206280142049
          6           1                  106.5                   0.19        10.951439991888
          6           2                  106.5                    0.2       11.2850309709864
          6           3                  106.5                   0.21        11.620997791316
          6           4                  106.5                   0.22       11.9590050985052
 
If we were going to populate a 2-dimensional array with the calculated prices, then Array(0,0) would contain 8.65125863951278 and Array(6,4) would contain 11.9590050985052. If we are not interested in the idx_row and idx_col columns, we can explicitly select the columns that we want.
SELECT row, col, Cast(val as money) as val
FROM wct.OptionMatrix(@z,@s,@x,@t,@r,@d,@v,@rv,'E','UNDERLYING',0.5,3,'VOL',.01,2)
This produces the following result.
                   row                    col                   val
---------------------- ---------------------- ---------------------
                 103.5                   0.18                8.6513
                 103.5                   0.19                8.9967
                 103.5                    0.2                 9.343
                 103.5                   0.21                  9.69
                 103.5                   0.22               10.0377
                   104                   0.18                8.9659
                   104                   0.19                9.3096
                   104                    0.2                9.6543
                   104                   0.21               10.0001
                   104                   0.22               10.3467
                 104.5                   0.18                9.2861
                 104.5                   0.19                9.6277
                 104.5                    0.2                9.9707
                 104.5                   0.21                10.315
                 104.5                   0.22               10.6602
                   105                   0.18                9.6118
                   105                   0.19                9.9511
                   105                    0.2               10.2921
                   105                   0.21               10.6345
                   105                   0.22               10.9783
                 105.5                   0.18                9.9428
                 105.5                   0.19               10.2795
                 105.5                    0.2               10.6183
                 105.5                   0.21               10.9588
                 105.5                   0.22               11.3008
                   106                   0.18               10.2792
                   106                   0.19                10.613
                   106                    0.2               10.9493
                   106                   0.21               11.2876
                   106                   0.22               11.6277
                 106.5                   0.18               10.6206
                 106.5                   0.19               10.9514
                 106.5                    0.2                11.285
                 106.5                   0.21                11.621
                 106.5                   0.22                11.959
 
If we wanted to PIVOT the results, we could enter the following SQL.
SELECT row, [0.18], [0.19], [0.20], [0.21], [0.22]
FROM (
      SELECT row, col, Cast(val as money) as val
      FROM wct.OptionMatrix(@z,@s,@x,@t,@r,@d,@v,@rv,'E','UNDERLYING',0.5,3,'VOL',.01,2)
      ) d
PIVOT (sum(val) for col in ([0.18], [0.19], [0.20], [0.21], [0.22])) as P
This produces the following result


Of course, this required that we know the column-values before running the SQL. The sp_OptionMatrix stored procedure will automatically figure out the column headings for you and execute the SQL by calling the table-valued function.
Let’s say you wanted to swap the rows and columns.
SELECT row, [103.5], [104], [104.5], [105], [105.5], [106], [106.5]
FROM (
      SELECT row, col, Cast(val as money) as val
      FROM wct.OptionMatrix(@z,@s,@x,@t,@r,@d,@v,@rv,'E','VOL',.01,2,'UNDERLYING',0.5,3)
      ) d
PIVOT (sum(val) for col in ([103.5], [104], [104.5], [105], [105.5], [106], [106.5])) as P
This produces the following result.



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service