1
2
3
4
5
6
7
8
A B C D E F G H
Solution
Chapter: 20
Problem: 9
Maggie’s Magazines (MM) has straight nonconvertible bond that currently yield 9%. MM’s stock sells for $22 per share,
has an expected constant growth rate of 6%, and has a dividend yield of 4$. MM plans on issuing convertible bonds that
48
49
50
51
52
53
54
55
56
57
58
59
60
62
63
64
65
66
67
Using the RATE function:
N = 9
PMT = $60.00
PV = -$1,000.00
73
74
75
76
77
78
Expected return to bondholders 8.325%
84
85
86
87
88
89
into the yellow cell):
return (multiplied by 1000) =
A B C D E F G H
8 37.80 1,209.60 No $920.57 $60.00
9 40.45 1,294.28 Yes $925.01 $1,354.28
10 43.28 1,384.87 Already $929.76 $0.00
11 46.31 1,481.82 Already $934.85 $0.00
12 49.55 1,585.54 Already $940.29 $0.00
13 53.02 1,696.53 Already $946.11 $0.00
14 56.73 1,815.29 Already $952.33 $0.00
15 60.70 1,942.36 Already $959.00 $0.00
16 64.95 2,078.32 Already $966.13 $0.00
17 69.49 2,223.81 Already $973.76 $0.00
18 74.36 2,379.47 Already $981.92 $0.00
19 79.56 2,546.04 Already $990.65 $0.00
20 85.13 2,724.26 Already $1,000.00 $0.00
Conversion year = 9
Value in conversion = 1,294.28$
b. What is the expected rate of return (i.e., before-tax component cost) on the proposed convertible issue?
FV = 1,294.28$
Rate = 8.325%
As a check, using the IRR function and the cash flows in column F:
9%
Current difference between bondholders’ current expected return and target
Hint: Use Goal seek to set the difference between the convertible bondholders’ current return and the
target return to zero by changing the input cell for the conversion ratio.
1
2
3
4
5
6
7
13
14
15
16
17
24
25
26
27
33
34
35
36
37
38
42
43
44
45
46
47
I
7/16/2015
Maggie‘s Magazines (MM) has straight nonconvertible bond that currently yield 9%. MM‘s stock sells for $22 per share,
after the coupon and dividend payments. Management will call the bonds when the bonds’ conversion value exceeds
25% of the bonds’ par value (not their call price).
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.)