Valuing bonds exercise, please see attached for fulldirections for assignment. Need this back by Sunday at 7pm. Please see the below for initial directions.

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 firms current bond rating. To prepare this information, you will have to determine Fords (ticker symbol F) current debt rating and the yield curve for their particular rating.

PLEASE DO and show all work in excel! 1. Begin by finding the current U.S. Treasury yield curve. I will give you the Treasury data; so you do not need to find it. 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 from March 28, 2014. I have gone to BondsOnline (www.bondsonline.com) and clicked \"Today's Market.\" Next, I clicked \"Corporate Bond Spreads.\" I have downloaded this table to Excel and copied it both below and to the excel spread sheet that I have given you. (We will work this problem with the spreads as of 3/28/14.) 3. Find the current bond rating for Ford. Go to Standard & Poor's Web site (www.standardandpoors.com). Select \"Find a Rating\" from the list at the left of the page, then select \"Credit Ratings Search.\" At this point, you will have to register (it's free). Next, you will be able to search by Organization Nameenter Ford Motor Company (ticker symbol F). Use the credit rating for the organization, not the specific issue ratings. This you will need to do. When I looked, there were ST and LT (use LT in local currency). (The rating that I found was for a LT bond issued September 6, 2013.) If you cannot find the rating, send your professor an email so that she/he can give it to you. 4. Return to Excel and create a timeline with the 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 (i.e., ten years). However, note that the spread is in basis points, which are 1/100th of a percentage point. (So you must multiply each of the spread numbers by .01 to add them to the Treasury rates which are in row 22 of the excel spread sheet (and the last line of the table in this exercise). 5. Compute the issue price of the bond, assuming that the bond is issued with semi-annual coupons (using as its initial yield to maturity the rate that you calculated for the ten-year corporate bond). You will be calculating the cash proceeds that could be raised from the issue. 6. Repeat steps 4 and 5 based on the assumption that Ford is able to raise its bond rating by one level. Compute the new yield based on the higher rating and the new bond price that would result. 7. Compute the additional cash proceeds that could be raised from the issue if the rating were improved. 8. In class we will look at valuing the bond if it were issued with different coupon rates and different maturities (using the data from the excel table). We will also calculate yield to maturity, yield to call and coupon payment in class. We also will look at the current Treasury yield curve and obtain what would be the rate on a ten-year Ford bond, using the spreads of March 28, 2014, but the yields as of Friday, October 9 (the Friday close before our class). Go to http://online.wsj.com/mdc/public/page/mdc_bonds.html over the weekend to obtain the yield on the ten-year Treasury. Please bring this excel spread sheet to class and have your computer open to excel during our synchronous session. Reuters Corporate Spreads for Industrials 3/28/2014 1 Rating 1 yr Aaa/AAA 5 Aa1/AA+ 10 Aa2/AA 14 Aa3/AA19 A1/A+ 23 A2/A 24 A3/A32 Baa1/BBB+ 38 Baa2/BBB 47 Baa3/BBB83 Ba1/BB+ 157 Ba2/BB 231 Ba3/BB305 B1/B+ 378 B2/B 452 B3/B526 Caa/CCC+ 600 US Treasury Yield 0.13 2 2 yr 8 18 29 34 39 39 49 61 75 108 182 256 330 404 478 552 626 0.45 3 4 3 yr 12 25 38 43 47 49 59 75 89 122 198 274 350 426 502 578 653 0.93 5 6 5 yr 18 34 50 54 58 61 72 92 107 140 217 295 372 450 527 604 682 1.74 7 8 7 yr 28 42 57 61 65 69 80 103 119 152 232 312 392 472 552 632 712 2.31 9 10 10 yr 42 54 65 69 72 77 89 115 132 165 248 330 413 495 578 660 743 2.74 30 30 yr 65 77 89 92 95 103 117 151 170 204 286 367 449 530 612 693 775 3.55