EXCEL金融计算实验指导

EXCEL 金融计算

南京审计学院金融学院

前     言

本实验指导手册为金融学院《金融学》、《证券投资学》课程配套书。该实验指导手册侧重于培养学生应用《金融学》、《证券投资学》课程所学的基本原理，利用EXCEL软件为计算工具，分析各种金融工具的风险与收益能力。

全手册共分三部分。第一部分复习现值、终值、年金等财务概念，了解EXCEL工具环境与常用财务函数的使用，关于债券、股票等原生工具的定价，利用久期模型分析债券利率风险。第二部分关于现代投资理论构造资产组合，通过计算加深对有效前沿、资本市场线和证券市场线等概念的理解。第三部分关于衍生工具的定价方法，二项式期权模型定价、布莱克-斯科尔斯期权定价模型的EXCEL实现与VBA编程计算。

实验一 更有效地使用EXCEL

实验目的与要求：

熟悉EXECL的运行环境，掌握数据导入方法，掌握图表与数据透视表的使用，了解常用函数的功能与使用方法。

实验指导：

一、概念释义：

1、数据输入与运算

在Excel环境下进行计算．所需要的数据大致有3种来源：手工输入、自动生成和从外部导入。当数据量很小而且又没有规律的情况下,一般采用手工输入的方法。而对于那些有规律的数据，如连续的数字或字符序列可以用Excel的填充命令自动生成。当数据量比较大或有现成的数据来源．如网络资源、数据库资源等可供应用，可以采用导入的方法。

  Excel可以读入不同来源、不同格式的多种数据文件。执行Excel上的“文件” ”打开”命令，就可以直接读人数据库文件、Web文件、XML文件、文本文件以及其他格式的电子表格  文件等。对于连接在网络《局域网、广域网或Web)上的计算机，这些文件可以保存在网络上的任何位置——只要使用者具有访问权限都可以接读入Excel。在读入非Excel格式文件时，Excel会自动将文件转换成为工作表格式。对于某些格式的数据，在进行这种格式转换时，可能需要用户做出一些选择，如在读入文本文件(．txt、．rtf)时。需要用户指定分列方式．可能的方式包括按固定宽度分列或按指定字符分列。

   Excel工作表中的数据有两种基本情况：由用户输入或导入的数据：经过计算得到的数据。用户直接在单元格中输入的数据属于前者，而后者则是通过公式或函数进行计算后得到的。在Excel中对于后面一类统称为公式，它是由等号开始，包括常量、对单元格或单元格区域的引用、运算符和函数等几种成分。在Excel中通过运算符可以执行多种运算：算术运算、文本运算、日期运算、比较运算。

2、数据运算与引用

输入或导入数据后，通过公式或函数进行加工。EXCEL常用运算包括：算术运算、文本运算、日期运算、比较运算。

数据引用：相对引用，如A1，B2；（当需要大量复制相同的公式）

          绝对引用，如$A$1；

          混合引用，$A1；

3、排序与筛选

排序按关键字重新排列顺序，筛选是从全体数据中符合条件的数据。

4、图表与数据透视表

图表是使用Excel进行各种运算分析时一个非常有用的工具．通过图表，可以更加直观地揭示数据之间的内在关系和变化趋势。本书中使用了大量的图表作为分析手段．来说明各种金融财务原理和表示计算结果。在Excel中创建图表比较简单．可以通过其内置的图表向导来自动生成各种不同类型的图表。单击常用工具栏上的按钮即可启动图表向导。使用图表向导制作图表分为4个步骤：选择图表类型．确定源数据．设定图表选项．安排图表位置。正确有效地制作图表关键在于前两步，首先要根据数据的性质和图表的目的选择合适的图表类型．其次要正确地设置源数据区、横纵坐标轴与数据系列标题。在图表选项中主要是对诸如图表标题、坐标轴标题、图表网格线、图例、数据标志等格式方面的要素进行设定，最后可以选择图表放置的位置，即可以放置在源数据所在工作表内．也可以建立单独的图表工作表。

   数据透视表是Excel中一项比较重要的高级功能。Excel的工作表本身是一个二维表，如果要用常规的Excel工作表表示具有多个维度的数据，就只能把数据”摊平”。然而，在实际应用中．经常需要同时按照多个方向对同一组数据进行分析。例如在分析企业销售收入的历史数据时．可能既要按照不同的时间如年份或季度来统计，又要按照部门或地区来统计．甚至会把几个维度结合在一起分析，这时就需要使用Excel的数据透视表。如果用一句话来概括数据透视表的本质．那就是数据透视表可以在二维的表格里表示三维或者的数据。

