see on the attach file "Assignment 3 443 2020" that's the questionand answer the question on attach file "Data assignemnt 3"
Assignments Haskayne School of Business University of Calgary 443 Securities and Investments Analysis Assignment 3 Miguel Palacios Fixed Income Securities and Valuation Please submit a short write-up (a spreadsheet is fine) performing the following analysis. Use the data from the file in D2L, which has data on the yield curve (more precisely, the zero-coupon yield curve) from 1985 to 2020. I encourage you to try to work it out by yourself, and then discuss it with your classmates. 1. Suppose its December 1989 (the Wall had just came down, Milli Vanilli was all the rage, Freddie Mercury was alive, and yours truly was practicing for the local precursor to the X Factor, where his band was finalist with a cover of Close to Me and original work that sounded like grunge before grunge was a thing, though, of course, Nirvana and the like where doing it on purpose, we were just incompetent1…so sorry for people who had not been born then, they just don’t know what they missed). You are looking into a new 30-year bond—its first payment will take place in December of 1990, the last in December 2019—that the US government is about to issue. a. Find the coupon rate that the bond should be issued so that it starts trading as close to par as possible, given that coupon rates change in .125% increments (i.e., there are 2.5% coupon rates, 2.625% coupon rates, but no 2.55% coupon rates).2 b. Find the price of this particular bond in December of every year, immediately after the payment for that year has taken place. c. Find the Yield-to-Maturity of this bond in December of 1989 (when issued) and in December of 2004 (immediately after the payment for that year). d. Find the return an investor would have obtained every year between each December (starting in 1989 through 2019). (Hint: don’t forget to include the payment they received each year!) e. Suppose an investor started with $1,000 invested in this bond in 1989, and that each December it reinvests all proceeds to buy more units of this bond. How much money would they end up with by December 2019?3 f. Suppose an investor started with $1,000 invested in this bond in 1989, and that each December it reinvests all proceeds to buy more units of this bond. What would have been the average return and volatility of its investment through December 2004? What about its Sharpe ratio? 1 We lost to this song by some of my high school classmates, which was definitively a song worthwhile losing to. The lead singer went on to become one of Colombia’s better-known artists. Here’s a recent song release. 2 In reality one wouldn’t have the data for the 30-year yield before issuing the 30-year bond (you would only have the yield curve up to 29 years), but let’s just ignore that for our exercise. 3 Back then you wouldn’t be able to buy fractions of a bond, but let’s assume our investor can. https://www.youtube.com/watch?v=BqGnUwoeb9U https://www.youtube.com/watch?v=HSQE0ZCqC68 https://www.youtube.com/watch?v=HSQE0ZCqC68 2. To answer the next two questions, download the latest financial statements for Chipotle (from here search for Chipotle, then select Chipotle, then pick the interactive data for the latest 10-K form—that would be for the year 2019, issued in February of 2020—and then click on “View Excel Document”, which appears just below the name of the firm). a. Calculate Chipotle’s average ROE and plowback ratios for the two most recent years available in the 10-K file. b. Calculate Chipotle’s EBITDA for the two most recent years available in the 10-K file. c. What was Chipotle’s Enterprise value/EBITDA multiple on the date the last EBITDA was made public? (You can assume it to have been the day Chipotle filed its most recent 10-K.) d. Using the Free Cash Flow model to value Chipotle, what would Chipotle’s equity value (total, and per share) be based on the cash flows reported on its most recent 10-K (you can find this in the consolidated statement of cash, taking the net cash from operating activities and the net cash from investing activities, ignoring net cash from financing), assuming that Chipotle’s free cash flows grow at rate of 10% for 10 years, and after that 1% in perpetuity? https://www.sec.gov/edgar/searchedgar/companysearch.html