please solve :

- please use the same format when solving the problem.

- show on excel

- show work

- show formulas

-explain

all images are from the same problem

Thank you!

7. Based on your analysis for 5. And 6. above, which of the three bond offerings is more sensitive to rate adjustments? Why? General Guidance: You are to complete your analysis of the bonds, using the questions as your guide, in an Excel file. You will then complete a case summary document, in Word, in which you will discuss the results of your analysis. Your written document will include data from the Excel analysis as needed. Your written portion should follow the format concepts from the case analysis example provided (title page, headings, etc.). You should focus on the information that leads to decisions and then be able to defend those decisions.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 1.30% 1.65% 2.30% 3.35% AA 1.76% 2. 11% 2.76% 3.81% A 2.15% 2.50% 3.15% 4.20% BBB 3.35% 3.70% 4.35% 5.40% BB 3.76% 4. 11% 4.76% 5.81% B 4. 12% 4.47% 5.12% 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%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: Young Corp Thomas Resorts Bally Hi, Inc. Coupon rate 4.38% 5.75% 6.90% Years to maturity 9 18 4 Current price $ 1,042.00 9 973.00 $ 1, 145.00 Rating 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