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

目录

前言..............................................6

致谢..............................................7

第1章介绍.......................................8

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

1.2收益分布假设............................................................9

1.3数学和统计方法..........................................................9

1.4数值方法................................................................9

1.5Excel解决方案..........................................................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模拟运算表(DataTables)..............................................................................................21

2.7.1建立单变量模拟运算表.............................................21

2.7.2建立双变量模拟运算表.............................................22

2.8XY图..................................................................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.7Excel矩阵函数小结................................................36

小结........................................................................36

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

3.1掌握VBA的好处........................................................38

3.2VBA的面向对象观点....................................................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.2VBA数组变量....................................................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

附录3AVisualBasic编辑器...................................................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力U载宏............................................................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.10Module1中的用户定义函数............................................106

6.11Module1中用于解决三类常见组合问题的函数............................107

6.12模块M中的宏功能....................................................109

小结.......................................................................110

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

7.1单因素模型.............................................................111

7.2估计8系数............................................................112

7.3资本资产定价模型(CAPM)........................................................................................114

7.4方差一协方差矩阵......................................................114

7.5风险值(VaR)..................................................................................................................116

7.6水平财富...............................................................118

7.7正态和对数正态分布矩之间的关系.......................................119

7.8Module!中的用户定义函数..............................................120

小结.......................................................................121

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

8.1传统业绩评价方法......................................................122

8.2主动一被动管理........................................................124

8.3风格分析(StyleAnalysis).............................................................................................126

8.4简单风格分析..........................................................127

8.5滚动时段风格分析......................................................128

8.6风格权重的置信区间....................................................129

8.7Module1中的用户定义函数..............................................131

小结.......................................................................133

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

9.1布莱克-舒尔斯公式的起源...............................................135

9.2布莱克-舒尔斯公式.....................................................136

9.3对冲投资组合(HedgePortfolios)................................................................................137

9.4风险中性定价..........................................................138

9.5风险中性定价的单期二叉树模型.........................................139

9.6期权平价关系(Put-CallParity)...................................................................................140

9.7红利(Dividends)............................................................................................................141

9.8美式期权的特征........................................................141

9.9数值方法..............................................................141

9.10波动率和非正态股票收益...............................................142

小结.......................................................................142

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

10.1二叉树介绍............................................................144

10.2简化的二叉树.........................................................145

10.3JR二叉树............................................................146

10.4CRR树...............................................................149

10.5二项分布近似与布莱克-舒尔斯公式......................................150

10.6CRR二叉树的收敛性..................................................151

10.7LR树................................................................152

10.8CRR树与LR树的比较.................................................153

10.9美式期权和CRR美式二叉树...........................................154

lO.lOModuleO和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.7Module1中的用户定义函数.............................................167

小结.......................................................................168

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

12.1蒙特卡罗模拟介绍.....................................................170

12.2对偶变量(AntitheticVariables)模拟....................................172

12.3准随机抽样(Quasi-RandomSampling)模拟.............................172

12.4模拟方法比较.........................................................174

12.5蒙特卡罗模拟中的希腊参数计算.......................................175

12.6数值积分.............................................................175

12.7Module1中的用户定义函数............................................176

小结.......................................................................178

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

13.1非正态分布假设下的布莱克-舒尔斯公式.................................180

13.2隐含波动率(ImpliedVolatility).................................................................................181

13.3调整偏度(Skewness)和峰度(Kurtosis)...............................................................182

13.4波动率曲线(TheVolatilitySmile).............................................................................184

13.5Module1中的用户定义函数.............................................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.1Vasicek利率期限结构模型..............................................196

15.2Vasicek模型对零息票债券欧式期权定价.................................198

15.3Vasicek模型对附息债券欧式期权定价...................................199

15.4CIR利率期限结构模型................................................200

15.5CIR模型对零息票债券欧式期权定价....................................201

15.6CIR模型附息债券欧式期权定价........................................201

15.7Module1中的用户定义函数............................................202

小结.......................................................................204

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

16.1对数正态分布利率树...................................................205

16.2正态利率二叉树.......................................................207

16.3BDT树...............................................................208

16.4用BDT树为债券期权定价.............................................209

16.5Module1中的用户定义函数............................................210

小结.......................................................................212

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

预测.......................................................................213

ARIMA模型...............................................................214

样条.......................................................................215

特征值和特征向量..........................................................216

刖s

当被问到为什么要攀登珠穆朗玛峰时，登山员通常会说：“因为它在那儿。”而我们写

