EXCEL金融计算实验指导

《金融学》试验指导手册

EXCEL金融计算

南京审计学院金融学院

前言

本试验指导手册为金融学院《金融学》、《证券投资学》课程配套书。

该试验指导手册侧重于培育学生应用《金融学》、《证券投资学》课程所学

的基本原理，利用EXCEL软件为计算工具，分析各种金融工具的风险与收

益实力。

全手册共分三部分。第一部分复习现值、终值、年金等财务概念，了

解EXCEL工具环境与常用财务函数的运用，关于债券、股票等原生工具的

定价，利用久期模型分析债券利率风险。其次部分关于现代投资理论构造

资产组合，通过计算加深对有效前沿、资本市场线和证券市场线等概念的

理解。第三部分关于衍生工具的定价方法，二项式期权模型定价、布莱克

-斯科尔斯期权定价模型的EXCEL实现与VBA编程计算。

试验一更有效地运用EXCEL

试验目的与要求：

熟识EXECL的运行环境，驾驭数据导入方法，驾驭图表与数据透视表

的运用，了解常用函数的功能与运用方法。

试验指导：

一、概念释义：

1、数据输入与运算

在Excel环境下进行计算.所须要的数据大致有3种来源：手工输入、

自动生成和从外部导入。当数据量很小而且又没有规律的状况下，一般采

纳手工输入的方法。而对于那些有规律的数据，如连续的数字或字符序列

可以用Excel的填充吩咐自动生成。当数据量比较大或有现成的数据来

源.如网络资源、数据库资源等可供应用，可以采纳导入的方法。

Excel可以读入不同来源、不同格式的多种数据文件。执行Excel上

的“文件”“打开“吩咐，就可以干脆读人数据库文件、Web文件、XML

文件、文本文件以与其他格式的电子表格文件等。对于连接在网络《局

域网、广域网或Web)上的计算机，这些文件可以保存在网络上的任何位置

——只要运用者具有访问权限都可以接读入Excel,在读入非Excel格式

文件时，Excel会自动将文件转换成为工作表格式。对于某些格式的数据,

在进行这种格式转换时，可能须要用户做出一些选择，如在读入文本文件

(.txt、,rtf)B'Jo须要用户指定分列方式.可能的方式包括按固定宽度

分列或按指定字符分列。

向对同一组数据进行分析。例如在分析企业销售收入的历史数据时,可能

既要依据不同的时间如年份或季度来统计，又要依据部门或地区来统

计.甚至会把几个维度结合在一起分析，这时就须要运用Excel的数据透

视表。假如用一句话来概括数据透视表的本质.那就是数据透视表可以在

二维的表格里表示三维或者多维的数据。

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

Excel作为商务计算的工具软件，其计算功能是通过函数和内置工具实

现的。函数是构成Excef计算公式的主要元素之一。在一般性的计算应用

和数据管理当中函数担当着绝大部分的工作。Excel大约内置有330个左

右的函数，这些函数依据功能和作用的不同，可以分成11大类。包括：

数据库函数.日期和时间函数，外部函数、工程函数、财务函数，信息函

数，逻辑函数、查找和引用函数，数学和三角函数，统计函数以与文本和

数据函数。

所谓自定义函数是指用户依据自己的须要通过Excel内置的

VBA(VisualBasicforApplication)功能模块编写代码，设计出实现特

定功能的函数。

4、假设分析工具

方案是保存在工作表当中并可以在相互之间进行切换的数据。当须要

调整运算参数视察计算结果而乂希望保存这些不同结果时，方案管理器是

一个合适的工具。

所谓敏感分析是指通过多次运用不同的参数，对某个或一组公式的运

算结果进行综合比较，从而了解不同参数在其可能的取值范围内对运算结

果的影响程度。模拟运算表是一个志向的敏感分析工具，它充分发挥表格

界面和运算公式的优势，用表格的形式表示出评价指标对分析指标的依靠

程度。

已知某个公式的运算结果，要求确定其中某个参数的适当值，这种问

题在数学上称为反函数。在EXCEL中，供应一个特地处理这类问题的工具

单变量求解。

二、操作示例

假定某投资项目，投资金额为10000,项目周期为5年。项目的预期现

金流量和贴现率都受经济形势的影响：假如经济不景气，则预期每年现金

流量为1200,贴现率为6%；假如经济旺盛，则预期每年现金流量为

2000.贴现率为10%；假如经济形势一般，则现金流量为1600o贴现

率为8%。要求对该项目进行评价。

SlicroirortK»c«l-Unoki同反

文芾Q）-a）根由⑺■入9格式切IAQ）矽需0）900）俯aQP健人一妻帮—闷餐・■3x

必金/」U飞g*△・，，•士£・21•&心即・“三

:宗体*12*BZU♦■混%,2/।IF*UA•j

A卜BI

