Browse By

Options Pricing – Pricing American Options – Calls and Puts – Spreadsheet implementation – Binomial trees

Pricing American Call Option

Assume the same parameters used for pricing for the European call. The adjustment will be made in Step 3 in column C of our option pricing spreadsheet and then copied to column D of our option pricing spread sheet and later columns.

At non terminal nodes the option will only be exercised early if it’s intrinsic value at that point in time, S-K, exceeds the expected present value of the option values at the successor nodes. That is, the formula at these non-terminal nodes will be  max[S-K, C]

where C is the value of the option at the node and is given by PVFactor * (value of up-node* p + value of down-node *(1-p))

and S is the price of the underlying at that node. In terms of the spreadsheet column C will read as follows for n=2:

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

Figure 86: Formulas for spreadsheet implementation of binomial tree for an American call option

And the numerical values will be:

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

Figure 87: Spreadsheet for American call option example

The American call option price is equal to 0.406.

Pricing American Put Option

Assume the same parameters as for the European put. The adjustment will be made in Step 3 in column C and then copied to column D and later columns.

At non terminal nodes the option will only be exercised early if it’s intrinsic value at that point in time, K-S, exceeds the expected present value of the option values at the successor nodes. That is, the formula at these non-terminal nodes will be max[K-S,C]

Where C is the value of the option at the node and is given by PVFactor * (value of up-node* p + value of down-node *(1-p))

and S is the price of the underlying at that node. In terms of the spreadsheet column C will read as follows for n=2:

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

Figure 88: Formulas for spreadsheet implementation of binomial tree for an American put option

And the numerical values will be:

Column A B C D
Row ST CT T-1?t T-2?t
1 47.619 - - 0.000
2 46.291 - 0.000 0.300
3 45.000 0.000 0.614 0.614
4 43.745 1.255 1.255 1.523
5 42.525 2.475 2.475 2.475

Figure 89: Spreadsheet for American put option example

The American put option price is equal to 0.614.

2 thoughts on “Options Pricing – Pricing American Options – Calls and Puts – Spreadsheet implementation – Binomial trees”

  1. Pingback: Options pricing using Binomial trees – Building an efficient option pricing spreadsheet in Excel
  2. Trackback: Options pricing using Binomial trees – Building an efficient option pricing spreadsheet in Excel
  3. Pingback: SOA Exam MFE/3F free course materials and paid for online video courses for MFE exam 2011 | Learning Corporate Finance
  4. Trackback: SOA Exam MFE/3F free course materials and paid for online video courses for MFE exam 2011 | Learning Corporate Finance

Comments are closed.