chapter25
UN-15BBASIC DURATION CALCULATIONYTM 7%Year Ct,At*Ct,A /PriceA*(1+YTM)t Ct,Bt*Ct,B /PriceB*(1+YTM)t1 70 0.0654 130 0.0855 2% 10.78653% 9.66774% 9.01105% 8.57926% 8.27367% 8.04599% 7.729413% 7.370715% 7.259317% 7.1729graph titleEffect of Coupon on DurationMaturity = 21, YTM = 15.00%A B C12345678910111213141516171819202122232425262728293031323334Page 678Page 102% 10.78653% 9.66774% 9.01105% 8.57926% 8.27367% 8.04599% 7.729413% 7.370715% 7.259317% 7.1729标题票面利率对久期的影响期限 = 21, YTM = 15.00%A B C12345678910111213141516171819202122232425262728293031323334Page 678 (中文)Page 1210 7.482715 8.814820 9.039825 8.788130 8.446140 7.966950 7.766860 7.697770 7.675980 7.6693graph titleEffect of Coupon on DurationCoupon rate = 4.0%, YTM = 15.00%A B C123456789101112131415161718192021222324252627282930313233Page 679Page 1410 7.482715 8.814820 9.039825 8.788130 8.446140 7.966950 7.766860 7.697770 7.675980 7.6693标题债券期限对久期的影响票面利率 = 4.0%, 到期收益率（YTM） = 15.00%A B C123456789101112131415161718192021222324252627282930313233Page 679 (中文)Page 16- =B9 , data table header债 券 期 限 对 久 期 的 影 响票 面 利 率 = 4 .0 % , 到 期 收 益 率 （ Y T M ） = 1 5 .0 0 %8 07 06 05 04 03 02 01 0001234567891 0期 限久期债券期限对久期的影响- =DATE(1996,5,21)- =DATE(1996+B3,5,21)到期收益率（如，折现率）- =DURATION(B2,B4,B6,B5,1)债券期限对久期的影响票面利率 = 4.0%, 到期收益率（YTM） = 15.00%D123456789101112131415161718192021222324252627282930313233Page 681Page 17DURATION OF BOND WITH UNEVEN PERIODSBrute Force Calculation and Dduration functionAlpha 0.3 Time until first coupon payment (in years)N 5 Number of paymentsYTM 6%Coupon 100Face 1,000Bond price 1,217 - =NPV(B4,B10:B14)*(1+B4)(1-B2)Period Payment t*Ct /Price*(1+YTM)t0.3 100 0.0242 - =(B10*A10)/(1+$B$4)A10/$B$71.3 100 0.09902.3 100 0.16533.3 100 0.22374.3 1,100 3.0249Duration 3.5371 - =SUM(C10:C14)Newly defined VBA function 3.5371 - =dduration(B3,B5/B6,B4,B2)Checking on formula: the bond duration (with first payment at alpha) should be the durationof the bond with payments at 1, 2, ., 5 plus (alpha-1):testing whether Excels duration function produces the same result:settlement 1996/5/22maturity 2000/9/8alpha 0.301369863coupon 10%ytm 6%3.5315 - =DURATION(C26,C27,C29,C30,1)Our formula 3.5371A B C D12345678910111213141516171819202122232425262728293031323334Page 681Page 18month 9day 8- =dduration(5,10%,6%,B2)Checking on formula: the bond duration (with first payment at alpha) should be the durationE F12345678910111213141516171819202122232425262728293031323334Page 681 (中文)Page 19非均匀支付债券久期Brute Force计算和Dduration 函数（Alpha） 0.3直到第一次支付时的时间（年）N 5支付次数到期收益率（YTM） 6%票面利息 100面值 1,000债券价格 1,217 - =NPV(B4,B10:B14)*(1+B4)(1-B2)时期 支付 t*Ct /价格*(1+YTM)t0.3 100 0.0242 - =(B10*A10)/(1+$B$4)A10/$B$71.3 100 0.09902.3 100 0.16533.3 100 0.22374.3 1,100 3.0249久期 3.5371 - =SUM(C10:C14)新定义的VBA函数 3.5371 - =dduration(B3,B5/B6,B4,B2)检查公式: 债券久期 (在Alpha时刻第一次支付)应该为债券支付在1, 2, ., 5期的久期相加 (alpha-1):检验Excel的duration函数是否得到同样的结果:起点 1996/5/22到期日 2000/9/8alpha 0.301369863票面利率 10%到期收益率（ytm） 6%3.5315 - =DURATION(C26,C27,C29,C30,1)公式 3.5371A B C D12345678910111213141516171819202122232425262728293031323334Page 681 (中文)Page 20月份 9天数 8- =dduration(5,10%,6%,B2)E F12345678910111213141516171819202122232425262728293031323334USING XIRR TO CALCULATE THE IRR WITHUNEVEN PAYMENTSCurrent date 3-十月-96Annual coupon 89 Paid January 1 for each of next 5 yearsMaturity date 1-一月-01Face value 1,000Price of bond 1,123Time to first payment 0.2466 - =(B12-B11)/365Date Payment3-十月-96 -1,1231-一月-97 891-一月-98 891-一月-99 891-一月-00 891-一月-01 1,089YTM 7.300% - =XIRR(C11:C16,B11:B16)A B C D123456789101112131415161718使用XIRR函数来计非均匀支付债券的内含报酬率（IRR）当前日期 3-十月-96年利息 89下5年每年1月1日支付到期日 1-一月-01面值 1,000债券价格 1,123从现在到首次支付的时间 0.2466 - =(B12-B11)/365日期 支付3-十月-96 -1,1231-一月-97 891-一月-98 891-一月-99 891-一月-00 891-一月-01 1,089YTM 7.300% - =XIRR(C11:C16,B11:B16)A B C D123456789101112131415161718Page 686, topPage 23ILLUSTRATION OF CALCULATIONOF YTM OF UNEVEN PERIODSThis spreadsheet illustrates the unevenYTM VBA function:the syntax of this function isunevenYTM(CouponRate,FaceValue,BondPrice,NumPayments,TimeFirst,epsilon)Coupon rate 7.90%Face value 1,000.00Bond price 1,123.00Number of payments 5Time to first payment 0.25Epsilon 0.00001 - Controls the accuracy of the YTM calculationYTM 6.138% - =unevenYTM(B3,B4,B5,B6,B7,B8)A B C12345678910111213141516171819202122232425262728293031Page 686, top (中文)Page 24非均匀支付债券YTM计算实例本表格演示了计算非均匀支付债券的YTM 的VBA 函数:函数的句法： 非均匀支付债券YTM(票面利率,面值,债券价格,支付次数,首次支付前的时间,最小正数)票面利率 7.90%面值 1,000.00债券价格 1,123.00支付次数 5首次支付前的时间 0.25最小正数 0.00001 - 控制 YTM 计算的精确性YTM 6.138% - =unevenYTM(B3,B4,B5,B6,B7,B8)A B C123456789101112131415161718192021222324252627282930Page 686, bottomPage 25USING DDURATION AND UNEVENYTM TOGETHERCoupon rate 7.90%Face value 1,000.00Bond price 1,123.00Number of payments 5Time to first payment 0.25Epsilon 0.00001 - Controls the accuracy of the YTM calculationYTM 6.138% - =unevenYTM(B2,B3,B4,B5,B6,B7)Duration 3.5959 - =dduration(B5,B2,B9,B6)A B C1234567891011Page 686, bottom (中文)Page 26将DDURATION 和 UNEVENYTM 联合使用票面利率 7.90%面值 1,000.00债券价格 1,123.00支付次数 5首次支付前的时间 0.25最小正数 0.00001 - 控制 YTM 计算的精确性YTM 6.138% - =unevenYTM(B2,B3,B4,B5,B6,B7)久期 3.5959 - =dduration(B5,B2,B9,B6)A B C1234567891011
