American Options Pricing Model in Excel: A Comprehensive Guide

American options are financial derivatives that give holders the right to buy or sell an asset at a specified price before the option's expiration date. Unlike European options, which can only be exercised at maturity, American options can be exercised at any time prior to expiration. This added flexibility makes pricing American options more complex. In this comprehensive guide, we will delve into the intricacies of pricing American options using Excel, providing a step-by-step approach and practical examples.

Understanding American Options

American options offer the right, but not the obligation, to buy (call) or sell (put) an underlying asset at a predetermined strike price before or at expiration. This flexibility can be advantageous in volatile markets, but it also adds complexity to the pricing model.

The Binomial Model

The Binomial Model is one of the most popular methods for pricing American options due to its flexibility in handling early exercise features. This model works by constructing a binomial tree that represents different possible paths the underlying asset price could take over time. The steps involved in using the Binomial Model in Excel are as follows:

1. Set Up the Binomial Tree Structure:

   • Nodes and Steps: Determine the number of steps or time intervals until expiration. Each step represents a period in which the asset price can either move up or down.
   • Asset Price Movement: Calculate the up and down factors, which are derived from the volatility of the underlying asset.

2. Calculate the Option Values at Each Node:

   • Terminal Payoffs: At the final nodes (expiration), calculate the payoff of the option, which is the maximum of zero or the difference between the strike price and the asset price for puts or the difference between the asset price and the strike price for calls.
   • Backward Induction: Move backward through the tree, calculating the option value at each node based on the possibility of early exercise and the discounted expected value of the option.

3. Excel Implementation:

   • Create a Binomial Tree: Use Excel's grid to set up the tree structure.
   • Calculate Up and Down Factors: Use volatility and time to derive these factors.
   • Fill in Payoff Values: Input the terminal payoffs and use formulas to compute values at earlier nodes.
   • Discounting: Apply discount factors to account for the time value of money.

Using Excel for Binomial Model

Here’s a step-by-step guide to implementing the Binomial Model in Excel:

1. Setup Your Spreadsheet:

   • Create a grid in Excel representing the binomial tree.
   • Label the rows for time steps and columns for different nodes.

2. Input Parameters:

   • Enter parameters such as the initial asset price, strike price, volatility, risk-free rate, and time to expiration.

3. Calculate Up and Down Factors:

   • Use the formula:
     • $u=e^{\sigma \sqrt{\Delta t}}$u=eσΔt​
     • $d=e^{-\sigma \sqrt{\Delta t}}$d=e−σΔt​ where $\sigma$σ is volatility and $\Delta t$Δt is the time step.

4. Fill in Asset Prices:

   • Calculate asset prices at each node based on the up and down factors.

5. Compute Option Payoffs:

   • For call options: $\text{Payoff}=\max (S-K,0)$Payoff=max(S−K,0)
   • For put options: $\text{Payoff}=\max (K-S,0)$Payoff=max(K−S,0)

6. Backward Induction:

   • Calculate the option price at each node using:
     • $\text{Value}=e^{-r\Delta t}(p\cdot {\text{Value}}_{up}+(1-p)\cdot {\text{Value}}_{down})$Value=e−rΔt(p⋅Valueup​+(1−p)⋅Valuedown​) where $p$p is the risk-neutral probability, and $r$r is the risk-free rate.

Practical Example:

Let’s walk through a practical example of pricing an American call option using Excel:

1. Initial Setup:

   • Asset Price ($S_0$S0​): $100
   • Strike Price ($K$K): $105
   • Volatility ($\sigma$σ): 20% per year
   • Risk-Free Rate ($r$r): 5% per year
   • Time to Expiration ($T$T): 1 year
   • Number of Steps: 3

2. Calculate Factors:

   • Up Factor ($u$u): $e^{0.2\sqrt{1/3}}\approx 1.122$e0.21/3​≈1.122
   • Down Factor ($d$d): $e^{-0.2\sqrt{1/3}}\approx 0.891$e−0.21/3​≈0.891
   • Risk-Neutral Probability ($p$p): $\frac{e^r-d}{u-d}\approx 0.556$u−der−d​≈0.556

3. Create the Tree:

   • Input the asset prices at each node.
   • Calculate the terminal payoffs for the call option.

4. Backward Calculation:

   • Compute the option price at each node moving backward.

Using Excel Functions:

To simplify, Excel functions can be utilized for certain calculations:

• UP and DOWN Factors: Use EXP() and SQRT() functions.
• Payoff Calculation: Use MAX() function.
• Discounting: Use EXP() for calculating the discount factor.

Tables and Visual Aids:

To enhance understanding, consider creating tables that show:

1. Asset Price Tree: Visual representation of asset prices at each node.
2. Option Value Tree: Values calculated at each node, showing early exercise values if applicable.
3. Payoff Tables: Detailed tables showing payoffs at expiration and option values at each step.

Conclusion

Pricing American options using Excel requires a detailed understanding of the Binomial Model and its implementation. By setting up a binomial tree and applying the necessary calculations, you can determine the fair value of American options and gain insights into their pricing dynamics. This approach not only provides a robust method for valuation but also helps in understanding the impact of various parameters on the option's price.