Black-Scholes Option Valuation

BlackScholes

Returns the Black-Scholes European call/put valuation.

=BlackScholes(call_put_flag, stock_price, strike_price, time_to_expiry, risk-free rate, dividend_yield, volatility)

' Returns 2.1334
=BlackScholes("c", 60, 65, 0.25, 0.08, 0, 0.3)
  • call_put_flag is whether the instrument is a call ("c") or a put ("p").
  • stock_price is the current value of the underlying stock.
  • strike_price is the option's strike price.
  • time_to_expiry is the time to maturity in years.
  • risk-free rate is the risk-free rate through expiry.
  • dividend_yield is the annual dividend yield.
  • volatility is the implied volatility at expiry.

GBlackScholes

Returns the Black-Scholes European call/put valuation.

=GBlackScholes(call_put_flag, stock_price, strike_price, time_to_expiry, risk-free rate, cost_of_carry, volatility)
  • call_put_flag is whether the instrument is a call ("c") or a put ("p").
  • stock_price is the current value of the underlying stock.
  • strike_price is the option's strike price.
  • time_to_expiry is the time to maturity in years.
  • risk-free rate is the risk-free rate through expiry.
  • cost_of_carry is the annualized cost of carry.
  • volatility is the implied volatility at expiry.

ImpliedVolatility

Returns the Black-Scholes implied volatility using the Newton-Raphson method.

=ImpliedVolatility(call_put_flag, stock_price, strike_price, time_to_expiry, risk-free rate, dividend_yield, price)

' Returns 0.3
=ImpliedVolatility("c", 60, 65, 0.25, 0.08, 0, 2.1334)
  • call_put_flag is whether the instrument is a call ("c") or a put ("p").
  • stock_price is the current value of the underlying stock.
  • strike_price is the option's strike price.
  • time_to_expiry is the time to maturity in years.
  • risk-free rate is the risk-free rate through expiry.
  • dividend_yield is the annual dividend yield.
  • price is the Black-Scholes European put/call valuation.

Black76

Returns the Black-76 valuation for options on futures and forwards.

=Black76(call_put_flag, forward, strike_price, time_to_expiry, risk-free_rate, volatility)

' Returns 4.7829
=Black76("c", 100, 98, 1, 0.05, 0.1)
  • call_put_flag is whether the instrument is a call ("c") or a put ("p").
  • forward is the current forward value.
  • strike_price is the option's strike price.
  • time_to_expiry is the time to maturity in years.
  • risk-free rate is the risk-free rate through expiry.
  • volatility is the implied volatility at expiry.

Swaption

Returns the Black-76 European payer/receiver swaption valuation.

=Swaption(pay_rec_flag, tenor, periods, swap_rate, strike_rate, time_to_expiry, risk-free_rate, volatility)
  • pay_rec_flag is whether the instrument is a payer ("p") or a receiver ("r").
  • tenor is the tenor of the swap in years.
  • periods is the number of compoundings per year.
  • swap_rate is the current underlying swap rate.
  • strike_rate is the option's strike rate.
  • time_to_expiry is the time to maturity in years.
  • risk-free_rate is the risk-free rate through expiry.
  • volatility is the implied volatility at expiry.

The Greeks

Returns the options Greek for a particular sensitivity. (Note: All functions for the Greeks share a common set of arguments, regardless of whether those inputs are used in a particular Greek's calculation.)

=BSDelta(call_put_flag, stock_price, strike_price, time_to_expiry, risk_free_rate, dividend_yield, volatility)

' Returns 0.37
=BSDelta("c", 60, 65, 0.25, 0.08, 0, 0.3)
  • call_put_flag is whether the instrument is a call ("c") or a put ("p").
  • stock_price is the current value of the underlying stock.
  • strike_price is the option's strike price.
  • time_to_expiry is the time to maturity in years.
  • risk-free_rate is the risk-free rate through expiry.
  • dividend_yield is the annual dividend yield.
  • volatility is the implied volatility at expiry.
Function Sensitivity of __ to changes in __
=BSDelta() option price underlying price
=Vega() option price volatility
=Theta() option price passage of time
=Rho() option price risk-free rate
=Gamma() option price delta
=Vanna() delta volatility
=Charm() delta passage of time
=Speed() gamma underlying price
=Zomma() gamma volatility
=Color() gamma passage of time
=DvegaDtime() vega passage of time
=Vomma() vega volatility
=DualDelta() option price strike price
=DualGamma() delta strike price

American and Bermudan Options

American

Returns the Barone-Adesi-Whaley approximation for an American option.

=American(call_put_flag, stock_price, strike_price, time_to_expiry, risk-free rate, dividend_yield, volatility)
  • call_put_flag is whether the instrument is a call ("c") or a put ("p").
  • stock_price is the current value of the underlying stock.
  • strike_price is the option's strike price.
  • time_to_expiry is the time to maturity in years.
  • risk-free rate is the risk-free rate through expiry.
  • dividend_yield is the annual dividend yield.
  • volatility is the implied volatility at expiry.

BermudanBinomial

Returns the binomial valuation for a Bermudan option.

=BermudanBinomial(call_put_flag, stock_price, strike_price, time_to_expiry, risk-free rate,
                  dividend_yield, volatility, potential_exercise_times, iterations)
  • call_put_flag is whether the instrument is a call ("c") or a put ("p").
  • stock_price is the current value of the underlying stock.
  • strike_price is the option's strike price.
  • time_to_expiry is the time to maturity in years.
  • risk-free rate is the risk-free rate through expiry.
  • dividend_yield is the annual dividend yield.
  • volatility is the implied volatility at expiry.
  • potential_exercise_times is a range of potential exercise times in years.
  • iterations is the number of calculations performed to increase precision. Defaults to 500.