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 Amzaon.com, build a binomial tree of Amazons 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 2018 (October-December) and January & February of 2019. Compute the continuous daily return as follows: = ln 1 Note: if you have N daily stock prices in 5 months, 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. Lets use the 12-month Treasury rate as the risk-free rate so that everyone is doing the same thing. (We should use the weekly rate, but the entire short-end of the yield curve is pretty flat so it hardly matters which maturity we use). c. Build a 17-week binomial tree for AMZN (this will take you from March 22 to July 19). 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 $1,764.77 on March 22. d. Price a 17-week CALL option on AMZN with a strike price of $1800. Use backward induction and risk-neutral valuation to work your way back to todays call price. In EXCEL, build a second tree. Start by filling in the last column (step 17); these are the payoffs at maturity that correspond to the stock prices in the last column in the stockprice tree you built in part c. (Hint: just use a formula like =Max(price-1800, 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 July 1800 call was selling for $105.35 on March 22. e. Price a 17-week PUT option on AMZN with a strike price of $1700. As a benchmark, the AMZN July 1700 put was selling for $79.05 on March 22. 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