UN-new chapter 17, A

A B C
EXPECTED RETURN ON A ONE-YEAR BOND
1 WITH AN ADJUSTMENT FOR DEFAULT
PROBABILITY
2 Face value, F 100
3 Price, P 90
4 Annual coupon rate, Q 8%
5 Default probability 20%
6 Recovery percentage 40%
7
8 Expected period 1 cash flow 94.4 #VALUE!
9 Expected return 4.89% #VALUE!
 UN-new chapter 17, B

A B C D E F

1
USING THE MMULT FUNCTION
To compute multi-period transition matrices
2 One-period transition matrix
3 A B C D E
4 A 0.9700 0.0200 0.0100 0.0000 0.0000
5 B 0.0500 0.8000 0.1500 0.0000 0.0000
6 C 0.0100 0.0200 0.7500 0.2200 0.0000
7 D 0.0000 0.0000 0.0000 0.0000 1.0000
8 E 0.0000 0.0000 0.0000 0.0000 1.0000
9
10 Two-period transition matrix
11 A B C D E
12 A 0.9420 0.0356 0.0202 0.0022 0.0000
13 B 0.0900 0.6440 0.2330 0.0330 0.0000
14 C 0.0182 0.0312 0.5656 0.1650 0.2200
15 D 0.0000 0.0000 0.0000 0.0000 1.0000
16 E 0.0000 0.0000 0.0000 0.0000 1.0000
17 #VALUE!
18
19 Three-period transition matrix
20 A B C D E
21 A 0.9157 0.0477 0.0299 0.0044 0.0022
22 B 0.1218 0.5217 0.2723 0.0513 0.0330
23 C 0.0249 0.0366 0.4291 0.1244 0.3850
24 D 0.0000 0.0000 0.0000 0.0000 1.0000
25 E 0.0000 0.0000 0.0000 0.0000 1.0000
26 #VALUE!
 UN-new chapter 17, C

A B C D E F

1
USING THE FUNCTION MATRIXPOWER
To compute multi-period transition matrices
2 One-period transition matrix
3 A B C D E
4 A 0.9700 0.0200 0.0100 0.0000 0.0000
5 B 0.0500 0.8000 0.1500 0.0000 0.0000
6 C 0.0100 0.0200 0.7500 0.2200 0.0000
7 D 0.0000 0.0000 0.0000 0.0000 1.0000
8 E 0.0000 0.0000 0.0000 0.0000 1.0000
9
10 t 10
11
12 t-period transition matrix
13 A B C D E
14 A #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
15 B #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
16 C #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
17 D #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
18 E #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
19 #VALUE!
 UN - new chapter 17, D

A B C D E F G H I J
1 CALCULATING THE EXPECTED BOND RETURN
2 Bond price 100.00% Payoff (t<N) Payoff (N)
3 Coupon rate, Q 7% Cells to right 7% Cells to right 107%
4 Recovery rate, l 50% are called 7% are called 107%
5 Bond term, N 5 "payoff1" 7% "payoff2" 107%
6 Initial rating B in row 20 50% in row 20 50%
7 0% 0%
8
9 A B C D E
10 Transition matrix → A 0.9700 0.0200 0.0100 0.0000 0.0000
11 B 0.0500 0.8000 0.1500 0.0000 0.0000
12 C 0.0100 0.0200 0.7500 0.2200 0.0000
13 D 0.0000 0.0000 0.0000 0.0000 1.0000
14 E 0.0000 0.0000 0.0000 0.0000 1.0000
15
16 Initial vector 0 1 0 0 0
17 #VALUE!
18
19 Year 0 1 2 3 4 5 6 7 8
20 Expected payoffs -1.0000 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 0.0000 0.0000 0.0000
21 Expected yield #VALUE! #VALUE!
22
23
=IF(year>bondterm,0,
24 IRR of expected IF(year=bondterm,MMULT(initial,MMULT(matrixpower(transition,year),payoff2)),
25 payoffs MMULT(initial,MMULT(matrixpower(transition,year),payoff1))))
26
27
28 Data table: Recovery percentage and expected yield
29 Recovery percentage, l #VALUE! #VALUE!
30 0% #VALUE! 7%
31 10% #VALUE! 7%
32 20% #VALUE! 7% Bond Expected Return and Recovery Rate
Bond price = 100%, Bond Rating = B, Coupon = 7.00%
33 30% #VALUE! 7% 8%
34 40% #VALUE! 7%
7%

6%

5%
Page 4
4%

3%
 UN - new chapter 17, D
Bond Expected Return and Recovery Rate
Bond price = 100%, Bond Rating = B, Coupon = 7.00%
8%
A B C D
7% E F G H I J
35 50% #VALUE! 7%
6%
36 60% #VALUE! 7%
37 70% #VALUE! 7% 5%
38 80% #VALUE! 7% 4%
39 90% #VALUE! 7%
3%
40 100% #VALUE! 7%
41 2%

42 Note: The data table has a series with the 1%

43 coupon rate appended so that in the graph we 0%
44 can see the convergence of the bond expected 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100%
45 return to the coupon rate (cells C30:C40)
46 Graph title below (notice use of text functions):

Bond Expected Return and Recovery Rate

47 Bond price = 100%, Bond Rating = B, Coupon
= 7.00%

Page 5
 UN - new chapter 17, D

K L M N O P Q R S T U V W
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 9 10 11 12 13 14 15 16 17 18 19 20 21
20 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0 0 0 0 0 0 0
21
22
23
24
25
26
27
28
29
30
31
32
33
34

Page 6
 UN - new chapter 17, D

K L M N O P Q R S T U V W
35
36
37
38
39
40
41
42
43
44
45
e of text functions):
46

47

Page 7
 UN - new chapter 17, D

