Excel Online Structured Activity: Convertible Bond Analysis Niendort Incorporated needs to raise $25 million to construct production facilities for a new type or USB memory device. The firm's straight nonconvertible debentures currently yield 9.5%. Its stock sells for $22.00 per share, has an expected constant growth rate of 5.5%, and has an expected dividend yield of 7%, for a total expected return on equity of 12.5% Investment bankers have tentatively proposed that therm raise the $25 million by issuing convertible debentures. These convertibles would have a $1,000 par value, carry a coupon rate of 8.5%, have a 20-year maturity, and be convertible into 33 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 s5 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 nile below. Open the spreadsheet and perform the required analysis to answer the questions below. X OND 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 - Dar what the hand in with the main * 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. A-Z at Year What is the bond's value in conversion when it is converted at this time? Do not round your intermediate calculatora. 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 pald.) Do not round your intermediate calculations. Round your answer to the nearest cent. $ b. What is the expected rate of return (1.0., berdere-tax component cost) on the proposed convertible issue? Do not round your intermediate calculations. Round your answer to two decimal places Reset Problem Convertible Bond Analysis UN 2 3 4 5 Amount of funds needed Annual yield on straight nonconvertible dobenturus, Id $25,000,000 1.00% $22.00 5.50% 7.00% 7.00% 6 Stock price, Po 7 Expected constant growth rate 8 Expected dividend yield 9 Expected retum on equity.. 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 hand value when conversion occurs $1.000 8.50% 20 20 5 $1075 $5 20.00% $1.200 Formulas NA