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

目 录

前言 .............................................................................................. 6 致谢 .............................................................................................. 7 第1章 介绍 ............................................................................... 8

1.1 金融学概览 ........................................................................................................................ 8

1.2 收益分布假设 .................................................................................................................... 9 1.3 数学和统计方法 ................................................................................................................ 9 1.4 数值方法 ............................................................................................................................ 9 1.5 Excel 解决方案 .................................................................................................................. 9 1.6 本书主题 .......................................................................................................................... 10 1.7 有关Excel工作簿 ........................................................................................................... 11 1.8 意见和建议 ...................................................................................................................... 11

第2章 高级Excel函数和过程 .............................................. 12

2.1 访问Excel函数 ............................................................................................................... 12 2.2 数学类函数 ...................................................................................................................... 13 2.3 统计类函数 ...................................................................................................................... 14

2.3.1 使用频率函数Frequency ..................................................................................... 15 2.3.2 使用分位数函数Quartile ..................................................................................... 17 2.3.3 使用正态函数Norm ............................................................................................ 17 2.4 查找类函数 ...................................................................................................................... 18 2.5 其他类型函数 .................................................................................................................. 19 2.6 审核工具 .......................................................................................................................... 20 2.7 模拟运算表（Data Tables） ........................................................................................... 21

2.7.1 建立单变量模拟运算表 ....................................................................................... 21 2.7.2 建立双变量模拟运算表 ....................................................................................... 22 2.8 XY图 ................................................................................................................................ 23 2.9 访问数据分析和规划求解 .............................................................................................. 26 2.10 使用区域名称 ................................................................................................................ 27 2.11 回归分析 ........................................................................................................................ 29 2.12 单变量求解 .................................................................................................................... 31 2.13 矩阵代数以及相关函数 ................................................................................................ 32

2.13.1 矩阵介绍 ............................................................................................................. 32 2.13.2 矩阵转置 ............................................................................................................. 33 2.13.3 矩阵相加 ............................................................................................................. 33 2.13.4 矩阵相乘 ............................................................................................................. 33 2.13.5 矩阵求逆 ............................................................................................................. 34 2.13.6 线性方程组求解 ................................................................................................. 35

2.13.7 Excel矩阵函数小结 ............................................................................................ 36 小结......................................................................................................................................... 36

第3章 VBA介绍 .................................................................... 38

3.1 掌握VBA的好处 ........................................................................................................... 38 3.2 VBA的面向对象观点 ..................................................................................................... 39 3.3 编写VBA宏 ................................................................................................................... 40

3.3.1 简单VBA子程序 ................................................................................................ 40 3.3.2 交互函数MsgBox ................................................................................................ 41 3.3.3 编写环境 ............................................................................................................... 42 3.3.4 输入代码并运行宏 ............................................................................................... 43 3.3.5 录制按键和编辑代码 ........................................................................................... 43 3.4 编程要素 .......................................................................................................................... 45

3.4.1 变量和数据类型 ................................................................................................... 45 3.4.2 VBA数组变量 ...................................................................................................... 46 3.4.3 控制结构 ............................................................................................................... 48 3.4.4 控制重复过程 ....................................................................................................... 48 3.4.5 在代码中使用Excel和VBA函数 ..................................................................... 50 3.4.6 编程的一般观点 ................................................................................................... 50 3.5 宏与电子表格之间的通信 .............................................................................................. 50 3.6 子程序实例 ...................................................................................................................... 53

3.6.1 图表 ....................................................................................................................... 54 3.6.2 正态概率散点图 ................................................................................................... 56 3.6.3 用规划求解产生有效边界 ................................................................................... 58 小结......................................................................................................................................... 61 附录3A Visual Basic编辑器 ................................................................................................. 62 附录3B 用‘相对引用’模式来录制按键 ......................................................................... 65

第4章 编写VBA用户定义函数........................................... 68

4.1 简单销售佣金函数 .......................................................................................................... 68 4.2 在工作表中创建Commission(Sales)函数 ...................................................................... 69 4.3 多参数期权定价函数 ...................................................................................................... 70 4.4 在VBA中操作数组 ....................................................................................................... 73 4.5 数组变量的期望和方差函数 .......................................................................................... 73 4.6 数组变量的组合方差函数 .............................................................................................. 75 4.7 输出数组形式的函数 ...................................................................................................... 77 4.8 在用户定义函数中调用Excel和VBA函数 ................................................................ 78

