Understanding Probability Distribution in Options Using Excel Formulas

Probability distribution in options is a way of describing the likelihood of different outcomes for the underlying asset’s price at a given time in the future. It is based on the assumption that the option prices reflect the market’s expectations and risk preferences for the asset.

One way to visualize the probability distribution in options is to use a curve that shows the probability of the asset’s price ending up in a certain range at expiration. The shape of the curve depends on the volatility and skewness of the asset, as well as the type and strike of the options.

For example, a normal distribution curve would imply that the asset’s price has equal chances of going up or down, and that the most likely outcome is close to the current price. A skewed distribution curve would imply that the asset’s price has more chances of going in one direction than the other, and that the most likely outcome is not near the current price.

One method to estimate the probability distribution in options is to use the implied volatility surface, which is a function that maps the option prices to the implied volatility for different strikes and maturities. By using the implied volatility surface, we can price a series of theoretical options with different strikes and the same expiration date, and then use them to construct a series of butterfly spreads, which are combinations of long and short options that have a payoff only if the asset’s price ends up in a specific range. The cost of the butterfly spreads divided by their payoff gives us an approximation of the probability of the asset’s price ending up in that range. By doing this for many overlapping ranges across all strikes, we can obtain a smooth probability distribution curve.

Basic Theory:

In options trading, probability distribution is used to estimate the likelihood of a specific price level being reached by the underlying asset at expiration. The most common probability distribution model used for this purpose is the normal distribution, often referred to as the bell curve.

The formula for calculating the probability of an option expiring in-the-money (ITM) is based on the cumulative distribution function (CDF) of the standard normal distribution. For a call option:

$P(\text{ITM}) = N(d_2)$

For a put option:

$P(\text{ITM}) = 1 - N(d_2)$

Where $N$ is the standard normal distribution function, and $d_2$ is calculated using the Black-Scholes-Merton formula:

$d_2 = \frac{\ln(\frac{S}{X}) + (r - \frac{\sigma^2}{2})T}{\sigma\sqrt{T}}$

Where:

$S$ is the current stock price,
$X$ is the option’s strike price,
$r$ is the risk-free interest rate,
$\sigma$ is the volatility of the stock, and
$T$ is the time to expiration.

Procedures:

Gather Necessary Data:
- Stock Price ( $S$ )
- Strike Price ( $X$ )
- Risk-free Interest Rate ( $r$ )
- Volatility ( $\sigma$ )
- Time to Expiration ( $T$ )
Calculate $d_2$ Using Black-Scholes-Merton Formula:
$d_2 = \frac{\ln(\frac{S}{X}) + (r - \frac{\sigma^2}{2})T}{\sigma\sqrt{T}}$
Use Excel Formulas to Calculate Probability of ITM:
- For Call option: $P(\text{ITM}) = N(d_2)$
- For Put option: $P(\text{ITM}) = 1 - N(d_2)$

Scenario:

Let’s consider a scenario with the following parameters:

Stock Price ( $S$ ): $100
Strike Price ( $X$ ): $105
Risk-free Interest Rate ( $r$ ): 5%
Volatility ( $\sigma$ ): 20%
Time to Expiration ( $T$ ): 0.5 years

Excel Calculation:

Calculate $d_2$ :
$d_2 = \frac{\ln(\frac{100}{105}) + (0.05 - \frac{0.2^2}{2}) \times 0.5}{0.2 \times \sqrt{0.5}}$
Calculate Probability of ITM for Call Option:
$P(\text{ITM}) = N(d_2)$
Calculate Probability of ITM for Put Option:
$P(\text{ITM}) = 1 - N(d_2)$

Excel Table:

Parameter	Value
Stock Price ( $S$ )	$100
Strike Price ( $X$ )	$105
Risk-free Rate ( $r$ )	5%
Volatility ( $\sigma$ )	20%
Time to Expiration ( $T$ )	0.5 years

Calculation	Formula	Result
Calculate $d_2$	$\frac{\ln(\frac{100}{105}) + (0.05 - \frac{0.2^2}{2}) \times 0.5}{0.2 \times \sqrt{0.5}}$	$-0.1887$
Probability of ITM (Call)	$N(d_2)$	0.4259
Probability of ITM (Put)	$1 - N(d_2)$	0.5741

Result:

In this scenario, the calculated probability of the call option expiring in-the-money is approximately 42.59%, while the put option has a probability of 57.41%.

Other Approaches:

Monte Carlo Simulation: This is an alternative method that involves simulating thousands of possible price paths for the underlying asset to estimate the probability distribution.
Historical Volatility: Instead of using implied volatility, historical volatility can be employed to derive a more realistic estimation of future price movements.