# 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.