5、内置函数和自定义函数

 Excel作为商务计算的工具软件，其计算功能是通过函数和内置工具实现的。函数是构成Excel计算公式的主要元素之一。在一般性的计算应用和数据管理当中函数承担着绝大部分的工作。Excel大约内置有330个左右的函数，这些函数按照功能和作用的不同，可以分成11大类。包括：数据库函数．日期和时间函数，外部函数、工程函数、财务函数，信息函数，逻辑函数、查找和引用函数，数学和三角函数，统计函数以及文本和数据函数。

  所谓自定义函数是指用户根据自己的需要通过Excel内置的VBA(Visual Basic for Application)功能模块编写代码，设计出实现特定功能的函数。

4、假设分析工具

方案是保存在工作表当中并可以在相互之间进行切换的数据。当需要调整运算参数观察计算结果而又希望保存这些不同结果时，方案管理器是一个合适的工具。

所谓敏感分析是指通过多次使用不同的参数，对某个或一组公式的运算结果进行综合比较，从而了解不同参数在其可能的取值范围内对运算结果的影响程度。模拟运算表是一个理想的敏感分析工具，它充分发挥表格界面和运算公式的优势，用表格的形式表示出评价指标对分析指标的依赖程度。

已知某个公式的运算结果，要求确定其中某个参数的适当值，这种问题在数学上称为反函数。在EXCEL中，提供一个专门处理这类问题的工具单变量求解。

二、操作示例

  假定某投资项目，投资金额为10 000，项目周期为5年。项目的预期现金流量和贴现率都受经济形势的影响：如果经济不景气，则预期每年现金流量为1 200，贴现率为6％；如果经济繁荣，则预期每年现金流量为2000．贴现率为1 O％；如果经济形势一般，则现金流量为1 600。贴现率为8％。要求对该项目进行评价。

实验二货币的时间价值

实验目的与要求：

货币的时间价值是一切金融计算的基础，在本实验中关键的概念是复利和贴现。通过实验要求学生掌握EXCEL常用财务函数使用。

实验指导：

一、概念释义：

  现值是指未来预期现金流现在的价值，净现值是现值减去初始成本。现值和净现值问题是学习资金时间价值的开始，理解资金时间价值含义并利用Excel实现其计算过程是学好本书的良好开端。

  1．现值

  若已知T期末将拥有一现金流CT，则其在折现率r下的现值为：

PV=CT/（1+r）T

  2.净现值

  净现值是未来的现金流的现值减去初始的投资现金流。

  3.终值

  指现金流在未来某时刻的价值。

  4．年金

  指一系列有稳定规律的、持续一段固定时期的现金收付活动。

  注：4个变量参与运算，RATE（），NPER（），函数名为计算内容。

二、操作示例

例1、假设今年年初将1000存入银行，并将在以后的9年中每年年初存入1000元，那么第十年年初的银行存款余额为多少？（终值）

例2．某人需要在4年后从银行取出10万元出国留学，假设银行按5%的复利计息，问现在必须一次性存入多少钱？

例3．某投资项目能在未来4年产生90元，100元，110元和80元的确定现金流，折现率是8%，求该投资的项目的现值。（多重现金流）

步骤1

步骤2

步骤3

步骤4

例4、假设某某客户为购房，申请了100000元的房屋抵押贷款，贷款的年利率6%，银行要求10年内还清，则每年末款多少？

例5、计息期与连续利率

例6、某人在银行取得了一笔一次性住房贷款，已知贷款额为30万元，银行复利计息，若10年后贷款本息和为45万元，求贷款年利率？

例7、某项目投资300万元后，将在未来4年每年获100万元的现金流入，采用的贴现率为10%，决策该项目可行。（内部收益函数，单变量方程）

实验三  债券收益与风险评估                       

实验目的与要求：

了解债券的市场价格与收益率的关系；债券的主要风险为利率风险，久期和凸性是刻画债券的两个重要特征。本实验要求熟悉债券的久期概念，掌握久期的计算。

实验指导：

一、概念释义

1、债券的价值与收益

债券的计算主要关注的两个指标价格和收益率，其基本原则是贴现现金流。

2、债券的久期

久期是指固定收益债券现金流支付时间的加权平均。权重是指每次支付的现金流的现值在债券总价中所占的比例。

