UN-new chapter 17, AEXPECTED RETURN ON A ONE-YEAR BONDWITH AN ADJUSTMENT FOR DEFAULTPROBABILITYFace value, F 100Price, P 90Annual coupon rate, Q 8%Default probability 20%Recovery percentage 40%Expected period 1 cash flow 94.4 b o n d te rm ,0 ,IF ( y e a r= b o n d te rm ,M M U L T (in itia l,M M U L T (m a trix p o w e r(tra n s itio n ,y e a r),p a y o f f 2 ),M M U L T (in itia l,M M U L T (m a trix p o w e r(tra n s itio n ,y e a r),p a y o f f 1 )IR R o f e x p e c te d p a y o ffsB o n d E x p e c t e d R e t u r n a n d R e c o v e r y R a t eB o n d p r i c e = 1 0 0 % , B o n d R a t i n g = B , C o u p o n = 7 . 0 0 %1 0 0 %9 0 %8 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %0 %1 %2 %3 %4 %5 %6 %7 %8 %A B C D E F G H I J K1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 860% 5.03% 7%70% 5.46% 7%80% 5.88% 7%90% 6.30% 7%100% 6.71% 7%Note: The data table has a series with thecoupon rate appended so that in the graph wecan see the convergence of the bond expectedreturn to the coupon rate (cells C30:C40)Graph title below (notice use of text functions):Bond Expected Return and Recovery RateBond price = 100%, Bond Rating = B, Coupon= 7.00%B o n d E x p e c t e d R e t u r n a n d R e c o v e r y R a t eB o n d p r i c e = 1 0 0 % , B o n d R a t i n g = B , C o u p o n = 7 . 0 0 %1 0 0 %9 0 %8 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %0 %1 %2 %3 %4 %5 %6 %7 %8 %A B C D E F G H I J K363738394041424344454647UN - new chapter 17, DPage 910 11 12 13 14 15 16 17 18 19 20 21 220.0000 0.0000 0.0000 0.0000 0.0000 0 0 0 0 0 0 0 0L M N O P Q R S T U V W X1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 10L M N O P Q R S T U V W X363738394041424344454647UN - new chapter 17, DPage 1123 24 25 26 27 28 29 30 31 32 33 34 350 0 0 0 0 0 0 0 0 0 0 0 0Y Z AA AB AC AD AE AF AG AH AI AJ AK1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 12Y Z AA AB AC AD AE AF AG AH AI AJ AK363738394041424344454647UN - new chapter 17, DPage 1336 37 38 39 400 0 0 0 0AL AM AN AO AP1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 14计算债券的期望收益率债券价格 100.00% Payoff (t b o n d te rm ,0 ,IF ( y e a r= b o n d te rm ,M M U L T (in itia l,M M U L T (m a trix p o w e r(tra n s itio n ,y e a r),p a y o f f 2 ),M M U L T (in itia l,M M U L T (m a trix p o w e r(tra n s itio n ,y e a r),p a y o f f 1 )期 望 补 偿 的 IR R债 券 期 望 收 益 和 回 收 率债 券 价 格 = 1 0 0 % , 债 券 等 级 = B ,票 面 利 率 = 7 . 0 0 %1 0 0 %9 0 %8 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %0 %1 %2 %3 %4 %5 %6 %7 %8 %回 收 率 （ % ）期望收益率（%）A B C D E F G H I J K1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 1560% 5.03% 7%70% 5.46% 7%80% 5.88% 7%90% 6.30% 7%100% 6.71% 7%注意: 数据表格中附加了一列的票面利率所以在图表中我们可以看到债券的期望收益率收敛于票面利率（单元格C30:C40）Graph title below (notice use of text functions):债券期望收益和回收率债券价格 = 100%, 债券等级 = B,票面利率=7.00%债 券 期 望 收 益 和 回 收 率债 券 价 格 = 1 0 0 % , 债 券 等 级 = B ,票 面 利 率 = 7 . 0 0 %1 0 0 %9 0 %8 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %0 %1 %2 %3 %4 %5 %6 %7 %8 %回 收 率 （ % ）期望收益率（%）A B C D E F G H I J K363738394041424344454647UN - new chapter 17, DPage 1610 11 12 13 14 15 16 17 18 19 20 21 220.0000 0.0000 0.0000 0.0000 0.0000 0 0 0 0 0 0 0 0L M N O P Q R S T U V W X1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 17L M N O P Q R S T U V W X363738394041424344454647UN - new chapter 17, DPage 1823 24 25 26 27 28 29 30 31 32 33 34 350 0 0 0 0 0 0 0 0 0 0 0 0Y Z AA AB AC AD AE AF AG AH AI AJ AK1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 19Y Z AA AB AC AD AE AF AG AH AI AJ AK363738394041424344454647UN - new chapter 17, DPage 2036 37 38 39 400 0 0 0 0AL AM AN AO AP1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 21CALCULATING THE EXPECTED BOND RETURNBond price 100.00% Payoff (t b o n d te rm ,0 ,IF ( y e a r= b o n d te rm ,M M U L T (in itia l,M M U L T (m a trix p o w e r(tra n s itio n ,y e a r),p a y o f f 2 ),M M U L T (in itia l,M M U L T (m a trix p o w e r(tra n s itio n ,y e a r),p a y o f f 1 )IR R o f e x p e c te d p a y o ffsB o n d E x p e c t e d R e t u r n a n d R e c o v e r y R a t eB o n d p r i c e = 1 0 0 % , B o n d R a t i n g = C , C o u p o n = 7 . 0 0 %1 0 0 %9 0 %8 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %- 2 0 %- 1 5 %- 1 0 %- 5 %0 %5 %1 0 %A B C D E F G H I J K1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 2260% -2.66% 7%70% -0.62% 7%80% 1.43% 7%90% 3.49% 7%100% 5.55% 7%Note: The data table has a series with thecoupon rate appended so that in the graph wecan see the convergence of the bond expectedreturn to the coupon rate (cells C30:C40)Graph title below (notice use of text functions):Bond Expected Return and Recovery RateBond price = 100%, Bond Rating = C, Coupon= 7.00%B o n d E x p e c t e d R e t u r n a n d R e c o v e r y R a t eB o n d p r i c e = 1 0 0 % , B o n d R a t i n g = C , C o u p o n = 7 . 0 0 %1 0 0 %9 0 %8 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %- 2 0 %- 1 5 %- 1 0 %- 5 %0 %5 %1 0 %A B C D E F G H I J K363738394041424344454647UN - new chapter 17, DPage 2310 11 12 13 14 15 16 17 18 19 20 21 220.0000 0.0000 0.0000 0.0000 0.0000 0 0 0 0 0 0 0 0L M N O P Q R S T U V W X1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 24L M N O P Q R S T U V W X363738394041424344454647UN - new chapter 17, DPage 2523 24 25 26 27 28 29 30 31 32 33 34 350 0 0 0 0 0 0 0 0 0 0 0 0Y Z AA AB AC AD AE AF AG AH AI AJ AK1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 26Y Z AA AB AC AD AE AF AG AH AI AJ AK363738394041424344454647UN - new chapter 17, DPage 2736 37 38 39 400 0 0 0 0AL AM AN AO AP1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 28计算期望债券收益债券价格 100.00% Payoff (t b o n d te rm ,0 ,IF ( y e a r= b o n d te rm ,M M U L T (in itia l,M M U L T (m a trix p o w e r(tra n s itio n ,y e a r),p a y o f f 2 ),M M U L T (in itia l,M M U L T (m a trix p o w e r(tra n s itio n ,y e a r),p a y o f f 1 )期 望 补 偿 的 IR R债 券 期 望 收 益 和 回 收 率债 券 价 格 = 1 0 0 % , 债 券 等 级 = C , 票 面 利 率 = 7 . 0 0 %1 0 0 %9 0 %8 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %- 2 0 %- 1 5 %- 1 0 %- 5 %0 %5 %1 0 %回 收 率 （ % ）期望收益率（%）A B C D E F G H I J K1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 2960% -2.66% 7%70% -0.62% 7%80% 1.43% 7%90% 3.49% 7%100% 5.55% 7%注意: 数据表格中附加了一列的票面利率所以在图表中我们可以看到债券的期望收益率收敛于票面利率（单元格C30:C40）Graph title below (notice use of text functions):债券期望收益和回收率债券价格 = 100%, 债券等级 = C, 票面利率 =7.00%债 券 期 望 收 益 和 回 收 率债 券 价 格 = 1 0 0 % , 债 券 等 级 = C , 票 面 利 率 = 7 . 0 0 %1 0 0 %9 0 %8 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %- 2 0 %- 1 5 %- 1 0 %- 5 %0 %5 %1 0 %回 收 率 （ % ）期望收益率（%）A B C D E F G H I J K363738394041424344454647UN - new chapter 17, DPage 3010 11 12 13 14 15 16 17 18 19 20 21 220.0000 0.0000 0.0000 0.0000 0.0000 0 0 0 0 0 0 0 0L M N O P Q R S T U V W X1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 31L M N O P Q R S T U V W X363738394041424344454647UN - new chapter 17, DPage 3223 24 25 26 27 28 29 30 31 32 33 34 350 0 0 0 0 0 0 0 0 0 0 0 0Y Z AA AB AC AD AE AF AG AH AI AJ AK1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 33Y Z AA AB AC AD AE AF AG AH AI AJ AK363738394041424344454647UN - new chapter 17, DPage 3436 37 38 39 400 0 0 0 0AL AM AN AO AP1234567891011121314151617181920212223242526272829303132333435UN - new chapter 17, DPage 35CALCULATING THE EXPECTED BOND RETURNBond price 88.00% Payoff (t b o n d te rm ,0 ,IF ( y e a r= b o n d te rm ,M M U L T (in itia l,M M U L T (m a trix p o w e r(tra n s itio n ,y e a r),p a y o f f 2 ),M M U L T (in itia l,M M U L T (m a trix p o w e r(tra n s itio n ,y e a r),p a y o f f 1 )IR R o f e x p e c te d p a y o ffsB o n d E x p e c t e d R e t u r n a n d R e c o v e r y R a t eB o n d p r i c e = 8 8 % , B o n d R a t i n g = B , C o u p o n = 7 . 0 0 % , Y T M = 9 . 1 8 3 %1 0 0 %9 0 %8 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %2 .5 %3 .5 %4 .5 %5 .5 %6 .5 %7 .5 %8 .5 %9 .5 %A B C D E F G H I J K12345678910111213141516171819202122232425262728293031323334UN - new chapter 17, DPage 3650% 6.30% 7%60% 6.87% 7%70% 7.43% 7%80% 8.00% 7%90% 8.55% 7%100% 9.10% 7%Note: The data table has a series with thecoupon rate appended so that in the graph wecan see the convergence of the bond expectedreturn to the coupon rate (cells C30:C40)Graph title below (notice use of text functions):Bond Expected Return and Recovery RateBond price = 88%, Bond Rating = B,Coupon = 7.00% , YTM = 9.183%YTM computation Cash flow0 -88.00%1 7%2 7%3 7%4 7%5 7%6 7%7 7%8 107%YTM 9.18% b o n d te rm ,0 ,IF ( y e a r= b o n d te rm ,M M U L T (in itia l,M M U L T (m a trix p o w e r(tra n s itio n ,y e a r),p a y o f f 2 ),M M U L T (in itia l,M M U L T (m a trix p o w e r(tra n s itio n ,y e a r),p a y o f f 1 )期 望 补 偿 的 IR R债 券 期 望 收 益 率 和 回 收 率债 券 价 格 = 8 8 % , 债 券 等 级 = B ,票 面 利 率 = 7 . 0 0 % , 到期 收 益 率 （ Y T M ） = 9 . 1 8 3 %1 0 0 %9 0 %8 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %2 .5 %3 .5 %4 .5 %5 .5 %6 .5 %7 .5 %8 .5 %9 .5 %回 收 率 （ % ）期望收益率（%）A B C D E F G H I J K12345678910111213141516171819202122232425262728293031323334UN - new chapter 17, DPage 4350% 6.30% 7%60% 6.87% 7%70% 7.43% 7%80% 8.00% 7%90% 8.55% 7%100% 9.10% 7%注意: 数据表格中附加了一列的票面利率所以在图表中我们可以看到债券的期望收益率收敛于票面利率（单元格C30:C40）Graph title below (notice use of text functions):债券期望收益率和回收率债券价格 = 88%, 债券等级 = B,票面利率 = 7.00%, 到期收益率（YTM） = 9.183%YTM 计算 现金流0 -88.00%1 7%2 7%3 7%4 7%5 7%6 7%7 7%8 107%YTM 9.18% $ B $ 6 ,0 ,IF (E 3 1 = $ B $ 6 ,M M U L T (in itia l,M M U L T (m a trix p o w e r(tra n s itio n ,E 3 0 ),p a y o f f 2 ),A n n u a liz e d IR R o f e x p e c te dp a y o ffs c o m p u te d u s in g a c tu a ld a te s w ith X I R R .C o u p o n p a id s e m ia n n u a lly1 0 0 %9 0 %8 0 %7 0 %6 0 %5 0 %4 0 %0 %2 %4 %6 %8 %1 0 %1 2 %1 4 %1 6 %1 8 %2 0 %R e c o v e r y p e r c e n t a g e lA B C D E F1234567891011121314151617181920212223242526272829303132333435363738394041424344454670% 8.52% 14.78%75% 10.28% 14.78%80% 12.15% 14.78%85% 14.15% 14.78%90% 16.27% 14.78%95% 18.53% 14.78%Cell B41 contains the data table header =B33step 5%YTM calculationYear 20-七月-0515-十一月-0515-三月-06 15-九月-06 15-三月-07Expected payoffs -0.8039 0.0528 0.0528 0.0528 0.0528Annualized YTM 14.78%1 0 0 %9 0 %8 0 %7 0 %6 0 %5 0 %4 0 %0 %2 %4 %6 %8 %1 0 %1 2 %1 4 %1 6 %1 8 %2 0 %R e c o v e r y p e r c e n t a g e lA B C D E F474849505152535455565758596061626364656667Payoff (t $ B $ 6 ,0 ,IF (E 3 1 = $ B $ 6 ,M M U L T (in itia l,M M U L T (m a trix p o w e r(tra n s itio n ,E 3 0 ),p a y o f f 2 ),1 0 0 %9 0 %8 0 %7 0 %6 0 %5 0 %4 0 %0 %2 %4 %6 %8 %1 0 %1 2 %1 4 %1 6 %1 8 %2 0 %R e c o v e r y p e r c e n t a g e lG H I J K L M N12345678910111213141516171819202122232425262728293031323334353637383940414243444546AMR Bond Expected Return vs. Recovery RateBond price = 77%, Bond Rating = CCC,Coupon = 10.55%, YTM = 14.78%15-九月-0715-三月-08 15-九月-0815-三月-0915-九月-0915-三月-10 15-九月-1015-三月-110.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.05281 0 0 %9 0 %8 0 %7 0 %6 0 %5 0 %4 0 %0 %2 %4 %6 %8 %1 0 %1 2 %1 4 %1 6 %1 8 %2 0 %R e c o v e r y p e r c e n t a g e l G H I J K L M N47484950515253545556575859606162636465666713 14 15 16 17 18 19 20 2115-九月-1115-三月-1215-九月-1215-三月-1315-九月-1315-三月-1415-九月-1415-三月-1515-九月-150.0188 0.0178 0.0169 0.0161 0.0154 0.0148 0.0142 0.0137 0.0132O P Q R S T U V W1234567891011121314151617181920212223242526272829303132333435363738394041424344454615-九月-1115-三月-1215-九月-1215-三月-1315-九月-1315-三月-1415-九月-1415-三月-1515-九月-150.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528O P Q R S T U V W47484950515253545556575859606162636465666722 23 24 25 26 27 28 29 3015-三月-1615-九月-1615-三月-1715-九月-1715-三月-1815-九月-1815-三月-1915-九月-1915-三月-200.0128 0.0124 0.0120 0.0116 0.0113 0.0110 0.0107 0.0105 0.0102X Y Z AA AB AC AD AE AF1234567891011121314151617181920212223242526272829303132333435363738394041424344454615-三月-1615-九月-1615-三月-1715-九月-1715-三月-1815-九月-1815-三月-1915-九月-1915-三月-200.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528 0.0528X Y Z AA AB AC AD AE AF47484950515253545556575859606162636465666731 32 33 34 35 36 37 38 3915-九月-2015-三月-2115-九月-2115-三月-2215-九月-2215-三月-2315-九月-2315-三月-2415-九月-240.0100 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000AG AH AI AJ AK AL AM AN AO1234567891011121314151617181920212223242526272829303132333435363738394041424344454615-九月-2012-三月-2115-九月-2115-三月-2215-九月-2215-三月-2315-九月-2315-三月-2415-九月-240.0528 1.0528 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000AG AH AI AJ AK AL AM AN AO47484950515253545556575859606162636465666740 41 42 43 44 45 46 47 4815-三月-250.0000AP AQ AR AS AT AU AV AW AX1234567891011121314151617181920212223242526272829303132333435363738394041424344454615-三月-250.0000AP AQ AR AS AT AU AV AW AX47484950515253545556575859606162636465666749 50 51 52 53 54 55 56 57AY AZ BA BB BC BD BE BF BG12345678910111213141516171819202122232425262728293031323334353637383940414243444546AY AZ BA BB BC BD BE BF BG47484950515253545556575859606162636465666758 59 60 61 62 63 64 65 66BH BI BJ BK BL BM BN BO BP12345678910111213141516171819202122232425262728293031323334353637383940414243444546BH BI BJ BK BL BM BN BO BP47484950515253545556575859606162636465666767 68 69 70 71 72 73 74 75BQ BR BS BT BU BV BW BX BY12345678910111213141516171819202122232425262728293031323334353637383940414243444546BQ BR BS BT BU BV BW BX BY47484950515253545556575859606162636465666776 77 78 79 80 81 82 83 84BZ CA CB CC CD CE CF CG CH12345678910111213141516171819202122232425262728293031323334353637383940414243444546BZ CA CB CC CD CE CF CG CH47484950515253545556575859606162636465666785 86 87 88 89 90 91 92 93CI CJ CK CL CM CN CO CP CQ12345678910111213141516171819202122232425262728293031323334353637383940414243444546CI CJ CK CL CM CN CO CP CQ47484950515253545556575859606162636465666794 95 96 97 98 99 100 101 102CR CS CT CU CV CW CX CY CZ12345678910111213141516171819202122232425262728293031323334353637383940414243444546CR CS CT CU CV CW CX CY CZ474849505152535455565758596061626364656667103 104 105 106 107 108 109 110 111DA DB DC DD DE DF DG DH DI12345678910111213141516171819202122232425262728293031323334353637383940414243444546DA DB DC DD DE DF DG DH DI474849505152535455565758596061626364656667112 113 114 115 116 117 118 119 120DJ DK DL DM DN DO DP DQ DR12345678910111