sp_OptionMatrix
Updated: 04 Sep 2012
Use the stored procedure sp_OptionMatrix to generate a result set of all 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. This stored procedure calls the tablevalued function OptionMatrix and formats the output into a matrix.
Syntax
DECLARE @CallPut nvarchar(4000)
DECLARE @AssetPrice float
DECLARE @StrikePrice float
DECLARE @TimeToMaturity float
DECLARE @RiskFreeRate float
DECLARE @DividendRate float
DECLARE @Volatility float
DECLARE @ReturnValue nvarchar(4000)
DECLARE @AmEur nvarchar(4000)
DECLARE @Row nvarchar(4000)
DECLARE @RowStep float
DECLARE @RowNumSteps int
DECLARE @Col nvarchar(4000)
DECLARE @ColStep float
DECLARE @ColNumSteps int
DECLARE @Notional float
DECLARE @Decimals int
 TODO: Set parameter values here.
EXECUTE [wctOptions].[wct].[sp_OptionMatrix]
@CallPut
,@AssetPrice
,@StrikePrice
,@TimeToMaturity
,@RiskFreeRate
,@DividendRate
,@Volatility
,@ReturnValue
,@AmEur
,@Row
,@RowStep
,@RowNumSteps
,@Col
,@ColStep
,@ColNumSteps
,@Notional
,@Decimals
GO
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 riskfree 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 riskfree 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 casesensitive. 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.
@Notional
Identifies the notional value of the option position. @Notional is an expression of type float or of a type that can be implicitly converted to float.
@Decimals
Identifies the number of decimal places in the returned values. @Decimal is an expression of type int or of a type that can be implicitly converted to int.
Return Code Values
0 (success) or 100 (failure)
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 BlackScholesMerton.
· American options are calculated using Bjerksund & Stensland 2002.
· @Row cannot be the same as @Col
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 20130621 and today’s date is 20120904. The continuously compounded risk free rate is 2% and the continuously compounded dividend rate is 1.25%. The volatility is 20%. 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. The notional amount is 1,000,000 and we want the results returned to 2 decimal places.
DECLARE @CallPut nvarchar(4000) = 'C'
DECLARE @AssetP float = 105
DECLARE @Strike float = 100
DECLARE @Time float = datediff(d,'20120904','20130621')/cast(365 as float)
DECLARE @RiskFree float = .02
DECLARE @Div float = .0125
DECLARE @Vol float = .20
DECLARE @ReturnValue nvarchar(4000) = 'P'
DECLARE @AmEur nvarchar(4000) = 'E'
DECLARE @Row nvarchar(4000) = 'UNDERLYING'
DECLARE @RowStep float = .50
DECLARE @RowNumSteps int = 3
DECLARE @Col nvarchar(4000) = 'VOL'
DECLARE @ColStep float = .01
DECLARE @ColNumSteps int = 2
DECLARE @Notional float = 1000000
DECLARE @Decimals int = 2
EXECUTE wct.sp_OptionMatrix
@CallPut
,@AssetP
,@Strike
,@Time
,@RiskFree
,@Div
,@Vol
,@ReturnValue
,@AmEur
,@Row
,@RowStep
,@RowNumSteps
,@Col
,@ColStep
,@ColNumSteps
,@Notional
,@Decimals
GO
This produces the following result
Remember, that this is a stored procedure and in TSQL the inputs to the stored procedure are not evaluated. So, if we had used the following syntax,
EXECUTE wct.sp_OptionMatrix
'C'
,105
,100
,datediff(d,'20120904','20130621')/cast(365 as float)
,.02
,.0125
,.20
,'P'
,'E'
,'UNDERLYING'
,.50
,3
,'VOL'
,.01
,2
,1000000
,2
GO
SQL Server will generate an error message. We could, however, have entered
EXECUTE wct.sp_OptionMatrix]
'C'
,105
,100
,0.794520547945205
,.02
,.0125
,.20
,'P'
,'E'
,'UNDERLYING'
,.50
,3
,'VOL'
,.01
,2
,1000000
,2
GO
and the stored procedure would have executed and returned the results as above.