Using Whaley’s Approximation for American Option Pricing in Excel

Summary

The document asks whether a basic Black–Scholes spreadsheet for European options can be adapted to price American options. It suggests Whaley’s approximation as a possible spreadsheet method when limited accuracy is acceptable. The approach requires numerical iteration to solve a transcendental equation, which can be implemented through successive spreadsheet cells.

The answer says that ten iterations are typically sufficient for the numerical solving step, but also cautions that iteration count does not fix the approximation’s accuracy limitations. It explicitly advises against using the resulting prices for trading. The document does not provide the formula, explain the approximation’s assumptions, compare it with a lattice or other American-option methods, or specify which contracts it handles well. It is therefore a brief implementation suggestion rather than a complete pricing guide.

Key ideas

• Whaley’s approximation is presented as a spreadsheet-compatible approach to American option pricing.
• The method requires iterating to solve a transcendental equation.
• Successive spreadsheet cells can hold the numerical iterations.
• More iterations improve the solution to the equation but do not remove the approximation’s pricing limitations.
• The source advises against relying on the approximation for trading.

Tags

# How do I modify my basic black scholes model in Excel to price american options?


# How do I modify my basic black scholes model in Excel to price american options?












I've modeled a basic black scholes model in Excel and I have been using it to price European options for backtesting purposes.

This has been working fantastically and I would like to adjust this to include an American options pricing function.

Is this possible to modify my Excel? Or is the math too complex for simple Excel formulas to handle?

## Answer by Brian B (score 1)

https://quant.stackexchange.com/a/35105

If you don't need particularly good accuracy you might try the Whaley approximation. Here's the original paper and an implementation in code.

The approximation requires you to run a few numerical iterations to solve a transcendental equation. In practice 10 is always enough so you can just create 10 cells side by side with successive iterations.

I strongly recommend against using the results for any kind of trading. The approximation will not be good enough no matter how many iterations you run.

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.