Understanding the Black-Scholes Option-Pricing Formula in Excel for Non-Dividend-Paying Assets

The Black-Scholes option-pricing formula is a mathematical model that calculates the fair value of a European call or put option on a non-dividend-paying asset, such as a stock. The formula takes into account six factors that affect the option price: 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 expected volatility of the underlying asset, and the expected dividend yield of the underlying asset.

The formula assumes that the underlying asset follows a geometric Brownian motion, which means that its price changes are random but follow a certain pattern. The formula also assumes that the option can only be exercised at maturity, and that there are no transaction costs or taxes involved.

The formula consists of two parts: the intrinsic value and the time value of the option. The intrinsic value is the difference between the current price of the underlying asset and the strike price of the option, if the option is in the money (meaning that it would be profitable to exercise it). The time value is the amount that the option holder is willing to pay for the possibility that the option will increase in value before expiration. The time value depends on the expected volatility of the underlying asset, which measures how much the price can fluctuate up or down. The higher the volatility, the higher the time value, because there is a greater chance that the option will become more valuable in the future.

The formula uses two functions, N and N’, to calculate the time value of the option. N is the standard normal cumulative distribution function, which gives the probability that a random variable with a normal distribution will be less than or equal to a given value. N’ is the standard normal probability density function, which gives the probability that a random variable with a normal distribution will be exactly equal to a given value. The formula also uses two variables, d1 and d2, which are derived from the six factors mentioned above.

Basic Theory

The Black-Scholes formula calculates the theoretical price of a European call or put option on a non-dividend-paying
stock. The key components of the formula are:

Current Stock Price (S): The current market price of the underlying asset.
Strike Price (K): The predetermined price at which the option holder can buy (for a call
option) or sell (for a put option) the underlying asset.
Time to Maturity (T): The remaining time until the option expires.
Risk-Free Interest Rate (r): The continuously compounded interest rate over the life of the
option.
Volatility (σ): The standard deviation of the stock’s returns, representing the degree of
variation of the stock price.

The Black-Scholes formula for a European call option (C) is given by:

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

Where:

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

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

The formula for a European put option (P) is similar but with a few changes.

Procedures in Excel

Let’s now go through the step-by-step process of implementing the Black-Scholes formula in Excel.

Create an Excel Table: Open Excel and create a table with the following columns: Current Stock
Price (S), Strike Price (K), Time to Maturity (T), Risk-Free Interest Rate (r), Volatility (σ), and Option
Price (C and P).
Enter Parameters: Input the relevant values for each parameter into the table.
Calculate d1 and d2: Use Excel formulas to calculate $d_1$ and $d_2$ based on the provided
formulas.
Apply Black-Scholes Formula: Utilize Excel formulas to implement the Black-Scholes formula for
both call and put options.

Scenario with Real Numbers

Let’s consider the following scenario:

Current Stock Price (S): $100
Strike Price (K): $105
Time to Maturity (T): 0.5 years
Risk-Free Interest Rate (r): 5%
Volatility (σ): 20%

Excel Calculation

Calculate $d_1$ and $d_2$ :
- $d_1 = \frac{\ln(100 / 105) + (0.05 + \frac{0.2^2}{2}) \times 0.5}{0.2 \times \sqrt{0.5}}$
- $d_2 = d_1 - 0.2 \times \sqrt{0.5}$
Apply Black-Scholes Formula for Call Option (C):
- $C = 100 \times N(d_1) - 105e^{-0.05 \times 0.5} \times N(d_2)$
Apply Black-Scholes Formula for Put Option (P):
- $P = 105e^{-0.05 \times 0.5} \times N(-d_2) - 100 \times N(-d_1)$

Results

For the given scenario, the calculated option prices are as follows:

Call Option (C): $3.98
Put Option (P): $7.02

These results indicate the theoretical prices of the options based on the Black-Scholes model.

Alternative Approaches

Online Calculators: Various online calculators are available for Black-Scholes calculations,
allowing for quick validation of Excel results.
VBA Implementation: Advanced users may choose to implement the Black-Scholes formula using
Visual Basic for Applications (VBA) to create a more dynamic and customizable solution.