EXCEL金融计算实验指导.docx

《EXCEL金融计算实验指导.docx》由会员分享，可在线阅读，更多相关《EXCEL金融计算实验指导.docx（16页珍藏版）》请在课桌文档上搜索。

1、金融学试验指导手册EXCE1.金融计算南京审计学院金触学院前.H木试验指导手册为金融学院G金融学、夕证券投资学课程配套书。该试验指导手册侧用于培育学生应用金融学、证券投资学课程所学的基木原理，利用EXCE1.软件为计算工具，分析各种金融I：具的风险与收益实力。全手册共分三部分。第一部分豆习现值、终值、年金等财务概念，r解EXCE1.工具环境与常用财务函数的运用，关于债券、股票等原生工具的定价，利用久期模型分析债券利率风险。其次部分关于现代投资理论构造资产组合，通过计算加深对有效前沿、资本市场线和证券市场线等概念的理解。第三部分关于衍生【具的定价方法，二项式期权模型定价、布莱克-斯科尔斯期权定价

2、模型的EXCE1.实现与VBA编程计算。试验一更有效地运用EXCE1.试验目的与要求：熟识EXEC1.的运行环境，驾驭数据导入方法，驾驭图表与数据透视表的运用，了解常用函数的功能与运用方法。试验指导：一、概念释义：1、数据输入与运算在Excel环境卜进行计算.所须要的数据大致有3种来源：手工输入、自动生成和从外部导入。当数据量:很小而且又没有规律的状况下,一股采纳手工输入的方法。而对于那些有规律的数据，如连续的数字或字符序列可以用Excel的填充吩咐自动生成。当数据量比较大或有现成的数据来源.如网络资源、数据库资源等可供应用，可以采纳导入的方法。Excel可以读入不同来源、不同格式的多种数据文

3、件。执行Excel上的“文件”“打开“吩咐，就可以干脆读人数据库文件、Web文件、XM1.文件、文木文件以与其他格式的电子表格文件等。对于连接在网络局域网、广域网或Web)上的计算机，这些文件可以保存在网络上的任何位置一一只要运用者具有访问权限都可以接读入EXCe1.在读入非EXCel格式文件时，EXCel会自动将文件转换成为工作表格式。对于某些格式的数据，在进行这种格式转换时，可能须要用户做出一些选择，如在读入文本文件(.txt、,rtf)时。须要用户指定分列方式.可能的方式包括按固定宽度分列或按指定字符分列。Excel工作表中的数据有两种基本状况：由用户输入或导入的数据:经过计算得到的数据

4、。用户干脆在单元格中输入的数据属于前者，而后者则是通过公式或函数进行计算后得到的。在EXCel中对于后面类统称为公式，它是由等号起先，包括常殳、对单元格或单元格区域的引用、运算符和函数等几种成分。在EXCel中通过运算符可以执行多种运第：算术运算、文本运算、日期运算、比较运算。2、图表与数据透视表图表是运用Excel进行各种运算分析时一个特别有用的工具.通过图表，可以更加直观地揭示数据之间的内在关系和改变趋势。本书中运用了大量的图表作为分析手段.来说明各种金融财务原理和表示计算结果。在Excel中创建图表比较简洁.可以通过其内置的图表向导来自动生成各种不同类型的图表。单击常用工具栏上的按钮即可

5、出动图表向导。运用图表向导制作图表分为4个步骤:选择图表类型.确定源数据.设定图表选项.支配图表位置。正确有效地制作图表关键在于前两步，首先要依据数据的性质和图表的目的选择合适的图表类型.其次要正确地设置源数据区、横纵坐标轴与数据系列标题。在图表选项中主要是对诸如图表标题、坐标轴标题、图表网格线、图例、数据标记等格式方面的要素进行设定，最终可以选择图表放置的位置，即可以放置在源数据所在工作表内,也可以建立单独的图表工作表。数据透视表是EXCel中一项比较重要的高级功能。EXCel的工作表木身是个二维表,假如要用常规的EXCel工作表表示具有多个维度的数据，就只能把数据“摊平“。然而，在实际应用

