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 Option Pricing
Two Step Binomial Model

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.

Trinomial Tree
Two Step Trinomial Model

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.

Trinomial Tree Excel

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. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

What is 6 + 2 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)