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, a simple cell reference is all you need to copypaste a formula across a row or down a column, an absolute cell reference or a wired 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 mto a cell or function. Instead, make a reference to the cell to 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 pour formulas, usually the Given Data section, 5.62596 $1,000 Annual coupon interest rate Par value Periods to maturity Yield to maturity 6.7696 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 questions, 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 bonds yield to maturity. Find the value of the security when the YTM is (1) 5.625 (2) 8.0%, and (3) 45%. 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 S1,000, a semiannual-pay coupon of 6.5%, remaining maturity of 22 years, and a current price of $87874. What is the bond's YTM? a 5 Solution a. What is the value of this corporate bond today? 1 2 Annual coupon payment Value of the bond b. What is the current vield for the H&W bond? Current yield 37 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? LAU 7 20 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? 20 11 32 3 24 Periods to maturity Seminmal yield to maturity Semiannual coupon payment Value of the bond a. 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. Yield to maturity 5.62596 The bond is valued at Value of the bond 5 56 7 38 39 10 11 Z 8% Yield to maturity Value of the bond The bond is valued at 4.596 The bond is valued at Yield to maturity Value of the bond -H 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%,remanmg maturity of 22 years, and a current price of $878.74 What is the bond's YTM? 46 e. The Jay & Austin Company has a bond issue outstanding with the following characteristics par of S1,000, a semiannual pay coupon of 6.5% remaining maturity of 22 years, and a current price of $878.74. What is the band's YTM? 7 Par value Annual coupon interest rate Years to maturity Value of the bond Periods to maturity Semiantaual coupon payment Semiannual yield to maturity Annual yield to maturity $1,000 6.598 22 5878.74 1 2 + Points 1 1 7 8 Requirements 5 1 In cell D21, by using cell references, calculate the annual coupon payment for the bond 2 In cell D22, by using cel references and the function P, 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 26, by using cell references, calculate the current vield of the bond 3 Note Refer to the values from Steps 1 and 2 in our calculations 4 4 in cell D30, by using cell references, calculate the number of periods to manurity for the band when coupon payments are made semiannually 5 In cell D31, by using cell references, calculate the semiannual yield to maurity for the bond 6. In cell 12 hu tine cell references calculate the semiamo con nument for the hond 1 5 59 70 1 1 22 23 1 1 $ 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% Note: The output of the expression or fimction you typed in this cell is expected as a positive number 9 In cell E38, choose whether the bond is valued at premium, discount, or par when YTM is 5.62546 10 In cell 041, by using cell references and the function PV, calculate the value of the bond when YTM is 896 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% 12 In cell 044 by using cell references and the function PV, calculate the vahie of the bond when YTM i3 4 5% Note. The output of the expression or function you typed in this cell is expected as a positive number 13 In cell E44, choose whether the bond is valued at premium discount, or par when YTM 45% 14 In cell D52, by using cell references, calculate the mamber of periods to maturity for the bond in parte. 15 In cell D53, by using cell references calculate the semiannual coupon payment for the bond in parte, 16 In cell D54, by using cell references and the function RATE, calculate the semiannual yield to maturity of the bond in parte Notes: 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 bond in parte 18 Save the workbook Close the workbook and then exit Excel Submit the workbook as directed 1 7 18 1 1 1 0 1 11 54 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, a simple cell reference is all you need to copypaste a formula across a row or down a column, an absolute cell reference or a wired 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 mto a cell or function. Instead, make a reference to the cell to 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 pour formulas, usually the Given Data section, 5.62596 $1,000 Annual coupon interest rate Par value Periods to maturity Yield to maturity 6.7696 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 questions, 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 bonds yield to maturity. Find the value of the security when the YTM is (1) 5.625 (2) 8.0%, and (3) 45%. 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 S1,000, a semiannual-pay coupon of 6.5%, remaining maturity of 22 years, and a current price of $87874. What is the bond's YTM? a 5 Solution a. What is the value of this corporate bond today? 1 2 Annual coupon payment Value of the bond b. What is the current vield for the H&W bond? Current yield 37 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? LAU 7 20 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? 20 11 32 3 24 Periods to maturity Seminmal yield to maturity Semiannual coupon payment Value of the bond a. 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. Yield to maturity 5.62596 The bond is valued at Value of the bond 5 56 7 38 39 10 11 Z 8% Yield to maturity Value of the bond The bond is valued at 4.596 The bond is valued at Yield to maturity Value of the bond -H 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%,remanmg maturity of 22 years, and a current price of $878.74 What is the bond's YTM? 46 e. The Jay & Austin Company has a bond issue outstanding with the following characteristics par of S1,000, a semiannual pay coupon of 6.5% remaining maturity of 22 years, and a current price of $878.74. What is the band's YTM? 7 Par value Annual coupon interest rate Years to maturity Value of the bond Periods to maturity Semiantaual coupon payment Semiannual yield to maturity Annual yield to maturity $1,000 6.598 22 5878.74 1 2 + Points 1 1 7 8 Requirements 5 1 In cell D21, by using cell references, calculate the annual coupon payment for the bond 2 In cell D22, by using cel references and the function P, 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 26, by using cell references, calculate the current vield of the bond 3 Note Refer to the values from Steps 1 and 2 in our calculations 4 4 in cell D30, by using cell references, calculate the number of periods to manurity for the band when coupon payments are made semiannually 5 In cell D31, by using cell references, calculate the semiannual yield to maurity for the bond 6. In cell 12 hu tine cell references calculate the semiamo con nument for the hond 1 5 59 70 1 1 22 23 1 1 $ 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% Note: The output of the expression or fimction you typed in this cell is expected as a positive number 9 In cell E38, choose whether the bond is valued at premium, discount, or par when YTM is 5.62546 10 In cell 041, by using cell references and the function PV, calculate the value of the bond when YTM is 896 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% 12 In cell 044 by using cell references and the function PV, calculate the vahie of the bond when YTM i3 4 5% Note. The output of the expression or function you typed in this cell is expected as a positive number 13 In cell E44, choose whether the bond is valued at premium discount, or par when YTM 45% 14 In cell D52, by using cell references, calculate the mamber of periods to maturity for the bond in parte. 15 In cell D53, by using cell references calculate the semiannual coupon payment for the bond in parte, 16 In cell D54, by using cell references and the function RATE, calculate the semiannual yield to maturity of the bond in parte Notes: 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 bond in parte 18 Save the workbook Close the workbook and then exit Excel Submit the workbook as directed 1 7 18 1 1 1 0 1 11 54
