寿险精算工作表建模工作簿建模过程说明.doc

9e7b0a141c3811767ac906a63c611201.pdf1从分期付款模型到寿险产品定价说明基本思路：在机房用 Excel，建模和推公式同步推进，把寿险精算的数学公式和精算控制循环融为一体预备部分1、 是否需要重温一下利息理论里面的几个基本算法，通过 Excel 建模来巩固概念，并为后续课程做好准备；2、 对精算符号的熟悉程度和理解程度这里要补充一个说明团体型个体型精算公式分类的幻灯片，例子是 和 的递推关系的四种等价表达方式与解释xA1第一部分：精算师是干什么的？用姚律翻译的《精算内控系统》一篇的图和材料来说明精算师的工作职责与技能：以精算控制循环为框架，从职责和技能组合来反推课程学习的内容抵押贷款在建模方面可以充当寿险产品的原型，但是两者的相似性比建模上的一致性深刻得多，换个角度说，由于在建模方面具备一致性，所以两者具有更多的相似性从静态特征来看，在定价和资产负债评估方面是相同的，从动态特征来看，动态的管理和业务组合面临的困难是相似的在证券化方面，抵押贷款的证券化走在前面，寿险产品的证券化也已经有了十几年的历史，现在有扩大的趋势就是说，从这个小例子里面，我们可以看到所谓的“金融一体化”的整个进程以及这个进程的业务基础和建模基础。

引子：不使用公式的分期付款计算器思考题：抵押贷款在房价波动下形成的负资产，和寿险业高利率保单造成的亏损，两者之间的共同点如何？学过下面的内容，大家就能够理解其中的关键了教学目的：这是用 Excel 工作表实现事件驱动现金流模型的原型展示从中获得的建模观念和 Excel 建模操作技巧适用于以后的各个例子，夸张一点说，把这个例子吃透弄懂，也就掌握了依赖 Excel 工作表的精算日常实务工作而这些实务工作曾经占据了精算师的大部分工作时间，现在有所下降，但大体上还是很重要的这里还没有包括借助 Excel VBA的建模活动使用这个引子的原因：破除精算特殊论这个例子遵循的是通用建模原则，经过放大和改造就能够自然覆盖大量的精算建模工作模型是对现实的模拟，总会包含对现实过程的抽象，也会忽略建模者认为不太重要的细节（这种忽略有其合理性，但也可能造成灾难） 建模是一个持续的反复试错过程，英文 Modeling 是动名词在这个过程中，建模者必须为抽象原型填充细节，让整个模型越来越接近真实业务流程，越来越能反映模型用户的需求和外部约束通用的 Unified Financial Analysis 建模概念框架，简写为 UFA】合同约定的不同（等额还款与等额本金两种）UFA 的五个输入量，五个输出量。

如果建模对象相对简单，可以忽略或者简化处理部分变量基本是：事件驱动现金流模型事件一词的英文是 Event，在会计术语中翻译为事项9e7b0a141c3811767ac906a63c611201.pdf2UFA 框架是一些软件工程师们总结出来的通用分析框架交易对手风险因素 财务合同 行为财务事件Event E1 E2 E3 En流动性LaR EaR VaR敏感性收支价值下面是非财务合同决定的现金流抽象出 5 个输入量和 5 个输出量，输入量包括1. Contracts：合同2. Risk factors：风险因素3. Counterparties：交易对手4. Behavioral assumptions：行为假设9e7b0a141c3811767ac906a63c611201.pdf35. Cost：成本输出变量包括：1. Expected liquidity cash flows：期望流动性和现金流2. Value：价值3. Income：利润4. Sensitivity：敏感性5. Risk：风险分析这样的一个框架结合通用的“账户递推”原型，就可以很好地描述大部分金融交易，对于和金融产品有关的课程来说，以及够用了。

