第十章在险价值量VaR的计算.ppt

2020/6/5,1,Excel与金融工程学厦门大学出版社，2010年版,第一章Excel基础第二章现金流的时间价值第三章固定收益证券定价第四章权证定价第五章远期与期货的定价与套利第六章互换的设计与定价第七章期权定价第八章房屋按揭抵押贷款及ARM第九章CMO的份额设计第十章在险价值量VaR的计算,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,2,第十章在险价值量VaR的计算,第一节在险价值量概述第二节计算VaR的方差协方差法第三节计算VaR的历史模拟法第四节计算VaR的蒙特卡洛法,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,3,第一节在险价值量概述,一、VaR的计算公式二、VaR的计算实例,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,4,一、VaR的计算公式,投资组合的在险价值量VaR的基本公式为：(10.1.1)其中是初始投资金额；是投资组合的预期投资收益率；是投资组合的收益率的标准差；是标准正态分布的分位数，可以由下面的公式得出：(10.1.2)其中，是置信水平。对于标准正态分布的分位数的计算可以通过Excel软件中的NORMSINV函数实现：=NORMSINV(1-)(10.1.3),南开大学经济学院金融学系周爱民（fineng）,2020/6/5,5,二、VaR的计算实例,（一）Excel的计算例10.1.1：利用Excel软件求解下面的问题。已知投资总额为100万元，投资组合期望收益率为12%，投资收益率标准差为5%，求在98%的置信水平下投资组合的VaR是多少？,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,6,（一）Excel的计算,首先在表格中输入以上数据，如图10.1.1所示。在单元格B6中输入：“=NORMSINV(1-$B$5)”；在单元格B7中输入：“=B2*(B3+B6*B4)”，计算得此项投资的VaR为1.73万元。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,7,（二）VBA的计算,本题也可以设计VBA程序设计一个计算VaR的模型。方法如下：1模型结构的设计设计如图10.1.2所示的结构。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,8,（二）VBA的计算,这个模型和上面的例题的区别在于添加了两个命令按钮，命令按钮“计算”和命令按钮“清除”，这样，在输入已知数据后，单击命令按钮“计算”即可计算出结果，单击命令按钮“清除”即可清除已知数据，回复模型初始状态，方便下一轮的计算。2宏的设置分别对命令按钮“计算”和命令按钮“清除”指定名为“计算”和“清除”的宏，均放在同一模块下，并编辑如图10.1.3的程序代码。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,9,图10.1.3计算VaR的宏代码,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,10,（二）VBA的计算,3数据输入这样就可以在单元格B2到B5中输入已知的数据，然后单击命令按钮“计算”，即可计算出这项投资的风险VaR。结果与上面的方法一致，如图10.1.4所示。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,11,第二节计算VaR的方差协方差法,一、基于Excel的建模二、基于VBA的建模,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,12,一、基于Excel的建模,计算VaR的方差协方差法又称模型构建法，该方法是计算VaR最常用的方法。在观察期资产收益率的概论密度函数已经知道的情况下，计算VaR。上面的假设就是概论密度函数符合正态分布。对于投资资产组合的期望和方差的求解方法可以参见资产组合相关章节。我们通过例题来理解这种方法，我们计算一个投资组合的风险价值。例10.2.1：利用Excel软件计算下面的问题。某个投资者用10万元投资到一个由3种证券组成的资产，期限为一年，经测算，各证券的收益率符合正态分布，有关数据如下面的表1和表2所示。计算在95%的置信水平下投资组合的最大损失。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,13,表10.2.1各证券的预期收益率、标准差和投资比例表10.2.2各证券的相关系数矩阵,南开大学经济学院金融学系周爱民（fineng）,一、基于Excel的建模,2020/6/5,14,第一步：,第一步：在Excel中输入已知数据，如图10.2.1所示,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,15,第二步：,第二步：计算组合的协方差矩阵、组合的收益率和标准差。关于协方差矩阵的计算，先列出标准差矩阵如图10.2.2所示。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,16,图10.2.3协方差阵的计算结果,在B23单元格输入公式：“=MMULT(B18:D20,MMULT(B12:D14,B18:D20)”，然后返回时矩阵运算需要同时按“ctrl+shift+enter”键。结果如图10.2.3所示。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,17,第三步：,第三步：组合收益率和方差的计算。在A27单元格输入汉字“组合预期收益率”，在B27单元格中输入计算公式“=B7*B9+C7*C9+D7*D9”，结果为9.6%；在A28单元格输入汉字“组合标准差”，在B28单元格中输入计算公式“=SQRT(MMULT(B9:D9,MMULT(B23:D25,TRANSPOSE(B9:D9)”，然后同时按“ctrl+shift+enter”键，得到结果0.028645（如图10.2.4所示）。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,18,第四步：,第四步：计算VaR。方法和例1相同，在A30单元格中输入“VaR=”，在B30单元格中输入公式“=B2*(B23+B28*NORMSINV(1-B4)”，就算结果为-4543.56元。这说明在95%的置信水平下该投资的最大损失为-4888.34，也就是说这一年的投资收益最低为4888.34元。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,19,二、基于VBA的建模,（一）设计模型的结构建立一个名为“投资风险价值方差-协方差方法计算模型”的工作簿，在sheet1中设计模型结构，如图10.2.5所示。这个模型中有三个命令按钮，其中“准备”按钮功能是在输入证券数量后，模型自动对工作表格格式化，方便用户输入数据。“计算”按钮的功能是计算出图10.2.5中F2到F8单元格的结果，其中在G3单元格中的结果就是本题的结果。“清除”按钮的功能是输入清除数据和计算结果，使模型恢复到上图的初始状态。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,20,图10.2.5设计VBA的模型结构,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,21,（二）编写“准备”的宏代码,对这三个命令按钮分别指定名为“准备”“计算”和“清除”的宏，均放在同一模块下，并编辑如下的程序代码：对于“准备”命令按钮指定一个名为“准备”的宏，并编写如下代码：Sub准备()Dimn,i,jAsIntegern=Cells(4,2)Cells(10,1)=输入各证券的预期收益率和投资比例Cells(10,1).HorizontalAlignment=xlCenter,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,22,（二）编写“准备”的宏代码,Range(Cells(10,1),Cells(10,1+n).SelectSelection.MergeWithSelection.Borders(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlMediumEndWithFori=1TonCells(11,1+i)=证券&iCells(11,1+i).HorizontalAlignment=xlCenter,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,23,（二）编写“准备”的宏代码,NextiCells(12,1)=预期收益率Cells(13,1)=标准差Cells(14,1)=投资比例Range(Cells(12,1),Cells(14,1).HorizontalAlignment=xlCenterRange(Cells(12,1),Cells(14,1+n).SelectWithSelection.Borders(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlMedium,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,24,（二）编写“准备”的宏代码,EndWithCells(15,1)=输入各投资证券间的协方差矩阵Cells(15,1).HorizontalAlignment=xlCenterRange(Cells(15,1),Cells(15,1+n).SelectSelection.MergeWithSelection.Borders(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlMedium,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,25,（二）编写“准备”的宏代码,EndWithFori=1TonCells(16,1+i)=证券&iCells(16,1+i).HorizontalAlignment=xlCenterForj=1TonCells(16+j,1)=证券&jCells(16+j,1).HorizontalAlignment=xlCenterNextjNextiFori=1TonForj=1Ton,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,26,（二）编写“准备”的宏代码,Cells(16+i,1+j).SelectWithSelection.Interior.ColorIndex=36.Pattern=xlSolidEndWithWithSelection.Borders(xlEdgeLeft).LineStyle=xlContinuous.Weight=xlMedium.Weight=xlThinEndWithWithSelection.Borders(xlEdgeTop).LineStyle=xlContinuous,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,27,（二）编写“准备”的宏代码,.Weight=xlMedium.Weight=xlThinEndWithWithSelection.Borders(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlMedium.Weight=xlThinEndWithWithSelection.Borders(xlEdgeRight).LineStyle=xlContinuous.Weight=xlMedium.Weight=xlThin,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,28,（二）编写“准备”的宏代码,EndWithNextjNextiRange(Cells(16+n,1),Cells(16+n,1+n).SelectWithSelection.Borders(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlMediumEndWithEndSub,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,29,（三）编写“计算”的宏代码,Sub计算()Dima,i,jAsIntegerDimmyrange1,myrange2,myrange3,myrange4AsStringn=Cells(4,2)Fori=1TonForj=1+iTonCells(16+j,1+i)=&Chr(65+j)&16+iNextjNextimyrange1=Chr(66)&12&:&Chr(65+n)&12myrange2=Chr(66)&13&:&Chr(65+n)&13,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,30,（三）编写“计算”的宏代码,myrange3=Chr(66)&14&:&Chr(65+n)&14myrange4=Chr(66)&17&:&Chr(65+n)&16+nCells(2,7)=sumproduct(&myrange1&,&myrange3&)Cells(3,7)=sqrt(sumproduct(&myrange3&,mmult(&myrange3&,&myrange4&)Cells(4,7)=b3*(normsinv(1-b7)*g3+g2)Cells(5,7)=g3“Cells(6,7)=b3*abs(normsinv(1-b7)*g5)Cells(7,7)=sumproduct(&myrange2&,&myrange3&)Cells(8,7)=b3*abs(normsinv(1-b7)*g7),南开大学经济学院金融学系周爱民（fineng）,2020/6/5,31,（三）编写“计算”的宏代码,Range(g2:g3).NumberFormat=0.00%Range(g4).NumberFormat=0.00Range(g5).NumberFormat=0.00%Range(g6).NumberFormat=0.00Range(g7).NumberFormat=0.00%Range(g8).NumberFormat=0.00“Windows(VaR计算2.xls).ActivateWindows(Book2).ActivateRange(D8).SelectActiveSheet.Shapes(Button3).SelectActiveSheet.Shapes(Button3).SelectSelection.Characters.Text=清除,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,32,（三）编写“计算”的宏代码,WithSelection.Characters(Start:=1,Length:=2).Font.Name=宋体.FontStyle=常规.Size=12.Strikethrough=False.Superscript=False.Subscript=False.OutlineFont=False.Shadow=False.Underline=xlUnderlineStyleNone.ColorIndex=xlAutomaticEndWithRange(D6).SelectActiveSheet.Shapes(Button3).SelectEndSub,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,33,（四）编写“清除”的宏代码,对于“清除”命令按钮指定一个名为“清除”的宏，并编写如下代码：Sub清除()Range(Cells(3,2),Cells(4,2)=Cells(7,2)=Range(g2:g8)=Rows(10:10000).SelectSelection.Deleteshift:=xlUpEndSub,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,34,（五）利用此模型计算本节的例题,在B3单元格中输入初始投资额“100000”，在B4单元格输入证券数量“3”，在B7单元格输入置信水平“95%”，然后单击“准备“按钮。得到如图10.2.6的结果。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,35,图10.2.6VBA的计算,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,36,（五）利用此模型计算本节的例题,然后输入证券的预期收益率标准差和投资比例，以及证券间的协方差矩阵。其中协方差矩阵在例题2的计算过程中已经得出。输入全部的已知数据后，单击“计算”命令按钮，我们可以计算出如下图的结果。其中G3单元格的结果就是风险价值VaR。结果为4888.34，和上面例题的计算结果一致。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,37,图10.2.7VBA的计算结果,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,38,方差协方差法的优点,方差协方差法的优点在于方法简单计算量小，不需要详细的历史数据，但是假设前提过于简单和理想化，可以用于快速估计一些包含大量彼此相同又独立的结构简单的资产组合的风险。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,39,第三节计算VaR的历史模拟法,一、基于Excel的建模二、基于VBA的计算,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,40,一、基于Excel的建模,（一）VaR的历史模拟法所谓历史模拟法就是以历史数据为依据，通过已经实现的收益率和市场价值变化来模拟下一期的市场价值变化，从而计算出风险价值。历史模拟法的前提假设是历史数据在将来可能重演。其主要步骤为：第一步：选择一定时间段内影响资产或组合的历史收益率的时间序列。第二步：采用实际价格计算未来一天的变化可能。例如我们利用过去n+1天的市场价格可以得到n个价格变化，这n个价格变化就是未来一天股票变化的n种可能。然后再以当前的价格为基准，我们就可以模拟出来未来一天的价格变化情况了。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,41,（一）VaR的历史模拟法,第三步：利用n个变化我们可以建立未来股票变化的概率分布，对于在一定置信水平下的VaR就可以找到一个可能的损失来对应。对于95%置信水平下的VaR，就是第5%*n个最坏的价值变化情况。下面通过例题来理解一下历史模拟法的模拟计算过程。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,42,（二）历史模拟法的Excel算法,例10.3.1：利用Excel软件计算下面的问题。某投资者投资10万元到两种证券，证券1的投资比例为40%，证券2的投资比例为60%，两种证券过去201天的历史数据如下图所示，利用历史模拟法计算该项投资在95%置信水平下明天的风险价值VaR。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,43,南开大学经济学院金融学系周爱民（fineng）,表10.3.1两种证券201天的历史价格数据,2020/6/5,44,计算过程如下，第一步：,第一步：在Excel中输入已知数据，如图10.3.1所示（数据太多，只列出部分数据）。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,45,第二步：,第二步：计算过去天股票价格变化的日收益率：其中，r为日收益率，为第t日的股票价格，为第t-1日的股票价格。如图10.3.2所示，在单元格输入公式“=B5/B4-1”，然后利用自动填充功能得到证券1的200个日收益率数据，同理在F单元格输入公式“=C5/C4-1”，利用自动填充功能得到证券2的200个日收益率数据。在Excel中的计算结果如图10.3.2所示。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,46,图10.3.2计算收益率,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,47,第三步：,第三步：计算明日证券的200种情况。若今日的证券1价格的为70.25元，证券2的价格为12.22元，则过去201天的日收益率变化为明日股票价格的200种变化可能，从而可以得到一个明日价格的概率分布。如图10.3.3所示，在H5单元格输入公式：“=(E6+1)*70.25”，然后利用自动填充功能得到明日证券1的200个可能的价格数据，同理在I5单元格输入公式：“=(F5+1)*12.22”，利用自动填充功能得到证券2的200个可能的价格数据。在Excel中的计算结果如图10.3.3所示。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,48,图10.3.3计算结果,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,49,第四步：,第四步：计算明日投资收益的200种可能。方法类似于上一步，已知，初始投资到证券1的资金为4万元，投资到证券2的资金为6万元，如下图所示，在K5单元格输入公式：“=$K$3*E5”，然后利用自动填充功能得到明日证券1的200个可能的投资收益数据，同理在I5单元格输入公式：“=$L$3*F5”，利用自动填充功能得到证券2的200个可能的投资收益数据。所以我们可以得出总投资收益，在M5单元格内输入公式：“=K5+L5”，然后利用自动填充功能得到明日总投资收益的200种可能，如图10.3.4所示。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,50,图10.3.4总投资收益的模拟,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,51,第五步：,第五步：我们对总收益进行升序排列，在95%的置信水平下，对于这200个总投资可能构成的概率分布，第十个最差的投资收益为-7925.92元，所以风险价值VaR为7925.92元。方法如图10.3.5所示：选定这200个数据，然后在工具栏中选定升序排列键，得到如下的结果，在014单元格中的结果就是第十个最差的投资收益。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,52,图10.3.5计算最差的收益率,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,53,最后：,通过这题我们还可以回顾一下一些常用统计量的计算方法，我们利用AVERAGE函数，STDEV函数，CORREL函数，COVAR函数可以分别计算两只证券的证券日变化率，证券价格和投资收益的平均值，标准差，相关系数和协方差，同时也可以求出总投资收益的期望和标准差。所有计算结果如图10.3.6所示。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,54,图10.3.6最后的计算结果,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,55,二、基于VBA的计算,本题也可以用Excel的VBA程序设计一个模型。过程类似于第二节介绍的步骤，模型设计具体方法如下：（一）模型结构的设计建立一个名为“投资风险价值历史模拟方法计算模型”的工作簿，在sheet1中设计模型结构，如图10.3.7,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,56,（一）模型结构的设计,这个模型中有三个命令按钮，其中“准备”按钮功能是在输入证券数量后，模型自动对工作表格格式化，方便用户输入数据。“计算”按钮的功能是计算出上图F2到F6单元格以及H3和H4单元格的结果，其中在H4单元格中的结果就是本题的结果。“清除”按钮的功能是输入清除数据和计算结果，使模型恢复到上图的初始状态。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,57,（二）三个宏代码的编写,对这三个命令按钮分别指定名为“准备”“计算”和“清除”的宏，均放在同一模块下，并编辑如下的程序代码：1“准备”命令按钮的宏代码Sub准备()Dimn,m,iAsIntegern=Cells(4,2)m=Cells(5,2)Cells(10,1)=输入个证券的投资比例Cells(10,1).HorizontalAlignment=xlCenterRange(Cells(10,1),Cells(10,1+n).SelectSelection.Merge,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,58,1“准备”命令按钮的宏代码,WithSelection.Borders(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlMediumEndWithCells(11,1)=证券Fori=1TonCells(11,1+i)=证券&iCells(11,1+i).HorizontalAlignment=xlCenterNextiCells(12,1)=投资比例“Range(Cells(12,1),Cells(12,1+n).Select,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,59,1“准备”命令按钮的宏代码,WithSelection.Borders(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlMediumEndWithCells(14,1)=输入各证券的历史数据Cells(14,1).HorizontalAlignment=xlCenterRange(Cells(14,1),Cells(14,1+n).SelectSelection.MergeWithSelection.Borders(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlMediumEndWith,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,60,1“准备”命令按钮的宏代码,Cells(15,1)=日期Cells(15,1).HorizontalAlignment=xlCenterFori=0TomCells(16+i,1)=iCells(16+i,1).HorizontalAlignment=xlCenterNextiFori=1TonCells(15,1+i)=证券&iCells(15,1+i).HorizontalAlignment=xlCenterNextiRange(Cells(16+m,1),Cells(16+m,1+n).Select,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,61,1“准备”命令按钮的宏代码,WithSelection.Borders(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlMediumEndWithEndSub,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,62,2“计算”命令按钮的宏代码,Sub计算()Dimn,m,nm,i,jAsIntegerDimmyrange1,myrange2,myrange3AsStringn=Cells(4,2)m=Cells(5,2)Cells(14,2+n)=计算过程Cells(14,2+n).HorizontalAlignment=xlCenterRange(Cells(14,2+n),Cells(14,3+n).SelectSelection.MergeWithSelection.Borders(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlMediumEndWith,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,63,2“计算”命令按钮的宏代码,Cells(15,2+n)=投资组合价格Cells(15,3+n)=每期收益Range(Cells(15,2+n),Cells(15,3+n).HorizontalAlignment=xlCenterFori=1Tom+1sumt=0Forj=1Tonsumt=sumt+Cells(12,1+j)*Cells(15+i,1+j)Cells(15+i,2+n)=sumtNextjNexti,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,64,2“计算”命令按钮的宏代码,Fori=1TomCells(16+i,3+n)=Cells(16+i,2+n)Cells(15+i,2+n)NextiRange(Cells(16+m,2+n),Cells(16+m,3+n).SelectWithSelection.Borders(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlMediumEndWithmyrange1=Chr(66+n)&17&:&Chr(66+n)&16+mmyrange2=Chr(67+n)&17&:&Chr(67+n)&16+m,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,65,2“计算”命令按钮的宏代码,myrange3=Chr(66+n)&16+mRange(f3)=average(&myrange1&)“Range(f4)=average(&myrange2&)Range(f5)=b3/&myrange3Range(f6)=f4*f5Range(f3:f6).NumberFormat=00.0nm=Int(Cells(5,2)*(1-Cells(6,2)Range(g3)=第&nm&个最坏的收益Range(h3)=small(&myrange2&,&nm&)Range(h4)=f5*abs(h3)Range(h3:h4).NumberFormat=00.0EndSub,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,66,3“清除”命令按钮的宏代码,Sub清除()Range(b3:b6).ClearContentsRange(f3:f6).ClearContentsRange(h3:h4).ClearContentsRange(g3)=第(x)个最坏的收益Rows(10:10000).Deleteshift:=xlUpEndSub,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,67,（三）模型应用,利用此模型可计算本节的例题。步骤如下。第一步：在B3到B6单元格分别输入已知条件，初始投资额10万元，证券数量2，历史数据个数为201个，所以在B5单元格输入200，置信水平95%，然后单击“准备“按钮。得到如图10.3.8的结果。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,68,图10.3.8输入已知条件,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,69,第二步：,第二步：在B12和C12单元格中输入投资比例，注意，此处的投资比例实际是投资者购买股票数量的比例，而不是投资额的比例，所以，当今日证券1价格为70.25，证券2的价格为12.22时，投资40%的初始金额到证券1，投资60%初始金额到证券2就意味着购买的股票数的比例约等于1：9，所以在B12单元格中输入10%，而不是40%，在C12单元格中输入90%，而不是60%。然后输入两只证券价格的历史数据。输入已知数据后，单击“计算”命令按钮，我们可以计算出如图10.3.9的结果。其中H4单元格的结果就是风险价值VaR。在置信区间为95%的风险价值为第十个最坏的收益所对应的VaR=7190.8，和上面的例题计算结果基本一致。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,70,图10.3.9计算结果,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,71,第四节计算VaR的蒙特卡洛法,一、蒙特卡洛方法的基本步骤二、蒙特卡洛模拟的例子,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,72,计算VaR的蒙特卡洛法,在第三节介绍的利用投资组合的历史数据来模拟计算风险价值，它是以股票价格的历史数据为基础，直接计算股票价格的变动率来得到每期的绝对收益，并以历史数据的最坏情况来确定投资组合的风险价值，这种方法尽管简单易行，但是由于证券市场千变万化，过去的股票价格并不能代表未来的情况，而对于蒙特卡洛方法，它的优点就是结合了方差-协方差法和历史模拟法，通过模拟随机数组构建一个为了价格的变化路径，即为了价格变化是满足随机过程的假设的，通过产生的随机价格通过历史模拟法的计算方法来计算投资组合的风险价值。实际上蒙特卡洛方法是上面两种方法的结合，它的优点就是在处理非线性的价格变化以及极端事件中发挥重要作用，但是也会产生模型设定风险。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,73,一、蒙特卡洛方法的基本步骤,蒙特卡洛模拟的主要内容就是选择随机过程和其随机变量的分布，并估计相应的参数。例如我们可以利用下面的股票价格模型来估计未来一段时期的股票价格：(10.4.1)其中，为股票在风险中性世界里的期望收益率；为标准差；为时间间隔。若和为年收益率的期望和标准差，则天的时间间隔表示为，此处250指的是一年股票交易的大约天数为250天，若和为日收益率的期望和标准差，则天的时间间隔表示为。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,74,一、蒙特卡洛方法的基本步骤,产生一个随机变量的序列(=1,2,3，,n)。利用随机过程计算出为来t时刻股票价格的n种可能，。利用这n种可能的价格来计算未来t时刻投资组合的价值变化，从而得出n中可能的投资收益。在这n中可能的投资收益中，对于在一定置信水平下的VaR就可以找到一个可能的损失来对应。对于95%置信水平下的VaR，就是第5%*n个最坏的价值变化情况。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,75,二、蒙特卡洛模拟的例子,我们通过例题来理解这种方法，我们计算一个投资组合的风险价值。例10.4.1：利用Excel软件计算下面的问题。某投资者投资10万元到两种证券，证券1的投资比例为40%，证券2的投资比例为60%，两种证券有关数据如表10.4.1所示，利用蒙特卡洛法计算该项投资在95%置信水平下明天的风险价值VaR。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,76,计算过程如下：第一步：,第一步：在Excel中输入已知数据，如图10.4.1和10.4.2所示。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,77,第二步：,第二步：利用蒙特卡洛方法模拟明日价格。我们利用公式：(10.4.2)其中已知股票的今日价格；日期望收益率和日波动率；时间t=1。利用Excel中NORMSINV(RAND()函数产生一个随机变量的序列(=1,2,3，,n)，其中NORMSINV(RAND()给出的是一个服从标准正太分布的抽样。这样就可以模拟出明天的股票价格的随机价格。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,78,第二步：,如图10.4.3所示：在B7单元格输入公式“=$B$2*EXP($B$3-$B$4*$B$4/2+$B$4*NORMSINV(RAND()”，然后利用自动填充功能得到明日证券1的1000个可能的价格数据。同理，在C7单元格输入公式“=$C$2*EXP($C$3-$C$4*$C$4/2+$C$4*NORMSINV(RAND()”，然后利用自动填充功能得到明日证券2的1000个可能的价格数据。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,79,图10.4.3蒙特卡洛模拟价格数据（部分）,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,80,第三步：,第三步：计算过去天股票价格变化的日收益率。其公式为：(10.4.3)其中，r为日收益率；为第t日的股票价格；为第t-1日的股票价格。在E7单元格输入公式“=B7/$B$2-1”，然后利用自动填充功能得到证券1的1000个可能的明日收益率数据，同理在F7单元格输入公式“=C7/$C$2-1”，利用自动填充功能得到证券2的1000个可能的明日收益率数据。在中的计算结果如图10.4.3所示。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,81,第四步：,第四步：计算明日投资收益的1000种可能。方法类似于上一步，已知，初始投资到证券1的资金为4万元，投资到证券2的资金为6万元，如下图所示，在H7单元格输入公式“=E7*$H$3”，然后利用自动填充功能得到明日证券1的1000个可能的投资收益数据，同理在I单元格输入公式“=$L$3*F5”，利用自动填充功能得到证券2的1000个可能的投资收益数据。所以我们可以得出总投资收益，在M5单元格内输入公式“=H7+I7”，然后利用自动填充功能得到明日总投资收益的1000种可能。然后可以利用RANK()函数对这1000个可能的收益进行排序，在K6单元格输入汉字“第n差收益”，然后再K7单元格输入公式“=RANK(J7,$J$7:$J$2006,1)”，然后利用自动填充功能得到对这1000个收益的升序排序，如图10.4.4,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,82,图10.4.4计算明日收益的100种可能,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,83,第五步：,第五步：对总收益率进行降序排列，计算VaR。如图10.4.5所示，在M7单元格输入公式“=SMALL($J$7:$J$1006,ROW(1:1)”，然后在N7单元格输入公式“=SMALL($K$7:$K$1006,ROW(1:1)”，所以在95%的置信水平下，对于这1000个总投资可能构成的概率分布，第50个最差的投资收益为-5129.28元，所以风险价值VaR为5129.28元。通过键盘上的F9键，我们可以不断的重新抽样，从而可以重复上面的过程得出不用的风险价值VaR.,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,84,图10.4.5VaR的计算,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,85,最后,此题也可以通过模拟的数据得出常见统计量，我们利用AVERAGE函数，STDEV函数，CORREL函数，COVAR函数可以分别计算两只证券的证券日变化率，证券价格和投资收益的平均值，标准差，相关系数和协方差，同时也可以求出总投资收益的期望和标准差。结果如图10.4.6所示。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,86,图10.4.6最后的计算结果,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,87,（二）基于VBA的模拟计算,对于蒙特卡洛方法也可以设计一个简单的VBA模型，从而简化计算过程，模型设计过程如下。1设计模型的结构建立一个名为“投资风险价值蒙特卡洛方法的计算模型”的工作簿，在sheet1中设计模型结构，如图10.4.7所示。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,88,1设计模型的结构,这个模型中有三个命令按钮，其中“准备”按钮功能是在输入证券数量后，模型自动对工作表格格式化，方便用户输入数据。“计算”按钮的功能是计算出上图F2到F6单元格以及H3和H4单元格的结果，其中在H4单元格中的结果就是本题的结果。“清除”按钮的功能是输入清除数据和计算结果，使模型恢复到上图的初始状态。,南开大学经济学院金融学系周爱民（fineng）,2020/6/5,89,2设计进度条,在利用蒙特卡洛方法计算投资组合的风险价值时，一般要进行多次循环计算，计算时间的长短取决于模拟的次数，我们可以设计一个进度条来反映模拟的进程。具体步骤如下。第一步：在VBA编辑器中插入一个用户窗体userform1，将caption属性设置为“正在进行模拟运算”，然后在窗体中插入两个文本框textbox1和textbox2，将其height属性设为15，width属性设置为225。第二步：在用户窗体中再插入6个标