/*------------------------------------*/
/*  Black-Scholes Pricing Function    */
/*  (c)2009 WestClinTech              */
/*                                    */
/*  This SQL Server function uses     */
/*  the XLeratorDB function packages  */
/*  available at:                     */
/*                                    */
/*  www.westclintech.com              */
/*------------------------------------*/


CREATE FUNCTION [dbo].[bs] 
(
	-- Add the parameters for the function here
	@Shareprice float, 
	@Strikeprice float,
	@Volatility float,
	@Time float,
	@Rate float
)
RETURNS 
@bs TABLE 
(
	-- Add the column definitions for the TABLE variable here
	stat_name varchar(50), 
	call_value float,
	put_value float
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	DECLARE @d1 as float,
	@d2 as float,
	@Val_Call as float,
	@Val_Put as float,
	@Delta_call as float,
	@Delta_put as float,
	@phi_d1 as float,
	@Vega as float,
	@Theta_call as float,
	@Theta_put as float,
	@Rho_call as float,
	@Rho_put as float,
	@lambda_call as float,
	@lambda_put as float,
	@Gamma as float
	
set @d1 = (Log(@shareprice / @strikeprice) + (@rate + POWER(@Volatility,2) / 2.0) * @time) / (@Volatility * Sqrt(@time))
	set @d2 = @d1 - (@Volatility * Sqrt(@Time))
set @Val_call = @Shareprice * wct.NORMSDIST(@d1) - @Strikeprice * EXP(-@rate*@time) * wct.NORMSDIST(@d2)
set @Val_put = (@Strikeprice * EXP(-@rate*@time) * wct.NORMSDIST(-@d2)) - (@Shareprice * wct.NORMSDIST(-@d1))
	set @Delta_Call = wct.NORMSDIST(@d1)
	set @Delta_Put = -wct.NORMSDIST(-@d1)
	set @Phi_d1 = (1 / Sqrt(2 * PI())) * (exp(-0.5 * power(@d1,  2)))
	set @Vega = @Shareprice * Sqrt(@Time) * @Phi_d1 * .01
set @Theta_call = -(@Shareprice * @Volatility * @Phi_d1 / (2 * Sqrt(@Time))) - (@Rate * (@Strikeprice * EXP(-@rate*@time)) * wct.NORMSDIST(@d2))
	set @Theta_call = @Theta_call / 365
set @Theta_put = -(@Shareprice * @Volatility * @Phi_d1 / (2 * Sqrt(@Time))) + (@Rate * (@Strikeprice * EXP(-@rate*@time)) * wct.NORMSDIST(-@d2))
	set @Theta_put = @Theta_put / 365
set @Rho_Call = @Time * (@Strikeprice * EXP(-@rate*@time)) * wct.NORMSDIST(@d2) * .01
set @Rho_put = -@Time * (@Strikeprice* EXP(-@rate*@time)) * wct.NORMSDIST(-@d2) * .01
	set @Lambda_Call = @Delta_Call * @Shareprice / @Val_call
	set @Lambda_Put = @Delta_Put * @Shareprice / @Val_put
	set @gamma = @Phi_d1 / (@Shareprice * @Volatility * Sqrt(@Time))

	INSERT into @bs VALUES ('Value', @Val_call, @Val_put)
	INSERT into @bs VALUES ('Delta', @Delta_call, @Delta_put)
	INSERT into @bs VALUES ('Vega', @Vega, @Vega)
	INSERT into @bs VALUES ('Theta', @Theta_call, @Theta_put)
	INSERT into @bs VALUES ('Rho', @Rho_call, @Rho_put)
	INSERT into @bs VALUES ('Lambda', @lambda_call, @lambda_put)
	INSERT into @bs VALUES ('Gamma', @Gamma, @Gamma)
	RETURN 
END


GO

 
CREATE FUNCTION [dbo].[bs1] 
(
	-- Add the parameters for the function here
	@Shareprice float, 
	@Strikeprice float,
	@Volatility float,
	@Time float,
	@Rate float
)
RETURNS 
@bs TABLE 
(
	-- Add the column definitions for the TABLE variable here
	d1 float NOT NULL,
	d2 float NOT NULL,
	Value_Call float NOT NULL,
	Value_Put float NOT NULL,
	Delta_call float NOT NULL,
	Delta_put float NOT NULL,
	Vega float NOT NULL,
	theta_call float NOT NULL,
	theta_put float NOT NULL,
	Rho_call float NOT NULL,
	Rho_put float NOT NULL,
	lambda_call float NOT NULL,
	lambda_put float NOT NULL,
	Gamma float NOT NULL
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	DECLARE @d1 as float,
	@d2 as float,
	@Val_Call as float,
	@Val_Put as float,
	@Delta_call as float,
	@Delta_put as float,
	@phi_d1 as float,
	@Vega as float,
	@Theta_call as float,
	@Theta_put as float,
	@Rho_call as float,
	@Rho_put as float,
	@lambda_call as float,
	@lambda_put as float,
	@Gamma as float
	

	set @d1 = (Log(@shareprice / @strikeprice) + (@rate + POWER(@Volatility,2) / 2.0) * @time) / (@Volatility * Sqrt(@time))
	set @d2 = @d1 - (@Volatility * Sqrt(@Time))
	set @Val_call = @Shareprice * wct.NORMSDIST(@d1) - @Strikeprice * EXP(-@rate*@time) * wct.NORMSDIST(@d2)
	set @Val_put = (@Strikeprice * EXP(-@rate*@time) * wct.NORMSDIST(-@d2)) - (@Shareprice * wct.NORMSDIST(-@d1))
	set @Delta_Call = wct.NORMSDIST(@d1)
	set @Delta_Put = -wct.NORMSDIST(-@d1)
	set @Phi_d1 = (1 / Sqrt(2 * PI())) * (exp(-0.5 * power(@d1,  2)))
	set @Vega = @Shareprice * Sqrt(@Time) * @Phi_d1 * .01
	set @Theta_call = -(@Shareprice * @Volatility * @Phi_d1 / (2 * Sqrt(@Time))) - (@Rate * (@Strikeprice * EXP(-@rate*@time)) * wct.NORMSDIST(@d2))
	set @Theta_call = @Theta_call / 365
	set @Theta_put = -(@Shareprice * @Volatility * @Phi_d1 / (2 * Sqrt(@Time))) + (@Rate * (@Strikeprice * EXP(-@rate*@time)) * wct.NORMSDIST(-@d2))
	set @Theta_put = @Theta_put / 365
	set @Rho_Call = @Time * (@Strikeprice * EXP(-@rate*@time)) * wct.NORMSDIST(@d2) * .01
	set @Rho_put = -@Time * (@Strikeprice* EXP(-@rate*@time)) * wct.NORMSDIST(-@d2) * .01
	set @Lambda_Call = @Delta_Call * @Shareprice / @Val_call
	set @Lambda_Put = @Delta_Put * @Shareprice / @Val_put
	set @gamma = @Phi_d1 / (@Shareprice * @Volatility * Sqrt(@Time))

	INSERT @bs 
SELECT @d1, @d2, @Val_call, @Val_put, @delta_call, @Delta_put, @Vega, @Theta_call, @Theta_put, @Rho_Call, @Rho_put, @lambda_Call, @Lambda_put, @Gamma
	RETURN 
END

GO