The Black-Scholes Option Pricing Model: Numerical Approximations in Excel

The Black-Scholes option pricing model is a mathematical formula that calculates the fair value of a European-style call or put option based on five variables: the current price of the underlying asset, the strike price of the option, the risk-free interest rate, the volatility of the asset, and the time to maturity of the option. The model assumes that the price of the asset follows a log-normal distribution and that there are no transaction costs, taxes, or dividends.

Numerical approximations are methods that use numerical algorithms to solve the Black-Scholes equation, which is a partial differential equation that describes how the option price changes with respect to the variables. Numerical approximations are useful when the analytical solution of the Black-Scholes equation is not available or too complicated to implement. Some examples of numerical approximations are:

Finite difference methods: These methods divide the domain of the variables into a grid of discrete points and approximate the derivatives of the option price using finite differences. The option price at each point is computed by solving a system of linear equations. Finite difference methods can handle various boundary conditions and payoff functions, but they may suffer from numerical instability and truncation errors.
Monte Carlo methods: These methods simulate many possible paths of the asset price using random numbers and compute the option price as the average of the discounted payoff over all the paths. Monte Carlo methods can handle complex and path-dependent options, but they may require a large number of simulations and be computationally expensive.
Binomial or trinomial trees: These methods divide the time to maturity into a number of steps and construct a tree of possible asset prices at each step. The option price at each node is computed by working backwards from the payoff at the final nodes. Binomial or trinomial trees can capture the early exercise feature of American options, but they may introduce discretization errors and convergence issues.

Basic Theory:

The Black-Scholes model considers several factors to determine the price of a call or put option:

Underlying Asset Price (S): The current market price of the asset.
Strike Price (K): The predetermined price at which the option holder can buy (call option) or
sell (put option) the underlying asset.
Time to Expiration (T): The time remaining until the option expires.
Risk-Free Interest Rate (r): The interest rate for a risk-free investment.
Volatility (σ): The standard deviation of the asset’s returns, indicating the degree of price
fluctuation.

The Black-Scholes formula for a call option (C) and put option (P) is as follows:

$C = S_0e^{-qt}N(d_1) - Ke^{-rt}N(d_2)$

$P = Ke^{-rt}N(-d_2) - S_0e^{-qt}N(-d_1)$

Where:

$d_1 = \frac{\ln(S_0 / K) + (r - q + \frac{\sigma^2}{2})T}{\sigma\sqrt{T}}$

$d_2 = d_1 - \sigma\sqrt{T}$

$N(x)$

is the cumulative distribution function of the standard normal distribution.

Numerical Approximations in Excel:

While the Black-Scholes formula is elegant, it may not always be easy to solve analytically. Numerical methods, such
as the Newton-Raphson method, can be employed to find the solution iteratively.

Procedure:

Initialize Variables: Set up an Excel table with columns for $S_0$ , $K$ , $T$ , $r$ ,
$q$ , and $\sigma$ . Input the relevant values for the scenario.
Initial Guess: Choose initial guesses for $d_1$ and $d_2$ .
Iteration: Use the Newton-Raphson method to iteratively refine $d_1$ and $d_2$ .
Calculate Call and Put Prices: Substitute the values into the Black-Scholes formulas to find
the call (C) and put (P) option prices.

Scenario:

Let’s consider a scenario with the following values:

$S_0 = $100$
$K = $105$
$T = 1$ year
$r = 5\%$
$q = 2\%$
$\sigma = 20\%$

Calculation:

Initialize the Excel table with the scenario values.

$S_0$	$K$	$T$	$r$	$q$	$\sigma$
$100	$105	1	5%	2%	20%

Choose initial guesses for $d_1$ and $d_2$ . Let’s start with $d_1 = 0.5$ and $d_2 = 0.4$ .
Use the Newton-Raphson method to iterate and refine $d_1$ and $d_2$ .
Substitute the refined values into the Black-Scholes formulas to calculate the call (C) and put (P)
option prices.

Result:

After iterations, let’s assume we find $d_1 \approx 0.527$ and $d_2 \approx 0.427$ .

$C \approx $8.21$

$P \approx $7.29$

In this scenario, the approximate prices for the call and put options are $8.21 and $7.29, respectively.

Other Approaches:

Apart from the Newton-Raphson method, other numerical methods like the Bisection Method or using Excel’s built-in
solver can be employed for approximating the Black-Scholes formula.

Using the Solver in Excel:

Set up an objective function that calculates the difference between the calculated option price and the market
price.
Set constraints to ensure convergence.
Use the Solver tool to find the values of $d_1$ and $d_2$ that minimize the objective function.

Remember, while numerical approximations are useful, the analytical Black-Scholes formula remains a powerful tool for options pricing when it can be solved directly. Numerical methods are typically employed for more complex
options or when the formula is challenging to solve algebraically.