《高级金融建模》这本书则出于相反的原因。无论是以前还是现在，几乎没有一本书重点突

出和解释VBA函数在Excel中的应用。另一方面，能够掌握金融领域数值方法精髓的书也

寥寥无几。

有人认为，像Excel这样的电子制表软件，不能满足高级技术和数值分析领域（如金融

衍生工具的定价）的需要，现在这种想法已经过时。以前通过专门的软件包和语言进行的计

算，现在可以应用有效的代码和VBA函数，在一台普通的电脑上只需一秒就可以完成。通

过使用Excel和VBA编码，可以使得以前处于黑箱中的计算过程明朗化。

最初，宏的出现拓宽了Excel的应用范围，后来这一应用促进了VBA语言在Excel中

的全面发展，从股票计算、期权计算，最后到债券计算，VBA广泛应用于金融领域中的各

种计算。在木书中，可以学习到一些新的Excel技巧，并可更深入地理解数值方法在金融中

的应用。

本书的基础部分来源于伦敦商学院的MBA选修课程讲义《基于计算机的金融建模》。

书中的股票部分是学习《资产组合管理》课程的基础，该课程每年在日内瓦的国际货币银行

中心举办一次。而关于期权和债券的章节则来自城市大学商学院计算机硕士课程《数值方

法》。本书适用于研究生和本科高年级学生。

使用本书时，读者必须采取积极尝试的态度，学会提出问题并解决问题，既要理解书

中的代码和VBA用户定义函数，也要勇于在实践中应用它们。由于假设资产收益服从对数

正态分布，并将二叉树作为一种核心数值方法，因此我们的解释可以建立在概率和统计中常

用的结论基础上。全书采用了统•的符号，并且用图片显示了Excel和VBA的应用过程，

这些都有助于读者更好地理解本书内容。

致谢

本书得益于之前的学术研究者和金融研究机构，他们发展了有关金融理论，并提出了

相应的数值方法，从而形成了本书的基本内容。用牛顿的话来说，''如果我看得更远，那是

因为我站在巨人的肩膀上，

感谢伦敦商学院和城市大学商学院的同事，特别是ElroyDinenis,保罗吗什和Kiriakos

VlahosD

同时，还感谢萨姆•惠特克对我们的热心鼓励，作为，位编辑，他付出了很大的努力和

耐心。

最后，感谢家人和朋友对我们的耐心，因为本书酝酿了较长一段时间，这期间给他们添

了不少麻烦。

第1章介绍

我们希望《高级金融建模》一书可以证明，能够应用电子制表软件成功地实现大部分

的金融模型。这些模型从二十世纪五十年代早期发展到九十年代末期，覆盖了整个金融领域,

包括股票、股票期权和债券期权。只要辅助使用VBA语言，这些模型完全能在Excel电子

表格中实现。而用户定义函数提供了一个方便的程序库，使得计算的速度和准确度大大提高。

《高级金融建模》应该看作是这个领域中传统教材的补充读物（它甚至是对传统教材

的纠正）。本书没有列出金融模型的详细推导过程，目的是为了能够涵盖更多的模型和方法,

特别是将重点放在更新的研究成果上。

金融领域发展的重要理论包括：二十世纪五十年代的组合理论，六十年代的资本资产

定价模型（CAPM）,以及七十年代的布莱克-舒尔斯公式，这些理论中的解析解现在都能直

接计算。这都得益于最近一二十年来发展的数值算法。通过选择适当的参数，二叉树方法在

股票和债券期权定价的数值算法中扮演着重要的角色。在最近几年，金融领域的研究重点落

在寻找有效的计算方法上，而不是理论本身。

尽管本书覆盖了大部分的金融领域，并且包括了不少复杂的模型，但只需应用Excel,

以及Excel中内嵌的函数和VBA程序，就能完美地解决问题。这使得我们可以将常用的假

设（对数正态分布）、数学问题（期望）和数值方法（二叉树）在金融建模领域统一起来。

当然，我们也努力确保本书使用一致和简单的符号，以便表达的更加清晰。

尝试在本书中覆盖大部分的金融研究课题，这对我们来说既是•个挑战也是一个机遇。

机遇就是我们可以纵览金融领域，并将资产定价中的假设、数学问题、数值方法和Excel

的解法连接起来，总结出一般性规律。在以下的几节中，将简要地描述在股票、期权和债券

计算中，关于金融、数学、数值方法和Excel特点方面的一些问题。以下的内容将会在以后

