基于Excel和VBA的高级金融建模.doc

财务精英俱乐部更多资料免费下载目 录前言6致谢7第1章 介绍81.1 金融学概览81.2 收益分布假设91.3 数学和统计方法91.4 数值方法91.5 Excel 解决方案91.6 本书主题101.7 有关Excel工作簿111.8 意见和建议11第2章 高级Excel函数和过程122.1 访问Excel函数122.2 数学类函数132.3 统计类函数142.3.1 使用频率函数Frequency152.3.2 使用分位数函数Quartile162.3.3 使用正态函数Norm172.4 查找类函数182.5 其他类型函数192.6 审核工具202.7 模拟运算表（Data Tables）212.7.1 建立单变量模拟运算表212.7.2 建立双变量模拟运算表222.8 XY图242.9 访问数据分析和规划求解262.10 使用区域名称262.11 回归分析272.12 单变量求解292.13 矩阵代数以及相关函数302.13.1 矩阵介绍312.13.2 矩阵转置312.13.3 矩阵相加322.13.4 矩阵相乘322.13.5 矩阵求逆332.13.6 线性方程组求解342.13.7 Excel矩阵函数小结35小结35第3章 VBA介绍373.1 掌握VBA的好处373.2 VBA的面向对象观点383.3 编写VBA宏393.3.1 简单VBA子程序393.3.2 交互函数MsgBox403.3.3 编写环境413.3.4 输入代码并运行宏423.3.5 录制按键和编辑代码423.4 编程要素443.4.1 变量和数据类型443.4.2 VBA数组变量453.4.3 控制结构473.4.4 控制重复过程473.4.5 在代码中使用Excel和VBA函数493.4.6 编程的一般观点493.5 宏与电子表格之间的通信493.6 子程序实例533.6.1 图表533.6.2 正态概率散点图563.6.3 用规划求解产生有效边界58小结61附录3A Visual Basic编辑器62附录3B 用相对引用模式来录制按键65第4章 编写VBA用户定义函数674.1 简单销售佣金函数674.2 在工作表中创建Commission(Sales)函数684.3 多参数期权定价函数694.4 在VBA中操作数组724.5 数组变量的期望和方差函数724.6 数组变量的组合方差函数754.7 输出数组形式的函数774.8 在用户定义函数中调用Excel和VBA函数784.8.1 在用户定义函数中使用VBA函数784.8.2 加载宏794.9 编写VBA函数的优缺点79小结80附录4A 演示函数如何处理数组80附录4B 二叉树期权定价函数82编写函数练习87第5章 股票的有关简介91第6章 投资组合最优化926.1 组合的均值和方差926.2组合的风险收益表示946.3用规划求解得到有效点946.4求有效边界（黄和利曾伯格的方法）966.5有约束边界组合986.6无风险资产和风险资产的结合996.7问题一 一种无风险资产和一种风险资产的组合1006.8问题二 存在两种风险资产的组合1016.9问题三 一种无风险资产和一个风险投资组合1026.10 Module1中的用户定义函数1046.11 Module1中用于解决三类常见组合问题的函数1056.12模块M中的宏功能107小结108第7章 资产定价1097.1单因素模型1097.2估计系数1107.3资本资产定价模型（CAPM）1127.4方差协方差矩阵1137.5风险值（VaR）1147.6水平财富1167.7正态和对数正态分布矩之间的关系1177.8 Module1中的用户定义函数118小结119第8章 投资组合业绩评价1208.1传统业绩评价方法1208.2 主动被动管理1228.3风格分析（Style Analysis）1248.4简单风格分析1258.5 滚动时段风格分析1268.6风格权重的置信区间1278.7 Module1中的用户定义函数129小结131第9章 股票期权介绍1339.1 布莱克-舒尔斯公式的起源1339.2 布莱克-舒尔斯公式1349.3 对冲投资组合（Hedge Portfolios）1359.4 风险中性定价1369.5 风险中性定价的单期二叉树模型1379.6期权平价关系（Put-Call Parity）1389.7 红利（Dividends）1399.8 美式期权的特征1399.9 数值方法1399.10 波动率和非正态股票收益140小结140第10章 二叉树14210.1 二叉树介绍14210.2 简化的二叉树14310.3 JR二叉树14410.4 CRR树14710.5 二项分布近似与布莱克-舒尔斯公式14810.6 CRR二叉树的收敛性14910.7 LR树15010.8 CRR树与LR树的比较15110.9 美式期权和CRR美式二叉树15210.10 Module0和Module1中的用户定义函数154小结155第11章 布莱克-舒尔斯公式15711.1 布莱克-舒尔斯公式15711.2 在Excel中运用布莱克-舒尔斯公式15811.3 外汇（Currencies）和商品（Commodities）期权15911.4 计算期权的希腊参数16011.5 对冲组合16111.6 布莱克-舒尔斯公式的正式推导16311.7 Module1中的用户定义函数165小结166第12章 欧式期权定价的其它数值方法16812.1 蒙特卡罗模拟介绍16812.2 对偶变量（Antithetic Variables）模拟17012.3 准随机抽样（Quasi-Random Sampling）模拟17012.4 模拟方法比较17212.5 蒙特卡罗 模拟中的希腊参数计算17212.6 数值积分17312.7 Module1中的用户定义函数174小结176第13章 非正态分布和隐含波动率17713.1 非正态分布假设下的布莱克-舒尔斯 公式17713.2 隐含波动率（Implied Volatility）17813.3 调整偏度（Skewness）和峰度（Kurtosis）18013.4 波动率曲线（The Volatility Smile）18113.5 Module1中的用户定义函数183小结185第14章 债券期权定价介绍18714.1 利率期限结构18814.2 附息债券的现金流和到期收益率18814.3 二叉树18914.4 布莱克的债券期权定价公式19014.5 久期和凸性19014.6 符号191小结192第15章 利率模型19315.1 Vasicek利率期限结构模型19315.2 Vasicek模型对零息票债券欧式期权定价19515.3 Vasicek模型对附息债券欧式期权定价19615.4 CIR利率期限结构模型19715.5 CIR模型对零息票债券欧式期权定价19715.6 CIR模型附息债券欧式期权定价19815.7 Module 1中的用户定义函数198小结200第16章 拟合利率期限结构20216.1 对数正态分布利率树20216.2 正态利率二叉树20416.3 BDT树20516.4 用BDT树为债券期权定价20616.5 Module 1中的用户定义函数207小结209附录 其它VBA函数210预测210ARIMA模型211样条212特征值和特征向量213前言当被问到为什么要攀登珠穆朗玛峰时，登山员通常会说：“因为它在那儿。”而我们写高级金融建模这本书则出于相反的原因。无论是以前还是现在，几乎没有一本书重点突出和解释VBA函数在Excel中的应用。另一方面，能够掌握金融领域数值方法精髓的书也寥寥无几。有人认为，像Excel这样的电子制表软件，不能满足高级技术和数值分析领域（如金融衍生工具的定价）的需要，现在这种想法已经过时。以前通过专门的软件包和语言进行的计算，现在可以应用有效的代码和VBA函数，在一台普通的电脑上只需一秒就可以完成。通过使用Excel和VBA编码，可以使得以前处于黑箱中的计算过程明朗化。最初，宏的出现拓宽了Excel的应用范围，后来这一应用促进了VBA语言在Excel中的全面发展，从股票计算、期权计算，最后到债券计算，VBA广泛应用于金融领域中的各种计算。在本书中，可以学习到一些新的Excel技巧，并可更深入地理解数值方法在金融中的应用。本书的基础部分来源于伦敦商学院的MBA选修课程讲义基于计算机的金融建模。书中的股票部分是学习资产组合管理课程的基础，该课程每年在日内瓦的国际货币银行中心举办一次。而关于期权和债券的章节则来自城市大学商学院计算机硕士课程数值方法。本书适用于研究生和本科高年级学生。使用本书时，读者必须采取积极尝试的态度，学会提出问题并解决问题，既要理解书中的代码和VBA用户定义函数，也要勇于在实践中应用它们。由于假设资产收益服从对数正态分布，并将二叉树作为一种核心数值方法，因此我们的解释可以建立在概率和统计中常用的结论基础上。全书采用了统一的符号，并且用图片显示了Excel和VBA的应用过程，这些都有助于读者更好地理解本书内容。致谢本书得益于之前的学术研究者和金融研究机构，他们发展了有关金融理论，并提出了相应的数值方法，从而形成了本书的基本内容。用牛顿的话来说，“如果我看得更远，那是因为我站在巨人的肩膀上”。感谢伦敦商学院和城市大学商学院的同事，特别是Elroy Dinenis，保罗马什和Kiriakos Vlahos。同时，还感谢萨姆惠特克对我们的热心鼓励，作为一位编辑，他付出了很大的努力和耐心。最后，感谢家人和朋友对我们的耐心，因为本书酝酿了较长一段时间，这期间给他们添了不少麻烦。第1章 介绍我们希望高级金融建模一书可以证明，能够应用电子制表软件成功地实现大部分的金融模型。这些模型从二十世纪五十年代早期发展到九十年代末期，覆盖了整个金融领域，包括股票、股票期权和债券期权。只要辅助使用VBA语言，这些模型完全能在Excel电子表格中实现。而用户定义函数提供了一个方便的程序库，使得计算的速度和准确度大大提高。高级金融建模应该看作是这个领域中传统教材的补充读物（它甚至是对传统教材的纠正）。本书没有列出金融模型的详细推导过程，目的是为了能够涵盖更多的模型和方法，特别是将重点放在更新的研究成果上。金融领域发展的重要理论包括：二十世纪五十年代的组合理论，六十年代的资本资产定价模型（CAPM），以及七十年代的布莱克-舒尔斯公式，这些理论中的解析解现在都能直接计算。这都得益于最近一二十年来发展的数值算法。通过选择适当的参数，二叉树方法在股票和债券期权定价的数值算法中扮演着重要的角色。在最近几年，金融领域的研究重点落在寻找有效的计算方法上，而不是理论本身。尽管本书覆盖了大部分的金融领域，并且包括了不少复杂的模型，但只需应用Excel，以及Excel中内嵌的函数和VBA程序，就能完美地解决问题。这使得我们可以将常用的假设（对数正态分布）、数学问题（期望）和数值方法（二叉树）在金融建模领域统一起来。当然，我们也努力确保本书使用一致和简单的符号，以便表达的更加清晰。尝试在本书中覆盖大部分的金融研究课题，这对我们来说既是一个挑战也是一个机遇。机遇就是我们可以纵览金融领域，并将资产定价中的假设、数学问题、数值方法和Excel的解法连接起来，总结出一般性规律。在以下的几节中，将简要地描述在股票、期权和债券计算中，关于金融、数学、数值方法和Excel特点方面的一些问题。以下的内容将会在以后的章节中详细地分析。1.1 金融学概览现代金融学作为一门学科与经济学分离，起源于1952年马可维茨创立的组合理论。马可维茨利用效用理论对个人投资者的选择进行建模，并且建立“均值方差”方法来检验收益（以资产的平均收益来度量）和风险（以资产收益的方差来度量）之间的关系。这一研究成果后来导致了夏普，林特恩和特雷纳的资本资产定价模型（CAPM）的发展。CAPM是一个均衡模型，它描述了股票的期望收益。模型中引入beta作为测量可分散风险的因子，并证明构建股票组合能够有效地减少个别风险事件带来的总体风险。另外一个重要的理论就是布莱克和舒尔斯的股票期权定价公式，这个公式是构筑在对冲组合（无风险）的基础上的。同时，默顿对布莱克-舒尔斯公式进行了扩展，使其适用于连续股利的情况，并可对商品期货期权和外汇期权定价。公式最初的推导需要解物理学中常见的扩散方程，但用风险中性方法也可以推导出来。1.2 收益分布假设尽管组合理论是根据个人投资者的选择推导出来的，但是它也可以通过对资产价格收益的分布进行合理假设来推导。标准的假设就是股票收益服从对数正态分布，或者假设股票的对数收益服从正态分布。最近，业界学者检验了实际分布同严格正态分布之间的偏离效应（偏度和峰度），并建议使用一些其它的分布（如逆gamma分布）。而债券与股票相比有许多不同之处，因此债券期权定价的出发点是短期利率。一般假设短期利率服从对数正态分布或正态分布。这些概率分布的特性被广泛应用于各种金融研究中。1.3 数学和统计方法在关于股票的章节中，涉及到最优化数学方法。这些最优化方法可能含有约束条件，如夏普基于资产收益所进行的分析。在他的分析中，代表线性回归的斜率。期权定价是在风险中性的条件下求统计学中的数学期望。对数股票价格的正态分布可以用离散的二项分布来近似。二项分布为计算期权的期望价格提供了一个框架。1.4 数值方法在关于组合最优化的章节中，最优化涉及到组合的方差，而解决最优化的数值方法是二次规划。风格分析也用到了二次规划，也就是使得误差的方差最小。而线性回归也是通过选择斜率系数来使误差项的平方和最小，尽管它通常不被看作是最优化问题。与一般最优化问题有所不同的是，线性回归为计算系数提供了一个直接公式。在为期权定价方面，二叉树方法为计算风险中性期望提供了一个分析框架。我们通过检验三个不同二叉树的收敛效应来强调参数选择的重要性。这些二叉树也可以给美式期权定价，在美式期权中，期权可以在到期日之前的任意时刻执行。在欧式期权中，像蒙特卡罗模拟和数值积分等技巧也经常用到。而数值迭代方法，特别是牛顿-拉夫森方法，可以用来估算期权市场价格中的隐含波动率。1.5 Excel 解决方案电子表格展示了如何应用Excel进行建模。在每张工作表中，所有单元格中的公式都很容易计算，而我们也尽量对单元格中的中间计算过程进行合并。电子表格具有灵活的特点，当参数改变时，结果也随之发生变化，这方便使用者检验参数对计算结果的影响。书中所有的模型和方法都会实现两次：一次通过电子表格，另一次通过VBA函数。这样做的目的是检验数值计算的精确度。部分VBA程序是宏，这通常被视为VBA在Excel中的主要应用。但绝大部分计算程序都是用户定义函数。我们会展示这些函数用VBA语言编写是如何的简单，并说明它们如何与Excel的内嵌函数结合在一起，包括功能强大的矩阵函数。Excel中的单变量求解（Goal Seek）和规划求解（Solver）是用来解决最优化问题的。我们会展示这些方法如何在VBA用户定义函数和宏中自动实现。Excel的另外一个未被充分利用的功能是数组函数（用CtrlShiftEnter组合键来调用），我们会在用户定义函数中使用它们。为了提高效率，在用户定义函数中使用的二叉树只采用了一维数组（向量）而不是二维数组（矩阵）。1.6 本书主题本书包括四部分，第一部分介绍用Excel进行高级建模的特点，其后三部分是其在金融领域的应用。应用的三部分内容分别涉及股票、股票期权和债券期权。第2章介绍本书需要用到的高级Excel函数和技巧。重点关注Excel中的数组函数，并用较短的篇幅介绍矩阵运算的相关知识。第3章介绍VBA编程环境和一种循序渐进地编写VBA子程序（宏）的方法。并用例子说明宏是如何自动操作和重复Excel中的任务的。第4章介绍VBA用户定义函数，它在金融计算中至关重要。强调如何处理标量变量和数组变量，包括将它们作为VBA函数的输入变量和输出变量。另外，用循序渐进的方法列举了一些例子。特别地，通过写用户定义函数为欧式期权（布莱克-舒尔斯公式）和美式期权定价（二叉树）。第5章介绍第一个应用部分如何处理股票。第6章讲解组合最优化，利用规划求解和分析解。规划求解经常用于电子表格计算，并能在VBA宏中自动执行，因此在本章的其他部分也会频繁出现。通过采用Excel和VBA中的数组函数，我们演示了如何得到资产组合有效边界上的点。组合理论的发展衍生分为三个常见问题，它们将在后面的章节中介绍。第7章转入资产定价，从单因子模型出发，介绍资本资产定价模型（CAPM），最后讨论风险值（VaR）。本章的另一个主题是关于资产对数收益服从正态分布的假设。第8章的主要内容是各种模型的效果测定，从最早使用的单参数测量到目前最具实用性的多因子模型（如风格分析）。在本书中，我们第一次说明在风格分析中如何确定资产权重的置信区间。第9章介绍第二个应用部分，即如何处理股票期权。在股票对数收益服从正态分布的假设下，我们演示了构建对冲组合在布莱克-舒尔斯期权定价公式中的重要地位。并具体解释了期权价值是风险中性条件下期权未来收入期望值的折现值。第10章介绍二叉树，它被看作是对数股票价格服从的连续正态分布在离散情况下的近似。实际应用时，由于二叉树方法能够有效地处理美式期权的定价问题，因此它成为期权定价数值方法的核心。本章为二叉树方法列举了三套不同的参数选择，其中包括LR树。与标准的二叉树相比，它拥有更好的收敛性和准确度。并利用一个九期树作例子，用户定义函数能够处理任何期数的二叉树定价。第11章回到布莱克-舒尔斯公式，并演示了它的适应性（能够对外汇和商品期货期权定价）和它对资产价格假设的依赖性。第12章介绍计算布莱克-舒尔斯公式中期望值的两种方法。这两种方法分别是蒙特卡罗模拟法和数值积分法。尽管对简单期权来说，这两种方法并没有多大的优势，但在为复杂的期权定价时，它们扮演着重要角色。第13章放开关于资产对数收益服从正态分布的假设，介绍在背离原来假设的基础上（主要通过改变偏度和峰度参数），由期权市场价格确定的隐含波动率曲线（volatility smile）。本章还介绍了计算欧式期权价格隐含波动率的有效方法。第14章为第三个应用部分，即如何处理债券期权。由于债券与股票相比存在许多不同的属性，因此为债券期权定价时出现的数学问题和使用的数值方法也有所不同。我们根据一系列零息票债券价格来定义期限结构，并展示用短期利率如何构建二叉树模型，这个模型可以为零息票债券现金流定价。第15章涵盖了两个利率模型，Vasicek模型以及考克斯，英格索尔和罗斯模型（CIR模型）。我们分析了零息票债券价格和零息票债券期权的解析解，并介绍了为附息债券期权定价的一种方法。第16章介绍了在给定零息票债券期限结构的情况下，如何用短期利率构建二叉树模型。构造著名的布莱克-德曼-托伊利率树模型（用电子表格和用户定义函数），并展示它如何为欧式和美式零息票债券期权定价。附录中是其它用户定义函数，这些函数与我们选定的几个应用部分的联系不很密切。但它们是有用的工具箱，可以作为ARIMA模型、样条、特征值和其它计算过程的函数。1.7 有关Excel工作簿第一部分集中介绍Excel函数和理解VBA语言。这部分有三个相关工作簿，AMFEXCEL，VBSUB和VBFNS分别对应于第2，3和4章。第二部分是关于股票的三个工作簿，EQUTY1，EQUTY2和EQUTY3，分别对应第6，7和8章。第三部分关于股票期权有四个工作簿，OPTION1，OPTION2，OPTION3和OPTION4，分别对应第10，11，12和13章。第四部分关于债券方面有两个工作簿，BOND1和BOND2，对应第14，15和16章，具体见书中的解释。附录有一个工作簿OTHERFNS。1.8 意见和建议尽管花费了很大精力收集材料和撰写本书，我们仍然很乐意接受意见、建议甚至是改正和改善。请发到电子邮箱或者通过以下网页与我们联系。/ifa/services/services.html或者www.business.city.ac.uk/irmi/mstaunton.html。第2章 高级Excel函数和过程本章回顾了本书用到的一些函数和过程。包括Excel中各类函数中的数学、统计和查找函数，以及常用过程，如建立模拟运算表（Data Tables）和用XY图显示结果等。还包括汇总数据集、进行回归分析以及访问Excel单变量求解（Goal Seek）和规划求解（Solver）的方法。目的是为了阐明和保证这些内容能扫清读者前面的障碍。高级Excel用户可以略过这些内容，或在需要的时候再来参考本章的内容。为了使这些不同的主题更有趣和更具有交互性，本文提供了一个包含本章全部例程的工作簿AMFEXCEL.xls，可以用来检测读者的熟练程度。2.1 访问Excel函数Excel提供了许多工作表函数，它们是一些已经编写好的计算程序。函数常用于电子表格的简单计算，在VBA宏代码和用户定义函数中也经常用到这些基本函数（见第三章和第四章）。点击标准工具栏中的粘贴函数按钮（标记为fx）就可以访问这些函数。函数向导如图2.1所示，函数分为几个不同的类别：如数学与三角函数类、统计类、逻辑类、查找与引用类，等等。【参照书中第9页的图2.1】图2.1 粘贴函数对话框显示数学与三角函数类别中的COMBIN函数如图2.1所示，数学与三角函数类别中的COMBIN函数被选中，这时对话框下面出现该函数输入值和输出值的简单描述。要想得到更详细的描述，可以点击帮助按钮（标记为?）。点击确定按钮之后，就会出现提供适当参数输入框的公式面板，如图2.2所示。需要输入的信息可以用键盘键入（如这里），也可以通过选择电子表格中的网格来引用（点击输入框右侧的按钮可以缩小公式面板）。注意，可以拖动公式面板离开它原来的位置。点击面板上的确定按钮或编辑栏中的勾号，就可以把公式输入到电子表格。【参照书中第10页的图2.2】图2.2 在公式面板中建立COMBIN函数图2.2显示，在公式面板里输入COMBIN函数参数的时候，编辑栏中会相应地出单元格公式的基本结构，而且粘贴函数按钮会呈现出按下状态。还应该注意的是，粘贴名称按钮（标记为 =ab）可以将已命名的单元格粘贴到公式中。（为单元格区域以及引用单元格区域命名的内容见2.10节。）不仅可以访问Excel自带的函数，粘贴函数按钮还可以访问用户定义函数，见第四章内容。讨论完如何访问函数后，接下来我们介绍一些常用的数学和统计类函数。2.2 数学类函数本书用到的数学与三角函数有：EXP(x)、LN(x)、SQRT(x)、RAND()、FACT(x)和COMBIN(number,number_chosen)。EXP(x)返回指数函数的值，exp(x)或。例如：l EXP(1)返回e的值（2.7183，小数位数为4）l EXP(2)返回的值（7.3891，小数位数为4）l EXP(-1)返回1/e或的值（0.36788，小数位数为5）在金融计算时，经常需要利用复利（或折现）因子将不同时段的现金流转换为未来价值（或现值）。给定连续复利r，则一年的复利因子为exp(r)，对应的年利率为，如果复利以年为基础，则公式为：关于连续复利以及EXP函数的应用，将在2.7.1节的模拟运算表中作进一步的阐述。LN(x)返回x的自然对数值。注意，x必须为正，否则函数会因数值溢出而返回#NUM!。例如：l LN(0.36788)的返回值为-1l LN(2.7183)的返回值为1l LN(7.3891)的返回值为2l LN(-4)的返回值为#NUM!在金融领域，我们经常与（自然）对数收益打交道，可以利用LN函数将收益值转换为对数收益。SQRT(x)返回x的平方根。很显然，x必须为非负，否则函数会因数值溢出而返回#NUM!。RAND()产生0, 1区间均匀分布的随机数。每次电子表格重新计算时，产生的随机数都不一样。用蒙特卡罗模拟法计算期权价格时，我们可以利用RAND()函数来产生随机数。FACT(number)返回整数number的阶乘，它等于123number。例如：l FACT(6)的返回值为720COMBIN(number,number_chosen)返回number个元素的组合值（子集大小为number_chosen），子集可以按任何顺序组合。例如，如果某支股票的价格在四个离散时间里要么上涨，要么下跌，则出现三次上涨（和一次下跌）序列的个数为：COMBIN(4,1) = 4 或者COMBIN(4,3) = 4也就是这样四个序列上涨上涨上涨下跌、上涨上涨下跌上涨、上涨下跌上涨上涨和下跌上涨上涨上涨。从统计的角度来说，COMBIN(4,3)表示从4个元素中选择3个元素的组合值，通常记为（或者通用的）。Excel中还有一些函数可以进行矩阵转置、矩阵相乘、或求方阵的逆。相应的函数分别为：l TRANSPOSE(array)返回矩阵array的转置l MMULT(array1,array2)返回两个矩阵的乘积l MINVERSE(array)返回矩阵array的逆矩阵这些函数都属于数学类函数。可能有些读者对矩阵并不熟悉，为了熟悉这些函数，我们将在本章的末尾对矩阵做介绍（见2.13）。2.3 统计类函数Excel中有一些函数可以快速汇总数据集（Excel术语叫数组）的一些特征。如函数AVERAGE(array)返回数组的平均值，STDEV(array)返回数组的标准差，MAX(array)和MIN(array)返回数组的最大值和最小值。为了考察数据集的分布，还要了解一些其他的函数。例如，QUARTILE函数返回一个数据集的四分位数，而FREQUENCY函数则返回一个数据集分组后的频率分布。Excel还提供一些概率分布函数，如正态分布函数NORMSDIST和正态分布反函数NORMSINV等。还有一些有用的二元（二个变量）统计函数，它们在进行回归分析和相关性分析时用处很大。例如：l INTERCEPT(known_ys,known_xs)l SLOPE(known_ys,known_xs)l RSQ(known_ys,known_xs)l STEYX(known_ys,known_xs)l CORREL(known_ys,known_xs)l COVAR(known_ys,known_xs)还有一个大家不太熟悉的数组函数LINEST(known_ys,known_xs)，它以数组的形式返回一些必要的回归统计量。此类函数将在2.11节介绍回归分析内容时作详细说明。我们还将把它们的返回值与数据回归分析过程中的回归结果作对比。下一节，我们将通过AMFEXCEL工作簿里Frequency and Snorm表中的例子来说明如何使用FREQUENCY、QUARTILE以及各种正态分布函数。2.3.1 使用频率函数FrequencyFREQUENCY(data_array,bins_array)统计一个数据集中出现在特定间隔（或bins）中的元素个数，并以一竖列数组返回。bins_array为用于对 data_array 中的数值进行分组的间隔数组。由于该函数是以数组的形式返回，所以在输入函数之前，必须在电子表格中为返回值选定一个相邻的单元格区域。我们从AMFEXCEL工作簿Frequency and Snorm表中的一个例子出发，来说明如何使用FREQUENCY函数。如图2.3所示，D10:D71列和E10:E71列中的月收益和对数收益（采用LN函数）数据的统计信息放在第4到第7行。假设现在我们想得到对数收益（E10:E71），即所谓data_array的频率分布。目的是为了检测这些数据是否近似地服从正态分布。首先，我们为分组确定间隔。观察一下最大和最小的对数收益，范围在-0.16到+0.20之间，分为10到12个间隔比较合适。将间隔值输入到G5:G14中，这些值作为对数收益分组的上边界。Return for months 1-62：162月份的收益数据；Summary Statistics：统计量；Returns：收益；Ln Returns：对数收益；Frequency Distribution：频率分布；Mean：均值；St Dev：标准差；Max：最大值；Min：最小值；interval：间隔；freq：频数；%freq：频率百分比；%cum freq：累积频率百分比；Month：月份；Total：共计【参照书中第13页的图2.3】图2.3 计算对数收益数据的频率分布为了正确地输入FREQUENCY函数，先选定单元格区域H5:H15。然后键入=，并点击粘贴函数按钮（标记为 fx）来完成句法：=FREQUENCY(E10:E71, G5:G14)在输入完最后一个括弧)后，将鼠标放在Excel的编辑栏，然后按下CtrlShiftEnter组合键，就可以完成函数输入了。（你需要用三个手指同时按下，否则无效。如果失败了，使输出范围继续保持在选中状态，然后按下编辑键（F2），必要时重新编辑公式，然后再次按下CtrlShiftEnter键。）此时你会看到单元格中的函数被一对大括弧括起来了，而且单元格G5:G15中出现了频率数组。结果如图2.4所示。在单元格H17中使用SUM函数检查一下频率数据之和是否等于62。查看结果，我们可以看出，没有低于-0.16的对数收益，处在-0.16和-0.12之间的有6个值，而大于0.20的对数收益也没有。（FREQUENCY返回数组的最底下单元格G15中，包含了超过bins上限0.20的数值个数。）由于FREQUENCY函数的输出是数组形式，所以不能对其中的某个单元格作修改。如果间隔数组改变了，则当前的输出数组必须先删除，然后重新输入函数。可以将计算出来的频率转换为百分比频率（分别除以数据集的大小62），并进而计算出累积百分比频率，如图2.4中的I列和J列所示。百分比频率和累积百分比频率公式可以在Frequency表中看到。Return for months 1-62：162月份的收益数据；Summary Statistics：统计量；Returns：收益；Ln Returns：对数收益；Frequency Distribution：频率分布；Mean：均值；St Dev：标准差；Max：最大值；Min：最小值；interval：间隔；freq：频数；%freq：频率百分比；%cum freq：累积频率百分比；Month：月份；Total：共计【参照书中第14页的图2.4】图2.4 对数收益的百分比频率和累积百分比频率分布显示累积百分比频率的最好方法是用XY图，数据点用一条没有标记的平滑曲线连接。要生成图2.5中所示的图形，可以选择单元格区域G5:G14和J5:J14作为数据源。注意，如果要选中一些不连续的区域，可以先选择第一个区域，然后按下Ctrl键不放松，再选择第二个以及后继的区域。Frequency Distribution：频率分布；interval：间隔；freq：频数；theory：理论值；Cumulative Frequency：累积频率；actual：实际值；%freq：频率百分比；%cum freq：累积频率百分比；Month：月份；Total：共计【参照书中第14页的图2.5】图2.5 累积百分比频率图（实际数据和严格正态分布的数据）对于正态分布的对数收益，累积分布呈一个S形（如图中的虚线所示）。而实际的对数收益数据则可能是由于偏度的原因，与正态分布有一些偏离。2.3.2 使用分位数函数Quartile QUARTILE(array,quart)函数返回数据集四分位数。其中的第二个参数quart是一个整数，表明返回哪一个四分位数：即，quart=0时，返回数组的最小值；quart=1时，返回第1/4分位数（即排序后，处于数组25%处的值）；quart=2时，返回中位数（50%）；quart=时，返回第3/4分位数（75%）；如果是4，返回最大值。QUARTILE函数提供了一个快捷并相对容易的方法来得到一个数据集的累积分布。例如在图2.6的单元格H22中输入：QUARTILE(E10:E71,G22)这里G22中的值为1，于是该函数返回第1/4四分位数。单元格中显示的数值为-0.043，也就是说，在选定的数组中，低于-0.043的对数收益占整个数据集的25%。第2/4分位数为0.028，是中位数，第3/4分位数为0.075，低于此数的对数收益占整个数据集的75%。图2.6还画出了区域H21:H25的XY图，并对数据点作了标记。可以看出，基于五个数据点上的累积分布曲线与图2.5中的分布曲线颇为相似。Quartiles：四分位数值；Cumulative Frequency from quartiles：四分位点的累积频率【参照书中第15页的图2.6】图2.6 使用QUARTILE计算Frequency表中的对数收益累积分布第3.5节演示如何在VBA中进行数组处理时会用到QUARTILE函数。与QUARTILE相关的函数，PERCENTILE(array,k)，返回一个数据集的第k个百分位点的数值，第4.7节演示如何编写数组函数时会用到此函数。2.3.3 使用正态函数Norm在统计类函数中，与正态分布有关的函数名称都是以NORM开头的，后面跟着字母S时，则专指标准正态分布。NORMSDIST(z)返回标准正态累积分布函数值。NORMSINV(probability)返回给定概率下标准正态分布的分位数。函数NORMDIST(x,mean,standard_dev,cumulative)的功能则强大的多，它适用于任何正态分布。如果cumulative的值为1（或TRUE），则返回累积分布函数值；如果cumulative的值为0（或FALSE），则返回概率密度函数值。图2.7显示，在Norm表中，单元格C5和D5中分别输入的是概率密度和左尾概率。这两个单元格公式都使用了NORMDIST函数，且均值和方差均为0和1。在C5中，函数最后一个输入项（cumulative）的值为0，因此返回值为概率密度，而在D5中，其值为1，从而得到左尾概率。给定左尾概率时的分位数可以用NORMINV函数得到，如单元格F5所示。拷贝这些公式，并检测其结果，进一步熟悉这些函数。最后，我们将得到对数收益的累积百分比频率分布。检验正态性的方法之一是采用观测数据的均值和方差，利用NORMDIST函数计算一组理论上的百分比频率。结果见工作表Frequency中的K列（见图2.5）。理论分布与实际收益分布在图2.5中一起显示。很明显，两者之间有一定的差距。Excel Normal Functions for N(0,1)：Excel正态分布函数【参照书中第17页的图2.7】图2.7 工作表Snorm中的一般正态分布函数Excel提供了大量的函数来进行数据汇总和理论分布建模。本书的股票和期权部分将用到它们。2.4 查找类函数在含有相关信息的表格中，查找函数可以根据不同的输入信息，检索出其相关的信息。例如，在图2.8中，我们利用VLOOKUP函数从波动率及对应的期权价格表中检索出给定波动率下的布莱克-舒尔斯期权价格。（布莱克-舒尔斯公式的基本理论见第11章。）一般来讲，函数VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)可以在给定表格（table_array）中的最左一列中查找出匹配值，然后返回同一行中指定列（col_index_num）中的数值。缺省情况下，表格中的第一列是按升序排列的（这就暗示range_lookup=1（或TRUE）。实际上，在本例中，最后一个参数可以忽略不写。关于查找的例子可以在工作表LookUp中找到。为了检验你是否已经理解，可以用VLOOKUP函数查找出不同销售额下应付的佣金，佣金率放在单元格区域F5:G7中。然后滚动到下面的布莱克-舒尔斯期权价格查询表，如图2.8所示。所要查找的波动率放在C17（20）中，表格数组为F17:G27，波动率按升序排列，期权价格在表格的第二列。因此，单元格D18中的公式为：VLOOKUP(C17, F17:G27,2)返回波动率为20时对应的期权价格为9.73。Black-Scholes Call Value Lookup Table：布莱克-舒尔斯看涨期权定价表；Volatility：波动率；BS Call Value：布莱克-舒尔斯看涨期权价值；【参照书中第17页的图2.8】图2.8 工作表LookUp中查找给定波动率下的期权价格要查找的值（lookup_value）可以近似（或精确）地匹配表格中第一列的数据，在匹配的基础上找到同一行中指定列的数据，并返回。可以试着在C17中输入不同的值，如20.5%、21.5%等，看看这个查找函数是如何工作的。range_lookup参数是一个逻辑值（TRUE或者FALSE），表明是进行近似匹配还是精确匹配。如果是TRUE或忽略，则进行近似匹配。这时如果没有找到完全相同的值，就会用比lookup_value小的最大值来匹配。如果是FALSE，VLOOKUP函数将进行精确匹配，这时没有找到完全相同的值就会返回错误值#NA。与VLOOKUP类似的函数HLOOKUP则对行数据进行匹配，先查找表格中第一行中的匹配数据，然后返回同一列中指定行的数据。MATCH和INDEX也是查找类函数，参看图2.8中的演示。函数MATCH(lookup_value,lookup_array,match_type)返回单一列（或行）中在指定方式下（match_type），与指定数值匹配的数组元素的相对位置。注意该函数返回的是匹配数值在数组中的位置，而不是数值本身。如果match_type为0，函数则返回精确匹配的数值位置，不管数组如何排序。如果match_type为1，则返回近似匹配的数值位置，此时需要数组按升序排列。如果match_type为-1，也返回近似匹配的数值位置，但此时需要数组按降序排列。在图2.8中，G列中的期权价格按升序排列。为了找出数组中匹配9.73的数值位置，可以在D22中输入公式：=MATCH(C21,G17:G27,1)其返回值为6。函数INDEX返回表或区域里特定行列中的数值。在图2.9中，特定的行和列为单元格C25和C26中的值，于是返回数组F17:G27中第六列、第二行中的值。Black-Scholes Call Value Lookup Table：布莱克-舒尔斯看涨期权定价表；Volatility：波动率；BS Call Value：布莱克-舒尔斯看涨期权价值；【参照书中第18页的图2.9】图2.9 工作表LookUp中的函数公式和结果如果数组是单一列（或者单一行），参数col_num（或row_num）可以为空。你可以改变一下单元格D27中的值来练习函数INDEX在这种情况下的使用方法。我们将在本书的股票部分应用VLOOLUP、MATCH以及INDEX函数。2.5 其他类型函数在介绍电子表格公式的同时，我们也想尽可能地介绍一些通用的公式，它们可以处理一些相关但不完全相同的情况。例如，图2.10中显示的是某只债券在某年的现金流，可以是0，可以是利息，也可以是本金加上利息。Bond Cashflows：债券现金流；Cash Flows for bonds：债券的现金流量【参照书中第18页的图2.10】图2.10 工作表Bonds中含有混合寻址以及嵌套IF函数的通用公式IF函数给出了两种状况下的不同结果，而含有一级嵌套的IF函数则可以给出三种不同的结果（嵌套越多，可给出的结果也越多）。单元格C11中的现金流公式含有一级嵌套：=IF($B11C$6,C$5,IF($B11=C$6,100+C$5,0)如果把C11中的公式拷贝到C11:H13中，就会得到每只债券每年的现金流。对于类型1的债券，现金流依赖于不同年份（单元格B11）和债券到期时间（C6）。如果年份在到期时间