Understanding Probability Density in Options Using Excel

Probability density in options is a way of measuring how likely the price of an underlying asset will be at a certain level at a certain time in the future. It is derived from the prices of different options on the same asset with different strike prices and expiration dates. The option prices reflect the market’s expectations of the future movements of the asset price.

One way to calculate the probability density in options is to use the implied probability densities1. This method uses the fact that the price of a call option is equal to the expected payoff of the option under the risk-neutral measure. By differentiating the option price with respect to the strike price, we can obtain the cumulative probability density, which is the probability that the asset price will be below a certain level. By taking the difference of the cumulative probability densities for two adjacent strike prices, we can obtain the probability density for the interval between them.

Another way to calculate the probability density in options is to use the Breeden-Litzenberger formula2. This formula relates the price of a call option to the probability density of the asset price at the expiration date. By using a finite difference approximation, we can estimate the probability density from the option prices for different strike prices.

The probability density in options can be used to trade the view on the future distribution of the asset price. For example, if we think that the implied probability density underestimates the likelihood of a large upward movement of the asset price, we can buy a call option with a high strike price, which is relatively cheap. If the asset price rises above the strike price, we can profit from the option. Alternatively, we can sell a call option with a low strike price, which is relatively expensive. If the asset price stays below the strike price, we can keep the option premium.

Basic Theory:

Probability density in options trading refers to the likelihood of an option reaching a specific price by its expiration date. It is a key concept in assessing the risk and potential return of an options strategy. The probability density function (PDF) represents the probability of the option’s price being within a particular range at expiration.

Procedures:

Define Parameters:
- Underlying Price (S)
- Strike Price (K)
- Time to Expiration (T)
- Volatility (σ)
- Risk-Free Rate (r)
Calculate d1 and d2:
- Use the Black-Scholes-Merton formula to find d1 and d2:
  $d_1 = \frac{{\ln(S/K) + (r + (\sigma^2)/2)T}}{{\sigma \sqrt{T}}}$
  
  $d_2 = d_1 - \sigma \sqrt{T}$
Calculate Probability Density:
- Use the standard normal distribution function (NORM.DIST in Excel) to find the probability of the option expiring in-the-money:
  $P(\text{{Option in-the-money}}) = N(d_2)$
Excel Formulas:
- Assuming the cell references for parameters are A1 (S), A2 (K), A3 (T), A4 (σ), and A5 (r):
  - For $d_1$ : =(LN(A1/A2) + (A5 + (A4^2)/2)*A3) / (A4 * SQRT(A3))
  - For $d_2$ : =A6 - A4 * SQRT(A3)
  - Probability of in-the-money: =NORM.DIST(A7, TRUE)

Scenario:

Let’s consider an example:

Underlying Price (S): $100
Strike Price (K): $110
Time to Expiration (T): 1 year
Volatility (σ): 0.2
Risk-Free Rate (r): 0.05

Calculations in Excel:

$d_1$ : =(LN(100/110) + (0.05 + (0.2^2)/2)*1) / (0.2 * SQRT(1)) (result is approximately -0.45)
$d_2$ : =-0.45 - 0.2 * SQRT(1) (result is approximately -0.65)
Probability of in-the-money: =NORM.DIST(-0.65, TRUE) (result is approximately 0.257)

Result:

The probability of the option expiring in-the-money is approximately 25.7%.

Alternative Approaches:

Monte Carlo Simulation: Simulate various possible price paths using random variables.
Binomial Model: Use a discrete-time model to calculate option prices and probabilities.