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. Annual coupon interest rate Par value Periods maturity Yield to maturity 5.625% $1,000 23 6.76% 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? Solution a. What is the value of this corporate bond today? Annual coupon payment Value of the bond S56.25 $869.40 b. What is the current yield for the H&W bond? Current yield 6.47% 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? Periods to maturity Semiannual yield to maturity Semiannual coupon payment Value of the bond 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.625% The bond is valued at Yield to maturity Value of the bond 8% Yield to maturity Value of the bond The bond is valued at 4.5% Yield to maturity Value of the bond The bond is valued at 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? $1,000 6.5% 22 $878.74 Par value Annual coupon interest rate Years to maturity Value of the bond Periods to maturity Semiannual coupon payment Semiannual yield to maturity Annual yield to maturity 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. Annual coupon interest rate Par value Periods maturity Yield to maturity 5.625% $1,000 23 6.76% 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? Solution a. What is the value of this corporate bond today? Annual coupon payment Value of the bond S56.25 $869.40 b. What is the current yield for the H&W bond? Current yield 6.47% 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? Periods to maturity Semiannual yield to maturity Semiannual coupon payment Value of the bond 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.625% The bond is valued at Yield to maturity Value of the bond 8% Yield to maturity Value of the bond The bond is valued at 4.5% Yield to maturity Value of the bond The bond is valued at 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? $1,000 6.5% 22 $878.74 Par value Annual coupon interest rate Years to maturity Value of the bond Periods to maturity Semiannual coupon payment Semiannual yield to maturity Annual yield to maturity
