Spreadsheet for a 2 step CRR model.

Spreadsheet for a 2 step CRR model.

We are going to use Excel to build a 2 step binomial model. The steps below are intended to assist.

1.1 Basic inputs

We begin by giving the inputs for the variables. These will change depending on the parameters given in each example/exercise.

1. The stock price (in cell B1) S0;

2. Exercise/Strike price (cell B2) K;

3. Annual risk free rate (cell B3) r;

4. Expiry (cell B4) T;

5. Number of time steps (cell B5) N;

6. Up-move (cell B7) u;

7. Down-move (cell B8) d.

As a first example, we will use the following variables:

1. S0 = 80

2. K = 78

3. r = 0,04 per annum

4. T = 2 years

5. N = 2

6. u = 1,05

7. d = 1/u

Suggestion: Label all the input values for the cells in the B column in the A column.

1.2 Basic formulae

Next we input the basic formulae needed for building our model.

The input for the formulae is provided in the square brackets.

1. Length of time interval per step (cell B6) ∆t = T N [=B4/B5].

2. The risk-neutral probability of an up-move (cell B9), since the short rate r is continuously compounded, is q = e r∆t−d u−d [=((EXP(B3*B6))-B8)/(B7-B8)].

3. The risk neutral probability of a down-move (cell B10) 1 − q.

1.3 Stock price tree

We now construct the 2 step stock price tree.

Suggestion: Put the label “Stock tree” in cell D1.

1. Start the first node of the stock tree (S0) in cell D2 [=B1] and then you branch it to the right; 2. the node uS0 [=B7*B1] in cell E2 and dS0 [=B8*B1] in cell E3;

3. the node u^2S0 [=E2*B7] in cell F2, the node udS0 = duS0 [=E2*B8] in cell F3 and the node d^2S0 [=E3*B8] in cell F4.

2 1.4 European put option tree Suggestion: Put the label “European put option” in cell D16.

1. Type the formula for the payoff of the European put option at T = 2 for the state

• uu in cell F17 [=MAX(B2-F2;0)]; • ud = du in cell F18;

• dd in cell F19. 2.

Compute the values of the option at the other nodes in the option tree.

3. Your formula in cell D18 should show the price of the European put option as 0,044479.

1.5 European call option tree

You can use put call parity or a 2 step option tree to determine the value of the European call option with the same parameters (same underlying, same expiry time and exercise price). The price of the European call option at t = 0 should be 8,041404.

Remark 1.1. Once your 2 step model is working correctly, save the file and use the model to complete the following exercise.

Excercise 1.1. Assume that a 2 step binomial stock model has the following parameters:

• T = 2 years

• N = 2

• u = 1,6

• d = 1/u

• Stock price at time 0: S0 = 140

• Strike price K = 160

• Continuously compunded annual risk free rate r = 8%.

Calculations in Excel without any rounding but give answers rounded off to two significant decimals.

1.1.1 The risk-neutral probability q is . . . . . . . . . .

1.1.2 The fair price of a European put option, expiring in 2 years time with an exercise price of 160 is . . . . . . . . . .

1.1.3 The fair price of a European call option, expiring in 2 years with an exercise price of 160 is . . . . . . . . . .