Problem 1 (20 points) Data case 1 Econ 670 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: Your boss is intrigued by the pricing of the 2.75's of 2028 relative to the 4.375's of 2038 (highlighted above)! The 10-year bond is trading at a dis- count while the 20-year bond is trading at a large premium. Your boss believes that there is a sure trading opportunity, and argues that buying the discount bond and simultaneously selling the premium bond short will result in a sure profit over the next 20 years. a. Without offending your boss, explain why his suggested trade may not be a sure winner. To ensure that your boss understands, put in an Excel spreadsheet "Problem 1" the coupon and principal received by bond holders of the two bonds (per $100 of face value). February 15, 2018 corresponds to date zero on the time axis (i.e., no coupon payments at date 0). Note that both bonds pay a semi-annual coupon (so watch out the coupon payments in every 6 months!) b. In the columns to the right, show the projected cash flows from the trade suggested by your boss (buying $100 of 2.75's of 2028 and short- selling $100 of 4.375's of 2038) over the next 20 years, assuming you can buy bonds at the asked price, short-sell at the bid, and hold bonds to maturity. Show the cash flows for each of the bonds, as well as the combined cash flows (cash flows of your portfolio, where you buy $100 of 2.75's of 2028 and short-sell $100 of 4.375's of 2038). c. Assuming Fixed Income Masters, LLC's cost of capital is 10% (semi- annually compounded), what is the net present value to your hedge fund of the above investment strategy? Hint: Calculate the discount factor in one column, then calculate the NPV next to it. d. Assuming Fixed Income Masters, LLC's cost of capital is only 3%, what is the net present value to your hedge fund of the above investment strategy? Intuitively explain to your boss the relationship between the NPV and the cost of capital. 123.7031 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 Chg 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.7188 -0.2344 11/15/2028 5.250 121.8516 121.8672 -0.2500 2/15/2029 5.250 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 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 | 4.500 Problem 1 (20 points) Data case 1 Econ 670 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: Your boss is intrigued by the pricing of the 2.75's of 2028 relative to the 4.375's of 2038 (highlighted above)! The 10-year bond is trading at a dis- count while the 20-year bond is trading at a large premium. Your boss believes that there is a sure trading opportunity, and argues that buying the discount bond and simultaneously selling the premium bond short will result in a sure profit over the next 20 years. a. Without offending your boss, explain why his suggested trade may not be a sure winner. To ensure that your boss understands, put in an Excel spreadsheet "Problem 1" the coupon and principal received by bond holders of the two bonds (per $100 of face value). February 15, 2018 corresponds to date zero on the time axis (i.e., no coupon payments at date 0). Note that both bonds pay a semi-annual coupon (so watch out the coupon payments in every 6 months!) b. In the columns to the right, show the projected cash flows from the trade suggested by your boss (buying $100 of 2.75's of 2028 and short- selling $100 of 4.375's of 2038) over the next 20 years, assuming you can buy bonds at the asked price, short-sell at the bid, and hold bonds to maturity. Show the cash flows for each of the bonds, as well as the combined cash flows (cash flows of your portfolio, where you buy $100 of 2.75's of 2028 and short-sell $100 of 4.375's of 2038). c. Assuming Fixed Income Masters, LLC's cost of capital is 10% (semi- annually compounded), what is the net present value to your hedge fund of the above investment strategy? Hint: Calculate the discount factor in one column, then calculate the NPV next to it. d. Assuming Fixed Income Masters, LLC's cost of capital is only 3%, what is the net present value to your hedge fund of the above investment strategy? Intuitively explain to your boss the relationship between the NPV and the cost of capital. 123.7031 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 Chg 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.7188 -0.2344 11/15/2028 5.250 121.8516 121.8672 -0.2500 2/15/2029 5.250 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 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 | 4.500