Black-Scholes Option Pricing and Greeks Formulas

Excel Price Feed provides several Excel formulas which use the Black-Scholes option pricing model. They include a formula for calculating the theoretical price of an option as well as formulas for the most commonly used "Greeks".

All formulas require percentages to be expressed in fractions of 100, for example 25% is 0.25.

Please note that Excel Price Feed also includes formulas for retrieving Option Chains from Yahoo Finance.

EPF.BlackScholes.Premium

This formula calculates the theoretical price (premium) of an option using the Black-Scholes option pricing formula.

=EPF.BlackScholes.Premium(optionType, underlyingPrice, strikePrice, timeToExpiry, volatility, interestRate, dividendYield)

The input parameters required are:

Parameter Description
Option Type The type of option, either Put or Call.
Can be specified as "Put" or "P" or "Call" or "C".
Underlying Price The current price of the underlying instrument.
Strike Price The strike price of the option.
Time To Expiry The time to expiration of the option, expressed as % of a year.
Volatility The volatility.
Interest Rate The continuously compounded risk-free interest rate.
Dividend Yield The continuously compounded dividend yield of the underlying.

EPF.BlackScholes.Delta

This formula calculates the Delta of an option using the Black-Scholes option pricing formula. Delta is the amount that an option changes with respect to a small change in the underlying.

=EPF.BlackScholes.Delta(optionType, underlyingPrice, strikePrice, timeToExpiry, volatility, interestRate, dividendYield)

The input parameters required are:

Parameter Description
Option Type The type of option, either Put or Call.
Can be specified as "Put" or "P" or "Call" or "C".
Underlying Price The current price of the underlying instrument.
Strike Price The strike price of the option.
Time To Expiry The time to expiration of the option, expressed as % of a year.
Volatility The volatility.
Interest Rate The continuously compounded risk-free interest rate.
Dividend Yield The continuously compounded dividend yield of the underlying.

EPF.BlackScholes.Gamma

This formula calculates the Gamma of an option using the Black-Scholes option pricing formula. Gamma quantifies the rate of change of the delta with respect to a change in the underlying.

=EPF.BlackScholes.Gamma(underlyingPrice, strikePrice, timeToExpiry, volatility, interestRate, dividendYield)

The input parameters required are:

Parameter Description
Underlying Price The current price of the underlying instrument.
Strike Price The strike price of the option.
Time To Expiry The time to expiration of the option, expressed as % of a year.
Volatility The volatility.
Interest Rate The continuously compounded risk-free interest rate.
Dividend Yield The continuously compounded dividend yield of the underlying.

EPF.BlackScholes.Theta

This formula calculates the Theta of an option using the Black-Scholes option pricing formula. Theta quantifies the amount that an option decays in one day.

=EPF.BlackScholes.Theta(optionType, underlyingPrice, strikePrice, timeToExpiry, volatility, interestRate, dividendYield)

The input parameters required are:

Parameter Description
Option Type The type of option, either Put or Call.
Can be specified as "Put" or "P" or "Call" or "C".
Underlying Price The current price of the underlying instrument.
Strike Price The strike price of the option.
Time To Expiry The time to expiration of the option, expressed as % of a year.
Volatility The volatility.
Interest Rate The continuously compounded risk-free interest rate.
Dividend Yield The continuously compounded dividend yield of the underlying.

EPF.BlackScholes.Rho

This formula calculates the Rho of an option using the Black-Scholes option pricing formula. Rho quantifies the change of an options value with respect to a change in the interest rate.

=EPF.BlackScholes.Rho(optionType, underlyingPrice, strikePrice, timeToExpiry, volatility, interestRate, dividendYield)

The input parameters required are:

Parameter Description
Option Type The type of option, either Put or Call.
Can be specified as "Put" or "P" or "Call" or "C".
Underlying Price The current price of the underlying instrument.
Strike Price The strike price of the option.
Time To Expiry The time to expiration of the option, expressed as % of a year.
Volatility The volatility.
Interest Rate The continuously compounded risk-free interest rate.
Dividend Yield The continuously compounded dividend yield of the underlying.