X Y Z AA AB AC AD AE AF AG AH AI AJ
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 22 23 24 25 26 27 28 29 30 31 32 33 34
20 0 0 0 0 0 0 0 0 0 0 0 0 0
21
22
23
24
25
26
27
28
29
30
31
32
33
34

Page 8
 UN - new chapter 17, D

AK AL AM AN AO AP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 35 36 37 38 39 40
20 0 0 0 0 0 0
21
22
23
24
25
26
27
28
29
30
31
32
33
34

Page 9
 UN - new chapter 17, D

A B C D E F G H I J
1 CALCULATING THE EXPECTED BOND RETURN
2 Bond price 100.00% Payoff (t<N) Payoff (N)
3 Coupon rate, Q 7% Cells to right 7% Cells to right 107%
4 Recovery rate, l 50% are called 7% are called 107%
5 Bond term, N 5 "payoff1" 7% "payoff2" 107%
6 Initial rating C in row 20 50% in row 20 50%
7 0% 0%
8
9 A B C D E
10 Transition matrix → A 0.9700 0.0200 0.0100 0.0000 0.0000
11 B 0.0500 0.8000 0.1500 0.0000 0.0000
12 C 0.0100 0.0200 0.7500 0.2200 0.0000
13 D 0.0000 0.0000 0.0000 0.0000 1.0000
14 E 0.0000 0.0000 0.0000 0.0000 1.0000
15
16 Initial vector 0 0 1 0 0.0000
17 #VALUE!
18
19 Year 0 1 2 3 4 5 6 7 8
20 Expected payoffs -1.0000 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 0.0000 0.0000 0.0000
21 Expected yield #VALUE! #VALUE!
22
23
=IF(year>bondterm,0,
24 IRR of expected IF(year=bondterm,MMULT(initial,MMULT(matrixpower(transition,year),payoff2)),
25 payoffs MMULT(initial,MMULT(matrixpower(transition,year),payoff1))))
26
27
28 Data table: Recovery percentage and expected yield
29 Recovery percentage, l #VALUE! #VALUE!
30 0% #VALUE! 7%
31 10% #VALUE! 7%
32 20% #VALUE! 7% Bond Expected Return and Recovery Rate
Bond price = 100%, Bond Rating = C, Coupon = 7.00%
33 30% #VALUE! 7% 8%
34 40% #VALUE! 7%
7%

6%

5%
Page 10
4%

3%
 UN - new chapter 17, D
Bond Expected Return and Recovery Rate
Bond price = 100%, Bond Rating = C, Coupon = 7.00%
8%
A B C D
7% E F G H I J
35 50% #VALUE! 7%
6%
36 60% #VALUE! 7%
37 70% #VALUE! 7% 5%
38 80% #VALUE! 7% 4%
39 90% #VALUE! 7%
3%
40 100% #VALUE! 7%
41 2%

42 Note: The data table has a series with the 1%

43 coupon rate appended so that in the graph we 0%
44 can see the convergence of the bond expected 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100%
45 return to the coupon rate (cells C30:C40)
46 Graph title below (notice use of text functions):

Bond Expected Return and Recovery Rate

47 Bond price = 100%, Bond Rating = C, Coupon
= 7.00%

Page 11
 UN - new chapter 17, D

K L M N O P Q R S T U V W
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 9 10 11 12 13 14 15 16 17 18 19 20 21
20 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0 0 0 0 0 0 0
21
22
23
24
25
26
27
28
29
30
31
32
33
34

Page 12
 UN - new chapter 17, D

K L M N O P Q R S T U V W
35
36
37
38
39
40
41
42
43
44
45
e of text functions):
46

47

Page 13
 UN - new chapter 17, D

X Y Z AA AB AC AD AE AF AG AH AI AJ
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 22 23 24 25 26 27 28 29 30 31 32 33 34
20 0 0 0 0 0 0 0 0 0 0 0 0 0
21
22
23
24
25
26
27
28
29
30
31
32
33
34

Page 14
 UN - new chapter 17, D

AK AL AM AN AO AP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 35 36 37 38 39 40
20 0 0 0 0 0 0
21
22
23
24
25
26
27
28
29
30
31
32
33
34

Page 15
 UN - new chapter 17, D

A B C D E F G H I J K
1 CALCULATING THE EXPECTED BOND RETURN
2 Bond price 88.00% Payoff (t<N) Payoff (N)
3 Coupon rate, Q 7% Cells F6:F6 7% Cells I3:I6 107%
4 Recovery rate, l 50% are called 7% are called 107%
5 Bond term, N 8 "payoff1" 7% "payoff2" 107%
6 Initial rating B in row 19 50% in row 19 50%
7 0% 0%
8
9 A B C D E
10 Transition matrix → A 0.9700 0.0200 0.0100 0.0000 0.0000
11 B 0.0500 0.8000 0.1500 0.0000 0.0000
12 C 0.0100 0.0200 0.7500 0.2200 0.0000
13 D 0.0000 0.0000 0.0000 0.0000 1.0000
14 E 0.0000 0.0000 0.0000 0.0000 1.0000
15
16 Initial vector 0 1 0 0 0.0000
17 #VALUE!
18
19 Year 0 1 2 3 4 5 6 7 8 9
20 Expected payoffs -0.8800 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 0.0000
21 Expected yield #VALUE!
22
23
=IF(year>bondterm,0,
24 IRR of expected IF(year=bondterm,MMULT(initial,MMULT(matrixpower(transition,year),payoff2)),
25 payoffs MMULT(initial,MMULT(matrixpower(transition,year),payoff1))))
26
27

28
Data table: Recovery percentage
and expected yield Bond Expected Return and Recovery Rate
Recover percentage, l Bond price = 88%, Bond Rating = B, Coupon = 7.00% , YTM = 9.183%
#VALUE! <-- Table header
29 7.5%
30 0% #VALUE! 7%
31 10% #VALUE! 7%
32 20% #VALUE! 7% 6.5%
33 30% #VALUE! 7%
5.5%