久期作为债券价格相对贴现率的弹性， 

3、修正久期

修正久期越大债券的价格变化幅度越大，利率风险越高。

设D为久期，r为贴现率（市场利率），则修正久期（MD）可定义为D/（1+r）

由，可得

二、操作示例

例1、某15年到期债券，息票年利率为10%，面值为1000元，试析债券票面利率与市场利率关系

例2、债券久期计算（原理）

3、久期度量债券的价格相对贴现率的弹性

（求导）

4、 EXCELL久期函数计算久期

5、修正久久期（将修正期与市场利率的预期相结合对债券价格变化率进行估计）

6、债券价格近似计算

7、股票估值

实验四  投资组合模型计算      

实验目的与要求：

掌握现代投资理论及其实现过程，学会证券的收益率矩阵和数字特征计算；计算投资组合的有效前沿，资本资产定价理论的EXCELL实现。

实验指导：

一、概念释义

股票的月收益率计算分为连续收益率和离散收益率两种，连续收益率为；离散收益率为；在考虑股利收入条件下，连续收益率为，离散收益率为

期望收益是指持有股票的投资者在下一个时期所能获得的收益。单个股票的期望收益可通过历史数据的样本均值来估计。

资产随机收益率的方差是偏离预期的期望，标准差是方差的平方根。方差、标准差刻画收益率变动，是风险的常用度量指标。

协方差是度量两种风险资产收益之间线性关联度的统计指标，正协方差收益为同向变动，负协方差收益为反向变动。

相关系数刻画两种随机变量的线性关联度。

二、操作示例

1、期望收益的计算

（1）计算股票的每月收益率

（2）计算股票的月期望收益率

（3）计算股票的年收益率

2、方差与标准差

在EXCELL中方差，样本方差，标准差，样本标准差分别用VAR、VARP、STEDV、STEDVP。通过EXCELL中的工具栏[fx]/[统计]。

3、协方差、相关系数

选择[fx]/[统计]/[covar]命令，对话框中选择数据区域，完成后单击确定。

选择[fx]/[统计]/[correl]命令，对话框中选择数据区域，完成后单击确定。

4、投资组合的期望收益和方差计算

运用EXCEL中的[模拟运算表]可计算出两种股票任意投资组合的期望收益和方差。

（1）建立工作表

（2）选定需要计算的单元区域

（3）单击[数据]菜单中的模拟运算表

（4）单击[确定]

使用EXCEL的图表功能绘制标准差—收益曲线。

5、多个资产投资组合

（1）计算风险资产构成的给定组合的数字特征

（2）由两个组合构成标准差—期望收益曲线

（3）描绘资产在坐标中的位置

（4）利用模拟运算表求不同投资比例下的期望收益与标准差

6、超额收益法计算方差—协方差矩阵

（1）计算每个资产的期望收益率

（2）计算超额收益矩阵

（3）计算超额收益转置矩阵

（4）计算方差协方差矩阵

实验五  有效前沿与资本资产定价模型

实验目的与要求：

均值方差与有效前沿理论是现代投资理论的基本内容，本实验在上一个实验的基础上探讨有效前沿与资本资产定价模型的计算问题。

实验指导：

一、概念释义

1、可行投资组合：满足投资比例之和为1的组合；

2、可行集：由所有可行投资组合构成的集合；

3、前沿投资组合：对于任意给定的期望收益水平，所有具有最小方差的投资组合构成的集合；

4、有效投资组合：对于任意给定的方差或标准差，具有最大收益率的投资组合；

5、有效前沿：由所有有效投资组合构成的集合；

6、资本市场线（CML）：指在标准差—期望收益坐标系中无风险资产和有效前沿曲线的切线，CML刻画了在存在无风险证券且无借贷时的投资组合有效前沿，此时有效前沿退化为直线。

7、证券市场线（SML）刻画任何资产或投资组合的期望收益与其对应的贝塔系数之间的线性关系。

二、操作示例

1、有效前沿计算

（1）资产组合

（2）两个前沿组合的计算

=MMULT(MINVERSE(C2:F5),E8:E11)      =F8/SUM(F$8:F$11)

3、市场组合M的计算与绘制

   假设有效组合N和无风险资产的投资比例为a,则新的资产组合P的期望收益和标准差分别为：      

  和  

4、证券市场线计算与绘制

