Help Ch20 POB Build a Model Excel File Edit View Insert Format Tools Data Window AutoSaver C SU- Home Insert Draw Page Layout Formulas Data Review View Arial 10 AA E EE Peste 3 BTU DA I H 029 x fix C D E Tell me what you want to do 8 General $ % ) : Delete Format 168 16% [4] Fri 6:16 PM Q E Qw Search Sheet Share Comments In dior sauvage EZY 4 2- Sort & Ideas Sensitivity X V Filter n Google Play M N O Prep your course with ion free flashcards Formatting as Table Styles F G H I J K L IV VUITTCI VIVIR 66 67 6. What is the expected rate of return (l.e., before-tax component cost) on the proposed convertible issue? on App Store 69 Using the RATE function: 70 N = 71 PMT = $60.00 72 PV = $1.000.00 73 FV = $ 1,056.51 74 75 Rate = 6.794% In Google Play 76 77 As a check, using the IRR function and the cash flows in column F: 78 79 Expected return to bondholders 6.794% 80 81 c. Assume that the convertible bondholders require a 9% rate of return. If the coupon rate remains unchanged, then 82 what conversion ratio will give a bond price of $1000? 83 84 Expected return required by convertible bondholders = 9% 85 86 Hint: Use Goal seek to set the difference between the convertible bondholders' current return and the target return to 87 zero by changing the input cell for the conversion ratio. 88 89 Current difference between bondholders' current expected return and target return 90 (multiplied by 1000) = 91 92 Conversion ratio (given original convertible coupon rate) that produces the required 93 yield (Note: after using Goal Seek, cut and paste the conversion ratio into the yellow 94 cell): Build a Model + Ready + 132% Help Ch20 POB Build a Model Excel File Edit View Insert Format Tools Data Window AutoSaver C SU- Home Insert Draw Page Layout Formulas Data Review View Arial 10 AA E EE Peste 3 BTU DA I H 029 x fix C D E Tell me what you want to do 8 General $ % ) : Delete Format 168 16% [4] Fri 6:16 PM Q E Qw Search Sheet Share Comments In dior sauvage EZY 4 2- Sort & Ideas Sensitivity X V Filter n Google Play M N O Prep your course with ion free flashcards Formatting as Table Styles F G H I J K L IV VUITTCI VIVIR 66 67 6. What is the expected rate of return (l.e., before-tax component cost) on the proposed convertible issue? on App Store 69 Using the RATE function: 70 N = 71 PMT = $60.00 72 PV = $1.000.00 73 FV = $ 1,056.51 74 75 Rate = 6.794% In Google Play 76 77 As a check, using the IRR function and the cash flows in column F: 78 79 Expected return to bondholders 6.794% 80 81 c. Assume that the convertible bondholders require a 9% rate of return. If the coupon rate remains unchanged, then 82 what conversion ratio will give a bond price of $1000? 83 84 Expected return required by convertible bondholders = 9% 85 86 Hint: Use Goal seek to set the difference between the convertible bondholders' current return and the target return to 87 zero by changing the input cell for the conversion ratio. 88 89 Current difference between bondholders' current expected return and target return 90 (multiplied by 1000) = 91 92 Conversion ratio (given original convertible coupon rate) that produces the required 93 yield (Note: after using Goal Seek, cut and paste the conversion ratio into the yellow 94 cell): Build a Model + Ready + 132%