excel求投资组合相关系数怎么求（Excel计算投资组合的在险价值VaR）

本文内容参考来源：www.investopedia.com/ask/answers/033115/how-can-you-calculate-value-risk-VaR-excel.asp

在险价值即Value at Risk (VaR)是最常用的风险计量指标之一，对风险进行计量的目的是为了识别和了解风险暴露的程度并作为风险管理的依据。

VaR计量法通常适用于一项资产以往的收益率分布呈现正态分的情况，也就是在置信区间给定的前提下，资产价值遭受损失的程度超过了某个临界值的可能性。因此，VaR本质上是基于损失概率计算的一个时间段内资产价值有可能遭受的最小损失。

与其他风险计量指标相比，VaR比较简单且直观，比如可以这么说“我们有99% 的把握认为某项资产在单个交易日里最大损失金额不超过5百万美元 。”

但VaR也存在一些缺陷，比如，一、其前提是资产收益呈现正态分布，但问题是有很多金融资产的收益率并不符合正态分布的规律，而是极端地集中于某一端，因此用正态分布的方法无法准确地进行预测；二、“有99% 的把握认为某项资产在单个交易日里最大损失金额不超过5百万美元 。”，这个5百万美元只是99% 的置信区间下最小的损失金额，但还有1%的概率资产的损失金额将远远超过5百万美元，到底是多少？不得而知；三、VaR的计算需要先知道资产收益的方差、协方差和标准差等几个参数，如果投资组合中资产种类比较少，那么计算过程相对简单一些，如果资产构成比较复杂，计算难度也会变得很大。

在险价值VaR的计算公式如下：

VaR=投资组合加权平均后的预期收益-（置信区间的Z分数*投资组合收益的标准差）*投资组合的市值

通常VaR计算损失概率的时间单位是年，但如果时间单位是星期，需要将计算公式中的预期收益除以一年所拥有的星期数52，并将投资组合中的标准差除以52的开方；如果时间单位是天，那么分母分别为252和252的开方。

以下为案例：

假设一个投资组合由波音公司BA和维萨信用卡公司VISA的股票构成，权重各占一半，组合的总资产为1,000,000美元，时间跨度为过去一年，具体就是2018年9月17日至2019年9月9日。

第一步，从Yahoo财经网站finance.yahoo.com下载每周股价的收盘价。

第二步，只保留下载数据中的Adj Close，即经调整后的收盘价

第三步，用LN函数计算每周股价的回报率，内为（当前值/前值），计算结果如下：

第四步，准备好参数表

第五步，这两只股票的权重各占50%，以过去一年每周的股价涨跌幅作为预期收益，用STDEV函数计算这两只股票周回报率的标准差，用CORREL函数计算这两只股票周回报率的相关系数。

第六步，计算投资组合的预期收益和收益的标准差，

其中,投资组合的预期收益=BA的市值权重*BA的预期收益 Visa的市值权重*Visa的预期收益=50%*0.145% 50%*0.362%=0.253%;

投资组合收益的标准差=（（BA的市值权重*BA的预期收益）^2 （Visa的市值权重*Visa的预期收益）^2 （2*两只股票之间的相关系数*BA的市值权重*BA的标准差*Visa的市值权重*Visa的标准差）^0.5=（（50%*0.145%）^2 （50%*0.362%）^2 （2*两只股票之间的相关系数*50%*50%*4.63%*2.81%））^0.5=1.6155%

第七步，置信区间与对应的Z分数/标准分数

在计算VaR的时候，常用的概率置信区间为95%和99%，95%的概率对应的标准分数为1.645；99%的概率对应的标准分数为0.9901。

第八步，VaR的计算

由于，

VaR=投资组合加权平均后的预期收益-（置信区间的Z分数*投资组合收益的标准差）*投资组合的市值

因此，在95%的置信区间下VaR=0.253%/52-1,000,000*(1.645*1.6155%)/(52)^0.5=-3,685.28；在99%的置信区间下VaR=0.253%/52-1,000,000*(2.33*1.6155%)/(52)^0.5=-5,219.88

计算结果显示，这个价值1,000,000美元，由波音公司BA和维萨信用卡公司Visa的股票构成，权重各占一半的投资组合的在一周内市值损失达-3,685.28美元的概率不超过95%；在一周内市值损失达-5,219.88美元的概率不超过99%。