实际上，这个模型经过扩展之后，已经成为给金融产品和业务编写软件的分析框架我们要牢记一点：在给企业级任务和流程建模方面，软件工程师们已经取得了很大的进展，并且完成了很多成功案例，而其他专业或者学科在这个方面是望尘莫及的1. 概念原型【金融交易的原则描述，交易双方，交换现金流组合，贷款利息优先支付】a) 可以用文字和图表两种方式来描述概念原型或者是包含文字的公式在一期之内，可以有下面的公式：本期期末贷款余额＝本期期初贷款余额×（1＋本期有效贷款利率）－本期还款额这个公式也可以，但是可能看不到足够的细节，比如我们可能关心本期还款额中有多少用于支付利息，又有多少用于“偿还本金”工作表建模的基本原则：在模型和用户能够忍受的限度内，分解计算公式这是英文名称 Spreadsheet 的基本精神所在：展开计算过程的细节具体细节到什么程度，取决于用户的需求和建模者的综合判断不过在这里例子中，多几个细节不会造成负担过重根据利息优先原则，首先应该计算的是：本期应偿还利息＝本期期初贷款余额×本期有效贷款利率然后剩下的部分用来偿还本金：本期本金偿还＝本期还款额－本期应偿还利息最后计算贷款余额：本期期末贷款余额＝本期期初贷款余额－本期本金偿还（如果本期还款额不足，即小于本期应偿还利息，就会出现利滚利的情形，现在这套公式可以容纳这种情形。

）在 0 时刻和 1 时刻之间的第 1 期，这样是可以处理的那么，时刻 1 和时刻 2 之间的第 2 期呢？这里有一个简单的处理手法，在还款之后的上一期期末贷款余额也就等于下一期期初的贷款余额然后，整个递推关系就可以滚动发展了这也是处理递推关系（或者数学归纳法）的常用手法，先放慢镜头，看看前两个或者前三个递推关系是怎么连成一体的在 Excel 建模的时候，还要考虑是否就可以复制公式，鼠标是否可以一拖到底我们还可以进一步细化这个概念原型，甚至考虑包含各种不确定性，比如提前还款之类，不过在复杂化之前，可以考虑建立一个非常简化的原型模型 】下面以一个按年分期付款的模型，还款方式为等额还款 （采用按年的原因是这个模型占用的空间比较小，建模者可以一眼看到整个模型的布局） 是否建立一个数学模型，用来说明上述计算过程，这是有必要的，不过我们对手头的这个问题已经足够熟悉，而且上面的文字描述型公式也是够用的9e7b0a141c3811767ac906a63c611201.pdf4贷款额是 10000 元，偿还期 5 年，等额还款方式，每年年末还款，贷款年利率 8%，求年还款额并给出整个还款期内的还款计划。

首先，Excel 提供了一个计算还款额的工作表函数 PMT()借此我们可以熟悉一下有关的几个财务函数有关函数说明和操作步骤见附录 1我们先把现在掌握的基本参数录入到工作表上，Excel 建模的时候，这个阶段不用多琢磨按照标准格式操作就可以了A B B（公式显示模式下）2 贷款额 10000 100003 偿还期 5 54 贷款利率 8.00% 0.085 年还款额 ¥-2,504.56 =PMT(B4,B3,B2,0,0)在单元格 B5 中录入=PMT(B4,B3,B2,0,0)，注意公式中的等号一定不能少或者用插入函数的方法在财务函数类中找到 PMT，然后利用公式向导逐个填入相应的变量这种方法更为常用，原因很简单，许多函数都有很多变量，而且变量之间的次序不能有错我们可以记住函数的功能和大致的样子，但是肯定记不住变量的顺序，所以有必要利用函数向导功能来辅助记忆另一个技巧是切换两种显示模式，也就是上表中的两种显示效果，一个是带有格式的计算结果，看不见公式；另一种是显示不带格式的公式如果工作表模型包含大量公式，则公式显示模式能够为审核、捉虫和理解逻辑关系提供良好的支持切换方式是组合键：Ctrl+~。

