Excel Assignment Finance/Real Estate

Description

MUST USE EXCEL AND SHOW FORMULAS: Submit as ONE excel file (for written answers, use Insert > Text Box). Show all work for full credit (any calculated cell in excel must contain a formula). Make sure your presentation is clear and answer in order, a separate tab for each question. You may use a separate tab for graphs (they must be visible when the sheet is scrolled all the way up, and not obscure any data). Rename tabs e.g. from Sheet1 to Q1-a, Q1-b, etc. 1. [15 pts] Use the par yield curve below to price the cash flows given in the left-most column. Quote the price to the closest penny. Period Maturity (yrs) Yield (BEY %) CF ($) 1 0.5 0.5 10 2 1 2 20 3 1.5 3 100 4 2 4 -30 5 2.5 5 50 6 3 6 50 7 3.5 7 100 8 4 7.5 200 9 4.5 8 0 10 5 8.4 1,000 2. [15 pts] Give and describe the no-arbitrage table to price a 2-year zero coupon loan 2 years from now, accounting for the actions of all parties. For concreteness sake, use the par yield curve in question 1. What is the lockable, annualized rate for such a loan? 3. [10 pts] Briefly describe the expectations hypothesis, and how the liquidity preference theory accounts for the observation that the yield curve tends to be upward sloped, rather than what is predicted by the expectations hypothesis.