SQL Server sp_OptionMatrix Function

Updated 2023-11-17 15:31:51.483000

Description

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 table-valued 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 [westclintech].[wct].[sp_OptionMatrix]
  @CallPut
 ,@AssetPrice
 ,@StrikePrice
 ,@TimeToMaturity
 ,@RiskFreeRate
 ,@DividendRate
 ,@Volatility
 ,@ReturnValue
 ,@AmEur
 ,@Row
 ,@RowStep
 ,@RowNumSteps
 ,@Col
 ,@ColStep
 ,@ColNumSteps
 ,@Notional
 ,@Decimals
GO

Arguments

Return Type

int

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.

@Row cannot be the same as @Col

Return Code Values: 0 (success) or 100 (failure).

Results are returned so that the lower left cell of the matrix has the smallest profit (biggest loss) and the upper right cell of the matrix has greatest profit (smallest loss) and the center cell has a profit of zero.

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 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, '2012-09-04', '2013-06-21') / 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;

This produces the following result.

Remember, that this is a stored procedure and in T-SQL 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,'2012-09-04','2013-06-21')
          /cast(365 as float)
 ,.02
 ,.0125
 ,.20
 ,'P'
 ,'E'
 ,'UNDERLYING'
 ,.50
 ,3
 ,'VOL'
 ,.01
 ,2
 ,1000000
 ,2;

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;

and the stored procedure would have executed and returned the results as above.