—麻舌理应用

2

投费10000

Hn

项目斗侬5"&fticeOnline

5年现金流2000

贴现率._____也

工

NVPI

工

8

9

试验二货币的时间价值

试验目的与要求：

货币的时间价值是一切金融计算的基础，在本试验中关键的概念是复利和

贴现。通过试验要求学生驾驭EXCEL常用财务函数运用。

试验指导：

一、概念释义：

现值是指将来预期现金流现在的价值，净现值是现值减去初始成本。现

值和净现值问题是学习资金时间价值的起先，理解资金时间价值含义并利

用Excel实现其计算过程是学好本书的良好开端。

1.现值

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

PV=3/(1+r)T

2.净现值

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

3.终值

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

4.年金

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

注：4个变量参加运算，RATE(),NPER(),函数名为计算内容-

二、操作示例

例1.某投资项目能在将来4年产生90元，100元，110元和80元的确定

现金流，折现率是8%,求该投资的项目的现值。（多重现金流）

步骤1

步骤2

tExcel-Bookl

（X）!!S（y）ftAO）格式如TAO）跖露6）«□«）电船QP

rJ2-

Er

步骤3

步

聚4

1<1licrosoftExcel-BookI

.淤帼&F檎丽■入g出:切工具口)

cIDI8I

I!

例2、假设今年年初将1000存入银行，并将在以后的9年中每年年初

存入1000元，那么第十年年初的银行存款余额为多少？（终值）

国licroxoftK«r.il-Bnnklq

：aj文件/«m(r)■入(pISKQ)ZAQ)总%。voqp♦射qpm/swwitenHB・.，X

一支▲U一“一AJ&山J心△，-ZJ-rJ_A_X_・2/4U“从・>_F.H

,iatotiCP,A.A-■

"-X>✓]A=FV(B3,A16,-1000,,1)

ABcDErGHIJ__KL1T

1总年存款线值何速

2-

3利率10%

4

5年例年为余荀存款劭当年利息公末东荀

60010CM)1001100;期B6KC

71110010002102310

82231010003313641

工336411000464.15105.1

1045105.11000610.51$715.61

1151000

①61000_1

7

U-1000FV

1421000

1591000

……6今10

E2

100..1)TTOOO-1«»

mrr[PfcjM

ra

■!'■

=17531J6TC6

♦于fiD*科*常网读分即付g方式.徐E）太殂投岱团去今循

ETyw0«1,痔定付加重旧・2啥43・1•翔刃；0或淳略

E■

■fM-M

£2

E-BBlIBB

E，关，介不商的电跄onJ*」!—

28

129

例3、假设某某客户为购房，申请了100000元的房屋抵押贷款，贷款

的年利率6蛤银行要求10年内还清，则每年末款多少？

国■^ro.oftEwcelBook1一一IzJlSB

♦为文件建)mi<D■入S。式3ZMa)矽一a♦口Q)密40P,入二・•，x

例4、计息期与连续利率

四licrosoftExcel-Book3L.lgJS

:9文猝9幅微，康梅丽一‘入GTIS^Q)ZAG)»«QF9Q<D俯前一・•♦X

i种j2-z_iLm«MaiMA-i-aLAiJLaaAAg»AJ

[_3▼/=至ECT($B$2,B<n

~'BCiDEFG~H-

1

2利率转控函数

名义单降

3APR12«

计息期计息次数百姓利串卧底

4

年

5-10.12=e££ect(1M2,B4)

-

620.1236

-■；0.12550831

7双月

一60.126162419

上月120.12682503

半月240.127159776

周520.127340937

362

例5某项目投资300万元后，将在将来4年每年获100万元的现金流

入，采纳的贴现率为10%,决策该项目可行。（内部收益函数，单

变量方程）

CUicra&otEjuceiBoofci

a]Xftqy也用⑦她入s的电）x*（i）«□!）制nqpw――日足•.»x

，归x•11ilB

神•12-B/U*喜羊/%%，：&思律

B17•%=B16-E16

年份现金㈱

00

200

300

400%

内郃收受率

贴现率0%

资金表

年份年初本金年fg付额所还利息所还本金

30010037.76%62.2305

2237.769510029.9347870.情522

3167.704310021.1136978.88631

488,8179710011.1820388,81797

L：.Y，同

20I

试验三债券利率风险评估

试验目的与要求：

债券的主要风险为利率风险，久期和凸性是刻画债券的两个重要特

征。本试验要求熟识债券的久期概念，驾驭久期的计算。（市场风险、信

用风险）

试验指导：

一、概念释义

1、债券的价值与收益

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

金流。

2、债券的久期

久期是指固定收益债券现金流支付时间的加权平均。权重是指每次支

付的现金流的现值在债券总价中所占的比例。之」.

〃官（1+尸）

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

力7（1+r）

3、修正久期

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

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

D/(Hr)

由MD--L芈，可得&)=-pxlSrxMD

