Black-Scholes Option Pricing Model in Excel

The Black-Scholes Option Pricing Model is a mathematical model that calculates the fair value of an option contract based on certain assumptions and variables. It was developed by Fischer Black, Myron Scholes, and Robert Merton in 1973, and it won them the Nobel Prize in Economics in 1997.

The model uses six variables to determine the price of a European-style call or put option. These variables are:

The current price of the underlying asset
The strike price of the option
The time to expiration of the option
The risk-free interest rate
The volatility of the underlying asset
The type of the option (call or put)

The model assumes that the underlying asset follows a lognormal distribution of returns, meaning that the prices are random but skewed to the right. It also assumes that there are no transaction costs, taxes, dividends, or arbitrage opportunities in the market. These assumptions simplify the calculation, but they also limit the applicability of the model to real-world scenarios.

The model outputs a theoretical value for the option, which can be compared to the market price to determine if the option is overpriced or underpriced. The model can also be used to hedge options in a portfolio and eliminate the risk factor.

The Black-Scholes Option Pricing Model is one of the most important and widely used models in finance, but it is not perfect. It has some limitations and drawbacks, such as:

It only applies to European options, which can only be exercised at expiration. American options, which can be exercised anytime before expiration, require a different model.
It assumes that the volatility of the underlying asset is constant and known, which is rarely the case in reality. Volatility can change over time and affect the option price significantly.
It assumes that the risk-free interest rate is constant and known, which is also unrealistic. Interest rates can fluctuate and affect the option price as well.
It does not account for the effects of dividends, taxes, or transaction costs, which can alter the profitability of the option.
It does not capture the impact of market sentiment, supply and demand, or other factors that can influence the option price.

To overcome some of these limitations, various extensions and modifications of the Black-Scholes model have been proposed and developed over the years, such as the binomial model, the Monte Carlo simulation, the implied volatility model, and the stochastic volatility model. These models try to incorporate more realistic assumptions and factors into the option pricing process, but they also increase the complexity and difficulty of the calculation.

Basic Theory:

The Black-Scholes model calculates the theoretical price of a European-style option by considering factors such as the current stock price, option strike price, time to expiration, volatility, and risk-free interest rate. The formula for calculating the call option price ( $C$ ) is as follows:

$C = S_0 \cdot N(d_1) - X \cdot e^{-rT} \cdot N(d_2)$

where:

$S_0$ = Current stock price
$X$ = Option strike price
$T$ = Time to expiration (in years)
$r$ = Risk-free interest rate
$N(d_1)$ and $N(d_2)$ are cumulative distribution functions of the standard normal distribution
$d_1 = \frac{\ln(S_0 / X) + (r + \frac{\sigma^2}{2})T}{\sigma \sqrt{T}}$
$d_2 = d_1 - \sigma \sqrt{T}$

For a put option, the formula is:

$P = X \cdot e^{-rT} \cdot N(-d_2) - S_0 \cdot N(-d_1)$

Procedures:

Open Excel and create a table with the following headers: Current Stock Price ( $S_0$ ), Option Strike Price ( $X$ ), Time to Expiration ( $T$ ), Risk-Free Interest Rate ( $r$ ), Volatility ( $\sigma$ ), Option Type, and Option Price.
Input the relevant data for each parameter in the table.
Utilize the Black-Scholes formulas to calculate the call and put option prices based on the provided inputs.