Question

1 Approved Answer

Posted on Oct 06, 2024

Use 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

Use 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 spot rates for Fords 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 Fords 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.

e.Use the spot rates to calculate the present value of each cash flow paid to the bondholders. Note here you have to use the one-year spot rate to discount the cash flow in 1 year, the two-year spot rate to discount the cash flow in 2 years etc.

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

g.Repeat steps 4) 6) 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.

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

image text in transcribed

2 1. Yield Curve 3 Date 4 05/20/2022 0.63 0.87 5 05/19/2022 0.65 0.91 6 05/18/2022 0.56 0.85 7 05/17/2022 0.61 0.85 8 05/16/2022 0.64 0.85 9 05/13/2022 0.67 0.79 10 05/12/2022 0.61 0.77 11 05/11/2022 0.59 0.77 12 05/10/2022 0.57 0.75 13 05/09/2022 0.51 0.73 14 05/06/2022 0.48 0.72 15 05/05/2022 0.49 0.71 16 05/04/2022 0.49 0.74 17 05/03/2022 0.48 0.77 18 05/02/2022 0.41 0.71 19 20 21 2. Current Yield Spreads for Various Bond Ratings 22 Spreads (in basis points) 23 24 Rating 1 yr 3 yr 25 Aaa/AAA 14 26 Aa1/AA+ 22 27 Aa2/AA 24 28 Aa3/AA- 25 29 A1/A+ 43 30 A2/A 46 31 A3/A- 50 32 Baa1/BBB+ 62 33 Baa2/BBB 65 34 Baa3/BBB- 72 35 Ba1/BB+ 185 36 Ba2/BB 195 37 Ba3/BB- 205 38 B1/B+ 265 39 B2/B 275 40 B3/B- 285 41 Caa/CCC 450 42 43 44 3. Ford Motor Co Credit Rating BB+ 1 Mo 2 Mo 2 yr 16 30 37 39 48 51 54 72 80 85 195 205 215 275 285 295 460 3 Mo 1.03 1.05 1.03 1.06 1.07 1.03 0.96 0.91 0.89 0.92 0.85 0.85 0.89 0.91 0.9 27 31 39 40 52 54 57 80 88 90 205 215 225 285 295 305 470 6 Mo 5 yr 1.51 1.52 1.56 1.57 1.54 1.47 1.44 1.43 1.44 1.43 1.41 1.37 1.44 1.45 1.49 40 48 54 58 65 67 72 92 97 102 215 225 235 315 325 335 495 1 Yr 7 yr 2.07 2.11 2.16 2.16 2.07 2.04 1.96 1.99 2.01 1.99 2.08 2.08 2.07 2.16 2.1 56 64 67 71 79 81 84 121 128 134 235 245 255 355 365 375 505 2 Yr 10 yr 2.6 2.63 2.68 2.71 2.58 2.61 2.56 2.66 2.62 2.61 2.72 2.71 2.66 2.78 2.73 68 77 80 81 93 95 98 141 151 159 255 265 275 395 405 415 515 3 Yr 30 yr 2.73 2.78 2.84 2.89 2.75 2.79 2.73 2.81 2.81 2.81 2.94 2.91 2.85 2.95 2.93 90 99 103 109 117 121 124 170 177 183 275 285 295 445 455 465 545 5 Yr 2.8 2.84 2.89 2.96 2.83 2.89 2.81 2.89 2.91 2.95 3.06 3.01 2.93 3.01 3.01 7 Yr 2.82 2.87 2.91 3 2.89 2.95 2.86 2.94 2.99 3.04 3.13 3.07 2.97 3.03 3.04 10 Yr 2.78 2.84 2.89 2.98 2.88 2.93 2.84 2.91 2.99 3.05 3.12 3.05 2.93 2.97 2.99 20 Yr 3.17 3.24 3.24 3.36 3.3 3.32 3.22 3.25 3.31 3.38 3.43 3.35 3.21 3.21 3.26 30 Yr 2.99 3.05 3.07 3.17 3.09 3.1 3 3.05 3.12 3.19 3.23 3.15 3.01 3.03 3.07 2 1. Yield Curve 3 Date 4 05/20/2022 0.63 0.87 5 05/19/2022 0.65 0.91 6 05/18/2022 0.56 0.85 7 05/17/2022 0.61 0.85 8 05/16/2022 0.64 0.85 9 05/13/2022 0.67 0.79 10 05/12/2022 0.61 0.77 11 05/11/2022 0.59 0.77 12 05/10/2022 0.57 0.75 13 05/09/2022 0.51 0.73 14 05/06/2022 0.48 0.72 15 05/05/2022 0.49 0.71 16 05/04/2022 0.49 0.74 17 05/03/2022 0.48 0.77 18 05/02/2022 0.41 0.71 19 20 21 2. Current Yield Spreads for Various Bond Ratings 22 Spreads (in basis points) 23 24 Rating 1 yr 3 yr 25 Aaa/AAA 14 26 Aa1/AA+ 22 27 Aa2/AA 24 28 Aa3/AA- 25 29 A1/A+ 43 30 A2/A 46 31 A3/A- 50 32 Baa1/BBB+ 62 33 Baa2/BBB 65 34 Baa3/BBB- 72 35 Ba1/BB+ 185 36 Ba2/BB 195 37 Ba3/BB- 205 38 B1/B+ 265 39 B2/B 275 40 B3/B- 285 41 Caa/CCC 450 42 43 44 3. Ford Motor Co Credit Rating BB+ 1 Mo 2 Mo 2 yr 16 30 37 39 48 51 54 72 80 85 195 205 215 275 285 295 460 3 Mo 1.03 1.05 1.03 1.06 1.07 1.03 0.96 0.91 0.89 0.92 0.85 0.85 0.89 0.91 0.9 27 31 39 40 52 54 57 80 88 90 205 215 225 285 295 305 470 6 Mo 5 yr 1.51 1.52 1.56 1.57 1.54 1.47 1.44 1.43 1.44 1.43 1.41 1.37 1.44 1.45 1.49 40 48 54 58 65 67 72 92 97 102 215 225 235 315 325 335 495 1 Yr 7 yr 2.07 2.11 2.16 2.16 2.07 2.04 1.96 1.99 2.01 1.99 2.08 2.08 2.07 2.16 2.1 56 64 67 71 79 81 84 121 128 134 235 245 255 355 365 375 505 2 Yr 10 yr 2.6 2.63 2.68 2.71 2.58 2.61 2.56 2.66 2.62 2.61 2.72 2.71 2.66 2.78 2.73 68 77 80 81 93 95 98 141 151 159 255 265 275 395 405 415 515 3 Yr 30 yr 2.73 2.78 2.84 2.89 2.75 2.79 2.73 2.81 2.81 2.81 2.94 2.91 2.85 2.95 2.93 90 99 103 109 117 121 124 170 177 183 275 285 295 445 455 465 545 5 Yr 2.8 2.84 2.89 2.96 2.83 2.89 2.81 2.89 2.91 2.95 3.06 3.01 2.93 3.01 3.01 7 Yr 2.82 2.87 2.91 3 2.89 2.95 2.86 2.94 2.99 3.04 3.13 3.07 2.97 3.03 3.04 10 Yr 2.78 2.84 2.89 2.98 2.88 2.93 2.84 2.91 2.99 3.05 3.12 3.05 2.93 2.97 2.99 20 Yr 3.17 3.24 3.24 3.36 3.3 3.32 3.22 3.25 3.31 3.38 3.43 3.35 3.21 3.21 3.26 30 Yr 2.99 3.05 3.07 3.17 3.09 3.1 3 3.05 3.12 3.19 3.23 3.15 3.01 3.03 3.07