Excel Online Structured Activity: Convertible Bond Analysis Niendorf Incorporated needs to raise $25 million to construct production facilities for a new type of USB memory device. The firm's straight nonconvertible debentures currently yield 9%. Its stock sells for $24.00 per share, has an expected constant growth rate of 6.5%, and has an expected dividend yield of 7%, for a total expected return on equity of 13.5%. Investment bankers have tentatively proposed that the firm raise the $25 million by issuing convertible debentures. These convertibles would have a $1,000 par value, carry a coupon rate of 8.0%, have a 20-year maturity, and be convertible into 32 shares of stock. Coupon payments would be made annually. The bonds would be noncallable for 5 years, after which they would be callable at a price of $1,075; this call price would decline by $5 per year in Year 6 and each year thereafter. For simplicity, assume that the bonds may be called or converted only at the end of a year, immediately after the coupon and dividend payments. Also assume that management would call eligible bonds if the conversion value exceeded 20% of par value (not 20% of call price). The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. 1 Open spreadsheet a. At what year do you expect the bonds will be forced into conversion with a call? Do not round your intermediate calculations. Round your answer up to the nearest whole number. at Year What is the bond's value in conversion when it is converted at this time? Do not round your intermediate calculations. Round your answer to the nearest cent. a. At what year do you expect the bonds will be forced into conversion with a call? Do not round your intermediate calculations. Round your answer up to the nearest whole number. at Year What is the bond's value in conversion when it is converted at this time? Do not round your intermediate calculations. Round your answer to the nearest cent. $ What is the cash flow to the bondholder when it is converted at this time (Hint: The cash flow includes the conversion value and the coupon payment, because the conversion occurs immediately after the coupon is paid.) Do not round your intermediate calculations. Round your answer to the nearest cent. $ b. What is the expected rate of return (i.e., before-tax component cost) on the proposed convertible issue? Do not round your intermediate calculations. Round your answer to two decimal places. % File Home Insert Formulas Data Review View Help Tell me what you want to do Editing Pc Arial 10 B A v ab Number .00 0 > 27 v B22 D E F G H I I 3 / B B 6.50% 7.00% 13.50% $1,000 8.00% 20 32 5 $1,075 $5 20.00% $1,200 A Expected constant growth rate 8 Expected dividend yield 9 Expected return on equity, rg 10 11 Par value on convertible debentures 12 Annual coupon rate on convertible debentures 13 Length of maturity (in years) 14 Conversion ratio (CR) 15 Noncallable period (in years) 16 Call price 17 Decline in call price per year, starting Year 6 18 Call occurs when conversion exceeds % of par value 19 Call occurs when conversion value exceeds 20 21 Determine the year bonds forced into conversion: 22 Year bonds forced into conversion 23 24 Determine the bond's value when conversion occurs: 25 Conversion value of bond 26 27 Determine cash flow to bondholder at time of conversion: 28 Cash flow to bondholder 29 30 Determine before-tax expected return on convertible issue: 31 Before-tax expected rate of return, l'Convertible 32 33 34 Formulas #N/A #N/A #N/A #N/A Excel Online Structured Activity: Convertible Bond Analysis Niendorf Incorporated needs to raise $25 million to construct production facilities for a new type of USB memory device. The firm's straight nonconvertible debentures currently yield 9%. Its stock sells for $24.00 per share, has an expected constant growth rate of 6.5%, and has an expected dividend yield of 7%, for a total expected return on equity of 13.5%. Investment bankers have tentatively proposed that the firm raise the $25 million by issuing convertible debentures. These convertibles would have a $1,000 par value, carry a coupon rate of 8.0%, have a 20-year maturity, and be convertible into 32 shares of stock. Coupon payments would be made annually. The bonds would be noncallable for 5 years, after which they would be callable at a price of $1,075; this call price would decline by $5 per year in Year 6 and each year thereafter. For simplicity, assume that the bonds may be called or converted only at the end of a year, immediately after the coupon and dividend payments. Also assume that management would call eligible bonds if the conversion value exceeded 20% of par value (not 20% of call price). The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. 1 Open spreadsheet a. At what year do you expect the bonds will be forced into conversion with a call? Do not round your intermediate calculations. Round your answer up to the nearest whole number. at Year What is the bond's value in conversion when it is converted at this time? Do not round your intermediate calculations. Round your answer to the nearest cent. a. At what year do you expect the bonds will be forced into conversion with a call? Do not round your intermediate calculations. Round your answer up to the nearest whole number. at Year What is the bond's value in conversion when it is converted at this time? Do not round your intermediate calculations. Round your answer to the nearest cent. $ What is the cash flow to the bondholder when it is converted at this time (Hint: The cash flow includes the conversion value and the coupon payment, because the conversion occurs immediately after the coupon is paid.) Do not round your intermediate calculations. Round your answer to the nearest cent. $ b. What is the expected rate of return (i.e., before-tax component cost) on the proposed convertible issue? Do not round your intermediate calculations. Round your answer to two decimal places. % File Home Insert Formulas Data Review View Help Tell me what you want to do Editing Pc Arial 10 B A v ab Number .00 0 > 27 v B22 D E F G H I I 3 / B B 6.50% 7.00% 13.50% $1,000 8.00% 20 32 5 $1,075 $5 20.00% $1,200 A Expected constant growth rate 8 Expected dividend yield 9 Expected return on equity, rg 10 11 Par value on convertible debentures 12 Annual coupon rate on convertible debentures 13 Length of maturity (in years) 14 Conversion ratio (CR) 15 Noncallable period (in years) 16 Call price 17 Decline in call price per year, starting Year 6 18 Call occurs when conversion exceeds % of par value 19 Call occurs when conversion value exceeds 20 21 Determine the year bonds forced into conversion: 22 Year bonds forced into conversion 23 24 Determine the bond's value when conversion occurs: 25 Conversion value of bond 26 27 Determine cash flow to bondholder at time of conversion: 28 Cash flow to bondholder 29 30 Determine before-tax expected return on convertible issue: 31 Before-tax expected rate of return, l'Convertible 32 33 34 Formulas #N/A #N/A #N/A #N/A