4.8.1 在用户定义函数中使用VBA函数 .................................................................... 79 4.8.2 加载宏 ................................................................................................................... 79 4.9 编写VBA函数的优缺点 ............................................................................................... 79 小结......................................................................................................................................... 80 附录4A 演示函数如何处理数组 ......................................................................................... 81 附录4B 二叉树期权定价函数 ............................................................................................. 82 编写函数练习 ......................................................................................................................... 87

第5章 股票的有关简介 ......................................................... 91 第6章 投资组合最优化 ......................................................... 92

6.1 组合的均值和方差 .......................................................................................................... 92 6.2组合的风险－收益表示 ................................................................................................... 94 6.3用规划求解得到有效点 ................................................................................................... 94 6.4求有效边界（黄和利曾伯格的方法） ........................................................................... 97 6.5有约束边界组合 ............................................................................................................... 99 6.6无风险资产和风险资产的结合 ..................................................................................... 101 6.7问题一 一种无风险资产和一种风险资产的组合 ....................................................... 101 6.8问题二 存在两种风险资产的组合 ............................................................................... 103 6.9问题三 一种无风险资产和一个风险投资组合 ........................................................... 104 6.10 Module1中的用户定义函数 ....................................................................................... 106 6.11 Module1中用于解决三类常见组合问题的函数 ........................................................ 107 6.12模块M中的宏功能 ..................................................................................................... 109 小结....................................................................................................................................... 110

第7章 资产定价 ................................................................... 111

7.1单因素模型 ..................................................................................................................... 111 7.2估计β系数 ..................................................................................................................... 112 7.3资本资产定价模型（CAPM） ..................................................................................... 114 7.4方差－协方差矩阵 ......................................................................................................... 114 7.5风险值（VaR） .............................................................................................................. 116 7.6水平财富 ......................................................................................................................... 118 7.7正态和对数正态分布矩之间的关系 ............................................................................. 119 7.8 Module1中的用户定义函数 ......................................................................................... 120 小结....................................................................................................................................... 121

第8章 投资组合业绩评价 ................................................... 122

8.1传统业绩评价方法 ......................................................................................................... 122 8.2 主动—被动管理 ............................................................................................................ 124 8.3风格分析（Style Analysis） .......................................................................................... 126 8.4简单风格分析 ................................................................................................................. 127 8.5 滚动时段风格分析 ........................................................................................................ 128 8.6风格权重的置信区间 ..................................................................................................... 129 8.7 Module1中的用户定义函数 ......................................................................................... 131 小结....................................................................................................................................... 133

第9章 股票期权介绍 ........................................................... 135

9.1 布莱克-舒尔斯公式的起源 .......................................................................................... 135 9.2 布莱克-舒尔斯公式 ...................................................................................................... 136 9.3 对冲投资组合（Hedge Portfolios） ............................................................................. 137 9.4 风险中性定价 ................................................................................................................ 138

9.5 风险中性定价的单期二叉树模型 ................................................................................ 139 9.6期权平价关系（Put-Call Parity） ................................................................................. 140 9.7 红利（Dividends） ....................................................................................................... 141 9.8 美式期权的特征 ............................................................................................................ 141 9.9 数值方法 ........................................................................................................................ 141 9.10 波动率和非正态股票收益 .......................................................................................... 142 小结....................................................................................................................................... 142

第10章 二叉树 ..................................................................... 144

10.1 二叉树介绍 .................................................................................................................. 144 10.2 简化的二叉树 .............................................................................................................. 145 10.3 JR二叉树 ..................................................................................................................... 146 10.4 CRR树 .......................................................................................................................... 149 10.5 二项分布近似与布莱克-舒尔斯公式 ......................................................................... 150 10.6 CRR二叉树的收敛性 .................................................................................................. 151 10.7 LR树 ............................................................................................................................ 152 10.8 CRR树与LR树的比较 ............................................................................................... 153 10.9 美式期权和CRR美式二叉树 .................................................................................... 154 10.10 Module0和Module1中的用户定义函数 ................................................................. 156 小结....................................................................................................................................... 157

第11章 布莱克-舒尔斯公式 ............................................... 159

