# How to Use the Binomial Distribution in Excel

July 14, 2023February 9th, 2024No Comments

This tutorial introduces binomial option pricing, and offers an Excel spreadsheet to help you better understand the principles. Additionally, a spreadsheet that binomial tree excel prices Vanilla and Exotic options with a binomial tree is provided. A binomial tree is a useful tool when pricing American options and embedded options.

1. We use the letter “B” to express a Binomial distribution, followed by the number of trials and the probability of success in each one.
2. We already know the option prices in both these nodes (because we are calculating the tree right to left).
3. Trinomial option pricing was proposed by Boyle (1986) and extends the binomial method to better reflect the actual behavior of financial instruments.
4. In this part we will create underlying price tree and option price tree in our spreadsheet.
5. We can just copy them (make sure the references to E4 are relative in the above formulas – no dollar signs).

Binomial methods for pricing options are easily implemented in a spreadsheet. This makes lattice methods particularly suitable for pricing American Options, which can be exercised at any time before maturity. The reason why this tutorial creates the trees first, and move size and probability formulas after, is that the former is the same for multiple binomial models. Therefore you can use the first three parts of the tutorial for different models. In the next parts, you can find move size and probability calculations for Jarrow-Rudd and Leisen-Reimer models.

## Binomial Tree for Pricing American Options

Where r is the IntRate input (cell B10) and Δt is step duration in years, or time to expiration divided by number of steps. Extending it to more steps is very simple, because the formulas stay the same throughout the tree. We can just copy them (make sure the references to E4 are relative in the above formulas – no dollar signs). Register the cell names in Excel using one of the methods introduced in part 1 (you can also right click the cell and select “Define Name”). This will allow us to use these names in formulas, which makes our formulas easier to write and understand.

Where r is the risk-free interest rate and Δt is duration of one step in years, calculated as t/n, where t is time to expiration in years (days to expiration / 365), and n is number of steps. We already know the option prices in both these nodes (because we are calculating the tree right to left). This chart below is the table for the price of the stock and the one below it is the table for the price of the option contract at corresponding prices (in table 2). And finally we have a table that shows the expected payoffs (in dollars) at these prices (in table 3) until the expiry when we buy 1 contract of this call option. 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.

See the example below, where I use the Black Scholes model to generate a payoff for an option contract until the expiry date by assuming each day until the expiry https://1investing.in/ is the expiry date. You can refer to our Options Profit Calculator template here. This Excel spreadsheet prices an American Option with a Trinomial Tree.

In the second part we have explained how binomial trees work. In this part we will create underlying price tree and option price tree in our spreadsheet. The binomial option pricing model is a way of finding the fair value of an option, which is a contract that gives you the right to buy or sell something at a fixed price in the future. This creates a tree of possible prices, where each branch represents a different scenario. The binomial option pricing model excel is useful for options traders to help estimate the theoretical values of options. Price movements of the underlying stocks provide insight into the values of options premium.

An important characteristic of Cox-Ross-Rubinstein trees is that the same prices keep reoccurring in subsequent steps. Our initial underlying price 100 appears not only in the first cell E4, but also in cells G5, I6, K7 – the middle node in every other step. Our underlying price tree (in cells E4-L11) is now correct, according to Cox-Ross-Rubinstein model. Binomial option pricing is based on a no-arbitrage assumption, and is a mathematically simple but surprisingly powerful method to price options. If you are creating trees with many steps, it is best to put each tree in its own sheet (with matching columns and rows for same steps and nodes). In this tutorial we are creating trees with only 7 steps, so we will put both in one sheet, next to our input cells.

This is done with an equation that varies with the type of option under consideration. For example, European and American options are priced with the equations below. Additionally, the variance of a risk-neutral asset and an asset in a risk neutral world match. It is the top node in the penultimate step (one step before expiration). Make sure the reference to L4 is relative (no dollar signs). Copy the formula from cell L13 to the seven cells below (L14-L20).

## How to Use the Binomial Distribution in Excel

This page explains the logic of binomial option pricing models – how option price is calculated from the inputs using binomial trees, and how these trees are built. Both our binomial trees – underlying price tree in cells E4-L11 and option price tree in cells E13-L20 – are correct now, using Cox-Ross-Rubinstein move sizes and probabilities. In the first part we have prepared and named our input cells.