Please help thank you.

Spreadsheet Exercise: Problem 11.36 A bond issued 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%. Complete the steps below using cell references to given data or previous calculations. In some cases, simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. s Annual coupon interest rate Par value Periods to maturity Yield to maturity 5.625% $1,000 23 6.76% 11 12 13 To Do a. What is the value of this corporate bond today? b. What is the current yield for the H&W bond? c. In the case of the H&W bond from question a, if the bond makes coupon payments semiannually, what would be the value of this security today? d. How would the price of the H&W bond react to changing market interest rates? To find out, determine how the price of the issue reacts to changes in the bond's yield to maturity. Find the value of the security when the YTM is (1) 5.625%, (2) 8.0%, and (3) 4.5%. Label your findings as being a premium, par, or discount bond. e. The Jay & Austin Company has a bond issue outstanding with the following characteristics: 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 YTM? 15 Solution a. What is the value of this corporate bond today? 17 18 19 20 0 1 E1 2 22 3 24 25 Annual coupon payment Value of the bond b. What is the current yield for the H&W bond? Current yield c. In the case of the H&W bond from question a, if the bond makes coupon payments semiannually, what would be the value of this security today? 28 -9 30 31 01 32 Periods to maturity Semiannual yield to maturity Semiannual coupon payment Value of the bond 34 d. How would the price of the H&W bond react to changing market interest rates? To find out, determine how the price of the issue reacts to changes in the bond's yield to maturity. Find the value of the security when the YTM is (1) 5.625%. (2) 8.0%, and (3) 4.5%. Label your findings as being a premium, par, or discount bond. 5 11.36 + Ready 34 d. How would the price of the H&W bond react to changing market interest rates? To find out, determine how the price of the issue reacts to changes in the bond's yield to maturity. Find the value of the security when the YTM is (1) 5.625%, (2) 8.0%, and (3) 4.5%. Label your findings as being a premium, par, or discount bond. 35 36 37 Yield to maturity Value of the bond 5.625% The bond is valued at 38 39 40 41 8% Yield to maturity Value of the bond The bond is valued at 42 43 4.5% Yield to maturity Value of the bond The bond is valued at 44 45 e. The Jay & Austin Company has a bond issue outstanding with the following characteristics: 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 YTM? 46 47 47 48 49 50 51 52 32 53 a Points 1 1 61 62 Par value $1,000 Annual coupon interest rate 6.5% Years to maturity 22 Value of the bond S878.74 Periods to maturity Semiannual coupon payment 54 Semiannual yield to maturity 55 Annual yield to maturity 56 57 58 Requirements 59 1 In cell D21, by using cell references, calculate the annual coupon payment for the bond 60 2 In cell D22, by using cell references and the function PV, calculate the value of the bond. Note: The output of the expression or function you typed in this cell is expected as a positive number. 3 In cell D26, by using cell references, calculate the current yield of the bond. Find & Select Note: Refer to the values from Steps 1 and 2 in your calculations. 4 In cell D30, by using cell references, calculate the number of periods to maturity for the bond when coupon payments are made semiannually 5 In cell D31, by using cell references, calculate the semiannual yield to maturity for the bond. 6 In cell D32, by using cell references, calculate the semiannual coupon payment for the bond. 7 In cell D33, by using cell references and the function PV, calculate the value of the bond when coupon payments are made semiannually. Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the values from Steps 4, 5, and 6 in your calculations. 8 In cell D38, by using cell references and the function PV, calculate the value of the bond when YTM is 5.625%. 70 Note: The output of the expression or function you typed in this cell is expected as a positive number. 71 9 In cell E38, choose whether the bond is valued at premium, discount, or par when YTM is 5.625%. 72 10 In cell D41, by using cell references and the function PV, calculate the value of the bond when YTM is 8%. 73 Note: The output of the expression or function you typed in this cell is expected as a positive number. 74 11 In cell E41, choose whether the bond is valued at premium, discount, or par when YTM is 8% 75 12 In cell 044, by using cell references and the function PV, calculate the value of the bond when YTM is 4.5% 1 63 64 65 1 1 1 1 1 66 67 1 68 69 1 1 1 1 1 11.36 + Ready whether the bonds are valued at premium, discount, or par. Steps to Perform: Step Instructions Points Possible 1 1 Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Start Excel. Download and open the workbook named: Smart_Zutter_Problem_11.36_Start.xlsx In cell D21, by using cell references, calculate the annual coupon payment for the bond. 2 In cell D22, by using cell references and the function PV, calculate the value of the bond. 1 Note: The output of the expression or function you typed in this cell is expected as a positive number. 3 In cell D26, by using cell references, calculate the current yield of the bond. 1 Note: Refer to the values from Steps 1 and 2 in your calculations. 4 1 In cell D30, by using cell references, calculate the number of periods to maturity for the bond when coupon payments are made semiannually. 5 In cell D31, by using cell references, calculate the semiannual yield to maturity for the bond. 1 6 In cell D32, by using cell references, calculate the semiannual coupon payment for the bond. 1 7 1 In cell D33, by using cell references and the function PV, calculate the value of the bond when coupon payments are made semiannually. Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the values from Steps 4, 5, and 6 in your calculations. 8 1 In cell D38, by using cell references and the function PV, calculate the value of the bond when YTM is 5.625%. Note: The output of the expression or function you typed in this cell is expected as a positive number. 9 1 In cell E38, choose whether the bond is valued at premium, discount, or par when YTM is 5.625%. Focu Grader - Instructions Excel 2016 Project Step Instructions Points Possible 10 1 In cell D41, by using cell references and the function PV, calculate the value of the bond when YTM is 8%. Note: The output of the expression or function you typed in this cell is expected as a positive number. 11 In cell E41, choose whether the bond is valued at premium, discount, or par when YTM is 8%. 1 12 1 In cell D44, by using cell references and the function PV, calculate the value of the bond when YTM is 4.5% Note: The output of the expression or function you typed in this cell is expected as a positive number. 13 1 In cell E44, choose whether the bond is valued at premium, discount, or par when YTM is 4.5%. 14 1 In cell D52, by using cell references, calculate the number of periods to maturity for the bond in parte. 15 1 In cell D53, by using cell references, calculate the semiannual coupon payment for the bond in part e. 16 1 In cell D54, by using cell references and the function RATE, calculate the semiannual yield to maturity of the bond in part e. Notes: 1. Enter 0.1 for Guess argument of the function RATE. 2. Refer to the values from Steps 14 and 15 in your calculations. 17 In cell D55, by using cell references, calculate the annual yield to maturity for the band in part 1 e. 18 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed 0 Total Points 17 [FO