BinomialEuro
Updated 2023-11-16 15:42:32.153000
Syntax
SELECT [westclintech].[wct].[BinomialEuro] (
<@CallPut, nvarchar(4000),>
,<@AssetPrice, float,>
,<@StrikePrice, float,>
,<@TimeToMaturity, float,>
,<@RiskFreeRate, float,>
,<@DividendRate, float,>
,<@Volatility, float,>
,<@NSteps, int,>
,<@ReturnValue, nvarchar(4000),>)
Description
Use the scalar function BinomialEuro to calculate the price, delta, gamma, theta, vega, rho or lambda of a European option using the Binomial Tree option pricing formula.
Arguments
@nSteps
the number of steps in the binomial tree. @nSteps is an expression of type int or of a type that can be implicitly converted to int.
@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.
@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:
{
"columns": [
{
"field": "@ReturnValue"
},
{
"field": "Returns"
}
],
"rows": [
{
"@ReturnValue": "'P','PRICE'",
"Returns": "Price"
},
{
"@ReturnValue": "'D','DELTA'",
"Returns": "Delta"
},
{
"@ReturnValue": "'G','GAMMA'",
"Returns": "Gamma"
},
{
"@ReturnValue": "'T','THETA'",
"Returns": "Theta"
},
{
"@ReturnValue": "'V','VEGA'",
"Returns": "Vega"
},
{
"@ReturnValue": "'R','RHO'",
"Returns": "Rho"
},
{
"@ReturnValue": "'L','LAMBDA'",
"Returns": "Lambda"
},
{
"@ReturnValue": "'DDDV','VANNA','DVEGADSPOT','DDELTADVOL'",
"Returns": "DdeltaDvol"
}
]
}
@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.
@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.
@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.
@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.
@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.
@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.
Return Type
float
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).
@nSteps must be greater than 1 (@nSteps > 1).
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
For pricing American options, use BJERKSUNDSTENSLAND or BINOMIALAMERICAN.
To use the Black-Scholes-Merton method for European options, use BLACKSCHOLESMERTON.
To get the implied volatility (given price), use BINOMIALEUROIV.
To calculate the price and all the Greeks, use BINOMIALPRICENGREEKS.
Examples
Calculate the price for a call option on 2012-09-04, expiring on 2012-12-15, with a current asset price of 99.5, a strike price of 100 and a volatility of 20%. The risk free rate is 2% and the dividend rate is 0.5%. The number of steps is 100.
SELECT cast(wct.BinomialEuro('C', --PutCall
99.5, --Asset Price
100, --Strike Price
datediff(d, '2012-09-04', '2012-12-15') / 365.0000, --Time-to-expiry
.02, --Risk Free Rate
.005, --Dividend Rate
.20, --Volatility
100, --Number of Steps
NULL --Return Value
) as money) as Price;
This produces the following result.
{"columns":[{"field":"Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Price":"4.1547"}]}
To calculate the delta for the same option:
SELECT cast(wct.BinomialEuro('C', --PutCall
99.5, --Asset Price
100, --Strike Price
datediff(d, '2012-09-04', '2012-12-15') / 365.0000, --Time-to-expiry
.02, --Risk Free Rate
.005, --Dividend Rate
.20, --Volatility
100, --Number of Steps
'D' --Return Value
) as money) as Delta
This produces the following result.
{"columns":[{"field":"Delta","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Delta":"0.5172"}]}
In this example we calculate the Call and Put prices for the same option with a series of different asset prices.
SELECT cast(n.S as money) as [Underlying],
cast(wct.BinomialEuro('C', --PutCall
n.s, --Asset Price
100, --Strike Price
datediff(d, '2012-09-04', '2012-12-15') / 365.0000, --Time-to-expiry
.02, --Risk Free Rate
.005, --Dividend Rate
.20, --Volatility
100, --Number of Steps
'P' --Return Value
) as money) as [Call Price],
cast(wct.BinomialEuro('P', --PutCall
n.s, --Asset Price
100, --Strike Price
datediff(d, '2012-09-04', '2012-12-15') / 365.0000, --Time-to-expiry
.02, --Risk Free Rate
.005, --Dividend Rate
.20, --Volatility
100, --Number of Steps
'P' --Return Value
) as money) as [Put Price]
FROM ( SELECT 98.5
UNION ALL
SELECT 99.0
UNION ALL
SELECT 99.5
UNION ALL
SELECT 100
UNION ALL
SELECT 100.5
UNION ALL
SELECT 101) n(S);
This produces the following result.
{"columns":[{"field":"Underlying","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Call Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Put Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Underlying":"98.50","Call Price":"3.6582","Put Price":"4.7384"},{"Underlying":"99.00","Call Price":"3.9064","Put Price":"4.4873"},{"Underlying":"99.50","Call Price":"4.1547","Put Price":"4.2362"},{"Underlying":"100.00","Call Price":"4.4029","Put Price":"3.9852"},{"Underlying":"100.50","Call Price":"4.6907","Put Price":"3.7737"},{"Underlying":"101.00","Call Price":"4.9785","Put Price":"3.5621"}]}