MicrosoftExcel在债券定价上的应用

1、债券教室Microsoft Excel在债券定价上的应用(五)收益率曲线的制作方法(一)三次样条拟合法在固息债、浮息债以及含权债的定价中，读者反复地看到两个概念：收益率曲线和利率 期限结构，不熟悉的读者很容易对这两个概念造成混淆，而实际上它们的定义截然不同，在债券市场上的应用也完全不一样，因此读者有必要进行区分。在本期债券教室中， 我们将首先向大家介绍收益率曲线的概念并用Excel演示收益率曲线的制作方法。收益率曲线的认识(Yield Curve)收益率曲线描绘的是附息债券的到期收益率(Yield to Maturity)与剩余年期的关系，横轴表示基准债券的剩余年期，纵轴表示不同剩余年期相对应

2、的到期收益率。曲线上的某一点代表着：如果将该点所对应的剩余年期的债券持有到期，期间所有的利息收入和期末返还的本金所带来的内部投资报酬率(Internal Rate of Return)为多少。收益率曲线的作用在于：对于任何一个剩余年期，在收益率曲线上都可以找到相对应的合理到期收益率。如果市场中某只债券的到期收益率大幅偏离收益率曲线，则说明有可能定价不合理，存在套利机会。需要注意的是：对于浮息债和含权债无法制作收益率曲线，因为浮息债和含权债在未来的现金流是不确定的，无法用以计算到期收益率。收益率曲线制作方法(一)三次样条拟合法(1)曲线拟合函数曲线拟合函数定义为(x为债券剩余年期，y为债券的到期

3、收益率)：y = a + bx + cx之+ dx3公式(1)其中，a、b、c、d都是参数，采用最小二乘法求解。(2)目标函数最小二乘法的目标函数为：一2、min (y - y)公式(2)其中，y为市场收益率，?为透过样条法计算出来的理论收益率。(3)优化方式选取市场中不同到期期限的基准债券的到期收益率，通过最小二乘法计算出a、b、c、d,求得收益率曲线。在Excel中制作收益率曲线的方法(一)三次样条拟合法(1)选取样本，计算到期收益率以制作上交所企业债的收益率曲线为例，我们选取出20个样本(在此基于演示方便)，分别列示出它们的剩余年期和到期收益率，一种方法是根据我们在前几期金融工程报中介绍

4、的收益率计算方法自行计算，具体过程在此不赘述，仅于下表中显示计算结果：另一种方法非常简单， 我们可以直接从“红顶收益战略家 本的剩余年期和收益率字段，操作方法参见以下画面：5.0”中直接导入这20个样立伸归报伽口行沽变跋d观毒曲理券皿帝陛疝都0结欧缶投蛆肥憧户gj篇蜿w视街怙wsh&o-! x|as尽丁宗回歪|欧贸团J5?芟市届3品种3报价才式|瞄宰3、咨,胃，* 女，120001弗宝棚1201QLQI中移动1201020L三峡债12020L三峡债120202DQ中移（511202030冲交通120205赚城技12020&口浦阳1120207。浦明11

5、202SS口债12030112030203苏园建120303。3三焕债120304临电网11120305Q3电网12030603中电投名称剩余年限收益率I0,092,30 7H5.943.9091111.334528817.204一沮32.303,20921Z.304565912.34.5741!7-4?4.3147,332.3777333.941 ?B.au4.1118。1Z.6?4424B-024.464928.0G4JB302B.4B4,4478&484.0698132457627.S24.3G?7120307。斓发债-I(2)设置曲线拟合函数，计算最小二乘法参数根据三次样条拟合

6、法，假设理论收益率满足公式(1),首先设置参数a=0, b=0, c=0, d=0,分别计算出各债券的理论收益率接着根据公式(2)，计算最小二乘法的参数， 即实际收益率与理论收益率的残差的平方, 从而得到最小目标函数E。有了目标函数，就可以利用规划求解计算出四个参数键选中单元格F20后，点击“工具”菜单中的“规划求解”(3)规划求解计算参数$C$24:$C$27,然后点击求解按钮:并将可变单元格设置为a, b, c, d所对应的四个单元格在Excel中将出现如下计算结果，即利用最小二乘法计算出的a, b, c, d四个参数。a, b, c, do用鼠标或键盘方向 ,在如下对话框中点选最小值，(

7、4)绘制收益率曲线和散点将收益率曲线的横轴分为30年(符合市场标准)，每隔0.5年取一个点，利用求解的曲线拟合方程计算出每个点对应的理论收益率，并将原有样本的剩余年期和对应的市场收益率列不在右边。Xj亶拼促】碗围图（X） BA d）格式。 XK（I）位据叫占口也）帮助叫-aX母 s12EEi * Tl-F=AndbljD ?luaiM ! s剑H111&叱序目国 q| 6里.眉壹1密fF -JElABC | DEFGHIJ _J曲姓拟会方器a .j,3By LT + GA十 LZA曲姓拟含参就2.299590216b0 0277772C402632P68dO.OOn3DS2剩泉年期理论

8、惧益率秉愉年期理谕收益率 弟除年期理谕收益率刺廉年期市场收芨平Q229959021QJ4J2854Q1214 44 皿 8390.098627585 .51142493*821.54 &加心5.95344.叩幻12L?07tM14ll.J4 咨的皿 5224J44P54 瓣1124J24.舶储152A91963124 却 6775622.54.4493597-417.2114.6663323HRMS51712.54 部 4| 3“234457S7 明2.3ri32162343.53J2fiO848 13438775IW33.54A699B93512315946 俊353337(M34J

9、fi 13.J4JEU26B 244 M 网第 7112.4334心渤36353S15O935 M4J8S319224.54508894197.430147144374343523714.54380011625453614310.3372.68823S4.53.7620C75154J724B1135.54369D92744.3374.QM63953B7O&22J15.54占6#1篮立264 WWCM46.SJ3742 m40553J69&4SS 164 52261126.54J&53603812.62744.海4164J6OD5116.543403261274.7059

10、19238.03564.4798426.541416257174327(577527.54.7(554449523,0744.8P174374J1JQS7517.JUL4 皿 72-1JB3255797如如 44J10644734JSD8IS4S0174928.54907(5356a.4fl045JDRSL9J927.5264 37234794A3A1331$.J4砧 3733D5.LE4429258737J.00348954470M5S3Q4AS79309491045024921 20J445085224-$C$24戚新 5*39t$C*+D4?电玲渤*1 舟 3| 用卜心_收州车曲挂制忤_

11、摆吐.到期收益车 牧盖车曲残E收益车曲线:Khl_r桂图也） 译自逸图定圈”、口。国词 4 胡鸟-r土 三窝 wk大剧 g数字用鼠标或键盘方向键选中插入图表的区域，点击“插入”菜单中的“图表”，以上述30年的剩余年期系列为x轴，对应的理论收益率系列为y轴，建立一条平滑曲线， 这条曲线就 是我们制作的收益率曲线。同时，以右侧列示样本的剩余年期系列为x轴，对应的市场收益率系列为y轴，往收益率曲线图表中添加入对应的散点。这样就可以直观地观测到曲线的拟合效果。(5)曲线的修正由于我们直接将市场上的所有债券价格拿来拟合收益率曲线，效果很可能会不理想，因此在实际制作中，还需要充分考虑以下四个因素并对曲线进行修正：该用哪些债券作为样本如何处理债券流动性