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?

  • 1
    $\begingroup$ There is no Black-Scholes-style, closed form formula for American options (except for a perpetual American put). You need to implement numerical techniques (eg. Monte Carlo simulation, finite difference solver for PDE equations, pricing trees) to price American options. $\endgroup$ – Daneel Olivaw Jul 12 '17 at 14:33

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.

  • $\begingroup$ thanks. what is a good approximation for trading though? there are so many simulations around, monte carlo, quadratic, binomial/trinomial tree. $\endgroup$ – james Jul 14 '17 at 4:49
  • $\begingroup$ My advice is not to write your own. The PDE solver in QuantLibXL (quantlib.org/quantlibxl) is good enough so long as you don't hold options closer than 2 weeks to maturity. $\endgroup$ – Brian B Jul 15 '17 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.