Hi,

"An underwriter bank plan to assist the IBM Corp. in the public debt issue. The plan is to issue $500m of debt with a 5% annual coupon, $100 face value, and a 10-year maturity. The objective is to determine the bond rating, and the most appropriate bond issue price. In addition, the company is expecting a revision of its current rating. You will need to determine the current rating, potential rating, and the corresponding to these ratings yield curves. Proceed as indicated below:

1. Start by finding the current U.S. Treasury yield curve as of Monday, Oct. 28th (https://we.tl/t-tmHvqGwv6V). Determine the credit rating and its prospects for IBM. For this, you will need the current issuer long term rating in local currency (https://we.tl/t-kOlbzIM97S). The expectation is provided in the column called CreditWatch/Outlook. Assume that in case of any change in the rating is decided by S&P, it will either move it one level up or one level down. See the S&P Global Ratings Definitions (https://we.tl/t-tBLfX1sDmM) Table 3 for the possible candidate ratings for IBM.
2. The Excel file ( https://we.tl/t-PGq6mM0Z8P) contains (i) the US corporate yield curve data and (ii) the US default spread data up to the CCC rating. Recall that all of this data is sourced as of date indicated in the A1 cell, and that all these figures are expressed in APR (annual percentage rates) terms. Mind the maturities (indicated in the column named "Tenor").
3. With this informations (the Treasury yields, the rating of IMB with its outlook and possible candidates, the corporate yields and spreads) create new Excel file and construct the timeline of an IBM bond's cash flows.
4. To price the IBM's bond, you'll need to create the required spot rates (as of Oct. 28th) for the issue:
5. Use the yields for the corresponding ratings / maturities (current and expected)
6. Use the sum of the Z-spread and the treasury yield of the same maturity. Note
7. that the spread is measured in basis points (1/100th of a percentage point, i.e.
8. 1/100 of 1%)
9. The yield curves, spreads etc. that you have found do not cover every year that you
10. will need for the new bonds. Fill these in by linearly interpolating the given yields and spreads. For example, the four-year spot rate and spread will be the average of the three- and five-year rates. There are no 8 and 9 years maturities for the government bonds. To interpolate these, you will have to have to compute the difference between the 7-year and 10-year bond yields, then divide it by 3. Call this "delta". The 8-year government bond yield will be then the 7-year bond + "delta". The 9-year government bond will be 7-year one + 2 "delta".
11. Use the spot rates to calculate the present value of each cash flow provided by the bond, and obtain the bond price. Determine how many bonds must be sold to raise the required amount of money. Determine what will be the total amount payable to bondholders at the maturity.
12. Repeat the steps 3-7 considering the rating revision."

Thanks !