Binomial option pricing model in Excel

In this lesson, we’ll learn how to set up the binomial option pricing model in Excel. This will allow us to value both call options and put options. We’ll focus on European options that can be exercised only at the expiry of the contract, rather than American options, which can be exercised before expiry.

Model inputs

In order to use the binomial option pricing model, we need a few inputs. To be specific, as you can see in Figure 1 below, five inputs are required. Let’s assume that the risk-free rate is 10%. For simplicity, you can imagine a risk-free bond (or even a bank account) such that if you purchase $100 worth of this bond (or deposit $100 into a bank account), you’d get back $110 at the end of the period. Let’s say the current stock price is $20. Each period, the stock price will go either up or down (hence the word “binomial”). We assume that the stock price can go up by 15% (i.e., the price would increase from $20 to $23) or down by 5% (i.e., the price would decrease to $19). The final input we need is the exercise price (a.k.a. strike price) of the option. Let’s set it to $20. The interpretation of the exercise price depends on whether we are dealing with a call option vs put option as we explain below. However, in either case, when the current stock price is equal to the exercise price, we have an at-the-money option.

Stock and bond payoffs

Next, we compute the period-end payoffs for the stock and the bond (see Figure 2 below). As discussed above, the stock price will either increase to S_u = (1+15%) * $20 = $23 or decrease to S_d = (1-5%) * $20 = $19. Regardless of what happens to the stock, the bond always yields a 10% return as it is risk free. Therefore, 1 unit of investment in the bond today always results in (1+10%) * 1 = 1.1 at the end of the period.

Call option payoffs

Next, we find the final payoffs of the European call option. A call option becomes valuable (“in the money“) when the stock price exceeds the exercise price. For example, if Tesla shares are currently worth $280 and your call option can be exercised at a price of $270, you can buy Tesla shares for $270 by exercising your option and immediately sell these shares for $280, pocketing a profit of $10/share. On the other hand, if the current share price is $265, you would choose not to exercise your option as there is no point paying $270 to the option writer when you can purchase the same shares $5 cheaper in the market. In summary, the call option payoff is equal to either $0 (when you don’t exercise) or “stock price − exercise price” (when you exercise).

In the spreadsheet, when the stock price goes down, the option payoff is (see Figure 3):

=MAX(0,K7-$D$7)

Note that this is equal to C_d = max($0, $19 − $20) = $0. And, when the stock price goes up, the option payoff is

=MAX(0,K6-$D$7)

This is equal to C_u = max($0, $23 − $20) = $3.

Call option price

To find the price of the European call option, we need to construct a portfolio consisting of the underlying stock and the risk-free bond, such that the payoffs of this portfolio are identical to the call option payoffs. Such a portfolio is sometimes referred to as a replicating portfolio. According to the law of one price, if two assets (the replicating portfolio and the call option in our case) have identical payoffs, they should trade at the same price. Otherwise, there would be an arbitrage opportunity. This implies that the price of the European call option today is the value of the replicating portfolio.

To construct the replicating portfolio, we need to compute two parameters: Δ (the amount of investment in the underlying stock) and B (the amount of investment in the risk-free bond). The formulas for these two parameters are as follows:

In our example, Δ = ($3 − $0) / ($23 − $19) = 0.75. We’d enter this in the spreadsheet as follows (see also Figure 4):

=(Y5-Y7)/(K5-K7)

Purchasing 0.75 shares at $20 today costs $15, and this investment will be worth either 0.75 * $23 = $17.25 or 0.75 * $19 = $14.25 at the end of the period (see cells H11, K10, and K12 in Figure 4).

Next, B = ($0 − 0.75 * $19) / (1+10%) = −$12.95. This means that we’re “borrowing” $12.95 at the risk-free rate. In the spreadsheet, we would have (see also Figure 5):

=(Y7-K7*D10)/(1+D5)

As we borrow $12.95 today, we need to repay (1+10%) * $12.95 = $14.25 at the end of the period (see cells O11, Q10, and Q12 in Figure 5).

Now, notice that the value of the replicating portfolio when the stock price goes up is (see cells K10, Q10, and Y10 in Figure 6):

$17.25 − $14.25 = $3

This is identical to the European call option payoff when the stock price goes up: C_u = $3 (cell Y5). And, the value of the replicating portfolio when the stock price goes down is (see cells K12, Q12, and Y12 in Figure 6):

$14.25 − $14.25 = $0

And, this is the same as the European call option payoff when the stock price goes down: C_d = $0 (cell Y7). Therefore, we have successfully constructed the replicating portfolio. Then, the European call option price is equal to the value of this portfolio today (see cells H11, O11, and V11 in Figure 6):

$15 − $12.95 = $2.05

Put option payoffs

In total contrast to call options, put options become valuable when stock prices decline. This is because put options allow you to sell shares at the exercise price, offering a hedge against downturns. The table below compares the payoffs for the call option we examined above with a put option that also has an exercise price of $20. As you can see, the put option payoff is $0 if the stock price increases to $23 and $1 if it decreases to $19.

Stock price goes	Call option payoff	Put option payoff
Up	MAX(0,K6-$D$7) = MAX($0,$23−$20) = $3	MAX(0,$D$7-K6) = MAX($0,$20−$23) = $0
Down	MAX(0,K7-$D$7) = MAX($0,$19−$20) = $0	MAX(0,$D$7-K7) = MAX($0,$20−$19) = $1

Put option price

The strategy for finding the value of the European put option is identical to the one for the European call option: construct a replicating portfolio using the underlying stock and the risk-free bond, and the value of that portfolio today is the put option price. Here are the corresponding formulas for Δ and B to be used in put option valuation:

We have Δ = ($0 − $1) / ($23 − $19) = −0.25. This implies a short position of 0.25 shares in the stock. The value of that position is −0.25 * $20 = −$5 today (see cell H11 in Figure 7). If the stock price increases to $23, the short position payoff worsens to −0.25 * $23 = −$5.75 (cell K10). If it decreases to $19, it becomes −0.25 * $19 = −$4.75. For the risk-free bond, we have B = ($1 − (−0.25) * $19) / (1+10%) = $5.23 (cell O11). This implies lending $5.23 at the risk-free rate, such that we get repaid (1 + 10%) * $5.23 = $5.75 (cells Q10 and Q12).

As in the case of the call option, the payoffs of the replicating portfolio (cells Y10 and Y12) match those of the put option (cells Y5 and Y7). Then, the value of the replicating portfolio today and, thus, the put option price is: −$5 + $5.23 = $0.23 (see cells H11, O11, and V11 in Figure 7).

Video tutorial and Excel template

What is next?

If you’ve enjoyed this tutorial on the binomial option pricing model, you might also like these tutorials.