Valuing bonds exercise

PLEASE D O and show all work in excel!

You are an intern with Ford Motor Company in its corporate finance division. The firm is planning to issue $50 million of 12% annual coupon bonds with a 10-year maturity. The firm anticipates an increase in its bond rating. Your boss wants you to determine the gain in the proceeds of the new issue if the issue is rated above the firm's current bond rating. To prepare this information, you will have to determine Ford's current debt rating and the yield curve for their particular rating.

1.Begin by finding the current U.S. Treasury yield curve. at the treasury web sit (www.treas.gov), search using term "yield curve" and select "US Treasury-Daily Treasury Yield Curve" Beware: There will be two links with the same title, look at the description below the link and select the one that does NOT say "Real Yield..." You want the nominal rates, Copy the table into Excel.

2.Find the current yield spreads for the various bond ratings. Unfortunately, the current spreads are available only for a fee, so we will use old ones. Go to BondsOnline (www.bondsonline.com) and clicked "Today's Market." Next, click "US Corporate Bond Spreads." Copy this table to the same Excel file as the Treasury yields.

Please find the Attached photo

3.Find the current bond rating for Ford Motor Co. Go to Standard & Poor's Web site. (www.standardandpoors.com). Select your country. Look for "Find a Rating" box under "Ratings" and enter Ford Motor Co. and select Ford Motor Co. from the list it returns. At this point, you will have to register (it's free). Use the issuer credit rating for "local long term".

4.Return to Excel and create a timeline with the cash flows and discount rates you will need to value the new bond issue.

a.To create the required rate for Ford's issue, add the appropriate spread to the Treasury yield of the same maturity.

b.The yield curve and spread rates you have found do not cover every year that you will need for the new bonds. Specifically, you do not have yields or spreads for four-, six-, eight-, and nine-year maturities. 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. The six-year rate and spread will be the average of the five- and seven-year rates. For years eight and nine you will have to spread the difference between years seven and ten across the two years.

c.To compute the spot rates for Ford's current debt rating, add the yield spread to the Treasury rate for each maturity. However, note that the spread is in basis points, which are 1/100th of a percentage point.

d.Compute the cash flows that would be paid to bondholders each year and add them to the timeline.

5.Use the spot rates to calculate the present value of each cash flow paid to the bondholders.

6.Compute the issue price of the bond and its initial yield to maturity.

7.Repeat steps 4-6 based on the assumption that Ford is able to raise its bond rating by one full-step (for example, BBB to A). Compute the new yield based on the higher rating and the new bond price that would result.

8.Compute the additional cash proceeds that could be raised from the issue if the rating were improved..