# Pricing American Options with a Trinomial Tree and Excel

Trinomial option pricing was proposed by Boyle (1986) and extends the binomial method to better reflect the actual behavior of financial instruments. Both methods can be used to calculate the fair value of American and Bermudan options, and converge to the same results at the limit.

The binomial method for option pricing is also often referred to as the Cox, Ross and Rubinstein model because of their role in developing the most common variant. However, binomial methods are now outdated and, apart from being easily implemented, have no significant advantage compared to other approaches.

Binomial trees expect an option to increase or decrease in value at every time step, as illustrated below.

Binomial methods for pricing options are easily implemented in a spreadsheet. Moreover, prices are given at every time step.  This makes lattice methods particularly suitable for pricing American Options, which can be exercised at any time before maturity.

But binomial models can become cumbersome and computationally inefficient.  Trinomial trees, however, allow the option value to increase, decrease or remain stationary at every time step, as illustrated below.

This means trinomial trees are a better description of the real-life behavior of financial instruments. Additionally, the trinomial method for option pricing converges much faster than the method method; this is especially significant for exotic options.

## Trinomial Tree in Excel

This Excel spreadsheet prices an American Option with a Trinomial Tree. An American option is a financial instrument that lets the owner buy (call) or sell (put) a stock at or before an agreed maturity time.

Simply enter your parameters, and click the button. Some VBA then carries out the  pricing calculation. If you would like the password to the VBA then please leave a comment.

Download Excel Spreadsheet to Price an American Option with a Trinomial Tre (protected VBA)

Buy Unlocked Spreasheet with Unlocked VBA for \$5

## 5 thoughts on “Pricing American Options with a Trinomial Tree and Excel”

1. I need someone to help me on 2 things

I need a example of Binomial Asian option

I would like to know how the trinomial tree set up like u, d, pu, pm, pd….

Any of you would help me, I would say a big thanks!

2. I’m about to post VBA code to price European options using a trinomial lattice. Should be up in a few days…

3. Norman Abraham says:

Good Evening,

I found your spreadsheet “American-Option-Trinomial-Tree” very interesting.

Do you have a spreadsheet for the “American-Option-Trinomial-Tree” similar to the “American-Option-Binomial-Tree” in which the “Draw Lattice. option exists?

Further when one draws the lattice, the formula is included in the cell to give the result, either the asset value or option value at that node.

Thank you,

Norm

4. Adel says:

I would really like to see how the process of the trinomial tree works,
my code doesn’t work.

Thank you