~键在横排数字键最左边的 1 的左边，通常就在 Tab 键的正上方注意 PMT 的计算结果是红色的赤字状态，原因是这是借款人要支出的款项在工作表模型的公式中还要大量使用的是“单元格名称”，如果是多个单元格，比如引用表格（比如生命表或者退保率表）中的参数，就要使用“区域名称”使用名称可以让公式更容易理解，即改善工作表模型的可读性我们来看看不使用名称的一个公式：=PMT(B4,B3,B2,0,0)这个公式的括号中都是 A1 格式的单元格，意义很难理解，而且在审核公式是否正确的时候，也会比较费劲，或者说费眼神现在来看通用的最佳实务 Best Practice为了对比使用名称效果，我们把工作表A2:B5 区域复制到 B8 单元格为左上角的区域中，单元格 B11 中的公式会自动变成=PMT(B10,B9,B8,0,0)，这是 Excel 提供的一项强大功能：提供模块的可复制性用鼠标单击单元格 B8，然后把鼠标光标移动到工作表列标上方最左边的格子，停住不动，就可以看到黄色提示条“名称框”，而名称框中显示的是 B8，这是单元格的内部名称，类似于地点的经纬度用用鼠标单击名称框，然后输入汉字“ 贷款额” ，输入结束，回车。

就可以看到名称框中显示的是“贷款额”9e7b0a141c3811767ac906a63c611201.pdf5同样，选择单元格 B9，在名称框中录入 “偿还期”，回车；选择单元格 B10，在名称框中录入“贷款利率” ，回车；选择 B11，在名称框中录入“年还款额”，回车就完成了对四个单元格的名称定义说明：单元格是 Excel 下面的一个对象，它具有众多属性，名称只是其中之一名称和单元格的取值、公式、字体等等，是并列关系就是修改名称不会影响到其他属性打个比方，单元格名称像通讯地址而不是 GPS 的经纬度批量定义名称刚才逐个单元格定义名称，总会让人觉得有些低效和弱智，有没有更高效的方法呢？有不过操作方法在 Excel 2003 和 2007 中略有差别在 2003 中可以通过“插入—名称—指定” 来找到实现批量定义名称的对话框，在 2007 中则需要在“公式—名称”中找到这个对话框实际操作的时候，需要先指定区域，然后再调用这个对话框还是以对 B8-B11 这四个单元格的名称指定为例在很多时候我们都可以设想一个理想的功能，而 Excel 往往就能够实现夸张一点说，是只有想不到，没有做不到的我们在建模型的时候，通常会在参与计算（或者说被其他单元格引用）的单元格的左边写上说明文字，这个说明仅仅是给 Excel 的用户看的。

比如在 A8 中写明“贷款额”，用户能够理解这是 B8 里面的数字的意义，但是仅仅这样做，Excel 并不会在贷款额这个词和 B8 之间建立任何联系为了节省工作量，我们自然希望能够把 A8 里面的文字变成 B8 的名称，最好能够把 A8-A11 里面文字变成 B 列中对应位置单元格的名称Excel 的名称指定功能正好能够实现这个偷懒的想法为了让 Excel 知道名称的来源，在调用功能之间就必须同时选择 A 列和 B 列的相应单元格，如图所示对话框“指定名称” 有一点自动功能，所谓名称创建于最左列，意思就是说，名称的来源是左边这一列比如 A 列中，A8 的文字就会变成B 列中 B8 的名称其余由此类推这样在写完变量说明之后，我们就可以批量定义名称了批量定义除了减少录入量以外，还有一项很好的防错功能：左边列的说明文字和右边列中单元格的名称肯定是一样的而重复录入则有可能导致错误由于单元格 B11 中的公式是在定义了单元格名称之前就已经写好的，所以公式里还是B8B9 之类的单元格名称Excel 也提供了时候补救措施，在 2003 中是“插入—名称—应用”我们在调出这个对话框之后，会发现它已经选择了这四个新定义的名称。

这是 Excel 的自动判断，依据的原则是，还没有在任何9e7b0a141c3811767ac906a63c611201.pdf6公式里出现过的名称都是应用的对象应该注意到， “年还款额”单元格，即 B11，并没有被任何单元格引用不过不要紧，单击“应用名称”对话框上的“。