11.1 布莱克-舒尔斯公式..................................................................................................... 159 11.2 在Excel中运用布莱克-舒尔斯公式 .......................................................................... 160 11.3 外汇（Currencies）和商品（Commodities）期权 ................................................... 161 11.4 计算期权的‘希腊’参数 .......................................................................................... 162 11.5 对冲组合 ...................................................................................................................... 163 11.6 布莱克-舒尔斯公式的正式推导 ................................................................................. 165 11.7 Module1中的用户定义函数 ....................................................................................... 167 小结....................................................................................................................................... 168

第12章 欧式期权定价的其它数值方法 ............................ 170

12.1 蒙特卡罗模拟介绍 ...................................................................................................... 170 12.2 对偶变量（Antithetic Variables）模拟 ...................................................................... 172 12.3 准随机抽样（Quasi-Random Sampling）模拟 ......................................................... 172 12.4 模拟方法比较 .............................................................................................................. 174 12.5 蒙特卡罗 模拟中的希腊参数计算 ............................................................................ 175 12.6 数值积分 ...................................................................................................................... 175 12.7 Module1中的用户定义函数 ....................................................................................... 176 小结....................................................................................................................................... 178

第13章 非正态分布和隐含波动率 .................................... 180

13.1 非正态分布假设下的布莱克-舒尔斯 公式 ............................................................... 180 13.2 隐含波动率（Implied Volatility） .............................................................................. 181

13.3 调整偏度（Skewness）和峰度（Kurtosis） ............................................................. 182 13.4 波动率曲线（The Volatility Smile） .......................................................................... 184 13.5 Module1中的用户定义函数 ....................................................................................... 185 小结....................................................................................................................................... 188

第14章 债券期权定价介绍 ................................................. 189

14.1 利率期限结构 .............................................................................................................. 190 14.2 附息债券的现金流和到期收益率 .............................................................................. 191 14.3 二叉树 .......................................................................................................................... 191 14.4 布莱克的债券期权定价公式 ...................................................................................... 192 14.5 久期和凸性 .................................................................................................................. 193 14.6 符号 .............................................................................................................................. 194 小结....................................................................................................................................... 194

第15章 利率模型 ................................................................. 196

15.1 Vasicek利率期限结构模型 ......................................................................................... 196 15.2 Vasicek模型对零息票债券欧式期权定价 .................................................................. 198 15.3 Vasicek模型对附息债券欧式期权定价...................................................................... 199 15.4 CIR利率期限结构模型 ............................................................................................... 200 15.5 CIR模型对零息票债券欧式期权定价 ....................................................................... 201 15.6 CIR模型附息债券欧式期权定价 ............................................................................... 201 15.7 Module 1中的用户定义函数 ...................................................................................... 202 小结....................................................................................................................................... 204

第16章 拟合利率期限结构 ................................................. 205

16.1 对数正态分布利率树 .................................................................................................. 205 16.2 正态利率二叉树 .......................................................................................................... 207 16.3 BDT树 .......................................................................................................................... 208 16.4 用BDT树为债券期权定价 ........................................................................................ 209 16.5 Module 1中的用户定义函数 ...................................................................................... 210 小结....................................................................................................................................... 212

附录 其它VBA函数 ............................................................. 213

预测....................................................................................................................................... 213 ARIMA模型 ........................................................................................................................ 214 样条....................................................................................................................................... 215 特征值和特征向量 ............................................................................................................... 216

前言

当被问到为什么要攀登珠穆朗玛峰时，登山员通常会说：“因为它在那儿。”而我们写《高级金融建模》这本书则出于相反的原因。无论是以前还是现在，几乎没有一本书重点突出和解释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的另外一个未被充分利用的功能是数组函数（用Ctrl＋Shift＋Enter组合键来调用），我们会在用户定义函数中使用它们。为了提高效率，在用户定义函数中使用的二叉树只采用了一维数组（向量）而不是二维数组（矩阵）。

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 意见和建议

尽管花费了很大精力收集材料和撰写本书，我们仍然很乐意接受意见、建议甚至是改正和改善。请发到电子邮箱mstaunton@london.edu或者通过以下网页与我们联系。 www.london.edu/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)或e。例如： ? EXP(1)返回e的值（2.7183，小数位数为4） ? EXP(2)返回e的值（7.3891，小数位数为4）