Page 16
4.5%
 Bond Expected Return and Recovery Rate
Bond price = 88%, Bond Rating = B, Coupon = 7.00% , YTM = 9.183%
7.5%
UN - new chapter 17, D

6.5%
A B C D E F G H I J K
34 40% #VALUE! 7%
5.5%
35 50% #VALUE! 7%
36 60% #VALUE! 7%
37 70% #VALUE! 7% 4.5%
38 80% #VALUE! 7%
39 90% #VALUE! 7%
40 100% #VALUE! 7% 3.5%
41
42 Note: The data table has a series with the
2.5%
43 coupon rate appended so that in the graph we
0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100%
44 can see the convergence of the bond expected
45 return to the coupon rate (cells C30:C40)
46 Graph title below (notice use of text functions):
Bond Expected Return and Recovery Rate
47 Bond price = 88%, Bond Rating = B, Coupon
= 7.00% , YTM = 9.183%
48
49
50
51
52
53 YTM computation Cash flow
54 0 -88.00%
55 1 7%
56 2 7%
57 3 7%
58 4 7%
59 5 7%
60 6 7%
61 7 7%
62 8 107%
63
64 YTM 9.18% #VALUE!
65 9.18% #VALUE!

Page 17
 UN - new chapter 17, D

L M N O P Q R S T U V W X
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 10 11 12 13 14 15 16 17 18 19 20 21 22
20 0.0000 0.0000 0.0000 0.0000 0.0000 0 0 0 0 0 0 0 0
21
22
23
24
25
26
27

28

29
30
31
32
33

Page 18
 UN - new chapter 17, D

Y Z AA AB AC AD AE AF AG AH AI AJ AK
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 23 24 25 26 27 28 29 30 31 32 33 34 35
20 0 0 0 0 0 0 0 0 0 0 0 0 0
21
22
23
24
25
26
27

28

29
30
31
32
33

Page 19
 UN - new chapter 17, D

AL AM AN AO AP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 36 37 38 39 40
20 0 0 0 0 0
21
22
23
24
25
26
27

28

29
30
31
32
33

Page 20
 A B C D E F

CALCULATING THE EXPECTED BOND RETURN

