Option Pricing with Binomial Trees - Excel
Budget: $10 – $30 USD
You need to submit an Excel file. You have six input cells: S, X, rc, σ, T, and N=6. All other cells should be
formulas and automatically computed. Test your spreadsheets thoroughly. Note that the
risk-free rate (rc) is continuously compounded and that you must use the EXP function, not (1+rc)T. There are no dividends in this exercise.
2. Black-Scholes model:
a. Use the above input to compute the Black-Scholes option price and check they satisfy the put-call parity.
3. Binomial model:
a. Compute u, d, r, p, and 1-p based on input variables.
?? = ??
??√??
?? − 1; ?? = ??−??√??
?? − 1; ?? = ??
????×??
?? − 1; ?? =
?? − ??
?? − ??
b. You should first implement the two-step trees to match all answers before you attempt the six-step trees. Keep your implementations of the two step trees on a separate sheet in the Excel file as a part of your submission.
c. Build seven trees, S, CE, PE, CA, PA, CB, and PB, and compute the EEP (early exercise premium) for CA and PA. All trees have six time periods.
d. One stock tree [S]: there should be only three unique formulas in the stock tree: a root, an up node, and a
down node. The rest of the nodes should be done by copying/pasting one of the three unique formulas. Use color coding to show the same formulas. You should not use the power function.
e. Two European-style option trees and two American-style option trees [CE, PE, CA, PA]: there should be only two unique formulas for each option tree: one for all leaf nodes and one for all non-leaf nodes. Your file should allow me to copy a formula from a leaf node and paste it onto a different leaf node in the same tree.
I can also copy a formula from a non-leaf node and paste it onto a different non-leaf node in the same tree.
Your options tree should remain correct. Use color coding to show the same formulas.
f. You may not use the property that CA = CE, which means you need to program the early-exercise feature of CA in your CA tree.
g. Two Bermudan-style option trees [CB, PB]: These options are not exercisable in the first two time periods but are exercisable in the last four. Build these two trees only after you have done [CE, PE, CA, PA].
5.
a. Make your Excel file easy and pleasant to read. Clearly label everything. You may use any format if it is
clear, but the solutions should be on one Excel sheet and based on one set of six input cells. No other
changes or inputs are needed to make your models work. Format all prices to two decimal places with a
dollar sign, e.g., $23.45. Format all percentages as a percent with two decimal places, e.g., 12.34%.
formulas and automatically computed. Test your spreadsheets thoroughly. Note that the
risk-free rate (rc) is continuously compounded and that you must use the EXP function, not (1+rc)T. There are no dividends in this exercise.
2. Black-Scholes model:
a. Use the above input to compute the Black-Scholes option price and check they satisfy the put-call parity.
3. Binomial model:
a. Compute u, d, r, p, and 1-p based on input variables.
?? = ??
??√??
?? − 1; ?? = ??−??√??
?? − 1; ?? = ??
????×??
?? − 1; ?? =
?? − ??
?? − ??
b. You should first implement the two-step trees to match all answers before you attempt the six-step trees. Keep your implementations of the two step trees on a separate sheet in the Excel file as a part of your submission.
c. Build seven trees, S, CE, PE, CA, PA, CB, and PB, and compute the EEP (early exercise premium) for CA and PA. All trees have six time periods.
d. One stock tree [S]: there should be only three unique formulas in the stock tree: a root, an up node, and a
down node. The rest of the nodes should be done by copying/pasting one of the three unique formulas. Use color coding to show the same formulas. You should not use the power function.
e. Two European-style option trees and two American-style option trees [CE, PE, CA, PA]: there should be only two unique formulas for each option tree: one for all leaf nodes and one for all non-leaf nodes. Your file should allow me to copy a formula from a leaf node and paste it onto a different leaf node in the same tree.
I can also copy a formula from a non-leaf node and paste it onto a different non-leaf node in the same tree.
Your options tree should remain correct. Use color coding to show the same formulas.
f. You may not use the property that CA = CE, which means you need to program the early-exercise feature of CA in your CA tree.
g. Two Bermudan-style option trees [CB, PB]: These options are not exercisable in the first two time periods but are exercisable in the last four. Build these two trees only after you have done [CE, PE, CA, PA].
5.
a. Make your Excel file easy and pleasant to read. Clearly label everything. You may use any format if it is
clear, but the solutions should be on one Excel sheet and based on one set of six input cells. No other
changes or inputs are needed to make your models work. Format all prices to two decimal places with a
dollar sign, e.g., $23.45. Format all percentages as a percent with two decimal places, e.g., 12.34%.