please answer on microsoft excel. please answer questions a,b,c,d,e

f. Using one or more of the four bonds, put together a $200,000 immunized portiolin fer Grace. Because this portfolio will now be immunized, will Grace be able to treat it as a buy and hold portfolio-one she can put away and forget about? Explain. eadsheets All bonds are priced according to the present value of their future cash flow streams. The key components of bond valuation are par value, coupon interest rate, term to maturity, and market yield. It is market yield that drives bond prices. In the market for bonds, the appropriate yield at which the bond should sell is determined first, and then that yield is used to find the market value of the bond. The market yield can also be referred to as the required rate of return. It implies that this is the rate of return that a rational investor requires before he or she will invest. in a given fixed-income security. Create a spreadsheet to model and answer the following bond valuation questions. Questions: a. One of the bond issues outstanding by H\&W Corporation has an annual-pay coupon of 5.625% plus a par value of $1,000 at maturity. This bond has a remaining maturity of 23 years. The required rate of return on securities of similar-risk grade is 6.76%. What is the value of this corporate bond today? b. What is the current yield for the HEcw bond? c. In the case of the HecW bond issue from question a, if the coupon interest payment is compounded on a semiannual basis, what would be the value of this security today? d. How would the price of the H\&CW bond react to changing markes intetest rares? To find out, determine how the price of the issue reacts to changes in the bond's yicld-to-maturity. (YTM). Find the value of the security when the YTM is (1) 5.625%,(2)8.0%H, and (3) 4.5%. Label your findings as being a premium, par, or discount bond; comment on your findings. e. The Jay \&E Austin Company has a bond issue outstanding with the following characteristice par of $1,000, a semiannual-pay coupon of 6.5%, remaining maturity of 22 years, and a current price of $878.74. What is the bond's yield-to-maturity (YTM)