Building a Binomial Tree in EXCEL with Market Data: This problem walks you through the process of building a binomial tree in EXCEL and using it to price options. In addition, you will use actual market data to estimate the market parameters that you need to do this (i.e., the risk-free rate and standard deviation of returns). In this problem, we will download price data for Amazon.com, build a binomial tree of Amazon's stock price, and finally you will price put and call options on the underlying AMZN stock using that tree.

Sources for daily stock price data and risk-free interest rates are:

http://finance.yahoo.com/q/hp?s=AMZN+Historical+Prices

http://www.bloomberg.com/markets/rates-bonds/government-bonds/us/

a. Download daily closing stock prices for Amazon (AMZN) for the 4th quarter of 2020 (October-December) and 1st two months of 2021 (through February 26). Compute the continuous daily return as follows:

= ln1

Note: if you have N daily stock prices, you will have (N-1) daily returns. Compute (and report) the standard deviation of the daily return. Compute (and report) the annualized standard deviation (based on 252 trading days in a year) as follows: = 252

b. Compute the UP and DOWN factors for a binomial model with weekly price changes (i.e., t = 1/52). Be sure to use the annualized standard deviation. Also compute and report the risk-neutral probability of an UP move. Let's use the 12-month Treasury rate as the risk-free rate so that everyone is doing the same thing. (We should use the 15-week rate, but the yield curve is extremely flat so it hardly matters which maturity we use).

c. Build a 15-week binomial tree for AMZN (this will take you from March 5 to June 18). The standard way to represent a binomial tree in EXCEL is to have each column represent a date (step). An UP move is a represented as the column directly to the right and on the same row. A DOWN move is represented as the column directly to the right and down one row. As you move across the spreadsheet the columns will grow one row longer with each step. AMZN closed at $3,000.46 on March 5.

d. Price a 15-week CALL option on AMZN with a strike price of $3200. Use backward induction and risk-neutral valuation to work your way back to today's call price. In EXCEL, build a second tree. Start by filling in the last column (week 15); these are the payoffs at maturity that correspond to the stock prices in the last column in the stock-price tree you built in part c. (Hint: just use a formula like =Max(price-3200, 0) where price references the appropriate cell from the last column of part c.) Work back one column at time, copy and pasting the risk-neutral valuation formulas. As a benchmark, the AMZN June 3200 call was selling for $131.50 on March 5. Compare your model solution, to this benchmark (they will not be the same).

e. Price a 15-week PUT option on AMZN with a strike price of $3100. As a benchmark, the AMZN June 3100 put was selling for $265.90 on March 5.

Hint: once you have built the trees for parts c and d; you can simply copy the tree from part d, and replace the call payoff formulas in the last column with the put payoff formulas. The tree will then use the risk-neutral valuation formulas to automatically fill in the tree and give you the put price today.