6、中.常常须要同时依据多个方向对同组数据进行分析。例如在分析企业销售收入的历史数据时.可能既要依据不同的时间如年份或季度来统i,又要依据部门或地区来统计.甚至会把几个维度结合在起分析，这时就须要运用Excel的数据透视表。假如用一句话来概括数据透视表的本质.那就是数据透视表可以在二维的表格里表示三维或者多维的数据、3、内置函数和自定义函数EXCel作为商务计算的工具软件，其计算功能是通过函数和内置工具实现的。函数是构成EXCef计算公式的主要元素之一。在一般性的计算应用和数据管理当中函数担当着绝大部分的工作。Excel大约内置有330个左右的函数，这些函数依据功能和作用的不同，可以分成11大类。

7、包括：数据库函数.口期和时间函数，外部函数、工程函数、财务函数，信息函数，逻辑函数、查找和引用函数，数学和三角函数，统计函数以与文本和数据函数。所谓自定义函数是指用户依据自己的须要通过Excel内置的VBA(VisualBasicforAPPliCatiOn)功能模块编写代码，设计出实现特定功能的函数。4、假设分析工具方案是保存在工作表当中并可以在相互之间进行切换的数据。当须要调整运算参数视察计算结果而又希望保存这些不同结果时，方案管理器是一个合适的工具。所谓敏感分析是指通过多次运用不同的参数，对某个或组公式的运算结果进行综合比较，从而解不同参数在其可能的取值范围内对运算结果的影响程度。模拟运

8、算表是个志向的敏感分析工具，它充分发挥表格界面和运算公式的优挎，用表格的形式表示出评价指标对分析指标的依靠程度。已知某个公式的运算结果，要求确定其中某个参数的适当值，这种问题在数学上称为反函数。在EXCE1.中，供应个特地处理这类问题的工具单变量求解。二、操作示例假定某投资项目，投资金额为100O0,项目周期为5年。项目的预期现金流量和贴现率都受经济形势的影响：假如经济不景气，则预期每年现金流量为1200,贴现率为6%：假如经济旺盛，则预期每年现金流量为2000.贴现率为10%；假如经济形势股，则现金流量为1600.贴现率为8%。要求对该项目进行评价C试骐二货币的时间价值试验目的与要求：货币的

9、时间价值是一切金融计算的基础，在本试验中关犍的概念是红利和贴现。通过试验要求学生驾驭EXCE1.常用财务函数运用。试验指导：一、概念释义：现值是指将来预期现金流现在的价值，净现值是现值减去初始成本。现值和净现值问题是学习资金时间价值的起先，理解资金时间价值含义并利用Excel实现其计算过程是学好本书的良好开端。1 .现值若已知T期末将拥有一现金流C1,则其在折现率r下的现值为：PV=C(l+r)2 .净现值净现值是将来的现金流的现值减去初始的投资现金流。3 .终值指现金流在将来某时刻的价值。4 .年金指系列有稳定规律的、持续段固定时期的现金收付活动。注：4个变量参加运算，RATE(),NPER

10、(),函数名为计算内容。二、操作示例例1.某投资项目能在将来4年产生90元，100元，UO元和80元的确定现金流，折现率是8%,求该投资的项目的现值。(多重现金流)步骤1步骤2步骤3步骤4例2、假设今年年初将100O存入银行，并将在以后的9年中每年年初存入100O元，那么第十年年初的银行存款余额为多少？（终值）例3、假设某某客户为购房，申请了100000元的房屋抵押贷款，贷款的年利率6峪银行要求10年内还清，则每年末款多少？例4、计息期与连续利率例5某项目投资300万元后，将在将来4年每年获100万元的现金流入，采纳的贴现率为10%，决策该项目可行。（内部收益函数，单变量方程）试疆三债券利率风

