Options Pricing – Pricing Call Options – Option pricing spreadsheet – Binomial trees

3 mins read

European Call Option – Spreadsheet Implementation of Binomial Tree

The problem with the traditional method of constructing the binomial tree for options pricing is that extra care must be taken to ensure that the right cells are picked up in the calculation. With the alternative options pricing method intermediate cells that would normally be left blank in the traditional method are also filled in.

This makes it easier to work with an options pricing spread sheet as the relevant cells do not have to be selectively chosen in the calculation. The entire column of formulas can easily be copied to the next column without having to truncate or expand the selection or modify the formulas. This does not affect the result obtained from the binomial options pricing method but eases the calculation especially for a larger number of time steps as the relevant column can simply be copied and pasted repeatedly to achieve the desired number of time steps.

This is illustrated in detail for the European call option below. The parameters used for the European call are the same as given in the examples above. Additional examples for European put, American call and put, barriers and other exotics are also given to illustrate how adjustments to the basic model are made:

Pricing European Call Option

Step 1: Determine the prices of the underlying asset at expiry. For a time step of n=2 this works out to 5 (2n+1) possible outcomes as compared to the 3 prices under the traditional method.

1 u2S0=46.561
2 uS0=45.262
3 S0=44.000
4 dS0=42.773
5 d2S0=41.580
Figure 81: Prices of the underlying asset at expiry

Step 2: Determine the terminal payoffs at the end of time T.

CT =Max(ST-K,0)
1 1.561
2 0.262
3 0
4 0
5 0
Figure 82: Terminal payoffs of the  European call option

Step 3: Calculate the expected present values of the option values at the time steps prior to expiry. The following table also illustrates how the formulas will be applied in the spread sheet for n=2:

Column A B C D
Row ST CT T-1?t T-2?t
1 =u2S0 =max(A1-K,0) =exp(-r?t)*{p*B1+(1-p)*B2} =exp(-r?t)*{p*C1+(1-p)*C2}
2 =A1*d =max(A2-K,0) =exp(-r?t)*{p*B1+(1-p)*B3} =exp(-r?t)*{p*C1+(1-p)*C3}
3 =A2*d =max(A3-K,0) =exp(-r?t)*{p*B2+(1-p)*B4} =exp(-r?t)*{p*C2+(1-p)*C4}
4 =A3*d =max(A4-K,0) =exp(-r?t)*{p*B3+(1-p)*B5} =exp(-r?t)*{p*C3+(1-p)*C5}
5 =A4*d =max(A5-K,0) =exp(-r?t)*{p*B4+(1-p)*B5} =exp(-r?t)*{p*C4+(1-p)*C5}

Figure 83: Formulas for spreadsheet implementation of binomial tree for a European call option

Two things to note about column C:

Firstly, the formulas for the first and last cells differ from those in the middle rows. This is because there is no cell prior to row 1 for which there is a payoff in column B and there is no cell after row 5 for which there is a payoff in column B. This is a necessary adjustment that has to be made in the spread sheet.

The second point is that column D is a replica of C except that it refers to cells in column C instead of B. This is evidence of the fact that once formulas for columns A, B and C are determined, later columns are simply a function of copying the column C formulas and pasting it forward, which simplifies the binomial tree construction process for larger time steps.

The numerical results are given below:

Column A B C D
Row ST CT T-1?t T-2?t
1 46.561 1.561 0.924 0.861
2 45.262 0.262 0.796 0.537
3 44.000 0.134 0.406
4 42.773 0.068
5 41.580

Figure 84: Spreadsheet for European call option example

Step 4: Determine the option price. This is the value in the n+1th cell in the last column of the spread sheet. For n=2, this is the third entry at time T-2?t, i.e. the entry in cell D3 above or 0.406. As we can see this is the same value that we have obtained using the traditional binomial tree construction method.

By increasing the time steps, which means recalculating ?t, u, d and p and simply adding on rows below and columns to the right above, greater accuracy in the option price can be obtained. For time steps n=200 the option price works out to 0.3445 which underestimates the price by 0.0007 as compared to overestimating it by 0.0609 when only 2 time steps were used.

It must be noted the progression towards the exact price given by the Black Scholes formula as time steps are increased, is fairly erratic- at times very close to the value and then oscillating away from it. In general, though as the time steps increase the precision increases. However, employing more steps does not necessarily lead to greater accuracy for other options such as barriers. In these cases it is more a question of selecting an appropriate value for n. This will be elaborated in more detail later.

Comments are closed.