Options pricing – Using binomial trees to price options in a spreadsheet

2 mins read

1.       Derivative and Options pricing

Derivatives and options are instruments whose payoffs depend on the movement of underlying assets. The price or value of the derivative instrument therefore can be evaluated by creating and valuing a portfolio of assets whose prices are easily observed in the market and whose cash flows replicate those of the options. While the value of a derivative trade may go up and down, Options by definition limit the downside for the buyer.

If you are beginner and would like to see more details about Options and Derivatives or Options pricing, please see the following product oriented courses

Options Crash Course for dummies

Options & Derivatives Products

Advance Options & Derivatives Crash course

Options Pricing reference

2.       Relative Pricing, Risk Neutral Probabilities and the Risk Free Rate

By the law of one price it then follows that in order to avoid arbitrage or riskless profits the value of the replicating portfolio and that of the derivative instrument should be equal. Alternatively this means that regardless of the investors’ risk preferences, the same hedge portfolio would be constructed to replicate the option’s cash flows.

This implies that the discount rate and the probabilities used in deriving the expected present value of the future cash flows of the security or alternatively the value of option do not depend on the true probabilities linked to the likelihood of payoffs or the degree of risk aversion of the investor. In arriving at the value of the derivative, risk neutral probabilities will be used to compute the expected present value of the future cash flows. Risk neutral probabilities are probabilities that make the expected rate of return of the asset equal to the risk free rate.

3.       Binomial Options Pricing

In the previous chapter we saw that the European calls and puts can be priced using Black Scholes formulas. However closed formed solutions are not always available for pricing other types of options such as American options and exotic options such as barriers, capped, etc. The prices of these options are derived using numerical methods such as the binomial trees and Monte Carlo simulation. This course focuses on an alternative method of implementing a two-dimensional binomial tree compared to that given in the previous chapter for pricing American options.  The alternate approach is based on the techniques documented by Professor Mark Broadie at Columbia Business School as part of his coursework in Security Pricing and Computational Finance courses at Columbia University.

In particular we will focus on implementing this method starting with European calls and puts, despite there being a convenient closed form solution for these options. This is done to illustrate the basic concepts of the methodology as well as its limitations. After this we illustrate how the concepts can be extended to price American options and exotics (in particular capped calls with automatic exercise, down-and-out and down-and-in calls). Adjustments to the basic model can be made to price other exotics but it must be noted that this may not always be possible due to the complexity and nature of the payoffs.