11、险评估试验目的与要求：债券的主要风险为利率风险，久期和凸性是刻画债券的两个重要特征。本试验要求熟识债券的久期概念，驾驭久期的计算。（市场风险、信用风险）试验指导：一、概念糕义1、债券的价值与收益债券的计算主要关注的两个指标价格和收益率，其基木原则是贴现现金流。2、债券的久期久期是指固定收益债券现金流支付时间的加权平均。权申是指每次支付的现金流的现值在债券总价中所占的比例p(l+r)久期作为债券价格相对贴现率的弹性，drf(i+r)3、修正久期修正久期越大债券的价格改变幅度越大，利率风险越高。设D为久期，r为贴现率(市场利率)，则修正久期(MD)可定义为D/(l+r)由M=型，可得W二-u.Vx

12、Wpdr二、操作示例1、债券价格计算(债券票面利率与市场利率关系)2、债券久期计算(原理)3、久期度量债券的价格相对贴现率的弹性之=-丝(求导)dr(1+r),t1+r4、EXCE1.1.久期函数计算久期5、修正久久期(将修正期与市场利率的预期相结合对债券价格改变率进行估计)6、债券价格近似计算试验四投资组合模型计算试验目的与要求：驾驭现代投资理论与其实现过程，学会证券的收益率矩阵和数字特征计算：计算投资组合的有效前沿，资本资产定价理论的EXCE1.1.实现。试验指导：一、概念释义股票的月收益率计算分为连续收益率和离散收益率两种，连续收益率为伊加，：离散收益率为7=与_|：在考虑股利收入条件下

13、，连续收益率为Q-I-I6.ln,离散收益率为i-Ipf-期望收益是指持有股票的投资者在卜.个时期所能获得的收益。单个股票的期望收益可通过历史数据的样本均值来估计。资产随机收益率的方差是偏离预期的期望，标准差是方差的平方根。方差、标准差刻诩收益率变动，是风险的常用度量指标。协方差是度量两种风险资产收益之间线性关联度的统计指标，正协方奉收益为同向变动，负协方差收益为反向变动。相关系数刻画两种随机变量的线性关联度O二、操作示例1、期望收益的计算（I）计算股票的每月收益率（2）计算股票的月期望收益率（3）计算股票的年收益率2、方差与标准差在EXCE1.1.中方差，样本方差，标准差，样本标准差分别用V

14、AR、VAKISTEDV,STEDVP.通过EXCE1.1.中的工具栏fx统计。3、协方差、相关系数选择fx统计covar吩咐，对话框中选择数据区域，完成后单击确定。选择fx统计coel吩咐，对话框中选择数据区域，完成后单击确定。4、投资组合的期望收益和方差计算运用EXCE1.中的模拟运算表可计算出两种股票随意投资组合的期望收益和方差.（1）建立工作表（2）选定须要计算的单元区域（3）单击数据菜单中的模拟运算表（4）单击确定运用EXCE1.的图表功能绘制标准差一收益曲线。5、多个资产投资组合（1）W算风险资产构成的给定组合的数字特征（2）由两个组合构成标准差一期望收益曲线（3）描绘资产在坐标中

15、的位置（4）利用模拟运算表求不同投资比例卜的期望收益与标准垄6、超额收益法汁算方差一协方差矩阵（1）计算每个资产的期望收益率（2）计算超额收益矩阵（3）计算超额收益转置矩阵（4）计算方差协方差矩阵试验五有效前沿与资本资产定价模型试验目的与要求：均值方差与有效前沿理论是现代投资理论的基本内容，本试验在上个试验的基础上探讨有效前沿与资本资产定价模型的巾算问题。试验指导：一、概念称义1、可行投资组合：满意投资比例之和为1的组合；2、可行集：由全部可行投资组合构成的集合：3、前沿投资组合：对于随意给定的期望收益水平，全部具有最小方差的投资组合构成的集合：4、有效投资组合：对于随意给定的方差或标准差，具

16、有最大收益率的投资组合：5、有效前沿：由全部有效投资组合构成的集合；6,资本市场线（CM1.）:指在标准差一期望收益坐标系中无风险资产和有效前沿曲线的切线，CM1.刻画了在存在无风险证券且无借贷限制时的投资组合有效前沿，此时有效前沿退化为直线。7、证券市场线（SM1.）刻画任何费产或投资组合的期望收益与其对应的贝塔系数之间的线性关系。二、操作示例1.有效前沿计算(1)资产组合(2)两个前沿组合的计算=MMU1.T(MINVERSE(C2:F5),EB:E11)=F8SUM(F$8:F$11)3、市场组合M的计算与绘制假设有效组合N和无风险资产的投资比例为a,则新的资产组合P的期望收益和标准差分

