please solve :

- please use the same format when solving the problem.

- show on excel

- show work

- show formulas

-explain

- can you brefiy answer question 1-7 please

Thank you!

Your grandfather is retired and living on his Social Security benefits and the interest he gets from savings. However, the interest income he receives has dwindled to only 2 percent a year on his $200,000 in savings as interest rates in the economy have dropped. You have been thinking about recommending that he purchase some corporate bonds with at least part of his savings as a way of increasing his interest income. Specifically, you have identified three corporate bond issues, all of which pay interest semi-annually, for your grandfather to consider. The first is an issue from the Young Corporation that pays semi-annual interest based on a 4.38% coupon rate and has 9 years before it matures. The second bond was issued by Thomas Resorts, and it pays semi-annual interest based on a 5.75% coupon rate and has 18 years until it matures. The final bond issue was sold by Bally Hi, Inc., and it pays a semi-annual coupon interest payment based on a coupon rate of 7.98% and has only 4 years until it matures. All three bond issues have a $1,000 par value. Each of these bonds have a different rating, which will impact your assessment for investment purposes. In addition to the ratings you have highlighted the current price of each of the bonds, as follows: Coupon rate Years to maturity Current price Rating Young Corp Thomas Resorts Bally Hi, Inc. 4.38% 5.75% 6.90% 9 18 1,042.00 $ 973.00 $ 1,145.00 AA B BBB $ After looking at the bonds' default risks and credit ratings, you have completed your research regarding bond yields based on rating and years to maturity. Specifically, you have developed the following table to use to begin your evaluation of the different bond offerings. - Bond Yields - By Rating / Maturity Rating 5-year 7-year 10-year 15-year AAA AA A BBB BB B 1.30% 1.76% 2.15% 3.35% 3.76% 4.12% 1.65% 2.11% 2.50% 3.70% 4.11% 4.47% 2.30% 2.76% 3.15% 4.35% 4.76% 5.12% 3.35% 3.81% 4.20% 5.40% 5.81% 6.17% In order to complete your analysis and provide a recommendation to your grandfather, you will need to perform the following analysis / assessment steps: 1. For each one of the bonds, given the price, coupon, maturity, etc., calculate the yield to maturity. 2. Based on the bond ratings and maturity (table above), identify the appropriate comparable market yield that would apply to each one of the bonds. 3. Based on the assigned comparable market yield for each bond, what value would you calculate for each bond offering? 4. Which of the three bonds would you recommend to your grandfather for investment purposes? Why? 5. Given that market yields change constantly based on market and economic conditions, evaluate the impact on the bond values if the comparable market yields were to increase by 1.5%. 6. Now, evaluate the impact on the bond values if the comparable market yields were to decrease by 1.5% 7. Based on your analysis for 5. And 6. above, which of the three bond offerings is more sensitive to rate adjustments? Why? Your grandfather is retired and living on his Social Security benefits and the interest he gets from savings. However, the interest income he receives has dwindled to only 2 percent a year on his $200,000 in savings as interest rates in the economy have dropped. You have been thinking about recommending that he purchase some corporate bonds with at least part of his savings as a way of increasing his interest income. Specifically, you have identified three corporate bond issues, all of which pay interest semi-annually, for your grandfather to consider. The first is an issue from the Young Corporation that pays semi-annual interest based on a 4.38% coupon rate and has 9 years before it matures. The second bond was issued by Thomas Resorts, and it pays semi-annual interest based on a 5.75% coupon rate and has 18 years until it matures. The final bond issue was sold by Bally Hi, Inc., and it pays a semi-annual coupon interest payment based on a coupon rate of 7.98% and has only 4 years until it matures. All three bond issues have a $1,000 par value. Each of these bonds have a different rating, which will impact your assessment for investment purposes. In addition to the ratings you have highlighted the current price of each of the bonds, as follows: Coupon rate Years to maturity Current price Rating Young Corp Thomas Resorts Bally Hi, Inc. 4.38% 5.75% 6.90% 9 18 1,042.00 $ 973.00 $ 1,145.00 AA B BBB $ After looking at the bonds' default risks and credit ratings, you have completed your research regarding bond yields based on rating and years to maturity. Specifically, you have developed the following table to use to begin your evaluation of the different bond offerings. - Bond Yields - By Rating / Maturity Rating 5-year 7-year 10-year 15-year AAA AA A BBB BB B 1.30% 1.76% 2.15% 3.35% 3.76% 4.12% 1.65% 2.11% 2.50% 3.70% 4.11% 4.47% 2.30% 2.76% 3.15% 4.35% 4.76% 5.12% 3.35% 3.81% 4.20% 5.40% 5.81% 6.17% In order to complete your analysis and provide a recommendation to your grandfather, you will need to perform the following analysis / assessment steps: 1. For each one of the bonds, given the price, coupon, maturity, etc., calculate the yield to maturity. 2. Based on the bond ratings and maturity (table above), identify the appropriate comparable market yield that would apply to each one of the bonds. 3. Based on the assigned comparable market yield for each bond, what value would you calculate for each bond offering? 4. Which of the three bonds would you recommend to your grandfather for investment purposes? Why? 5. Given that market yields change constantly based on market and economic conditions, evaluate the impact on the bond values if the comparable market yields were to increase by 1.5%. 6. Now, evaluate the impact on the bond values if the comparable market yields were to decrease by 1.5% 7. Based on your analysis for 5. And 6. above, which of the three bond offerings is more sensitive to rate adjustments? Why