|
预设条件： 1、一个投资组合由3只交易所交易基金构成，分别为：跟踪现货金价走势的GLD，跟踪20年以上期限美国国债价格走势的TLT，跟踪标准普尔500指数走势的SPY。这3只交易所交易基金的初始配置权重如下：
2 、投资组合的总市值为1,000,000 3 、在险价值VaR的置信水平为99% 准备工作： 将Excel表分成三个部分，左侧为收盘价和月度回报率： 中间为预设参数： 右侧为各个计算步骤： 计算步骤： 1、数据的下载和整理。 通过雅虎财经yahoo.com下载这3只交易所交易基金在2018年3月至2020年3月期间的基金月度收盘价，注意下载的原始数据包括开盘价open、收盘价close、最高价high、最低价low和经调整后的收盘价Adj close共5个报价，只留下经调整后的收盘价Adj close（见下图中红圈处）即可，因交易所交易基金像股票一样会定期分红，经调整后的收盘价会根据分红金额对收盘价进行除权处理，因此能更加准确地反应实际回报率。 这3只交易所交易基金在此期间共有25个月度收盘价，如下： 2、用EXCEL的LN()函数计算月度回报率，公式为LN（本收盘价/前收盘价），24个月度回报率如下： 3、计算单项资产回报率的标准差。比如，GLD回报率标准差的计算公式为STDEVP()，（）内为GLD的月度回报率所在单元格E3：E26，SPY和TLT基金回报率的标准差按相同办法计算，结果如下：
4、计算各单项资产回报率之间的相关系数。比如，GLD和SPY回报率之间的相关系数的计算公式为CORREL()，（）内为GLD和SPY的月度回报率所在单元格E3：E26和F3：F26，GLD和TLT以及TLT和SPY基金回报率之间的相关系数按相同办法计算，结果如下： 5、99%的置信水平所对应的标准分数是多少？可以用normsinv（0.99）函数进行计算，normsinv计算的是某个概率值对应的标准分数，结果为2.3263。
6、预设参数部分准备完毕，现在开始具体的计算过程。首先设置单项资产回报率的波动率矩阵。
7、根据各单项资产回报率之间的相关系数，设置相关系数矩阵
8、用MMULT()函数计算形成单项资产波动率*相关系数的矩阵，在N11单元格内输入公式“= MMULT(N2:P4,N7:P9) ”，（）内的N2:P4为单项资产回报率的波动率矩阵所在单元格，N7:P9为各单项资产回报率之间的相关系数矩阵所在单元格，然后将光标的覆盖范围扩展至N11:P13，按F2，再按 Ctrl+Shift+Enter。
结果如下：
9、用MMULT函数计算形成单项资产波动率*相关系数*波动率的矩阵，结果如下：
10、将单项资产配置权重呈横向排列，
用MMULT函数计算形成单项资产配置权重*波动率*相关系数*波动率的矩阵。在N21单元格内输入公式“= MMULT(N19:P19,N15:P17) ”，（）内的N19:P19为单项资产的配置权重横向排列后所在单元格，N15:P17为单项资产波动率*相关系数*波动率矩阵所在单元格， 然后将光标的覆盖范围扩展至N21:P21，按F2，再按Ctrl+Shift+Enter。
结果如下：
11、将单项资产的配置权重呈纵向排列，
用MMULT函数计算单项资产配置权重*波动率*相关系数*波动率*权重’的结果。在N27单元格内输入公式“= MMULT(N21:P21,N23:N25) ”，（）内的N21:P21为单项资产配置权重*波动率*相关系数*波动率矩阵所在单元格，N23:N25为单项资产的配置权重纵向排列后所在单元格。
12、投资组合加权平均后回报率的波动率计算公式为SQRT(单项资产配置权重*波动率*相关系数*波动率*权重’的结果)，结果为2.15%
13、最后一步是用公式“=J13*N29*K14”计算投资组合的在险价值，其中J13为投资组合的市值1,000,000，K14为99%的置信水平所对应的标准分数，其实无需计算在标准正态分布表上也可以很方便地查到大概的数值2.33，当然函数计算的结果更加准确一些，为2.3263。
N29就是投资组合加权平均后回报率的波动率，数值为2.15%。这个计算结果显示：有99%的把握可以认为，这个市值为1,000,000的投资组合在一个月的时间里最大亏损不超过50,113,75，或者说，该投资组合单月亏损超过50,113,75的概率只有1%。
|
|
|
