EXCEL和VBA金融建模.docx

1.1 金融学概览 现代金融学作为一门学科与经济学分离，起源于1952年马可维茨创立的组合理论。马可维茨利用效用理论对个人投资者的选择进行建模，并且建立“均值方差”方法来检验收益（以资产的平均收益来度量）和风险（以资产收益的方差来度量）之间的关系。这一研究成果后来导致了夏普，林特恩和特雷纳的资本资产定价模型（CAPM）的发展。CAPM是一个均衡模型，它描述了股票的期望收益。模型中引入beta作为测量可分散风险的因子，并证明构建股票组合能够有效地减少个别风险事件带来的总体风险。另外一个重要的理论就是布莱克和舒尔斯的股票期权定价公式，这个公式是构筑在对冲组合（无风险）的基础上的。同时，默顿对布莱克-舒尔斯公式进行了扩展，使其适用于连续股利的情况，并可对商品期货期权和外汇期权定价。公式最初的推导需要解物理学中常见的扩散方程，但用风险中性方法也可以推导出来。1.2 收益分布假设 尽管组合理论是根据个人投资者的选择推导出来的，但是它也可以通过对资产价格收益的分布进行合理假设来推导。标准的假设就是股票收益服从对数正态分布，或者假设股票的对数收益服从正态分布。最近，业界学者检验了实际分布同严格正态分布之间的偏离效应（偏度和峰度），并建议使用一些其它的分布（如逆gamma分布）。而债券与股票相比有许多不同之处，因此债券期权定价的出发点是短期利率。一般假设短期利率服从对数正态分布或正态分布。这些概率分布的特性被广泛应用于各种金融研究中。1.3 数学和统计方法 在关于股票的章节中，涉及到最优化数学方法。这些最优化方法可能含有约束条件，如夏普基于资产收益所进行的分析。在他的分析中，b代表线性回归的斜率。期权定价是在风险中性的条件下求统计学中的数学期望。对数股票价格的正态分布可以用离散的二项分布来近似。二项分布为计算期权的期望价格提供了一个框架。1.4 数值方法 在关于组合最优化的章节中，最优化涉及到组合的方差，而解决最优化的数值方法是二次规划。风格分析也用到了二次规划，也就是使得误差的方差最小。而线性回归也是通过选择斜率系数来使误差项的平方和最小，尽管它通常不被看作是最优化问题。与一般最优化问题有所不同的是，线性回归为计算b系数提供了一个直接公式。在为期权定价方面，二叉树方法为计算风险中性期望提供了一个分析框架。我们通过检验三个不同二叉树的收敛效应来强调参数选择的重要性。这些二叉树也可以给美式期权定价，在美式期权中，期权可以在到期日之前的任意时刻执行。在欧式期权中，像蒙特卡罗模拟和数值积分等技巧也经常用到。而数值迭代方法，特别是牛顿-拉夫森方法，可以用来估算期权市场价格中的隐含波动率。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。第2章高级Excel函数和过程本章回顾了本书用到的一些函数和过程。包括Excel中各类函数中的数学、统计和查找函数，以及常用过程，如建立模拟运算表（Data Tables）和用XY图显示结果等。还包括汇总数据集、进行回归分析以及访问Excel单变量求解（Goal Seek）和规划求解（Solver）的方法。目的是为了阐明和保证这些内容能扫清读者前面的障碍。高级Excel用户可以略过这些内容，或在需要的时候再来参考本章的内容。为了使这些不同的主题更有趣和更具有交互性，本文提供了一个包含本章全部例程的工作簿AMFEXCEL.xls，可以用来检测读者的熟练程度。2.1 访问Excel函数 Excel提供了许多工作表函数，它们是一些已经编写好的计算程序。函数常用于电子表格的简单计算，在VBA宏代码和用户定义函数中也经常用到这些基本函数（见第三章和第四章）。点击标准工具栏中的粘贴函数按钮（标记为fx）就可以访问这些函数。函数向导如图2.1所示，函数分为几个不同的类别：如数学与三角函数类、统计类、逻辑类、查找与引用类，等等。如图2.1所示，数学与三角函数类别中的COMBIN函数被选中，这时对话框下面出现该函数输入值和输出值的简单描述。要想得到更详细的描述，可以点击帮助按钮（标记为?）。点击确定按钮之后，就会出现提供适当参数输入框的公式面板，如图2.2所示。需要输入的信息可以用键盘键入（如这里），也可以通过选择电子表格中的网格来引用（点击输入框右侧的按钮可以缩小公式面板）。注意，可以拖动公式面板离开它原来的位置。点击面板上的确定按钮或编辑栏中的勾号，就可以把公式输入到电子表格。图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)或xe。例如： EXP(1)返回e的值（2.7183，小数位数为4） EXP(2)返回2e的值（7.3891，小数位数为4） EXP(-1)返回1/e或1e-的值（0.36788，小数位数为5） 在金融计算时，经常需要利用复利（或折现）因子将不同时段的现金流转换为未来价值（或现值）。给定连续复利r，则一年的复利因子为exp(r)，对应的年利率为，如果复利以年为基础，则公式为： arexp()1arr=-关于连续复利以及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的阶乘，它等于123number。例如： FACT(6)的返回值为720 COMBIN(number,number_chosen)返回number个元素的组合值（子集大小为number_chosen），子集可以按任何顺序组合。例如，如果某支股票的价格在四个离散时间里要么上涨，要么下跌，则出现三次上涨（和一次下跌）序列的个数为： COMBIN(4,1) = 4 或者COMBIN(4,3) = 4 也就是这样四个序列上涨上涨上涨下跌、上涨上涨下跌上涨、上涨下跌上涨上涨和下跌上涨上涨上涨。从统计的角度来说，COMBIN(4,3)表示从4个元素中选择3个元素的组合值，通常记为（或者通用的）。43CnrCExcel中还有一些函数可以进行矩阵转置、矩阵相乘、或求方阵的逆。相应的函数分别为：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_ys,known_xs) SLOPE(known_ys,known_xs) RSQ(known_ys,known_xs) STEYX(known_ys,known_xs) CORREL(known_ys,known_xs) COVAR(known_ys,known_xs) 还有一个大家不太熟悉的数组函数LINEST(known_ys,known_xs)，它以数组的形式返回一些必要的回归统计量。此类函数将在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：162月份的收益数据； Summary Statistics：统计量； Returns：收益； Ln Returns：对数收益； Frequency Distribution：频率分布； Mean：均值；St Dev：标准差；Max：最大值；Min：最小值；interval：间隔；freq：频数； %freq：频率百分比；%cum freq：累积频率百分比；Month：月份；Total：共计为了正确地输入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：共计显示累积百分比频率的最好方法是用XY图，数据点用一条没有标记的平滑曲线连接。要生成图2.5中所示的图形，可以选择单元格区域G5:G14和J5:J14作为数据源。注意，如果要选中一些不连续的区域，可以先选择第一个区域，然后按下Ctrl键不放松，再选择第二个以及后继的区域。Frequency Distribution：频率分布； interval：间隔；freq：频数；theory：理论值；Cumulative Frequency：累积频率； actual：实际值； %freq：频率百分比；%cum freq：累积频率百分比；Month：月份；Total：共计对于正态分布的对数收益，累积分布呈一个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：四分位点的累积频率第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正态分布函数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：布莱克-舒尔斯看涨期权价值；如果数组是单一列（或者单一行），参数col_num（或row_num）可以为空。你可以改变一下单元格D27中的值来练习函数INDEX在这种情况下的使用方法。我们将在本书的股票部分应用VLOOLUP、MATCH以及INDEX函数。2.5 其他类型函数 在介绍电子表格公式的同时，我们也想尽可能地介绍一些通用的公式，它们可以处理一些相关但不完全相同的情况。例如，图2.10中显示的是某只债券在某年的现金流，可以是0，可以是利息，也可以是本金加上利息。Bond Cashflows：债券现金流；Cash Flows for bonds：债券的现金流量IF函数给出了两种状况下的不同结果，而含有一级嵌套的IF函数则可以给出三种不同的结果（嵌套越多，可给出的结果也越多）。单元格C11中的现金流公式含有一级嵌套： =IF($B11C$6,C$5,IF($B11=C$6,100+C$5,0) 如果把C11中的公式拷贝到C11:H13中，就会得到每只债券每年的现金流。对于类型1的债券，现金流依赖于不同年份（单元格B11）和债券到期时间（C6）。如果年份在到期时间之前（B11C6）则现金流为0。内嵌的IF函数处理到期年份以及到期年份之后的现金流，而外围的IF函数则处理第一种状态下的利息收入。在输入公式时我们采用了混合寻址方式，这是为了保证在拷贝单元格公式时，引用能够作正确地变化。我们输入C$6和C$5是为了保证在沿着列C向下拷贝时，始终将行5和行6中的数据作为到期年份和利息率。而$B11则随着年份的不同变为$B12和$B13。我们输入$B11，则当公式拷贝到D列中时，B列中的数据仍然作为年份来计算，而C$5和C$6则变为D$5和D$6。在复制复杂模型的结果时，编写这种通用公式得到的收获比节约的时间更有价值。 2.6 审核工具 对于复杂的单元格公式，使用审核按钮组将会给你很大的帮助。从菜单栏中调出该按钮组的方法之一，是点击视图菜单下的工具栏，然后选择用户定义。在如图2.11中所示的用户定义对话框中，选中审核，就会出现审核按钮组。这组按钮如图2.11所示，它们的名称从左到右分别为追踪引用单元格、移去引用单元格追踪箭头、追踪从属单元格。回到电子表格，选中单元格C11，然后点击追踪引用单元格按钮，就可以显示出单元格C11中引用到的单元格，如图2.12所示。（图中也显示了F13中引用到的单元格。）点击移去引用单元格追踪箭头就可以清除这些箭头。Bond Cashflows：债券现金流；Cash Flows for bonds：债券的现金流量用户定义对话框可以按你的意愿来定制工具栏。如果你点击命令页，并选中某一类别，你可以选择并拖动命令列表中的工具按钮将按钮拖到工具栏中。相反，你也可以选择和拖动按钮将按钮移出工具栏（使它们返回工具框）。2.7 模拟运算表（Data Tables） 模拟运算表（Data Tables）可以执行单元格公式一系列的重复计算，而不用拷贝或重新输入公式。AMFEXCEL工作簿中有不少模拟运算表的应用实例。这里用CompoundDTab工作表中计算复利和折现因子的例子说明单变量和双变量输入模拟运算表的使用方法。在工作表DSBTab中也有其他一些关于模拟运算表应用的例子。2.7.1 建立单变量模拟运算表 图2.13给出了利率为5%的年连续复利因子（单元格C10中）。利率为5%的年折现因子则放在单元格C11中。单元格中的公式也同时显示出来。可以得到包含不同时段复利因子和折现因子的表格，如t=1、2、一直到10年。用模拟运算表来完成这项任务，首先规划一下适当的版面，如图中的第16行及其下方所示的那样。需要重复计算的公式放在模拟运算表的第一行（行16）。因此在D16中，公式为=C10，这使得它与单元格C10中的公式联系起来。同样的，在E16中，公式为=C11。作为输入变量，时间t的信息则放在C列，其起始行在公式行之下。注意，单元格C16是处在公式行中，并且它的值为空。图2.13例子中所谓的表格区域就是C16:E26。Continuous Compounding：连续复利； Enter formula(s) for output：输入公式；Enter numbers for input variable t：为参数t输入数字； 【参照书中第21页的图2.13】图2.13 工作表CompoundDTab中单变量输入模拟运算表版面布局现在工作表就可以进行模拟运算表计算了，只需简单地做如下操作： 选中表格区域C16:E26 在主菜单中，点击数据中的模拟运算表在对话框中：列输入单元格中输入单元格C7，然后点击确定按钮结果如图2.14所示，表中单元格格式的可读性更强。单元格中显示的是数字，但实际上包含的是数组公式。这些数值是动态的，如果假定的数据如利率r，或变量t发生了改变，则它们将被重新计算一遍。可以试着将C5中的利率改成6%，然后观察单元格数值的变化。接下来，请将利率改回5%。Compounding/Discounting Factors：复利/折现因子； Enter formula(s) for output：输入公式；Enter numbers for input variable t：为参数t输入数字； 【参照书中第22页的图2.14】图2.14 模拟运算表中不同时段的复利因子和折现因子2.7.2 建立双变量模拟运算表 假设我们希望计算出不同利率不同时段的折现因子（单元格C11中的公式）。同样，首先必须规划双变量输入的模拟运算表版面布局。一种可能的版面布局如图2.15所示。Enter formula(s) for output：输入公式； Enter numbers for input variable t：为参数t输入数字； 【参照书中第22页的图2.15】图2.15 CompoundDTab中双变量的模拟运算表版面布局这里，表格区域为C30:I40。第一列数值是计算折现因子需要用到的时间t（列输入变量）。第30行中则列出五种利率值（行输入变量）。在表格左上角的单元格（C30）中则包含着根据不同利率和时间计算折现因子的公式。C30中的公式为=C11，它与C11中的折现因子计算公式相联系。计算表格中数据的步骤如下：选择表格区域C30:I40 在主菜单中，点击数据中的模拟运算表在对话框中：列输入单元格输入C7 行输入单元格为C5 然后点击确定按钮其结果如图2.16所示。将图中利率为5%时计算出的数据与图2.14中的数据做一个对比，看看是否一致。Enter formula(s) for output：输入公式； Enter numbers for input variable t：为参数t输入数字； 【参照书中第23页的图2.16】图2.16 模拟运算表中不同利率不同时段的折现因子模拟运算表在处理what-ifs类问题时极为有用。其优点在于表格能够自动地适应模型的变化。而其缺点在于：如果工作表中含有大量的模拟运算表，当输入改变后，连续的重新计算会降低处理速度。基于这个原因，有时可能需要关掉模拟运算表的自动重算功能。建立模拟运算表需要注意一下几点： 到目前为止，Excel要求模拟运算表的输入单元格必须与模拟运算表在同一个工作表中。模拟运算表单元格中的公式是以数组形式出现的，例如，模拟运算表单元格的输入为=TABLE(C5,C7)，其中的C5和C7是输入单元格。既然是以数组的形式出现，那么你就不能编辑它。如果要重建或者扩建模拟运算表，可以先选中所有包含了=TABLE()的单元格，然后点击编辑菜单下的清除所有，或者直接按Del键。改变输入或其他假定的数据，会导致模拟运算表重新计算，除非缺省的计算方法做了有意的改动。在大型模型中，模拟运算表中每一个输入的重新计算可能需要很长的时间，这时可以关闭模拟运算表的自动重算功能。要做到这一点，可以进行如下操作点击工具菜单下的选项点击重新计算页，选中除模拟运算表外，自动重算选项当自动重算功能关闭后，按下F9，强迫所有的模拟运算表重新计算一遍。如果你对期权定价的布莱克-舒尔斯公式很熟悉，你可以通过建立工作表BSDTab中建议的三个模拟运算表来巩固模拟运算表方面的知识。它同样可以用来计算布莱克-舒尔斯看涨期权价格对即期股票价格S的敏感度，以及对其他输入变量的敏感度。2.8 XY图 Excel提供了许多类型的图表，但是在数学、科学和金融方面，XY散点图是最有用的。在不会产生混淆的情况下，我们简称其为XY图。关于XY图，很重要的一点是它的X轴和Y轴都是数值刻度。而所有其他的双轴图表（包括折线图），只有纵坐标轴是数值刻度，而横坐标轴则是标签型。可以用图表向导来建立一个XY图，共有四个步骤，它们分别是图表类型、图表源数据、图表选项和图表位置。其中最重要的是第二步，图表源数据。这些步骤已经在2.7.1节中讨论单输入变量模拟运算表的结果时提到，并在图2.14中作了演示。CompoundDTab工作表中画图所用到的模拟运算表结果区域为C17:E26，C列中的数据作为x值，D列和E列中的数据用来作图。选中作图的区域后，按下列步骤进行： 点击主工具栏中的图表向导按钮。（它像一个小的条形图。）在步骤1的对话框中（如图2.17所示），选择图表类型；这里，选择XY散点图子图表类型中的无数据点平滑线散点图。点击按下不放可查看示例按钮。如果觉得不错，就可以点击下一步按钮。在步骤2的对话框中，检查数据区域输入框中的数据源是否是正确的，注意，Excel将这个数据区域理解为三列数据。点击系列页，可以看到系列1中的X值和Y值。点击名称输入框，选择工作表中单元格或是直接键入（compound）的方式为当前激活的系列加一个名称。图2.18中显示系列2的名称改成了单元格E15中的discount。点击下一步按钮继续。在步骤3的对话框中，输入图表选项，如标题、网格线、图例等。在我们的例子中，象图2.19中显示的那样，增加标题并去掉网格线就够了。点击下一步按钮继续。在步骤4的对话框中，决定图表位置。图表可以作为工作表的一部分（嵌入式图表），也可以作为一个分开的图表页。通常是作为一个嵌入式图表，这样就可以看到图表源数据变化的效果了，如图2.20显示的那样。从专业的角度来看，图2.21这种未经加工的图表还需要做进一步的修饰，用以改进它的外观。主要的改进有，将绘图区的背景颜色改为白色（无背景颜色），在图表选项中的坐标轴页里改变坐标轴，就像在标题和图例中那样。经常需要改变的是坐标轴刻度，以及标题的字体等。绘图区的大小也需要调整。有的数据系列可能需要显示数据点（激活数据系列并重新设定格式）。通过修改，就可以得到图2.14中显示的那种图表了。Compounding/Discounting Factors：复利/折现因子；2.9 访问数据分析和规划求解 Excel有一些附加的模块，这些模块在完全安装时是可用的，但如果选择节省空间的安装，则被忽略。我们需要用到规划求解和数据分析回归过程，因此需要先检查一下工具菜单，看看它们是否可用。如图2.22，其中含有规划求解和数据分析选项。如果工具菜单中没有，你可以点击加载宏选项（也在工具菜单中）。在如图2.22所示的对话框中选中分析工具库、分析工具库VBA函数和规划求解，然后点击确定按钮。安装完毕后，你就可以在工具菜单中看到这两个选项了。在解最优化问题时，可以对规划求解的使用作最好的描述，因此这部分内容将放在本书股票部分的6.5节，在那里，使用规划求解来求解最优的组合权重。在利用函数和分析工具库来进行回归分析之前，我们先简单地介绍一下区域名称，它在选择和引用大范围数据时是很有用的。2.10 使用区域名称 图2.23显示的是AMFEXCEL工作簿Beta表中的股票A和股票指数收益的开始部分。在接下来的一节里，我们要对股票收益和指数收益进行回归分析，看看它们之间是否存在一定的关系。为了详细说明计算过程，先给包含数据的单元格区域取一个名称是很有用的，比如说，将数据区域B5:B64命名为ShareA，将数据区域C5:C64命名为Index。需要命名的区域被选中后，就可以在主菜单栏左下方的名称框里键入你想取的名称，如图2.23所示。这样，在Beta工作表中，名称ShareA就与区域B5:B64绑定起来。另外，还可以选择插入菜单下的名称选项，然后点击定义选项来为选中的区域命名，在出现的对话框中键入名称ShareA即可，如图2.23所示。这样，选择和引用收益区域时就可以用它的名称来代替了，比如选定名称框中的名称ShareA，或是在输入函数时使用粘贴名称按钮。2.11 回归分析 假设读者已经对简单的回归分析（两个变量）很熟悉，在本书的股票部分将用到它。这里，我们概述一下怎样利用Excel来进行必要的计算。实际上，Excel有多种方法来进行回归运算，主要有两类：使用Excel函数和使用分析工具库中的简单回归程序。我们将用Beta工作表中的股票和指数收益来演示这两种不同的简单回归方法，首先用Excel函数，然后使用数据分析回归过程。如果你对这些函数和过程不熟悉，Beta工作表提供了一个适当的练习版面。Excel提供了一些进行回归分析时经常用到的函数。图2.24展示了回归方程的截距、斜率函数，以及两种拟和度函数：R平方和标准差（STEYX表示standard eror of Y given X）。由于数据区域已被命名，所以可以用粘贴名称按钮把它们的名称输入到函数中。这些函数结果会随着输入数据（ShareA和Index区域中的数据）的变化而动态变化。Excel regression functions：Excel回归函数； 【参照书中第29页的图2.24】图2.24 Beta工作表中的Excel回归函数除了上面提到的这些函数，还有一个LINEST数组函数，它以两列收益数据为输入项，返回数组形式的基本回归量。注意，在书写LINEST句法时，为函数的输出选择一个适当大小的区域是很重要的。图2.25显示了函数LINEST（ShareA,Index,1）在函数参数面板中输入参数时的情况，此时输出区域F40:G44已经被选定。Const参数为空，而Stats参数为1，这时会输出全部的回归统计值。数组输出如图2.26所示（包括注解）。除了斜率和截距（这里它们的标记分别为Beta和Alpha），LINEST还提供了拟合的标准差，以及一个基本的方差分析结果（也叫ANOVA） Output from Linest(Remember Ctrl+Shift+Enter)： Linest函数的输出结果（记住要按下组合键Ctrl+Shift+Enter） 【参照书中第30页的图2.26】图2.26 Beta表中采用LINEST函数来分析收益数据的数组输出下一步，我们来练习一下分析工具库中的回归过程。首先，点击菜单工具下的数据分析选项，然后点击回归，于是就出现了图2.27中显示的对话框。同样，对话框中的Y区域和X区域可以通过选择单元格区域来引用，也可以通过输入名称来引用（如果区域已经命名的话）。一般来说，指定一个数据表中左上角的单元格作为输出区域比接受缺省输出选项（将结果输出到一个新工作表中）更方面一些。结果如图2.28所示，回归方程的截距和斜率放在单元格F23和F24中。R平方和标准差（残差的标准方差）放在F12和F13中。输出是静态的，也就是说，这些数值不再随着输入数据的变化而变化。如果任何输入数据发生了改变，分析过程是不需要调整的，但必须手动重新计算。【参照书中第31页的图2.28】图2.28 Beta工作表中分析工具库回归程序的分析结果为了对比演示函数的动态特性，我们假设4月份的指数后来被更正为0.08（不是现在C8中的-0.08）。如果输入改变了，则F31:F34以及F40:F44中的函数结果会立即更新，例如，RSQ会从0.4755变为0.3260。而要想更新分析工具库的分析结果，则必须将回归过程重新运行一遍。分析工具库程序的静态输出特性使得它们在计算时不像Excel函数那样有吸引力。这些程序是由Excel版本4中的旧XLM宏语言编写的。更重要的是，不同于大多数Excel函数，它们很难在VBA用户定义函数中被调用。2.12 单变量求解 单变量求解是另一类Excel静态过程。这种工具可以在已知单个公式预期结果的情况下，求解公式的输入值。例如，在图2.29中，股票期权的布莱克-舒尔斯看涨期权价格与市场价格不一致。（布莱克-舒尔斯期权价格依赖于标的股票的波动率，并需要对未来波动率作估计。）假设我们想要知道波动率大小为多少时，能使得两者一致，也就是使得两者之间的差距（G9）为0。这是单变量求解的一个典型应用。在BSDTab表中使用单变量求解，可以点击工具菜单下的单变量求解选项。填写如图2.30中所示的输入框，然后点击确定按钮。单变量求解的处理结果如图2.31所示，要使布莱克-舒尔斯期权价格与市场价格一致，波动率必须为23%。Black-Scholes Option Values：布莱克-舒尔斯期权价值； Black-Scholes Call Value：布莱克-舒尔斯看涨期权价值【参照书中第32页的图2.29】单变量求解从一个初值开始，然后采用一种迭代方法使结果与设定值逐渐靠近。可变单元格中的数值就作为初值，在这里是20%。求解时，你可以改变一个单元格中的输入值，也可以改变多个单元格中的