? EXP(-1)返回1/e或e的值（0.36788，小数位数为5）

在金融计算时，经常需要利用复利（或折现）因子将不同时段的现金流转换为未来价值（或现值）。给定连续复利r，则一年的复利因子为exp(r)，对应的年利率为ra，如果复利以年为基础，则公式为：

?12x

ra?exp(r)?1

关于连续复利以及EXP函数的应用，将在2.7.1节的模拟运算表中作进一步的阐述。 LN(x)返回x的自然对数值。注意，x必须为正，否则函数会因数值溢出而返回#NUM!。例如：

? LN(0.36788)的返回值为-1 ? LN(2.7183)的返回值为1 ? LN(7.3891)的返回值为2 ? LN(-4)的返回值为#NUM!

在金融领域，我们经常与（自然）对数收益打交道，可以利用LN函数将收益值转换为对数收益。

SQRT(x)返回x的平方根。很显然，x必须为非负，否则函数会因数值溢出而返回#NUM!。 RAND()产生[0, 1]区间均匀分布的随机数。每次电子表格重新计算时，产生的随机数都不一样。用蒙特卡罗模拟法计算期权价格时，我们可以利用RAND()函数来产生随机数。 FACT(number)返回整数number的阶乘，它等于1×2×3×…×number。例如：

? FACT(6)的返回值为720

COMBIN(number,number_chosen)返回number个元素的组合值（子集大小为number_chosen），子集可以按任何顺序组合。例如，如果某支股票的价格在四个离散时间里要么上涨，要么下跌，则出现三次上涨（和一次下跌）序列的个数为：

COMBIN(4,1) = 4 或者COMBIN(4,3) = 4 也就是这样四个序列‘上涨－上涨－上涨－下跌’、‘上涨－上涨－下跌－上涨’、‘上涨－下跌－上涨－上涨’和‘下跌－上涨－上涨－上涨’。从统计的角度来说，COMBIN(4,3)表示从4个元素中选择3个元素的组合值，通常记为4C3（或者通用的nCr）。

Excel中还有一些函数可以进行矩阵转置、矩阵相乘、或求方阵的逆。相应的函数分别为：

? TRANSPOSE(array)返回矩阵array的转置 ? MMULT(array1,array2)返回两个矩阵的乘积 ? MINVERSE(array)返回矩阵array的逆矩阵

这些函数都属于数学类函数。可能有些读者对矩阵并不熟悉，为了熟悉这些函数，我们将在本章的末尾对矩阵做介绍（见2.13）。

2.3 统计类函数

Excel中有一些函数可以快速汇总数据集（Excel术语叫‘数组’）的一些特征。如函数AVERAGE(array)返回数组的平均值，STDEV(array)返回数组的标准差，MAX(array)和MIN(array)返回数组的最大值和最小值。

为了考察数据集的分布，还要了解一些其他的函数。例如，QUARTILE函数返回一个数据集的四分位数，而FREQUENCY函数则返回一个数据集分组后的频率分布。 Excel还提供一些概率分布函数，如正态分布函数NORMSDIST和正态分布反函数NORMSINV等。 还有一些有用的二元（二个变量）统计函数，它们在进行回归分析和相关性分析时用处很大。例如：

? INTERCEPT(known_y?s,known_x?s) ? SLOPE(known_y?s,known_x?s) ? RSQ(known_y?s,known_x?s) ? STEYX(known_y?s,known_x?s) ? CORREL(known_y?s,known_x?s) ? COVAR(known_y?s,known_x?s)

还有一个大家不太熟悉的数组函数LINEST(known_y?s,known_x?s)，它以数组的形式返回一些必要的回归统计量。此类函数将在2.11节介绍回归分析内容时作详细说明。我们还将把它们的返回值与数据回归分析过程中的回归结果作对比。

下一节，我们将通过AMFEXCEL工作簿里Frequency and Snorm表中的例子来说明如何使用FREQUENCY、QUARTILE以及各种正态分布函数。

2.3.1 使用频率函数Frequency

FREQUENCY(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：1－62月份的收益数据； 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的编辑栏，然后按下Ctrl＋Shift＋Enter组合键，就可以完成函数输入了。（你需要用三个手指同时按下，否则无效。如果失败了，

本文来源：https://www.bwwdw.com/article/15e.html