的章节中详细地分析。

1.1金融学概览

现代金融学作为一门学科与经济学分离，起源于1952年马可维茨创立的组合理论。马

可维茨利用效用理论对个人投资者的选择进行建模，并且建立“均值一方差”方法来检验收

益（以资产的平均收益来度量）和风险（以资产收益的方差来度量）之间的关系。这一研究

成果后来导致了夏普，林特恩和特雷纳的资本资产定价模型（CAPM）的发展。CAPM是一

个均衡模型，它描述了股票的期望收益。模型中引入beta作为测量可分散风险的因子，并

证明构建股票组合能够有效地减少个别风险事件带来的总体风险。

另外一个重要的理论就是布莱克和舒尔斯的股票期权定价公式，这个公式是构筑在对

冲组合（无风险）的基础上的。同时，默顿对布莱克-舒尔斯公式进行了扩展，使其适用于

连续股利的情况，并可对商品期货期权和外汇期权定价。公式最初的推导需要解物理学中常

见的扩散方程，但用风险中性方法也可以推导H1来。

1.2收益分布假设

尽管组合理论是根据个人投资者的选择推导出来的，但是它也可以通过对资产价格收

益的分布进行合理假设来推导。标准的假设就是股票收益服从对数正态分布，或者假设股票

的对数收益服从正态分布。最近，业界学者检验了实际分布同严格正态分布之间的偏离效应

（偏度和峰度），并建议使用一些其它的分布（如逆gamma分布）。

而债券与股票相比有许多不同之处，因此债券期权定价的出发点是短期利率。一般假

设短期利率服从对数正态分布或正态分布。这些概率分布的特性被广泛应用于各种金融研究

中。

1.3数学和统计方法

在关于股票的章节中，涉及到最优化数学方法。这些最优化方法可能含有约束条件，

如夏普基于资产收益所进行的分析。在他的分析中，£代表线性回归的斜率。

期权定价是在风险中性的条件下求统计学中的数学期望。对数股票价格的正态分布可

以用离散的二项分布来近似。二项分布为计算期权的期望价格提供了一个框架。

1.4数值方法

在关于组合最优化的章节中，最优化涉及到组合的方差，而解决最优化的数值方法是

二次规划。风格分析也用到了二次规划，也就是使得误差的方差最小。而线性回归也是通过

选择斜率系数来使误差项的平方和最小，尽管它通常不被看作是最优化问题。与一般最优化

问题有所不同的是，线性回归为计算仅系数提供了个直接公式。

在为期权定价方面，二叉树方法为计算风险中性期望提供了一个分析框架。我们通过

检验三个不同二叉树的收敛效应来强调参数选择的重要性。这些二叉树也可以给美式期权定

价，在美式期权中，期权可以在到期日之前的任意时刻执行。

在欧式期权中，像蒙特卡罗模拟和数值积分等技巧也经常用到。而数值迭代方法，特

别是牛顿-拉夫森方法，可以用来估算期权市场价格中的隐含波动率。

1.5Excel解决方案

电子表格展示了如何应用Excel进行建模。在每张工作表中，所有单元格中的公式都很

容易计算，而我们也尽量对单元格中的中间计算过程进行合并。电子表格具有灵活的特点，

当参数改变时，结果也随之发生变化，这方便使用者检验参数对计算结果的影响。

书中所有的模型和方法都会实现两次：一次通过电子表格，另•次通过VBA函数。这

样做的目的是检验数值计算的精确度。

部分VBA程序是宏，这通常被视为VBA在Excel中的主要应用。但绝大部分计算程序

都是用户定义函数。我们会展示这些函数用VBA语言编写是如何的简单.，并说明它们如何

与Excel的内嵌函数结合在一起，包括功能强大的矩阵函数。

Excel中的单变量求解（GoalSeek）和规划求解（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章放开关于资产对数收益服从正态分布的假设，介绍在背离原来假设的基础上（主

要通过改变偏度和峰度参数），由期权市场价格确定的隐含波动率曲线（volatilitysmile）。

本章还介绍了计算欧式期权价格隐含波动率的有效方法。

第14章为第三个应用部分，即如何处理债券期权。由于债券与股票相比存在许多不同

的属性，因此为债券期权定价时出现的数学问题和使用的数值方法也有所不同。我们根据一

系列零息票债券价格来定义期限结构，并展示用短期利率如何构建二叉树模型，这个模型可

以为零息票债券现金流定价。