par

二、操作示例

1、债券价格计算（债券票面利率与市场利率关系）

♦力文A3Mid）R田9―入（！）格无8XA<X>rBQ）■口*助蟆-Th，”一w•-，x

4q4・，一2J,L上<>,,:“■

-XV修NPV(b2.M：blS)

2

80

80

80

80

io

11

12

13

ILIL必

I心：bl5)]

",

17

IE

11-•

20

21

22

2S

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

1<1licro«oftExcel-BookI

：^j/微在-根山&BAa)<^(D)iAa)十努®十口，)俯而陵区何妻帮细

d—aM-d-2£二,—也”.；

♦定体*12«BzU'12网•j--・3'A・一

-Xx/AJsum(C5"M)

债券价格计售

JL市场甲蟀8%

3

50.074074=A5*b5/(&bl64(l+4bA2)A5)

60.137174

70.19052

80.23521

0.272233

0.302481

0.326755

800.345772

800.3601T9

10805.00249

债匆价格久期

>

¥1,000.007.24GS88|=gun(C5：cbl)1

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

dPq-CDP（求导）

~dr~为+广厂—-1+7

国

licroxoftExc«l-Book!iL■▲向▲汉I

xn«i）眠微出根图支）■入a）整式sxxa）划8②■口a＞记肥®健入佬/带IMT*

£」」J」，」，3乂4-LJ•2X•21：U,辿4>i00»-VA

;%体•is•Bzu*■，gT%,］力浓浓_二♦A♦昌

B19▼5

AB1cDEFG1HlJ1KLT

1债券价格计弊

2rS%

3r*Ar8.01%Ar/Cl+r)9.2592&E-05=(b3-b2)/(Bb2)

q年份CT,A

51800.074074'=A5*b5/(&bl«Kl+4U2)-A5)

62800.137174

73800.19052

84800.23521

95800.272233

106800.302481

U-7800.326755

128800.345772

139800.360179

1010805.00249

债劈价格久期

¥1,000.007.246888=suo(CS:cl4)

P*AP¥999.33=npv(b3,k5:bl4)

△p/p

D「17.24362=-B18/E3

4、EXCELL久期函数计算久期

口叵屈

根的9■入a)笥式aIACD效髡awoo)俯勒OP

^£2^2^©JAZ〃G・e,l.l：♦11.w”

",-X>/7r^lJRATibN(DATE(2004,2,l)：DATE(2014,?,l),8»,中,1)

-AB-ICDE~FC-

债券价格计算

8%

LAT8.01%

年份Ct,A

XJUTIOM

0.0740

DATE(2OO<,2.1)^39018

0.1371

■uvrityDATE©0112,D______勾：<16T1

30.190

国*006

40.235

0.2722n.

0.3024Fr«q«4ne)r3

0.3267

=T2<68»79H

30.3457近同按周0付3）皙价正券的雾周・月

90.3601

105.002TU有价证册的年收a本

债势价格久期

¥1,000.oo7.2468

P*AP计箕给果=1246OT911

△p/p置昊法三勖*）必防00

［风洛1）17.24以

5、修正久久期（将修正期与市场利率的预期相结合对债券价格改变

率进行估计）

MD」叱△p=-PxA/-xMD

pdr

1<1licro»oftExcel-Book!

：iJ文猝Q)根030)BAa)苟式⑥IAO)矽务®«□(1)俯aOP

D21▼H

债券价格计算

2r

3r*Ar△r/(l+r)9.25926BE5=(W-b2)/(Bb2)

q年份Ct,A

51.0.。7W=A2也WA5)

620.137174

70.19052

维0.23521

_?0.272233

100.302481

110.326755

800.345772

800.360179

10805.00249

债券价格

YL000.007.246883=SUJ>(C5:C14)

¥-0.00067

7.24362=-B18/E3

6・70Tg匕c19/11吐

6、债券价格近似计算

试验四投资组合模型计算

试验目的与要求：

驾驭现代投资理论与其实现过程，学会证券的收益率矩阵和数字特征计

算；计算投资组合的有效前沿，资本资产定价理论的EXCELL实现。

试验指导：

一、概念释义

股票的月收益率计算分为连续收益率和离散收益率两种，连续收益率为

rf=ln^-；离散收益率为在考虑股利收入条件下，连续收益率为

.In空外，离散收益率为广”也T

々-Ipl-\

期望收益是指持有股票的投资者在下一个时期所能获得的收益。单个股票

的期望收益可通过历史数据的样本均值来估计。

资产随机收益率的方差是偏高预期的期望，标准差是方差的平方根。方差、

标准差刻画收益率变动，是风险的常用度量指标。

协方差是度量两种风险资产收益之间线性关联度的统计指标，正协方差收

