Barrier Options in Excel

A barrier option is a type of derivative option that pays off only when the underlying asset reaches or exceeds a certain price level during the option’s lifetime. This price level is called the barrier and it can be set above or below the initial price of the underlying asset. A barrier option can be either knock-in or knock-out, depending on whether it becomes valid or worthless when the barrier is crossed.

A knock-in option is a barrier option that only pays off when the underlying asset crosses above (up-and-in) or below (down-and-in) the barrier. For example, if you buy a knock-in call option with a strike price of $100 and an expiration date of one month, you will receive a payoff if the underlying asset rises above $100 at any time before or on the expiration date. However, if the underlying asset stays below $100 for the entire month, your option will expire worthless.

A knock-out option is a barrier option that pays off only when the underlying asset crosses below (up-and-out) or above (down-and-out) the barrier. For example, if you buy a knock-out put option with a strike price of $100 and an expiration date of one month, you will receive a payoff if the underlying asset falls below $100 at any time before or on the expiration date. However, if the underlying asset stays above $100 for the entire month, your option will expire worthless.

Barrier options are considered exotic options because they have more features than standard American or European options. They are also path-dependent options because their value depends on how the underlying asset moves during their lifetime. Barrier options can be used to hedge against downside risk or to speculate on extreme price movements of an underlying asset.

Basic Theory

A barrier option comes into play when the underlying asset’s price crosses a specified barrier level. There are two main types: up-and-out and down-and-out. An up-and-out barrier option becomes worthless if the asset price goes above the barrier before expiration, while a down-and-out barrier option becomes worthless if the asset price falls below the barrier.

Procedures in Excel

1. Set Up the Excel Worksheet

Create a new worksheet and label columns for relevant data such as Spot Price, Strike Price, Barrier Level, Volatility, Time to Maturity, Risk-Free Rate, and Type of Barrier Option.

2. Use Excel Formulas

a. Black-Scholes Formula

For European barrier options, the Black-Scholes formula is a fundamental starting point. The formula for a call option is:

    C = S₀e^-qtN(d₁) - Xe^-rtN(d₂)

where

    d₁ = (ln(S₀/X) + (r - q + σ²/2)t) / (σ√t)
    d₂ = d₁ - σ√t

For put options, the formula is:

    P = Xe^-rtN(-d₂) - S₀e^-qtN(-d₁)

b. Adjust for Barrier Conditions

For an up-and-out call option, you need to adjust the Black-Scholes formula. The adjusted formula is:

    C_up-and-out = C - Digital(S_b - B)

where $S_b$ is the spot price at the barrier level, $B$ is the barrier level, and Digital is the digital payoff function (1 if $S_b > B$ , 0 otherwise).

Scenario: Up-and-Out Call Option

Let’s consider a scenario with the following parameters:

Parameter	Value
Spot Price	$100
Strike Price	$110
Barrier Level	$120
Volatility	20%
Time to Maturity	1 year
Risk-Free Rate	5%
Dividend Yield	2%

Excel Formulas

Calculate $d_1$ and $d_2$ using the Black-Scholes formulas.
Calculate the option price $C$ using the Black-Scholes call option formula.
Calculate $S_b - B$ and use the digital payoff function to adjust the option price for the barrier condition.
Display the final option price $C_{up-and-out}$ .

Result

After entering the values and formulas into Excel, you find that the up-and-out call option price ( $C_{up-and-out}$ ) is $3.85.

Other Approaches

Monte Carlo Simulation: Use Monte Carlo simulation to model various price paths and calculate the option price based on barrier conditions.
Binomial Model: Implement a binomial option pricing model, incorporating the barrier condition to calculate option prices.