please show all answers in Excel with formulas.
DE G H ] 6 Question 4. (20 points) The Aleander Company plans to issue $10,000,000 of 10-year bonds at par next June, with semiannual interest payments. The company's current cost of debt is 10 percent. However, the firm's financial manager is concerned that interest rates will increase in coming months, and has decided to take a short position in U.S. government t-bond futures. See the settlement data below for t-bond futures. (Note: One standard futures contract is $100,000) 8 Low Settle Change 10 Futures Prices: Treasury Bonds--$100,000; Pts. 32nds of 100% Delivery Month Open High (1) (2) Dec 103'14 103-14 Mar 102'11 102'23 (4) (5) Open Interest (7) 678,000 135,855 102'11 102'17 100'28 10101 June 101'14 101'26 99'32 99'08 17,255 a. (1) Calculate the implied yield on the futures contract? (2) How many futures contracts will be needed to hedge potential losses in bond proceeds (based on current market conditions) due to waiting (round up to the nearest integer)? (3) Calculate the total value of the hedge position. b. Interest rates in general increase by 200 basis points The bond's terms don't change and that the coupon rate is still 10%. (1) Calculate the amount of proceeds be given the new market rates? (2) Calculate the loss in proceeds based on the original target for proceeds?? c. Assume that Zinn had entered the hedge found in part a. (1) Calculate the new price of the hedge position? (2) Calculate the gain on the hedge? (3) Calculate the net effect of the loss of proceeds and the ga on the hedge? 34 d. Is this problem an example of a perfect hedge or a cross hedge? Is it an example of speculation or hedging? Why? DE G H ] 6 Question 4. (20 points) The Aleander Company plans to issue $10,000,000 of 10-year bonds at par next June, with semiannual interest payments. The company's current cost of debt is 10 percent. However, the firm's financial manager is concerned that interest rates will increase in coming months, and has decided to take a short position in U.S. government t-bond futures. See the settlement data below for t-bond futures. (Note: One standard futures contract is $100,000) 8 Low Settle Change 10 Futures Prices: Treasury Bonds--$100,000; Pts. 32nds of 100% Delivery Month Open High (1) (2) Dec 103'14 103-14 Mar 102'11 102'23 (4) (5) Open Interest (7) 678,000 135,855 102'11 102'17 100'28 10101 June 101'14 101'26 99'32 99'08 17,255 a. (1) Calculate the implied yield on the futures contract? (2) How many futures contracts will be needed to hedge potential losses in bond proceeds (based on current market conditions) due to waiting (round up to the nearest integer)? (3) Calculate the total value of the hedge position. b. Interest rates in general increase by 200 basis points The bond's terms don't change and that the coupon rate is still 10%. (1) Calculate the amount of proceeds be given the new market rates? (2) Calculate the loss in proceeds based on the original target for proceeds?? c. Assume that Zinn had entered the hedge found in part a. (1) Calculate the new price of the hedge position? (2) Calculate the gain on the hedge? (3) Calculate the net effect of the loss of proceeds and the ga on the hedge? 34 d. Is this problem an example of a perfect hedge or a cross hedge? Is it an example of speculation or hedging? Why