益为同向变动，负协方差收益为反向变动。

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

二、操作示例

1、期望收益的计算

(I)计算股票的每月收益率

(2)计算股票的月期望收益率

(3)计算股票的年收益率

<1licrosoftEscel-Bookl)回留

文台¥楠用9»Aa）0式电）IM0）矽务0）«□（!）俯册QP•-X

iJ_•»*£

：条体•12•B/UX・313%，［之1沮涯.--%7F

F18▼5

AB1CDEF1GH1IJKILMrz

1股票A股票B

2月份价格收益率价格收受率

302545

4124.88-0.0048.44.74-0.00579=ln(D4/D3)

5224.41-0.0190T46.90.04715

6323,597.0541T45.36f.03339

7426.460.11481150.770.112675

8526.570.01537653.220.047129

9627.910.037$7553.310.00169

10728.G40.02S81962.650.16144

L1829,720.03701665.60.046012

12932.稣0.10408.66.760.017528

131036.220.0937：78.60.1632W

U1137.240.02777278.14-0.005g7

151237.03-0.0056668.53-0.13123

1E，月期望收益率0.0327330.035051:average(83:E15)

17年收益率0.3928S30.420609

18I

19

9n

2、方差与标准差

在EXCELL中方差，样本方差，标准差，样本标准差分别用VAR、VARP、

STEDV\STEDVPo通过EMCELL中的工具栏［fx］/［统计］。

国licroxoftEMCRI-Book1

文节0）■入⑴电式电)IA0)»«•!)SOO)俯朗QP埋人雯爱斯切为MSB

2」i3山▲AU/2•2X•21•必“」•”：

■124日1u・■，且」，%•241逐季

Al▼5

AB1CD1E1FGHIJ1KLT

段场

,JL股票AB股票A

2月份价格收益率价格收益率收益率与均值差收益率与均值差乘枳

302545

q124.88-0.0048.44.74-0.00579=ln(D4/D3)-0.03764刻1-0.0408452d30.001534

5224.41-0.0190*46.90.04715-0.0518090980.01209896f.00063

6323.59-0.0知？<15.36f.03339-0.066907705-0.0684377760.004579

7426,460.11481150.770.1126750.0820735510.077624210.006371

8526.ST0.015576220.047129-0.01136】450.0120775??P・00021

9627,910.03797553.310.001690.005236911-0.033361094-0.00017

3728.M。.02581962.650.16144<006918420.126388991YL00087

11829.720.03701665.60.0460120.0042781640.0109612554.69E-05

12932,930.10408166.760.0175280.071345383-0.017522357f.00125

131036.220.0d37：78.60.1632680.0609724210.1282168410.007818

141137.240.02777278.14f.00557-0.004965714-0.0409203e90.000203

151237.03-0.0两68.53-0.13123-0.038392772-0.1662812440.0063次

16月期望收益率0.0327380.035051=average(E3:E15)彷方差0.001烟

17年收立率0.3928S30.420609=eie*12=covar(a<l：3l5.e4：el5)0.0020

E

E方差（月）0.00216*0.006329=varp(e4:el5)

困

1差（年）0.04^5550.079555

n0.0260030.075948=e19*12

199

3、协力差、相关系数

选择［fx］/［统计］/［covar］吩咐，对话框中选择数据区域，完成后单击

确定。

选择［fx］/［统计］/［correl］吩咐，对话框中选择数据区域，完成后单

击确定。

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

运用EXCEL中的［模拟运算表］可计算出两种股票随意投资组合的期望

收益和方差。

（1）建立工作表

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

（3）单击［数据］菜单中的模拟运算表

（4）单击［确定］

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

汽力文件daa>梅田9»Aa)&式s»IAQ)swovaa>ffmoi)建2♦二带切为川心--»x

LJ3AAd:「_•_>•二.4<>Vs

[%0•12•BZD•,f_•0*•-

M3，片_______________________________

~~kB'：IDEFGIHiIT~~T

3.389423691186330000%

0.31157283536683600然

08柝门,

0.0558193.—4236可惧拟运算样板

0.0795550.03505076

0.0755160.034877281

0.0716040.W4703W33.55000000%

0.0678430.034530324

3.M000000%

0.0642590.034356846

期3.45000000%

0.0608830.034183368

与3.40000000%

0.0577510.03400988$

3.3S00000W

0.0549050.033836411fflI♦系列11

0.0523910.033662*2s3.30000000%

0.0502610.033489454加3.25000000%

即

0.0485W0.0333159763.20000000%

0.0473460.0331424973.1500000M

0.0466460.0329690193.10000000*

0.0464^0.03279554

0.0468730.032622062组合标造差

0.0477920.032448版

0.04921S0.032275106

0.0510990.032101627

0.0533950.031928148

0.0560530.03175467

0.8Mg0.81381】91

.0.0622620.031407713