USM FIN 617 Week 6 Excel Project

Excel programming: option pricing withsix-step binomial tree

1. This project is anindividual project. You may not work with others.

2. The total point for this project is 15% of your course grade.

3. You need to submit an Excel file. Use examples in the lecture notes as a reference. Fully understand the examples in notes before you start to work on this project.

4. You need to havesix input cells:S, X, r_annual, ?_annual, T, and N=6. All other cells should be formulas and automatically computed. Note that the risk free rate (r_annual) iscontinuously compounded and that you need to use the EXP function.

5. For the Binomial Model (8% of course grade):

a. Based on input variables, compute u, d, r, p, and 1-p.

b. Produce five trees, S, C^E, P^E, C^A, and P^A, and EEP (early exercise premium) for C^A and P^A.

c. Four option trees: there should be only two unique formulas for each option tree: one formula forall leaf nodes and one formula forall 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.

d. One stock tree: there should be onlythree unique formulas in the stocks tree: root, up node, and down node. The rest of the nodes should be done by copy/paste one of the three unique formulas. You shouldnot use the power function.

e. You maynot use the property that C^A = C^E, which means in your C^A tree, you need to program the early-exercise feature of C^A.

f. Do not turn in two-period trees. They are used for only demonstration purposes.

6. For the Black-Scholes Model (7% of course grade):

a. Fill-in the entries in the Black-Scholes section of the spreadsheet (Below the Binomial Model).

b. Fill-in the Summary Table

c. Make a copy (NOT a cell reference) of your initial stock price into cellF90.

d. Complete the three Data Tables and Graph them.

7. For The Entire Assignment:

a. Make your Excel file easy and pleasant to read. Carefully and clearlylabeleverything. You may use any format (including the one given in the next page) as long as it is clear. Format all prices to two decimal places, with a dollar sign in the front,e.g. $23.45. Format all percentages as a percent,e.g., 12.34%.

b. Rename your Excel file by adding your name,e.g., for Prof. Barrett the fileUSM1_FIN_617_Week06_OptionModel.xlsx would becomeUSM1_FIN_617_Week06_OptionModel _Barrett_Brian.xlsx.

Do not use any other file names!

c. Check your work carefully before you submit your file to me. If you resubmit, you first submission will be deleted.

Input	S	X	rAnnual	sAnnual	T	N	u	d	r	p	1-p
Input	$100.00	$109.00	10.00%	36.00%	0.25	2	9.42%	-8.61%	1.26%	54.73%	45.27%
Binomial	Ca	$3.13		Ce	$3.13		EEP(Ca)	$0.00
Binomial	Pa	$10.05		Pe	$9.44		EEP(Pa)	$0.61
		period 0	period 1	period 2		IV(call)	IV(put)
				$119.72		$10.72	$0.00
			$109.42
	Stock	$100.00		$100.00		$0.00	$9.00
			$91.39
				$83.53		$0.00	$25.47
		period 0	period 1	period 2			period 0	period 1	period 2
				$10.72					$0.00
	European		$5.80			European		$4.02
	Call Option	$3.13		$0.00		Put Option	$9.44		$9.00
			$0.00					$16.25
				$0.00					$25.47
		period 0	period 1	period 2			period 0	period 1	period 2
				$10.72					$0.00
	American		$5.80			American		$4.02
	Call Option	$3.13		$0.00		Put Option	$10.05		$9.00
			$0.00					$17.61
				$0.00					$25.47