Question

This is Question (exercise) 5 from Chapter 23 in the book Financial Modeling by Simon Benninga 4th edition.

An Underwriter issues a new 7-year C-rated bond at par. The anticipated recovery rate in default of the bond is expected to be 55%. What should be the coupon rate on the bond so that its expected return is 9%? Assume the transition matrix of exercise 2.(I tried to copy it bellow, hope it helps)

1 0 0 0 0

0.06 0.90 0.03 0.01 0

0.02 0.05 0.88 0.05 0

0 0 0 0 1

0 0 0 0 1

Answer 1

Calculating Expected Bond Return
Bond Price	100%			state	Payoff(t<N)		Payoff(N)
Coupon Rate Q	20%	Found using Goal Seek		A	20%		120%
Recovery Rate R	55%			B	20%		120%
Bond Term, N	7			C	20%		120%
Initial Rating	B			D	55%		55%
				E	0%		0%
	1	A	B	C	D	E
Transition Matrix	A	1	0	0	0	0
	B	0.06	0.9	0.03	0.01	0
	C	0.02	0.05	0.88	0.05	0
	D	0	0	0	0	1
	E	0	0	0	0	1
Initial Vector		0	1	0	0	0
formula in C16	=IF(UPPER($B$6)=C9,1,0)
Year	0	1	2	3	4	5	6	7
Expected Payoffs	-1	0.204009	0.202178	0.200172	0.198049	0.195855	0.193628	0.191399
Expected Return	9%	=IRR(B20:I20)

This question has been solved using the goal seek function in excel by setting expected return as 9% and by changing the  Coupon rate cell accordingly.

since the term was only 7 years, i have used a direct approach for calculating 7 year transition matrices, however, use of VBA function as suggested in the book would be more appropriate when faced with longer terms.