Data case 1 Econ 670 Problem 2 (40 points) Show all your work to receive full credit. Completed assignments will include an Excel file with calculations for problems, as well as discussion parts (e.g, P1, d, "Intuitively explain to your boss the relationship between the NPV and the cost of capital). Please use one sheet for each question. Excel file must be submitted through Canvas by October 7, 2020, 6:45pm. It is February 15, 2018. You work as an analyst for the hedge fund Fixed Income Masters, LLC and have been asked analyze the pricing of several bonds issued by the U.S. Treasury to identify attractive relative value op- portunities. You download the following price data for Treasury bonds and notes from the Bloomberg terminal: U.S. Treasury Quotes Treasury Notes & Bonds Thursday, February 15, 2018 Treasury note and bond data are representative over-the-counter quotations as of 3pm Eastern time. For notes and bonds callable prior to maturity, yields are computed to the earliest call date for issues quoted above par and to the maturity date for issues below par. Maturity Coupon Bid Asked Che 8/15/2027 6.375 129.2578 129.2734 -0.2578 11/15/2027 2.250 94.8984 94.9141 -0.1719 11/15/2027 6.125 127.6172 127.6328 -0.2656 2/15/2028 2.750 99.1953 99.2109 -0.1484 8/15/2028 5.500 123.7031 123.7188 -0.2344 11/15/2028 5.250 121.8516 121.8672 -0.2500 2/15/2029 122.2500 122.3125 -0.2891 8/15/2029 6.125 131.5938 131.6563 -0.2891 5/15/2030 6.250 134.4688 134,5313 -0.3281 2/15/2031 5.375 126.8203 126.8828 -0.3125 2/15/2036 4.500 121.8984 121.9609 -0.2891 2/15/2037 4.750 126.1563 126.2188 -0.3438 5/15/2037 5.000 129.9297 129.9922 -0.3359 2/15/2038 4.375 121.1875 121.2500 -0.3516 5/15/2038 4.500 123.2344 123.2969 -0.3438 2/15/2039 3.500 108.0469 108.1094 -0.3281 5/15/2039 4.250 119.7188 119.7813 -0.3750 8/15/2039 4.500 123.7422 123.8047 -0.3828 You decide to focus on the 2.75's of 2028 and price the bond using the zero- coupon yield curve downloaded from Gurkaynak, Sack, and Wright (2006): https://www.federalreserve.gov/econresdata/feds/2006/index.htm a. Download the data from the link under the article "The U.S. Treasury yield curve: 1961 to present". Save data on an excel sheet named "yields data". Then save the zero-coupon yield curve as of 02/15/2018 for maturities from 1 to 10 years (SVENYO1 to SVENY10) in an Excel spreadsheet"Problem 2". Save the yield curve in a column format i.e., 1 year rate in row 1, 2 year rate in row 2, etc.). b. You notice that the yields are all continuously compounded. Translate the yields into semi-annual compounding using the following formula: R=nx [exp - 1] (1) where, R is the semi-annually compounded yield in percent, n = 2, and r is the continuously compounded yield in percent, and exp" is the exponential function (use the "exp(x)" function in Excel). c. You also notice that the yield curve you downloaded is annual whereas the bond cash flows will be paid semi-annually. You look up on Bloomberg and see that the interest rate for the 26-week T-bill is 1.85%. You know that T-bills are zero coupon security, so you can use this interest rate directly as the 0.5-year rate. For the other maturities, you decide to linearly interpolate between the annual yields to create a semi-annual yield curve over 10 year's period, i.e., T=0.5, 1, 1.5....10. Save the interpolated yield curve in a column format. (Please google linear in- terpolation.) d. In a column next to the interpolated yield curve, compute the corre- sponding discount factors for each half-year period. e. In a column next to the discount factors, paste the bond's cash flows (coupon and principal payments) from Problem 1. Use the discount factors you derived from the yield curve to price each of the promised cash flows and the bond. Report the estimated bond price. f. How does your bond price estimate compare to the bid and the asked 5.250 Data case 1 Econ 670 Problem 2 (40 points) Show all your work to receive full credit. Completed assignments will include an Excel file with calculations for problems, as well as discussion parts (e.g, P1, d, "Intuitively explain to your boss the relationship between the NPV and the cost of capital). Please use one sheet for each question. Excel file must be submitted through Canvas by October 7, 2020, 6:45pm. It is February 15, 2018. You work as an analyst for the hedge fund Fixed Income Masters, LLC and have been asked analyze the pricing of several bonds issued by the U.S. Treasury to identify attractive relative value op- portunities. You download the following price data for Treasury bonds and notes from the Bloomberg terminal: U.S. Treasury Quotes Treasury Notes & Bonds Thursday, February 15, 2018 Treasury note and bond data are representative over-the-counter quotations as of 3pm Eastern time. For notes and bonds callable prior to maturity, yields are computed to the earliest call date for issues quoted above par and to the maturity date for issues below par. Maturity Coupon Bid Asked Che 8/15/2027 6.375 129.2578 129.2734 -0.2578 11/15/2027 2.250 94.8984 94.9141 -0.1719 11/15/2027 6.125 127.6172 127.6328 -0.2656 2/15/2028 2.750 99.1953 99.2109 -0.1484 8/15/2028 5.500 123.7031 123.7188 -0.2344 11/15/2028 5.250 121.8516 121.8672 -0.2500 2/15/2029 122.2500 122.3125 -0.2891 8/15/2029 6.125 131.5938 131.6563 -0.2891 5/15/2030 6.250 134.4688 134,5313 -0.3281 2/15/2031 5.375 126.8203 126.8828 -0.3125 2/15/2036 4.500 121.8984 121.9609 -0.2891 2/15/2037 4.750 126.1563 126.2188 -0.3438 5/15/2037 5.000 129.9297 129.9922 -0.3359 2/15/2038 4.375 121.1875 121.2500 -0.3516 5/15/2038 4.500 123.2344 123.2969 -0.3438 2/15/2039 3.500 108.0469 108.1094 -0.3281 5/15/2039 4.250 119.7188 119.7813 -0.3750 8/15/2039 4.500 123.7422 123.8047 -0.3828 You decide to focus on the 2.75's of 2028 and price the bond using the zero- coupon yield curve downloaded from Gurkaynak, Sack, and Wright (2006): https://www.federalreserve.gov/econresdata/feds/2006/index.htm a. Download the data from the link under the article "The U.S. Treasury yield curve: 1961 to present". Save data on an excel sheet named "yields data". Then save the zero-coupon yield curve as of 02/15/2018 for maturities from 1 to 10 years (SVENYO1 to SVENY10) in an Excel spreadsheet"Problem 2". Save the yield curve in a column format i.e., 1 year rate in row 1, 2 year rate in row 2, etc.). b. You notice that the yields are all continuously compounded. Translate the yields into semi-annual compounding using the following formula: R=nx [exp - 1] (1) where, R is the semi-annually compounded yield in percent, n = 2, and r is the continuously compounded yield in percent, and exp" is the exponential function (use the "exp(x)" function in Excel). c. You also notice that the yield curve you downloaded is annual whereas the bond cash flows will be paid semi-annually. You look up on Bloomberg and see that the interest rate for the 26-week T-bill is 1.85%. You know that T-bills are zero coupon security, so you can use this interest rate directly as the 0.5-year rate. For the other maturities, you decide to linearly interpolate between the annual yields to create a semi-annual yield curve over 10 year's period, i.e., T=0.5, 1, 1.5....10. Save the interpolated yield curve in a column format. (Please google linear in- terpolation.) d. In a column next to the interpolated yield curve, compute the corre- sponding discount factors for each half-year period. e. In a column next to the discount factors, paste the bond's cash flows (coupon and principal payments) from Problem 1. Use the discount factors you derived from the yield curve to price each of the promised cash flows and the bond. Report the estimated bond price. f. How does your bond price estimate compare to the bid and the asked 5.250