Updated: 04 Sep 2012

Use the stored procedure sp_OptionPLMatrix to generate a result set of profit (loss) by varying two inputs into the theoretical value of the option. For example, you could generate a result set that shows the profit (loss) based on changes in the price of the underlying asset and the volatility. This stored procedure calls the table-valued function OptionPLMatrix and formats the output into a matrix.

DECLARE @CallPut nvarchar(4000)

DECLARE @AssetPrice float

DECLARE @StrikePrice float

DECLARE @TimeToMaturity float

DECLARE @RiskFreeRate float

DECLARE @DividendRate float

DECLARE @Volatility float

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_OptionPLMatrix]

@CallPut

,@AssetPrice

,@StrikePrice

,@TimeToMaturity

,@RiskFreeRate

,@DividendRate

,@Volatility

,@AmEur

,@Row

,@RowStep

,@RowNumSteps

,@Col

,@ColStep

,@ColNumSteps

,@Notional

,@Decimals

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**.

the price of the underlying asset.* @AssetPrice* is an expression of type **float** or of a type that can be implicitly converted to **float**.

the exercise price of the option.* @StrikePrice* is an expression of type **float** or of a type that can be implicitly converted to **float**.

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**.

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**.

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**.

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**.

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**.

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'

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**.

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**.

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'

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**.

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**.

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**.

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**.

· *@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*

· 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.

In this example, we are going to calculate how the changes in the underlying and volatility will affect the profit on 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 @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_OptionPLMatrix

@CallPut

,@AssetP

,@Strike

,@Time

,@RiskFree

,@Div

,@Vol

,@AmEur

,@Row

,@RowStep

,@RowNumSteps

,@Col

,@ColStep

,@ColNumSteps

,@Notional

,@Decimals

GO

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_OptionPLMatrix

'C'

,105

,100

,datediff(d,'2012-09-04','2013-06-21')/cast(365 as float)

,.02

,.0125

,.20

,'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_OptionPLMatrix

'C'

,105

,100

,0.794520547945205

,.02

,.0125

,.20

,'E'

,'UNDERLYING'

,.50

,3

,'VOL'

,.01

,2

,1000000

,2

GO

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