Black-Scholes Excel Library

black-scholes.gcode

// BS Call calculation function

BSCall = LAMBDA(Stock,Exercise,Rate,Sigma,Time,Yield, 
Stock * EXP(-Yield * Time) *
    NORM.S.DIST(
        (LN(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * SQRT(Time)),
        TRUE
    ) -
    Exercise * EXP(-Rate * Time) *
        NORM.S.DIST(
            (LN(Stock / Exercise) + (Rate - (Sigma ^ 2) / 2) * Time) / (Sigma * SQRT(Time)),
            TRUE
        )

);


//BS Put option calculation function


BSPut = LAMBDA(Stock,Exercise,Rate,Sigma,Time,Yield,-Stock * EXP(-Yield * Time) *
    NORM.S.DIST(
        -(LN(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * SQRT(Time)),
        TRUE
    ) +
    Exercise * EXP(-Rate * Time) *
        NORM.S.DIST(
            -(LN(Stock / Exercise) + (Rate - (Sigma ^ 2) / 2) * Time) / (Sigma * SQRT(Time)),
            TRUE
        )
  );
  
//Put Call Greek Functions

BSCallDelta = LAMBDA(Stock, Exercise, Rate, Sigma, Time, Yield, NORM.S.DIST(
    (LN(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * SQRT(Time)),
    TRUE
) * EXP(-Yield * Time)
);

BSGamma = LAMBDA(Stock, Exercise, Rate, Sigma, Time, Yield, EXP(-Yield * Time) *
    NORM.S.DIST(
        (LN(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * SQRT(Time)),
        FALSE
    ) / (Stock * Sigma * Time ^ 0.5)


);

BSPutDelta = LAMBDA(Stock, Exercise, Rate, Sigma, Time, Yield, (
    (
        NORM.S.DIST(
            (LN(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * SQRT(Time)),
            TRUE
        )
    ) - 1
) * EXP(-Yield * Time)

);
BSRhoCall = LAMBDA(Stock, Exercise, Rate, Sigma, Time, Yield, NORM.S.DIST(
    (LN(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * SQRT(Time)) -
        Sigma * SQRT(Time),
    TRUE
) * Exercise * Time * EXP(-Rate * Time) / 100

);
BSRhoPut = LAMBDA(Stock, Exercise, Rate, Sigma, Time, Yield, -NORM.S.DIST(
    -(LN(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * SQRT(Time)) -
        Sigma * SQRT(Time),
    TRUE
) * Exercise * Time * EXP(-Rate * Time) / 100

);
BSThetaCall = LAMBDA(Stock, Exercise, Rate, Sigma, Time, Yield, TradingDays, (1 / TradingDays) *
    (
        -(
            Stock * Sigma * EXP(-Yield * Time) *
                NORM.S.DIST(
                    (LN(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * SQRT(Time)),
                    FALSE
                ) / (2 * SQRT(Time))
        ) -
            Rate * Exercise * EXP(-Rate * Time) *
                NORM.S.DIST(
                    (LN(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) /
                        (Sigma * SQRT(Time)) - Sigma * SQRT(Time),
                    TRUE
                ) +
            Yield * Stock * EXP(-Yield * Time) *
                NORM.S.DIST(
                    (LN(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * SQRT(Time)),
                    TRUE
                )
    )

);
BSThetaPut = LAMBDA(Stock, Exercise, Rate, Sigma, Time, Yield, TradingDays, (1 / TradingDays) *
    (
        -(
            Stock * Sigma * EXP(-Yield * Time) *
                NORM.S.DIST(
                    (LN(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * SQRT(Time)),
                    FALSE
                ) / (2 * SQRT(Time))
        ) +
            Rate * Exercise * EXP(-Rate * Time) *
                NORM.S.DIST(
                    -(LN(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) /
                        (Sigma * SQRT(Time)) - Sigma * SQRT(Time),
                    TRUE
                ) -
            Yield * Stock * EXP(-Yield * Time) *
                NORM.S.DIST(
                    -(LN(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) /
                        (Sigma * SQRT(Time)),
                    TRUE
                )
    )


);

BSVega = LAMBDA(Stock, Exercise, Rate, Sigma, Time, NORM.S.DIST(
    (LN(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * SQRT(Time)),
    FALSE
) * Stock * SQRT(Time) / 100


);
  
  

Black-Scholes Option Pricing Set
Stock: Current price of the underlying
Exercise: strike price of the stock
Rate: Risk free rate %
Time: Number of years until expiration
Sigma: Volatility
Yield: dividend or interest rate yield of the asset