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

*in*Options Pricing

## 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 | S_{T} |
C_{T} |
T-1?t | T-2?t |

1 |
=u^{2}S_{0} |
=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 | S_{T} |
C_{T} |
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 | S_{T} |
C_{T} |
T-1?t | T-2?t |

1 |
=u^{2}S_{0} |
=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 | S_{T} |
C_{T} |
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”

Comments are closed.