贝塔系数计算方法   =SLOPE（B2：B11，$D$2:$D$11）

               或  =COVAR（B2：B11，$D$2:$D$11）/VARP（$D$2:$D$11）

5、有效前沿VBA计算程序

实验六   期权定价模型计算              

实验目的与要求：

衍生证券是一种重要的金融资产，本实验复习期权定价理论的基础上，给出期权的收入函数和利润曲线，掌握期权和股票的投资策略及EXCEL计算。

实验指导：

一、概念释义

1、看涨期权

2、看跌期权

3、执行价格

4、终止日期

5、股票价格

6、实值、虚值和两平

二、操作示例

1、股票交易的利润函数

设7月份以市场价格40元买入或卖空一份股票A，如果持有或结清至9月份，股票交易的利润函数。

2、期权买卖利润函数

3、保护性看跌期权（S+P）

4、抛补的看涨期权（S-C）

5、垂直价差套购（C1-C2）

以不同的执行价格买入一份（或多份价格不同）看涨期权和卖出一份（或多份不同价）看涨期权（看跌期权）。买入的执行价低，卖出的执行价高，为“牛市价差套购”；反之，为“熊市价差套购”

6、（C+P）

同时买进具有相同执行价格与相同到期时间的同一种股票的看涨与看跌期权。

7、双限期权（S+P-C）

实验七   二项式期权定价模型计算              

实验目的与要求：

二项式期权定价模型假定标的资产（股票）在每个时间周期的期末价格只有两种可能状态（上涨或下跌）对应的股票及股票期权的价格变化过程可以用二叉树表示，依据风险中性定价方法，通过计算每个树叉的状态价格来对期权定价。

本实验要求学生了解期权中性风险定价机制，掌握单阶段欧式二项期权定价模型的EXCEL实现过程，并将之推广到美式期权定价的计算，应用VBA编定期权定价程序。

实验指导：

一、概念释义

期权复制是指通过股票与无风险债券来构造一个投资组合，并使得该组合在任何状态下（股票价格上涨或下跌）的未来现金流和该看涨期权的未来现金流完全相同。1call=A shares + B bonds。依据无套利定价原则，看涨期权的（现在）价格和其复制（股票和债券的组合）的（现在）价格相等，即期权可通过债券和股票来定价。

二、操作示例

1、单阶段二项期权定价模型：

（1）依据无套利原则通过期权复制的方法定价

（2）依据风险中性原则计算状态价格定价

（3）平价关系检验

对应看跌期权的价格为1.11元，且股票和看跌期权价值之和与看涨期权和执行价格的现值之和相等，平价关系成立。

2、二阶段的二项式定价模型

3、欧式期权的VB编程计算

（1）看涨期权

（2）看跌期权

4、美式期权的VB编程计算

（1）看涨期权

（2）看跌期权

实验八    Black-Schols定价模型计算 

实验目的与要求：

布莱克-斯科尔斯（Black-scholes，1973）期权定价公式是金融和经济学研究最多应用最广的期权定价公式，通过本实验学生了解该公式的两个假设：无风险证券的利率和股票价格的标准差在期权的执行期间保持不变，且考虑的是连续时间周期的情形。掌握BS公式的Excel实现过程，运用VB定义简化BS公式的计算，并根据期权的定价反推股票价格的波动性。

实验指导：

一、概念释义

Black-Scholes公式的欧式看涨和看跌期权的价格C和P分别为：

  式中，

二、操作示例

1、Black-Schols定价公式的EXCEL实现

2、期权价格和内在价值的分析

3、Black-Schols定价VB编程计算

4、隐含波动率的VB编程计算

5、运用VBA函数计算隐含波动率

习题：

1、一名学生在入学时从银行申请助学贷款40000元，按贷款合同，该生要从毕业起10年内分期偿还这笔贷款，每年年终偿还固定金额，贷款利率为5%，从该生毕业起开始计息。要求计算该生毕业后每年须偿还的贷款金额，以及各年偿还金额中本金和利息各多少？

2、某公司上一年实际派发股利每股0.8元，预期该公司的股利将保持10%的增长率，该股预期收益率为15%，预测未来8年中，每年的股利与股价，并画趋势图。

3、假定某股票当前的市场价格为100元，一年后它的价格有两种可能，一种是上涨20%，为120元，另一种下降10%，为90元。当前无险利率为5%。该股票的欧式看涨期权执行价格与当前股票价格相同，一年后到期。用二项式期权定价模型计算期权价格。