17、别为：E(rp)-arf+(-a)E(rN)和-(l-11)2五、抛补的看涨期权（S-C）六、垂直价差套购（CI-C2）以不同的执行价格买入份（或多份价格不同）看涨期权和卖出份（或多份不同价）看涨期权（看跌期权）。买入的执行价低，卖出的执行价高,为“牛市价差套购”;反之，为“熊市价差套购”七、对敲（C+P）同时买进具有相同执行价格与相同到期时间的同种股票的看涨与看跌期权。八、双限期权（S+P-C）试疆七二项式期权定价模型计算试验目的与要求：二项式期权定价模型假定标的资产（股票）在每个时间周期的期末价格只有两种可能状态（上涨或卜.跌）对应的股票与股票期权的价格改变过程可以用二叉树表示，依据风险中

18、性定价方法，通过计算每个树叉的状态价格来对期权定价。木试验要求学生了解期权中性风险定价机制，驾驭单阶段欧式二项期权定价模型的EXCE1.实现过程，并将之推广到美式期权定价的计算，应用VBA编定期权定价程序。试验指导：一、概念糕义期权发制是指通过股票与无风险债券来构造个投资组合，并使得该组合在任何状态下（股票价格上涨或下跌）的将来现金流和该看涨期权的将来现金流完全相同。Icall=Ashares+Bbonds依据无套利定价原则,看涨期权的（现在）价格和其更制（股票和债券的组合）的（现在）价格相等，即期权可通过债券和股票来定价。二、操作示例1，单阶段二项期权定价模型：(I)依据无套利原则通过期权更

19、制的方法定价(2)依据风险中性原则计算状态价格定价(3)平价关系检验对应看跌期权的价格为1.11元，且股票和看跌期权价值之和与看涨期权和执行价格的现值之和相等，平价关系成立。2,二阶段的二项式定价模型3、欧式期权的期编程计算(1)看涨期权(2)看跌期权4、美式期权的VB编程计算(1)看涨期权(2)看跌期权试验八Black-Schols定价模型计算试验目的与要求：布莱克-斯科尔斯(BIaCk-SChOIeS,1973)期权定价公式是金融和经济学探讨最多应用最广的期权定价公式，通过本试验学生了解该公式的两个假设：无风险证券的利率和股票价格的标准差在期权的执行期间保持不变，且考虑的是连续时间周期的情

20、形。驾驭BS公式的EXCel实现过程，运用VIJ定义简化BS公式的it算，并依据期权的定价反推股票价格的波动性。试验指导：一、概念释义Black-Scholes公式的欧式看涨和看跌期权的价格C和P分别为：式中旬=ESX+;+内2)Jd2=dP后二、操作示例1、BIaCk-SChoIS定价公式的EXCE1.实现2、期权价格和内在价值的分析3,Black-Schols定价VB编程计算4、除含波动率的YB编程计算5、运用VBA函数计算隐含波动率习题：1、名学生在入学时从银行申请助学贷款40000元，按贷款合同，该生要从毕业起10年内分期偿还这笔贷款，每年年终偿还固定金额，贷款利率为5也从该生毕业起起先计息。要求计算该生毕业后每年须偿还的贷款金额，以与各年偿还金额中本金和利息各多少？2、某公司上年实际派发股利每股0.8元，预期该公司的股利将保持10%的增长率，该股预期收益率为15%,假料将来8年中，每年的股利与股价，并画趋势图。3、假定某股票当前的市场价格为100元，一年后它的价格有两种可能，一种是上涨20%,为120元，另种卜降10%,为90元。当前无险利率为5%。该股票的欧式看涨期权执行价格与Wl前股票价格相同，一年后到期,用二项式期权定价模型计算期权价格。