How to Implement a Trinomial Option Pricing Model in MS-Excel?

Abstract

Among the several popular option pricings models exists the binomial and trinomial option pricing modes. Several authors have shown how to easily implement in MS-Excel the binomial option pricing model which assumes prices to either go up or down only. The trinomial option pricing model, however, assumes prices could go up, down or remain the same. Although it is a viable and robust option pricing model, a simple procedure to implement it in Excel is not easy to find. This manuscript briefly discusses the trinomial option model, and using =IF() commands and coding in appropriate formulae, elucidates a simple procedure to implement it in Excel. The stock lattice structure can be easily extended to the desired size by clicking and dragging the cursor across, and then down. Using terminal period pay-offs, and recursively carrying out present value calculations, one period back, one at a time, option prices can be estimated for American and European options.