SQL Server sp_BinomialTree Function

Updated 2023-11-16 16:12:23.223000

Description

Use the stored procedure sp_BinomialTree to generate a result set of all the values in the binomial tree used to calculate the theoretical price of an option. This stored procedure calls the table-valued function BinomialTree 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 @nSteps int
DECLARE @AmEur nvarchar(4000)
 
-- TODO: Set parameter values here.
 
EXECUTE [westclintech].[wct].[sp_BinomialTree]
  @CallPut
 ,@AssetPrice
 ,@StrikePrice
 ,@TimeToMaturity
 ,@RiskFreeRate
 ,@DividendRate
 ,@Volatility
 ,@nSteps
 ,@AmEur
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.

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

Examples

Create the binomial tree for an American 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.

DECLARE @CallPut nvarchar(4000) = 'C';
DECLARE @AssetP float = 99.5;
DECLARE @Strike float = 100
DECLARE @Time float = DATEDIFF(d, '2012-09-04', '2012-12-15') / CAST(365 AS float);
DECLARE @RiskFree float = .02;
DECLARE @Div float = .005;
DECLARE @Vol float = .20;
DECLARE @nSteps int = 100;
DECLARE @AmEur nvarchar(4000) = 'E';

EXECUTE wct.sp_BinomialTree @CallPut,
                            @AssetP,
                            @Strike,
                            @Time,
                            @RiskFree,
                            @Div,
                            @Vol,
                            @nSteps,
                            @AmEur;

Here is the output.