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.
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. |
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. |
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. |
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. |
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. |