第15章涵盖了两个利率模型，Vasicek模型以及考克斯，英格索尔和罗斯模型（CIR

模型）。我们分析了零息票债券价格和零息票债券期权的解析解，并介绍了为附息债券期权

定价的一种方法。

第16章介绍了在给定零息票债券期限结构的情况下,如何用短期利率构建二叉树模型。

构造著名的布莱克-德曼-托伊利率树模型（用电子表格和用户定义函数），并展示它如何为

欧式和美式零息票债券期权定价。

附录中是其它用户定义函数，这些函数与我们选定的几个应用部分的联系不很密切。但

它们是有用的工具箱，可以作为ARIMA模型、样条、特征值和其它计算过程的函数。

1.7有关Excel工作簿

第一部分集中介绍Excel函数和理解VBA语言。这部分有三个相关工作簿，

AMFEXCEL,VBSUB和VBFNS分另U对应于第2,3和4章。

第二部分是关于股票的三个工作簿，EQUTY1,EQUTY2和EQUTY3,分别对应第6,

7和8章。

第三部分关于股票期权有四个工作簿，0PTI0N1,0PTI0N2,OPTIONS和OPTION4,

分别对应第10,11，12和13章。

第四部分关于债券方面有两个工作簿，BOND1和BOND2,对应第14,15和I16章，

具体见书中的解释。

附录有一个工作簿0THERFNS。

1.8意见和建议

尽管花费了很大精力收集材料和撰写本书，我们仍然很乐意接受意见、建议甚至是改

正和改善。请发到电子邮箱mstaunton@或者通过以下网页与我们联系。

/ifa/services/services.html或者

www.business.city.ac.uk/irmi/mstaunton.htmL

第2章高级Excel函数和过程

本章回顾了本书用到的一些函数和过程。包括Excel中各类函数中的数学、统计和查找

函数，以及常用过程，如建立模拟运算表（DataTables）和用XY图显示结果等。还包括

汇总数据集、进行回归分析以及访问Excel单变量求解（GoalSeek）和规划求解（SoMer）

的方法。目的是为了阐明和保证这些内容能扫清读者前面的障碍。高级Excel用户可以略过

这些内容，或在需要的时候再来参考本章的内容。为了使这些不同的主题更有趣和更具有交

互性，本文提供了一个包含本章全部例程的工作簿AMFEXCEL.xls,可以用来检测读者的

熟练程度。

2.1访问Excel函数

Excel提供了许多工作表函数，它们是一些已经编写好的计算程序。函数常用于电子表

格的简单计算，在VBA宏代码和用户定义函数中也经常用到这些基本函数（见第三章和第

四章）。

点击标准工具栏中的粘贴函数按钮（标记为fx）就可以访问这些函数。函数向导如图

2.1所示，函数分为几个不同的类别：如数学与三角函数类、统计类、逻辑类、查找与引用

类，等等。

粘贴函数为凶

函数分类©:函数名®:

常用函

ATAN

部

全

ATAN2

务

财ATAHH

与

期

日CEIUNGJ

阂

臂

腰jCQMBIN

与

找

笥COS

据

库COSH

数

本DEGREES

文

辑EVEN

逻

息EXP

信

FACT

COMBIN（number,number_chosen）

计算从给定元素数目的集合中提取若干元素的组合数.有关使用的方

程式，查看“帮助”.

团I确定|取消|

【参照书中第9页的图2.1】

图2.1粘贴函数对话框显示数学与三角函数类别中的COMBIN函数

如图2.1所示，数学与三角函数类别中的COMBIN函数被选中，这时对话框下面出现

该函数输入值和输出值的简单描述。要想得到更详细的描述，可以点击帮助按钮（标记为？）。

点击确定按钮之后，就会出现提供适当参数输入框的公式面板，如图2.2所示。需要输

入的信息可以用键盘键入（如这里），也可以通过选择电子表格中的网格来引用（点击输入

框右侧的按钮可以缩小公式面板)。注意，可以拖动公式面板离开它原来的位置。点击面板

上的确定按钮或编辑栏中的勾号，就可以把公式输入到电子表格。

回文件(E)编辑(日视图(切插入①格式9)工具(I)数据(0)窗口(出)帮助(由

口片口副缗阪等跑I"吏履工养却刈随名】

▼10▼|BI-|室室.圉|4%,懦手|率率

COMBIN▼!X>/==CO・BIN(4,1)

-C0MBIN_________________________________

Numberp+j=4