EXCEL金融计算实验指导.docx
《EXCEL金融计算实验指导.docx》由会员分享，可在线阅读，更多相关《EXCEL金融计算实验指导.docx（45页珍藏版）》请在冰点文库上搜索。
EXCEL金融计算实验指导
《金融学》实验指导手册
EXCEL 金融计算
南京审计学院金融学院
1
前言
本实验指导手册为金融学院《金融学》、《证券投资学》课程
配套书。
该实验指导手册侧重于培养学生应用《金融学》、《证券
投资学》课程所学的基本原理，利用 EXCEL 软件为计算工具，分
析各种金融工具的风险与收益能力。
全手册共分三部分。
第一部分复习现值、终值、年金等财务
概念，了解 EXCEL 工具环境与常用财务函数的使用，关于债券、
股票等原生工具的定价，利用久期模型分析债券利率风险。
第二
部分关于现代投资理论构造资产组合，通过计算加深对有效前沿、
资本市场线和证券市场线等概念的理解。
第三部分关于衍生工具
的定价方法，二项式期权模型定价、布莱克-斯科尔斯期权定价
模型的 EXCEL 实现与 VBA 编程计算。
2
实验一 更有效地使用 EXCEL
实验目的与要求：
熟悉 EXECL 的运行环境，掌握数据导入方法，掌握图表与数据透视表的使用，了解
常用函数的功能与使用方法。
实验指导：
一、概念释义：
1、数据输入与运算
在 Excel 环境下进行计算．所需要的数据大致有 3 种来源：
手工输入、自动生成和
从外部导入。
当数据量很小而且又没有规律的情况下,一般采用手工输入的方法。
而对
于那些有规律的数据，如连续的数字或字符序列可以用 Excel 的填充命令自动生成。
当
数据量比较大或有现成的数据来源．如网络资源、数据库资源等可供应用，可以采用导
入的方法。
Excel 可以读入不同来源、不同格式的多种数据文件。
执行 Excel 上的“文件”→”
打开”命令，就可以直接读人数据库文件、Web 文件、XML 文件、文本文件以及其他格
式的电子表格 文件等。
对于连接在网络《局域网、广域网或 Web）上的计算机，这些文
件可以保存在网络上的任何位置——只要使用者具有访问权限都可以接读入 Excel。
在
读入非 Excel 格式文件时，Excel 会自动将文件转换成为工作表格式。
对于某些格式的
数据，在进行这种格式转换时，可能需要用户做出一些选择，如在读入文本文件
（．txt、．rtf）时。
需要用户指定分列方式．可能的方式包括按固定宽度分列或按指定
字符分列。
Excel 工作表中的数据有两种基本情况：
由用户输入或导入的数据：
经过计算得到
的数据。
用户直接在单元格中输入的数据属于前者，而后者则是通过公式或函数进行计
算后得到的。
在 Excel 中对于后面一类统称为公式，它是由等号开始，包括常量、对单
元格或单元格区域的引用、运算符和函数等几种成分。
在 Excel 中通过运算符可以执行
多种运算：
算术运算、文本运算、日期运算、比较运算。
2、图表与数据透视表
图表是使用 Excel 进行各种运算分析时一个非常有用的工具．通过图表，可以更加
直观地揭示数据之间的内在关系和变化趋势。
本书中使用了大量的图表作为分析手
3
段．来说明各种金融财务原理和表示计算结果。
在 Excel 中创建图表比较简单．可以通
过其内置的图表向导来自动生成各种不同类型的图表。
单击常用工具栏上的按钮即可启
动图表向导。
使用图表向导制作图表分为 4 个步骤：
选择图表类型．确定源数据．设定
图表选项．安排图表位置。
正确有效地制作图表关键在于前两步，首先要根据数据的性
质和图表的目的选择合适的图表类型．其次要正确地设置源数据区、横纵坐标轴与数据
系列标题。
在图表选项中主要是对诸如图表标题、坐标轴标题、图表网格线、图例、数
据标志等格式方面的要素进行设定，最后可以选择图表放置的位置，即可以放置在源数
据所在工作表内．也可以建立单独的图表工作表。
数据透视表是 Excel 中一项比较重要的高级功能。
Excel 的工作表本身是一个二维
表，如果要用常规的 Excel 工作表表示具有多个维度的数据，就只能把数据”摊平”。
然而，在实际应用中．经常需要同时按照多个方向对同一组数据进行分析。
例如在分析
企业销售收入的历史数据时．可能既要按照不同的时间如年份或季度来统计，又要按照
部门或地区来统计．甚至会把几个维度结合在一起分析，这时就需要使用 Excel 的数据
透视表。
如果用一句话来概括数据透视表的本质．那就是数据透视表可以在二维的表格
里表示三维或者多维的数据。
3、内置函数和自定义函数
ExceI 作为商务计算的工具软件，其计算功能是通过函数和内置工具实现的。
函数
是构成 Excef 计算公式的主要元素之一。
在一般性的计算应用和数据管理当中函数承担
着绝大部分的工作。
ExceI 大约内置有 330 个左右的函数，这些函数按照功能和作用的
不同，可以分成 11 大类。
包括：
数据库函数．日期和时间函数，外部函数、工程函数、
财务函数，信息函数，逻辑函数、查找和引用函数，数学和三角函数，统计函数以及文
本和数据函数。
所谓自定义函数是指用户根据自己的需要通过 Excel 内置的 VBA（Visual Basic for
Application）功能模块编写代码，设计出实现特定功能的函数。
4、假设分析工具
方案是保存在工作表当中并可以在相互之间进行切换的数据。
当需要调整运算参数
观察计算结果而又希望保存这些不同结果时，方案管理器是一个合适的工具。
所谓敏感分析是指通过多次使用不同的参数，对某个或一组公式的运算结果进行综
合比较，从而了解不同参数在其可能的取值范围内对运算结果的影响程度。
模拟运算表
4
是一个理想的敏感分析工具，它充分发挥表格界面和运算公式的优势，用表格的形式表
示出评价指标对分析指标的依赖程度。
已知某个公式的运算结果，要求确定其中某个参数的适当值，这种问题在数学上称
为反函数。
在 EXCEL 中，提供一个专门处理这类问题的工具单变量求解。
二、操作示例
假定某投资项目，投资金额为 10 000，项目周期为 5 年。
项目的预期现金流量和贴现
率都受经济形势的影响：
如果经济不景气，则预期每年现金流量为 1 200，贴现率为 6
％；如果经济繁荣，则预期每年现金流量为 2000．贴现率为 1 O％；如果经济形势一般，
则现金流量为 1 600。
贴现率为 8％。
要求对该项目进行评价。
5
6
实验二货币的时间价值
实验目的与要求：
货币的时间价值是一切金融计算的基础，在本实验中关键的概念是复利和贴现。
通过实
验要求学生掌握 EXCEL 常用财务函数使用。
实验指导：
一、概念释义：
现值是指未来预期现金流现在的价值，净现值是现值减去初始成本。
现值和净现值问
题是学习资金时间价值的开始，理解资金时间价值含义并利用 Excel 实现其计算过程是
学好本书的良好开端。
1．现值
若已知 T 期末将拥有一现金流 CT，则其在折现率 r 下的现值为：
PV=CT/（1+r）T
2.净现值
净现值是未来的现金流的现值减去初始的投资现金流。
3.终值
指现金流在未来某时刻的价值。
4．年金
指一系列有稳定规律的、持续一段固定时期的现金收付活动。
注：
4 个变量参与运算，RATE（），NPER（），函数名为计算内容。
二、操作示例
例 1．某投资项目能在未来 4 年产生 90 元，100 元，110 元和 80 元的确定现金流，折现
率是 8%，求该投资的项目的现值。
（多重现金流）
步骤 1
7
步骤 2
步骤 3
步骤 4
8
例 2、假设今年年初将 1000 存入银行，并将在以后的 9 年中每年年初存入 1000 元，
那么第十年年初的银行存款余额为多少？
（终值）
例 3、假设某某客户为购房，申请了 100000 元的房屋抵押贷款，贷款的年利率 6%，
银行要求 10 年内还清，则每年末款多少？
9
例 4、计息期与连续利率
例 5 某项目投资 300 万元后，将在未来 4 年每年获 100 万元的现金流入，采用的贴
现率为 10%，决策该项目可行。
（内部收益函数，单变量方程）
10
实验三债券利率风险评估
实验目的与要求：
债券的主要风险为利率风险，久期和凸性是刻画债券的两个重要特征。
本实验要求
熟悉债券的久期概念，掌握久期的计算。
（市场风险、信用风险）
实验指导：
一、概念释义
1、债券的价值与收益
债券的计算主要关注的两个指标价格和收益率，其基本原则是贴现现金流。
2、债券的久期
久期是指固定收益债券现金流支付时间的加权平均。
权重是指每次支付的现金流的
现值在债券总价中所占的比例。
D =
1
p
n
t=1
t
t
久期作为债券价格相对贴现率的弹性，
dP / P
dr /（1+ r）
= -D
3、修正久期
修正久期越大债券的价格变化幅度越大，利率风险越高。
设 D 为久期，r 为贴现率（市场利率），则修正久期（MD）可定义为 D/（1+r）
由 MD = -
1 dp
p dr
，可得 ∆p = - px∆rxMD
二、操作示例
1、债券价格计算（债券票面利率与市场利率关系）
11
2、债券久期计算（原理）
3、久期度量债券的价格相对贴现率的弹性
dP
dr
N
t+1
= -
DP
1 + r
（求导）
12
4、 EXCELL 久期函数计算久期
5、修正久久期（将修正期与市场利率的预期相结合对债券价格变化率进行估计）
MD = -
1 dP
p dr
∆p ≅ -P ⨯ ∆r ⨯ MD
6、债券价格近似计算
13
14
实验四投资组合模型计算
实验目的与要求：
掌握现代投资理论及其实现过程，学会证券的收益率矩阵和数字特征计算；计算投资组
合的有效前沿，资本资产定价理论的 EXCELL 实现。
实验指导：
一、概念释义
股票的月收益率计算分为连续收益率和离散收益率两种，连续收益率为 r = ln
P
P -1
；离散
收益率为 r =
P + Divt
为 r =
P -1
P
P -1
-1
P + Divt
P -1
，离散收益率
期望收益是指持有股票的投资者在下一个时期所能获得的收益。
单个股票的期望收益可
通过历史数据的样本均值来估计。
资产随机收益率的方差是偏离预期的期望，标准差是方差的平方根。
方差、标准差刻画
收益率变动，是风险的常用度量指标。
协方差是度量两种风险资产收益之间线性关联度的统计指标，正协方差收益为同向变动，
负协方差收益为反向变动。
相关系数刻画两种随机变量的线性关联度。
二、操作示例
1、期望收益的计算
（1）计算股票的每月收益率
（2）计算股票的月期望收益率
（3）计算股票的年收益率
15
2、方差与标准差
在 EXCELL 中方差，样本方差，标准差，样本标准差分别用 VAR、VARP、STEDV、
STEDVP。
通过 EXCELL 中的工具栏[fx]/[统计]。
3、协方差、相关系数
选择[fx]/[统计]/[covar]命令，对话框中选择数据区域，完成后单击确定。
选择[fx]/[统计]/[correl]命令，对话框中选择数据区域，完成后单击确定。
4、投资组合的期望收益和方差计算
16
运用 EXCEL 中的[模拟运算表]可计算出两种股票任意投资组合的期望收益和方差。
（1） 建立工作表
（2） 选定需要计算的单元区域
（3） 单击[数据]菜单中的模拟运算表
（4） 单击[确定]
使用 EXCEL 的图表功能绘制标准差—收益曲线。
5、多个资产投资组合
（1）计算风险资产构成的给定组合的数字特征
（2）由两个组合构成标准差—期望收益曲线
（3）描绘资产在坐标中的位置
（4）利用模拟运算表求不同投资比例下的期望收益与标准差
17
6、超额收益法计算方差—协方差矩阵
（1）计算每个资产的期望收益率
18
（2）计算超额收益矩阵
（3）计算超额收益转置矩阵
（4）计算方差协方差矩阵
19
实验五有效前沿与资本资产定价模型
实验目的与要求：
均值方差与有效前沿理论是现代投资理论的基本内容，本实验在上一个实验的基础
上探讨有效前沿与资本资产定价模型的计算问题。
实验指导：
一、概念释义
1、 可行投资组合：
满足投资比例之和为 1 的组合；
2、 可行集：
由所有可行投资组合构成的集合；
3、 前沿投资组合：
对于任意给定的期望收益水平，所有具有最小方差的投资组合构成
的集合；
4、 有效投资组合：
对于任意给定的方差或标准差，具有最大收益率的投资组合；
5、 有效前沿：
由所有有效投资组合构成的集合；
6、 资本市场线（CML）：
指在标准差—期望收益坐标系中无风险资产和有效前沿曲线
的切线，CML 刻画了在存在无风险证券且无借贷限制时的投资组合有效前沿，此时
有效前沿退化为直线。
7、 证券市场线（SML）刻画任何资产或投资组合的期望收益与其对应的贝塔系数之间的
线性关系。
二、操作示例
1、 有效前沿计算
（1） 资产组合
20
（2） 两个前沿组合的计算
=MMULT（MINVERSE（C2:
F5）,E8:
E11）=F8/SUM（F$8:
F$11）
3、市场组合 M 的计算与绘制
假设有效组合 N 和无风险资产的投资比例为 a,则新的资产组合 P 的期望收益和标准
差分别为：
E（rp ） = arf + （1- a）E（rN ）
和
p2
σ 2 = （1- a）2σ N
21
4、证券市场线计算与绘制
E（rx） = rf + βx（E（rM） -rf ）
22
贝塔系数计算方法=SLOPE（B2：
B11，$D$2:
$D$11）
或 =COVAR（B2：
B11，$D$2:
$D$11）/VARP（$D$2:
$D$11）
5、有效前沿 VBA 计算程序
23
实验六期权定价模型计算
实验目的与要求：
衍生证券是一种重要的金融资产，本实验复习期权定价理论的基础上，给出期权的
收入函数和利润曲线，掌握期权和股票的投资策略及 EXCEL 计算。
实验指导：
一、概念释义
1、 看涨期权
2、 看跌期权
3、 执行价格
4、 终止日期
5、 股票价格
6、 实值、虚值和两平
二、操作示例
1、股票交易的利润函数
设 7 月份以市场价格 40 元买入或卖空一份股票 A，如果持有或结清至 9 月份，股票
交易的利润函数。
24
三、期权买卖利润函数
四、保护性看跌期权（S+P）
25
五、抛补的看涨期权（S-C）
六、垂直价差套购（C1-C2）
以不同的执行价格买入一份（或多份价格不同）看涨期权和卖出一份（或多份不同价）
看涨期权（看跌期权）。
买入的执行价低，卖出的执行价高，为“牛市价差套购”；反之，
为“熊市价差套购”
26
七、对敲（C+P）
同时买进具有相同执行价格与相同到期时间的同一种股票的看涨与看跌期权。
八、双限期权（S+P-C）
27
实验七二项式期权定价模型计算
实验目的与要求：
二项式期权定价模型假定标的资产（股票）在每个时间周期的期末价格只有两种可
能状态（上涨或下跌）对应的股票及股票期权的价格变化过程可以用二叉树表示，依据
风险中性定价方法，通过计算每个树叉的状态价格来对期权定价。
本实验要求学生了解期权中性风险定价机制，掌握单阶段欧式二项期权定价模型的
EXCEL 实现过程，并将之推广到美式期权定价的计算，应用 VBA 编定期权定价程序。
实验指导：
一、概念释义
期权复制是指通过股票与无风险债券来构造一个投资组合，并使得该组合在任何状
态下（股票价格上涨或下跌）的未来现金流和该看涨期权的未来现金流完全相同。
1call=A shares + B bonds。
依据无套利定价原则，看涨期权的（现在）价格和其复制
（股票和债券的组合）的（现在）价格相等，即期权可通过债券和股票来定价。
二、操作示例
1、单阶段二项期权定价模型：
（1）依据无套利原则通过期权复制的方法定价
28
（2）依据风险中性原则计算状态价格定价
（3）平价关系检验
对应看跌期权的价格为 1.11 元，且股票和看跌期权价值之和与看涨期权和执行价
格的现值之和相等，平价关系成立。
29
2、二阶段的二项式定价模型
3、欧式期权的 VB 编程计算
（1）看涨期权
30
（2）看跌期权
4、美式期权的 VB 编程计算
（1）看涨期权
31
（2）看跌期权
32
实验八Black-Schols 定价模型计算
实验目的与要求：
布莱克-斯科尔斯（Black-scholes，1973）期权定价公式是金融和经济学研究最多
应用最广的期权定价公式，通过本实验学生了解该公式的两个假设：
无风险证券的利率
和股票价格的标准差在期权的执行期间保持不变，且考虑的是连续时间周期的情形。
掌
握 BS 公式的 Excel 实现过程，运用 VB 定义简化 BS 公式的计算，并根据期权的定价反
推股票价格的波动性。
实验指导：
一、概念释义
Black-Scholes 公式的欧式看涨和看跌期权的价格 C 和 P 分别为：
C = SN （d1） - Xe-rT N （d 2）
P = Xe-rT N （-d 2） - SN （-d1）
式中 d1 =
ln（S / X ） + （r + σ 2 / 2）T
σ T
， d 2 = d1- σ T
二、操作示例
1、Black-Schols 定价公式的 EXCEL 实现
2、期权价格和内在价值的分析
33
3、Black-Schols 定价 VB 编程计算
4、隐含波动率的 VB 编程计算
34
5、运用 VBA 函数计算隐含波动率
习题：
1、 一名学生在入学时从银行申请助学贷款 40000 元，按贷款合同，该生要从毕业起 10
年内分期偿还这笔贷款，每年年终偿还固定金额，贷款利率为 5%，从该生毕业起开
始计息。
要求计算该生毕业后每年须偿还的贷款金额，以及各年偿还金额中本金和
利息各多少？
35
2、 某公司上一年实际派发股利每股 0.8 元，预期该公司的股利将保持 10%的增长率，
该股预期收益率为 15%，预测未来 8 年中，每年的股利与股价，并画趋势图。
3、 假定某股票当前的市场价格为 100 元，一年后它的价格有两种可能，一种是上涨
20%，为 120 元，另一种下降 10%，为 90 元。
当前无险利率为 5%。
该股票的欧式看
涨期权执行价格与当前股票价格相同，一年后到期。
用二项式期权定价模型计算期
权价格。
36
升级会员 