1 This version computes the expected bond return for AMR taking into a
Uses annual transition matrix
2 Bond price 76.75% Payoff (t<N)
3 Coupon rate, Q 10.55% Vector to AAA
4 Actual price (includes accrued) 80.39% #VALUE! right AA
5 Recovery rate, l 50.00% called A
6 Maturity date 12-Mar-21 "payoff1" BBB
7 Current date 20-Jul-05 BB
8 Initial rating CCC B
9 Bond YTM 14.78% Coupon paid CCC
10 semiannually Default
11 E
12
13 original probability of migrating to rating by year en
14 Transition matrix → rating AAA AA A BBB
15 AAA 0.9366 0.0583 0.0040 0.0008
16 AA 0.0066 0.9172 0.0694 0.0049
17 A 0.0007 0.0225 0.9176 0.0519
18 BBB 0.0003 0.0025 0.0483 0.8926
19 BB 0.0003 0.0007 0.0044 0.0667
20 B 0.0000 0.0010 0.0033 0.0046
21 CCC 0.0016 0.0000 0.0031 0.0093
22 Default 0.0000 0.0000 0.0000 0.0000
23 E 0.0000 0.0000 0.0000 0.0000
24
25
26 AAA AA A BBB BB
27 Initial vector 0 0 0 0 0
28
29 Computing the AMR bond expected return
30 0 1 2 3 4
31 Date 20-Jul-05 15-Nov-05 15-Mar-06 15-Sep-06 15-Mar-07
32 Expected payoffs -0.8039 #VALUE! #VALUE! #VALUE! #VALUE!
33 Expected bond return Err:504 #VALUE!
34
35 Annualized IRR of expected =IF(E31>$B$6,0,
36 payoffs computed using actual IF(E31=$B$6,MMULT(initial,MMULT(matrixpower(
37 dates with XIRR. MMULT(initial,MMULT(matrixpower(transition,E30
38
39

40 Data table: Recovery percentage and expected AMR bond return

41 Recovery percentage, l Err:504 YTM
42 45% Err:504 14.78% 16%
43 50% Err:504 14.78%
14%
44 55% Err:504 14.78%
12%
10%
8%
6%
4%
 16%
14%
A B C D E F
60% Err:504 14.78% 12%
45
46 65% Err:504 14.78% 10%
47 70% Err:504 14.78% 8%
48 75% Err:504 14.78%
6%
49 80% Err:504 14.78%
85% Err:504 14.78% 4%
50
51 90% Err:504 14.78% 2% Recovery

52 95% Err:504 14.78% 0%

53 40% 50% 60% 70% 80%
54 #VALUE!
55
56
57 step 5%

58

59
60
61
62
63
64 YTM calculation
65 Year 20-Jul-05 15-Nov-05 15-Mar-06 15-Sep-06 15-Mar-07
66 Expected payoffs -0.8039 0.0528 0.0528 0.0528 0.0528
67 Annualized YTM 14.78%
 G H I J K L M

PECTED BOND RETURN

urn for AMR
1 taking into account actual dates
ansition matrix
2 Payoff (t<N) Payoff (N)
3 5.28% Vector to 105.28% Accrued interest calculation
4 5.28% right 105.28% Last payment date
5 5.28% called 105.28% Next payment date
6 5.28% "payoff2" 105.28% Current date
7 5.28% 105.28% Percentage of period
8 5.28% 105.28% Accrued interest
9 5.28% 105.28%
10 50.00% 50.00%
11 0% 0%
12
13to rating by year end (%)
ty of migrating
14 BB B CCC Default E
15 0.0003 0.0000 0.0000 0.0000 0.0000
16 0.0006 0.0009 0.0002 0.0001 0.0000
17 0.0049 0.0020 0.0001 0.0004 0.0000
18 0.0444 0.0081 0.0016 0.0022 0.0000
19 0.8331 0.0747 0.0105 0.0098 0.0000
20 0.0577 0.8419 0.0387 0.0530 0.0000
21 0.0200 0.1074 0.6396 0.2194 0.0000
22 0.0000 0.0000 0.0000 0.0000 1.0000
23 0.0000 0.0000 0.0000 0.0000 1.0000
24
25
26 B CCC Default E
27 0 1 0 0
28
29
30 5 6 7 8 9 10 11
31 15-Sep-07 15-Mar-08 15-Sep-08 15-Mar-09 15-Sep-09 15-Mar-10 15-Sep-10
32 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
33
34
35
$6,0,
36
6,MMULT(initial,MMULT(matrixpower(transition,E30),payoff2)),
37
al,MMULT(matrixpower(transition,E30),payoff1))))
38
39

ected AMR bond

40 return
41
42
43
44
 G H I J K L M
45
46
47
48
49
50
51 Recovery percentage l

52
60% 53 70% 80% 90% 100%
54
55
56
57

58 AMR Bond Expected Return vs. Recovery Rate

Bond price = 77%, Bond Rating = CCC,
Coupon = 10.55%, YTM = 14.78%
59
60
61
62
63
64
65 15-Sep-07 15-Mar-08 15-Sep-08 15-Mar-09 15-Sep-09 15-Mar-10 15-Sep-10
66 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528
67
 N O P Q R S T U V

2
rued interest calculation
3
4 15-Mar-05
5 15-Sep-05
6 20-Jul-05
7 0.69
8 0.0364
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 12 13 14 15 16 17 18 19 20
31 15-Mar-11 15-Sep-11 15-Mar-12 15-Sep-12 15-Mar-13 15-Sep-13 15-Mar-14 15-Sep-14 15-Mar-15
32 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
33
34
35
36
37
38
39

40

41
42
43
44
 N O P Q R S T U V
45
46
47
48
49
50
51
52
53
54
55
56
57

58

59
60
61
62
63
64
65 15-Mar-11 15-Sep-11 15-Mar-12 15-Sep-12 15-Mar-13 15-Sep-13 15-Mar-14 15-Sep-14 15-Mar-15
66 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528
67
 W X Y Z AA AB AC AD AE

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 21 22 23 24 25 26 27 28 29
31 15-Sep-15 15-Mar-16 15-Sep-16 15-Mar-17 15-Sep-17 15-Mar-18 15-Sep-18 15-Mar-19 15-Sep-19
32 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
33
34
35
36
37
38
39

40

41
42
43
44
 W X Y Z AA AB AC AD AE
45
46
47
48
49
50
51
52
53
54
55
56
57

58

59
60
61
62
63
64
65 15-Sep-15 15-Mar-16 15-Sep-16 15-Mar-17 15-Sep-17 15-Mar-18 15-Sep-18 15-Mar-19 15-Sep-19
66 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528
67
 AF AG AH AI AJ AK AL AM AN

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 30 31 32 33 34 35 36 37 38
31 15-Mar-20 15-Sep-20 15-Mar-21 15-Sep-21 15-Mar-22 15-Sep-22 15-Mar-23 15-Sep-23 15-Mar-24
32 #VALUE! #VALUE! 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
33
34
35
36
37
38
39

40

41
42
43
44
 AF AG AH AI AJ AK AL AM AN
45
46
47
48
49
50
51
52
53
54
55
56
57

58

59
60
61
62
63
64
65 15-Mar-20 15-Sep-20 12-Mar-21 15-Sep-21 15-Mar-22 15-Sep-22 15-Mar-23 15-Sep-23 15-Mar-24
66 0.0528 0.0528 1.0528 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
67
 AO AP AQ AR AS AT AU AV AW

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 39 40 41 42 43 44 45 46 47
31 15-Sep-24 15-Mar-25
32 0.0000 0.0000
33
34
35
36
37
38
39

40

41
42
43
44
 AO AP AQ AR AS AT AU AV AW
45
46
47
48
49
50
51
52
53
54
55
56
57

58

59
60
61
62
63
64
65 15-Sep-24 15-Mar-25
66 0.0000 0.0000
67
 AX AY AZ BA BB BC BD BE BF

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 48 49 50 51 52 53 54 55 56
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 BG BH BI BJ BK BL BM BN BO

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 57 58 59 60 61 62 63 64 65
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 BP BQ BR BS BT BU BV BW BX

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 66 67 68 69 70 71 72 73 74
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 BY BZ CA CB CC CD CE CF CG

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 75 76 77 78 79 80 81 82 83
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 CH CI CJ CK CL CM CN CO CP

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 84 85 86 87 88 89 90 91 92
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 CQ CR CS CT CU CV CW CX CY

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 93 94 95 96 97 98 99 100 101
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 CZ DA DB DC DD DE DF DG DH

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 102 103 104 105 106 107 108 109 110
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 DI DJ DK DL DM DN DO DP DQ

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 111 112 113 114 115 116 117 118 119
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 DR DS DT DU DV DW DX DY DZ

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 120 121 122 123 124 125 126 127 128
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 EA EB EC ED EE EF EG EH EI

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 129 130 131 132 133 134 135 136 137
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 EJ EK EL EM EN EO EP EQ ER

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 138 139 140 141 142 143 144 145 146
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 ES ET EU EV EW EX EY EZ FA

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 147 148 149 150 151 152 153 154 155
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 FB FC FD FE FF FG FH FI FJ

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 156 157 158 159 160 161 162 163 164
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 FK FL FM FN FO FP FQ FR FS

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 165 166 167 168 169 170 171 172 173
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 FT FU FV FW FX FY FZ GA GB

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 174 175 176 177 178 179 180 181 182
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 GC GD GE GF GG GH GI GJ GK

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 183 184 185 186 187 188 189 190 191
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 GL GM GN GO GP GQ GR GS GT

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 192 193 194 195 196 197 198 199 200
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 GU GV GW GX GY GZ HA HB HC

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 201 202 203 204 205 206 207 208 209
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 HD HE HF HG HH HI HJ HK HL

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 210 211 212 213 214 215 216 217 218
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 HM HN HO HP HQ HR HS HT

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 219 220 221 222 223 224 225 226
31
32
33
34
35
36
37
38
39

40

41
42
43
44
 COMPUTING THE SQUARE ROOT OF THE TRANSITION MATRIX

The one-year transition matrix

original probability of migrating to rating by year end (%)
rating AAA AA A BBB BB B CCC Default E
AAA 0.9366 0.0583 0.0040 0.0008 0.0003 0.0000 0.0000 0.0000 0.0000
AA 0.0066 0.9172 0.0694 0.0049 0.0006 0.0009 0.0002 0.0001 0.0000
A 0.0007 0.0225 0.9176 0.0519 0.0049 0.0020 0.0001 0.0004 0.0000
BBB 0.0003 0.0025 0.0483 0.8926 0.0444 0.0081 0.0016 0.0022 0.0000
BB 0.0003 0.0007 0.0044 0.0667 0.8331 0.0747 0.0105 0.0098 0.0000
B 0.0000 0.0010 0.0033 0.0046 0.0577 0.8419 0.0387 0.0530 0.0000
CCC 0.0016 0.0000 0.0031 0.0093 0.0200 0.1074 0.6396 0.2194 0.0000
Default 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 1.0000
E 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 1.0000

The square root of the one-year transition matrix as computed by Mathematica

Note the negative entries
original probability of migrating to rating by year end (%)
rating AAA AA A BBB BB B CCC Default E
AAA 0.9677 0.0303 0.0015 0.0004 0.0001 0.0000 0.0000 0.0000 0.0000
AA 0.0034 0.9574 0.0362 0.0021 0.0002 0.0004 0.0001 0.0000 0.0000
A 0.0003 0.0117 0.9573 0.0272 0.0023 0.0010 0.0000 0.0003 -0.0001
BBB 0.0001 0.0012 0.0254 0.9439 0.0238 0.0038 0.0008 0.0017 -0.0007
BB 0.0002 0.0003 0.0018 0.0359 0.9115 0.0406 0.0056 0.0068 -0.0026
B 0.0000 0.0005 0.0017 0.0018 0.0314 0.9161 0.0225 0.0510 -0.0248
CCC 0.0009 -0.0001 0.0016 0.0050 0.0105 0.0624 0.7988 0.2706 -0.1495
Default 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 1.0000
E 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 1.0000

The semi-annual transition matrix. To eliminate the negative entries in the

Mathematica matrix above:
a. We assume that a transition from AAA, ... , CCC --> E is impossible (i.e.: last column
is zero except for last two entries).
b. We set all other negative entries to zero.
c. We set default probability so that each row sums to 1.

original probability of migrating to rating by year end (%)

rating AAA AA A BBB BB B CCC Default E
AAA 0.9677 0.0303 0.0015 0.0004 0.0001 0.0000 0.0000 0.0000 0.0000
AA 0.0034 0.9574 0.0362 0.0021 0.0002 0.0004 0.0001 0.0001 0.0000
A 0.0003 0.0117 0.9573 0.0272 0.0023 0.0010 0.0000 0.0001 0.0000
BBB 0.0001 0.0012 0.0254 0.9439 0.0238 0.0038 0.0008 0.0010 0.0000
BB 0.0002 0.0003 0.0018 0.0359 0.9115 0.0406 0.0056 0.0041 0.0000
B 0.0000 0.0005 0.0017 0.0018 0.0314 0.9161 0.0225 0.0261 0.0000
CCC 0.0009 0.0000 0.0016 0.0050 0.0105 0.0624 0.7988 0.1208 0.0000
Default 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 1.0000
E 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 1.0000
 A B C D E F

CALCULATING THE EXPECTED BOND RETURN

1 This version computes the expected bond return for AMR taking into a
Uses semi-annual transition matrix
2 Bond price 76.75% Payoff (t<N)
3 Coupon rate, Q 10.55% Vector to AAA
4 Actual price (includes accrued) 80.39% #VALUE! right AA
5 Recovery rate, l 50.00% called A
6 Maturity date 12-Mar-21 "payoff1" BBB
7 Current date 20-Jul-05 BB
8 Maturity (years) 15.65 B
9 Number of semiannual payments 30.00 Coupon paid CCC
10 Initial rating CCC semiannually Default
11 Bond YTM 14.81% E
12
13 original probability of migrating to rating by year en
14 Transition matrix → rating AAA AA A BBB
15 AAA 0.9677 0.0303 0.0015 0.0004
16 AA 0.0034 0.9574 0.0362 0.0021
17 A 0.0003 0.0117 0.9573 0.0272
18 BBB 0.0001 0.0012 0.0254 0.9439
19 BB 0.0002 0.0003 0.0018 0.0359
20 B 0.0000 0.0005 0.0017 0.0018
21 CCC 0.0009 -0.0001 0.0016 0.0050
22 Default 0.0000 0.0000 0.0000 0.0000
23 E 0.0000 0.0000 0.0000 0.0000
24
25
26 AAA AA A BBB BB
27 Initial vector 0 0 0 0 0
28
29 Period 0 1 2 3 4
30 Date 20-Jul-05 15-Sep-05 15-Mar-06 15-Sep-06 15-Mar-07
31 Expected payoffs -0.8039 #VALUE! #VALUE! #VALUE! #VALUE!
32 Expected yield Err:504 #VALUE!
33
34
35
36
37
38

39
Data table: Recovery percentage
and expected yield
40 Recovery percentage, l Err:504 YTM AMR
<-- Table Bond Expected Return vs. R
header
0% Err:504 14.81% 16% Bond price = 77%, Bond Rating =
41
42 10% Err:504 14.81% 14% Coupon = 10.55%, YTM = 14.81%
43 20% Err:504 14.81%
44 30% Err:504 14.81% 12%

10%

8%

6%
 AMR Bond Expected Return vs. R
16% Bond price = 77%, Bond Rating =
14% Coupon = 10.55%, YTM = 14.81%

A B C 12%
D E F
45 40% Err:504 14.81%
10%
46 50% Err:504 14.81%
47 60% Err:504 14.81% 8%
48 70% Err:504 14.81%
80% Err:504 14.81% 6%
49
50 90% Err:504 14.81% 4%
51 100% Err:504 14.81%
52 2%
53 Note: The data table has a series with the
0%
54 YTM appended so that in the graph we
0% 10% 20% 30% 40% 50% 60%
55 can see the relation of the bond expected Recovery percentage l
56 return to the YTM.

57

58
59
60
61
62
63 YTM calculation
64 Year 20-Jul-05 15-Sep-05 15-Mar-06 15-Sep-06 15-Mar-07
65 Expected payoffs -0.8039 0.0528 0.0528 0.0528 0.0528
66 Annualized YTM 14.81%
 G H I J K L M

ECTED BOND RETURN

urn for AMR1 taking into account actual dates
transition matrix
2 Payoff (t<N) Payoff (N)
3 5.28% Vector to 105.28% Accrued interest calculation
4 5.28% right 105.28% Last payment date
5 5.28% called 105.28% Next payment date
6 5.28% "payoff2" 105.28% Current date
7 5.28% 105.28% Percentage of period
8 5.28% 105.28% Accrued interest
9 5.28% 105.28%
10 50.00% 50.00%
11 0% 0%
12
ty of migrating13to rating by year end (%)
14 BB B CCC Default E
15 0.0001 0.0000 0.0000 0.0000 0.0000
16 0.0002 0.0004 0.0001 0.0001 0.0000
17 0.0023 0.0010 0.0000 0.0001 0.0000
18 0.0238 0.0038 0.0008 0.0010 0.0000
19 0.9115 0.0406 0.0056 0.0041 0.0000
20 0.0314 0.9161 0.0225 0.0261 0.0000
21 0.0105 0.0624 0.7988 0.1208 0.0000
22 0.0000 0.0000 0.0000 0.0000 1.0000
23 0.0000 0.0000 0.0000 0.0000 1.0000
24
25
26 B CCC Default E
27 0 1 0 0
28
29 5 6 7 8 9 10 11
30 15-Sep-07 15-Mar-08 15-Sep-08 15-Mar-09 15-Sep-09 15-Mar-10 15-Sep-10
31 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
32
33
34
35
36
37
38

39

R Bond Expected
40 Return vs. Recovery Rate
nd price = 77%,
41 Bond Rating = CCC,
upon = 10.55%,
42 YTM = 14.81%
43
44
R Bond Expected Return vs. Recovery Rate
nd price = 77%, Bond Rating = CCC,
upon = 10.55%, YTM = 14.81%

G H I J K L M
45
46
47
48
49
50
51
52
53
54
20% 30% 40% 50% 60% 70% 80% 90% 100%
55
Recovery percentage l
56

57 AMR Bond Expected Return vs. Recovery Rate

Bond price = 77%, Bond Rating = CCC,
Coupon = 10.55%, YTM = 14.81%
58
59
60
61
62
63
64 15-Sep-07 15-Mar-08 15-Sep-08 15-Mar-09 15-Sep-09 15-Mar-10 15-Sep-10
65 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528
66
 N O P Q R S T U V

2
rued interest calculation
3
4 15-Mar-05
5 15-Sep-05
6 20-Jul-05
7 0.69
8 0.0364
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 12 13 14 15 16 17 18 19 20
30 15-Mar-11 15-Sep-11 15-Mar-12 15-Sep-12 15-Mar-13 15-Sep-13 15-Mar-14 15-Sep-14 15-Mar-15
31 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
32
33
34
35
36
37
38

39

40
41
42
43
44
 N O P Q R S T U V
45
46
47
48
49
50
51
52
53
54
55
56

57

58
59
60
61
62
63
64 15-Mar-11 15-Sep-11 15-Mar-12 15-Sep-12 15-Mar-13 15-Sep-13 15-Mar-14 15-Sep-14 15-Mar-15
65 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528
66
 W X Y Z AA AB AC AD AE

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 21 22 23 24 25 26 27 28 29
30 15-Sep-15 15-Mar-16 15-Sep-16 15-Mar-17 15-Sep-17 15-Mar-18 15-Sep-18 15-Mar-19 15-Sep-19
31 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
32
33
34
35
36
37
38

39

40
41
42
43
44
 W X Y Z AA AB AC AD AE
45
46
47
48
49
50
51
52
53
54
55
56

57

58
59
60
61
62
63
64 15-Sep-15 15-Mar-16 15-Sep-16 15-Mar-17 15-Sep-17 15-Mar-18 15-Sep-18 15-Mar-19 15-Sep-19
65 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528
66
 AF AG AH AI AJ AK AL AM AN

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 30 31 32 33 34 35 36 37 38
30 15-Mar-20 15-Sep-20 15-Mar-21 15-Sep-21 15-Mar-22 15-Sep-22 15-Mar-23 15-Sep-23 15-Mar-24
31 #VALUE! 0 0 0 0 0 0 0 0
32
33
34
35
36
37
38

39

40
41
42
43
44
 AF AG AH AI AJ AK AL AM AN
45
46
47
48
49
50
51
52
53
54
55
56

57

58
59
60
61
62
63
64 15-Mar-20 15-Sep-20 12-Mar-21 15-Sep-21 15-Mar-22 15-Sep-22 15-Mar-23 15-Sep-23 15-Mar-24
65 0.0528 0.0528 1.0528 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
66
 AO AP

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 39 40
30 15-Sep-24 15-Mar-25
31 0 0
32
33
34
35
36
37
38

39

40
41
42
43
44
 AO AP
45
46
47
48
49
50
51
52
53
54
55
56

57

58
59
60
61
62
63
64 15-Sep-24 15-Mar-25
65 0.0000 0.0000
66
 UN - new chapter 17, F

A B C
1 CALCULATING A BOND'S BETA
2 Market risk premium, E(rm) - rf 8.40%
3 rf 7%
4 Expected bond return 8.00%
5 Implied bond beta 0.119 #VALUE!
6
7 Tax-adjusted SML (see section 2.6)
8 Market risk premium, E(rm) - rf 8.40%
9 rf 7%
10 Corporate tax rate, TC 40%
11 Expected bond return 8.00%
12 Implied bond beta 0.089 #VALUE!
 COMPARING THE EXPECTED RETURNS WITH
SEMIANNUAL VS. ANNUAL TRANSITION MATRICES
Using actual dates and XIRR
Expected bond return
Recovery Annual Semiannual Expected Bond Returns with Annual and Semiannual
percentage, transition transition Transition Matrices
l matrix matrix 1200%
0% Err:504 Err:504
10% Err:504 Err:504 1000% Annual
transition matrix
20% Err:504 Err:504 Semiannual
800% transition matrix
30% Err:504 Err:504
40% Err:504 Err:504
600%
55% Err:504 Err:504
60% Err:504 Err:504
400%
65% Err:504 Err:504
80% Err:504 Err:504 200%
90% Err:504 Err:504
100% Err:504 Err:504 0%
0% 10% 20% 30% 40% 55% Recovery
60% percentage
65% 80% l
90% 100%
S

nd Semiannual
Annual
Just as a check
matrix
Semiannual
Bond price 76.75% #REF!
Coupon rate, Q 10.55% #REF!
Recovery rate, l 50.00% #REF!
Maturity date 12-Mar-21 #REF!
Current date 20-Jul-05 #REF!
Maturity (years) 15.65 #REF!
Number of semiannual payments 31.00 #REF!
Initial rating CCC #REF!
Bond YTM 14.08% #REF!

percentage
0% l
90% 100%
Semiannual
 COMPARING AMR BOND BETAS
Using the tax-adjusted CAPM (section 2.6)
E(rM) 8%
rf 3.90%
Corp. tax rate, TC 40.00%

BOND BETA 3.50 AMR BOND BETAS

Tax-adjusted CAPM
Annual Semiannual 3.00
Recovery transition transition
percentage, l matrix matrix 2.50 Annual
transition matrix
0% Err:504 Err:504 2.00 Semiannual
10% Err:504 Err:504 transition matrix
20% Err:504 Err:504 1.50
30% Err:504 Err:504 1.00
40% Err:504 Err:504
55% Err:504 Err:504 0.50
60% Err:504 Err:504 0.00
65% Err:504 Err:504 0% 10% 20% 30% 40% 55% 60% 65% 80% 90% 100%
80% Err:504 Err:504 -0.50 Recovery percentage l
90% Err:504 Err:504 -1.00
100% Err:504 Err:504
-1.50
6)

ETAS
CAPM

60% 65% 80% 90% 100%

Recovery percentage l
 A B C D E F

CALCULATING THE EXPECTED BOND RETURN

1 This version computes the expected bond return for AMR taking into a
Uses annual transition matrix
2 Bond price 76.75% Payoff (t<N)
3 Coupon rate, Q 10.55% Vector to AAA
4 Actual price (includes accrued) 80.39% #VALUE! right AA
5 Recovery rate, l 50.00% called A
6 Maturity date 12-Mar-21 "payoff1" BBB
7 Current date 20-Jul-05 BB
8 Maturity (years) 15.65 B
9 Number of semiannual payments 30.00 Coupon paid CCC
10 Initial rating CCC semiannually Default
11 Bond YTM 14.81% E
12
13 original probability of migrating to rating by year en
14 Transition matrix → rating AAA AA A BBB
15 AAA 0.9366 0.0583 0.0040 0.0008
16 AA 0.0066 0.9172 0.0694 0.0049
17 A 0.0007 0.0225 0.9176 0.0519
18 BBB 0.0003 0.0025 0.0483 0.8926
19 BB 0.0003 0.0007 0.0044 0.0667
20 B 0.0000 0.0010 0.0033 0.0046
21 CCC 0.0016 0.0000 0.0031 0.0093
22 Default 0.0000 0.0000 0.0000 0.0000
23 E 0.0000 0.0000 0.0000 0.0000
24
25
26 AAA AA A BBB BB
27 Initial vector 0 0 0 0 0
28
29 Period 0 1 2 3 4
30 Date 20-Jul-05 15-Sep-05 15-Mar-06 15-Sep-06 15-Mar-07
31 Expected payoffs -0.8039 #VALUE! #VALUE! #VALUE! #VALUE!
32 Expected yield Err:504 #VALUE!
33
34 Annualized IRR of expected =IF(year>bondterm,0,
35 payoffs computed using actual IF(year=bondterm,MMULT(initial,MMULT(matrixpo
36 dates with XIRR. MMULT(initial,MMULT(matrixpower(transition,yea
37
38

39
Data table: Recovery percentage
and expected yield
40 Recovery percentage, l Err:504 YTM <-- Table header
41 0% Err:504 14.81%
42 10% Err:504 14.81%
43 20% Err:504 14.81%
44 30% Err:504 14.81%
 A B C D E F
45 40% Err:504 14.81%
46 50% Err:504 14.81%
47 60% Err:504 14.81%
48 70% Err:504 14.81%
49 80% Err:504 14.81%
50 90% Err:504 14.81%
51 100% Err:504 14.81%
52
53 Note: The data table has a series with the
54 YTM appended so that in the graph we
55 can see the relation of the bond expected
56 return to the YTM.

57

58
59
60
61
62
63 YTM calculation
64 Year 20-Jul-05 15-Sep-05 15-Mar-06 15-Sep-06 15-Mar-07
65 Expected payoffs -0.8039 0.0528 0.0528 0.0528 0.0528
66 Annualized YTM 14.81%
 G H I J K L M

PECTED BOND RETURN

urn for AMR
1 taking into account actual dates
ansition matrix
2 Payoff (t<N) Payoff (N)
3 5.28% Vector to 105.28% Accrued interest calculation
4 5.28% right 105.28% Last payment date
5 5.28% called 105.28% Next payment date
6 5.28% "payoff2" 105.28% Current date
7 5.28% 105.28% Percentage of period
8 5.28% 105.28% Accrued interest
9 5.28% 105.28%
10 50.00% 50.00%
11 0% 0%
12
13to rating by year end (%)
ty of migrating
14 BB B CCC Default E
15 0.0003 0.0000 0.0000 0.0000 0.0000
16 0.0006 0.0009 0.0002 0.0001 0.0000
17 0.0049 0.0020 0.0001 0.0004 0.0000
18 0.0444 0.0081 0.0016 0.0022 0.0000
19 0.8331 0.0747 0.0105 0.0098 0.0000
20 0.0577 0.8419 0.0387 0.0530 0.0000
21 0.0200 0.1074 0.6396 0.2194 0.0000
22 0.0000 0.0000 0.0000 0.0000 1.0000
23 0.0000 0.0000 0.0000 0.0000 1.0000
24
25
26 B CCC Default E
27 0 1 0 0
28
29 5 6 7 8 9 10 11
30 15-Sep-07 15-Mar-08 15-Sep-08 15-Mar-09 15-Sep-09 15-Mar-10 15-Sep-10
31 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
32
33
34
ndterm,0,
35
dterm,MMULT(initial,MMULT(matrixpower(transition,year),payoff2)),
36
al,MMULT(matrixpower(transition,year),payoff1))))
37
38

39

40
41
42
43
44
 G H I J K L M
45
46
47
48
49
50
51
52
53
54
55
56 Graph title below (notice use of text functions):

AMR Bond Expected Return vs. Recovery Rate

57 Bond price = 77%, Bond Rating = CCC,
Coupon = 10.55%, YTM = 14.81%

58
59
60
61
62
63
64 15-Sep-07 15-Mar-08 15-Sep-08 15-Mar-09 15-Sep-09 15-Mar-10 15-Sep-10
65 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528
66
 N O P Q R S T U V

2
rued interest calculation
3
4 15-Mar-05
5 15-Sep-05
6 20-Jul-05
7 0.69
8 0.0364
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 12 13 14 15 16 17 18 19 20
30 15-Mar-11 15-Sep-11 15-Mar-12 15-Sep-12 15-Mar-13 15-Sep-13 15-Mar-14 15-Sep-14 15-Mar-15
31 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
32
33
34
35
36
37
38

39

40
41
42
43
44
 N O P Q R S T U V
45
46
47
48
49
50
51
52
53
54
55
56

57

58
59
60
61
62
63
64 15-Mar-11 15-Sep-11 15-Mar-12 15-Sep-12 15-Mar-13 15-Sep-13 15-Mar-14 15-Sep-14 15-Mar-15
65 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528
66
 W X Y Z AA AB AC AD AE

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 21 22 23 24 25 26 27 28 29
30 15-Sep-15 15-Mar-16 15-Sep-16 15-Mar-17 15-Sep-17 15-Mar-18 15-Sep-18 15-Mar-19 15-Sep-19
31 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
32
33
34
35
36
37
38

39

40
41
42
43
44
 W X Y Z AA AB AC AD AE
45
46
47
48
49
50
51
52
53
54
55
56

57

58
59
60
61
62
63
64 15-Sep-15 15-Mar-16 15-Sep-16 15-Mar-17 15-Sep-17 15-Mar-18 15-Sep-18 15-Mar-19 15-Sep-19
65 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528
66
 AF AG AH AI AJ AK AL AM AN

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 30 31 32 33 34 35 36 37 38
30 15-Mar-20 15-Sep-20 15-Mar-21 15-Sep-21 15-Mar-22 15-Sep-22 15-Mar-23 15-Sep-23 15-Mar-24
31 #VALUE! 0 0 0 0 0 0 0 0
32
33
34
35
36
37
38

39

40
41
42
43
44
 AF AG AH AI AJ AK AL AM AN
45
46
47
48
49
50
51
52
53
54
55
56

57

58
59
60
61
62
63
64 15-Mar-20 15-Sep-20 12-Mar-21 15-Sep-21 15-Mar-22 15-Sep-22 15-Mar-23 15-Sep-23 15-Mar-24
65 0.0528 0.0528 1.0528 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
66
 AO AP

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 39 40
30 15-Sep-24 15-Mar-25
31 0 0
32
33
34
35
36
37
38

39

40
41
42
43
44
 AO AP
45
46
47
48
49
50
51
52
53
54
55
56

57

58
59
60
61
62
63
64 15-Sep-24 15-Mar-25
65 0.0000 0.0000
66