Start with the partial model in the file Ch24 P06 Build a Model.xls on the textbooks Web site. Use the information and data below.

F. Pierce Products Inc. is financing a new manufacturing facility with the issue in March of $20,000,000 of 20-year bonds with semiannual interest payments. It is now October, and if Pierce were to issue the bonds now, the yield would be 10% because of Pierces high risk. Pierces CFO is concerned that interest rates will climb even higher in coming months and is considering hedging the bond issue. The following data are available:

a. Create a hedge with the futures contract for Pierces planned March debt offering of $20 million using the March Treasury Bond futures contract. What is the implied yield on the bond underlying the futures contract?

b. Suppose that interest rates fall by 300 basis points. What are the dollar savings from issuing the debt at the new interest rate? What is the dollar change in value of the futures position? What is the total dollar value change of the hedged position?

c. Create a graph showing the effectiveness of the hedge if the change in interest rates, in basis points, is 300, 200, 100, 0, 100, 200, or 300. Show the dollar cost (or savings) from issuing the debt at the new interest rates, the dollar change in value of the futures position, and the total dollar value change.

Please include the formulas for the calculations.

Futures Prices: Treasury Bonds- $100,000; Pts. 32nds of 100%