Option Pricing in Excel Formulas

Pricing of options is the process of determining how much an option contract is worth. An option is a contract that gives the buyer the right, but not the obligation, to buy or sell an underlying asset at a fixed price before or at a certain date. There are two types of options: calls and puts. A call option gives the buyer the right to buy the underlying asset, while a put option gives the buyer the right to sell the underlying asset.

The value of an option depends on several factors, such as the current price of the underlying asset, the strike price of the option, the time until the option expires, the volatility of the underlying asset, the interest rate, and the dividends paid by the underlying asset. These factors affect the probability that the option will be exercised, or used, by the buyer.

There are different mathematical models that use these factors to calculate the theoretical value of an option, or the fair price that an option should have. Some of the most common models are the Black-Scholes model, the Binomial model, and the Monte Carlo model. Each model has its own assumptions and limitations, and may be more suitable for certain types of options or underlying assets.

The Black-Scholes model is one of the most widely used models for pricing European-style options, which can only be exercised at the expiration date. The model uses a formula that takes into account the current price, strike price, time to expiration, volatility, interest rate, and dividend yield of the underlying asset. The model assumes that the underlying asset follows a lognormal distribution, that the interest rate and volatility are constant, and that there are no transaction costs or taxes.

The Binomial model is a simpler model that can be used to price both European-style and American-style options, which can be exercised anytime before or at the expiration date. The model divides the time until expiration into a number of intervals, and assumes that the underlying asset can only move up or down by a certain percentage in each interval. The model then calculates the value of the option at each possible scenario, and works backwards to find the value of the option at the present time.

The Monte Carlo model is a more flexible model that can be used to price complex options or options with underlying assets that do not follow a lognormal distribution. The model uses a computer simulation to generate a large number of random scenarios for the underlying asset, and calculates the value of the option for each scenario. The model then averages the values of the option across all scenarios to find the expected value of the option.

Basic Theory:

Options are financial derivatives that provide the holder with the right, but not the obligation, to buy (call option) or sell (put option) an underlying asset at a predetermined price (strike price) before or at the expiration date. The two primary components influencing option prices are intrinsic value and time value.

Intrinsic Value: The difference between the current market price of the underlying asset and the option’s strike price.
Time Value: The value associated with the time remaining until the option’s expiration.

The Black-Scholes-Merton model and the Binomial model are popular methods for calculating option prices.

Procedures:

Collect Information:
- Underlying stock price (S)
- Option strike price (K)
- Time to expiration (T)
- Volatility of the underlying asset (σ)
- Risk-free interest rate (r)
- Dividend yield (if applicable)
Calculate d1 and d2:
- $d1 = \frac{{\ln\left(\frac{S}{K}\right) + \left(r + \frac{\sigma^2}{2}\right)T}}{{\sigma\sqrt{T}}}$
- $d2 = d1 - \sigma\sqrt{T}$
Calculate Call and Put Option Prices:
- Call Option Price ( $C$ ):
  $C = S \cdot N(d1) - K \cdot e^{-rT} \cdot N(d2)$
- Put Option Price ( $P$ ):
  $P = K \cdot e^{-rT} \cdot N(-d2) - S \cdot N(-d1)$

Comprehensive Explanation:

Let’s consider a scenario:

$S = 100$ (Current stock price)
$K = 95$ (Option strike price)
$T = 0.5$ years (Time to expiration)
$\sigma = 0.2$ (Volatility)
$r = 0.05$ (Risk-free interest rate)

Scenario Calculation:

Calculate $d1$ and $d2$ :
- $d1 = \frac{{\ln\left(\frac{100}{95}\right) + \left(0.05 + \frac{0.2^2}{2}\right) \cdot 0.5}}{{0.2 \cdot \sqrt{0.5}}}$
- $d2 = d1 - 0.2 \cdot \sqrt{0.5}$
Calculate Call and Put Option Prices:
- Call ( $C$ ):
  $100 \cdot N(d1) - 95 \cdot e^{-0.05 \cdot 0.5} \cdot N(d2)$
- Put ( $P$ ):
  $95 \cdot e^{-0.05 \cdot 0.5} \cdot N(-d2) - 100 \cdot N(-d1)$

Excel Table:

Parameter	Value
S	100
K	95
T	0.5
σ	0.2
r	0.05

Calculations	Formula	Result
$d1$	$\frac{{\ln\left(\frac{100}{95}\right) + \left(0.05 + \frac{0.2^2}{2}\right) \cdot 0.5}}{{0.2 \cdot \sqrt{0.5}}}$	0.5415
$d2$	$d1 - 0.2 \cdot \sqrt{0.5}$	0.3415
Call ( $C$ )	$100 \cdot N(d1) - 95 \cdot e^{-0.05 \cdot 0.5} \cdot N(d2)$	$10.84
Put ( $P$ )	$95 \cdot e^{-0.05 \cdot 0.5} \cdot N(-d2) - 100 \cdot N(-d1)$	$4.52