a$.$ For each year, calculate: $(1)$ the anticipated stock price; $(2)$ the anticipated conversion value; $(3)$ the anticipated straight$-$bond price; and $(4)$ the cash flow to the investor assuming conversion occurs. At what year do you expect the bonds will be forced into conversion with a call? What is the bond$$s value in conversion when it is converted at this time? 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 is immediately after the coupon is paid.$)$

Will call the issue in the first year that the conversion value exceeds

Year "Anticipated stock price at year end

$(1)"$ "Conversion Value

$(2)"$ Convert? $($Yes$,$ no$,$ or already$)$ "Straight debt value of convertible

$(3)"$ "Cash flow to bondholder if converted

$(4)"$

$022\mathrm{.}00704\mathrm{.}00$ $$1,000\mathrm{.}00-$$$1,000\mathrm{.}00$

$123\mathrm{.}54753\mathrm{.}28$ No $$894\mathrm{.}06$ $$60\mathrm{.}00$

$225\mathrm{.}19806\mathrm{.}01$ No $$896\mathrm{.}64$ $$60\mathrm{.}00$

$326\mathrm{.}95862\mathrm{.}43$ No $$899\mathrm{.}41$ $$60\mathrm{.}00$

$428\mathrm{.}84922\mathrm{.}80$ No $$902\mathrm{.}37$ $$60\mathrm{.}00$

$530\mathrm{.}86987\mathrm{.}40$ No $$905\mathrm{.}53$ $$60\mathrm{.}00$

$633\mathrm{.}021,056\mathrm{.}51$ No $$908\mathrm{.}92$ $$60\mathrm{.}00$

$735\mathrm{.}331,130\mathrm{.}47$ No $$912\mathrm{.}55$ $$60\mathrm{.}00$

$837\mathrm{.}801,209\mathrm{.}60$ No $$916\mathrm{.}42$ $$60\mathrm{.}00$

$940\mathrm{.}451,294\mathrm{.}28$ Yes $$920\mathrm{.}57$ $$1,354\mathrm{.}28$

$1043\mathrm{.}281,384\mathrm{.}87$ Done $$925\mathrm{.}01$ $$0\mathrm{.}00$

$1146\mathrm{.}311,481\mathrm{.}82$ Done $$929\mathrm{.}76$ $$0\mathrm{.}00$

$1249\mathrm{.}551,585\mathrm{.}54$ Done $$934\mathrm{.}85$ $$0\mathrm{.}00$

$1353\mathrm{.}021,696\mathrm{.}53$ Done $$940\mathrm{.}29$ $$0\mathrm{.}00$

$1456\mathrm{.}731,815\mathrm{.}29$ Done $$946\mathrm{.}11$ $$0\mathrm{.}00$

$1560\mathrm{.}701,942\mathrm{.}36$ Done $$952\mathrm{.}33$ $$0\mathrm{.}00$

$1664\mathrm{.}952,078\mathrm{.}32$ Done $$959\mathrm{.}00$ $$0\mathrm{.}00$

$1769\mathrm{.}492,223\mathrm{.}81$ Done $$966\mathrm{.}13$ $$0\mathrm{.}00$

$1874\mathrm{.}362,379\mathrm{.}47$ Done $$973\mathrm{.}76$ $$0\mathrm{.}00$

$1979\mathrm{.}562,546\mathrm{.}04$ Done $$981\mathrm{.}92$ $$0\mathrm{.}00$

$2085\mathrm{.}132,724\mathrm{.}26$ Done $$990\mathrm{.}65$ $$0\mathrm{.}00$

Conversion year $=9$

Value in conversion $=$ $$1,294\mathrm{.}28$

b$.$ What is the expected rate of return $($i$.$e$.,$ before$-$tax component cost$)$ on the proposed convertible issue?

Using the RATE function:

N $=9$

PMT $=$ $$60\mathrm{.}00$

PV $=-$$$1,000\mathrm{.}00$

FV $=$ $$1,294\mathrm{.}28$

Rate $=8\mathrm{.}325\%$

As a check, using the IRR function and the cash flows in column F:

Expected return to bondholders $8\mathrm{.}325\%$