Excel公式和函数典型案例—多种风险资产的最优投资组合.docxVIP

Excel公式和函数典型案例—多种风险资产的最优投资组合

一、核心理论基础与Excel工具的适用性

投资组合的“最优”与否，取决于投资者对风险和收益的权衡。均值-方差模型假设投资者是风险厌恶的，其目标是寻找有效前沿上的点。有效前沿由一系列在特定风险水平下具有最高预期收益，或在特定预期收益下具有最低风险的投资组合构成。

Excel凭借其强大的数据处理、函数计算和图表绘制功能，成为实现这一目标的理想工具。我们无需高深的编程知识，仅通过内置函数和数据分析工具，即可完成从数据处理到组合优化的全过程。

二、案例数据准备与基础指标计算

（一）案例背景与数据选取

假设我们考虑三类风险资产：A（代表某股票指数）、B（代表某债券指数）和C（代表某商品指数）。我们收集了它们过去若干个月度的收益率数据（为简化计算，此处假设数据已整理完毕，无缺失值）。

月份

资产A收益率(%)

资产B收益率(%)

资产C收益率(%)

:---:

:------------:

:------------:

:------------:

1

...

...

...

...

...

...

...

n

...

...

...

（二）计算各资产的预期收益率

预期收益率通常用历史收益率的算术平均值来估计。

在Excel中，假设资产A的收益率数据位于B2到Bn单元格（n为样本数量），则其预期收益率E(R_A)的计算公式为：

`=AVERAGE(B2:Bn)`

同样方法计算E(R_B)和E(R_C)，分别放在D2、D3、D4单元格（假设）。

（三）计算资产收益率的协方差矩阵

协方差矩阵描述了资产收益率之间的联动性，是计算组合风险的关键。对于三种资产，协方差矩阵为：

资产A

资产B

资产C

:---:

:---:

:---:

:---:

资产A

σ_AA

σ_AB

σ_AC

资产B

σ_BA

σ_BB

σ_BC

资产C

σ_CA

σ_CB

σ_CC

其中，σ_AA是资产A收益率的方差，σ_AB是资产A和资产B收益率的协方差，且σ_AB=σ_BA。

在Excel中，计算资产A和资产B的协方差（假设资产B数据在C2到Cn）：

`=COVARIANCE.P(B2:Bn,C2:Cn)`

使用COVARIANCE.P函数（总体协方差）。同理计算其他协方差项和方差项（方差是协方差的特例，即资产与自身的协方差）。将计算结果组织成上述矩阵形式，例如放置在F2:H4区域。

三、模拟资产配置组合与计算组合收益和风险

（一）设定资产权重

投资组合由各资产的权重构成，权重之和为1。我们通过随机模拟生成大量可能的权重组合。

1.生成随机权重：在Excel中，使用RAND()函数生成(0,1)之间的随机数作为初始权重。例如，在J2、K2、L2单元格分别输入：

`=RAND()`

`=RAND()`

`=RAND()`

2.权重归一化：确保权重之和为1。在M2单元格计算总权重：`=J2+K2+L2`。然后在N2、O2、P2单元格计算归一化后的权重：

`=J2/M2`

`=K2/M2`

`=L2/M2`

这N2、O2、P2单元格的数值即为资产A、B、C在该模拟组合中的权重w_A、w_B、w_C。

（二）计算组合预期收益率

组合预期收益率E(R_p)是各资产预期收益率的加权平均：

E(R_p)=w_A*E(R_A)+w_B*E(R_B)+w_C*E(R_C)

在Excel中，假设E(R_A)在D2，E(R_B)在D3，E(R_C)在D4，当前组合权重在N2、O2、P2，则：

`=SUMPRODUCT(N2:P2,D2:D4)`

将结果放在Q2单元格。

（三）计算组合收益率的方差和标准差

组合方差σ_p2的计算公式为：

σ_p2=w_A2σ_AA+w_B2σ_BB+w_C2σ_CC+2w_Aw_Bσ_AB+2w_Aw_Cσ_AC+2w_Bw_Cσ_BC

这可以通过矩阵乘法更简洁地表示。在Excel中，我们可以利用MMULT函数进行矩阵运算。假设协方差矩阵在F2:H4，当前组合权重在N2:P2（行向量），则组合方差为：

`=MMULT(MMULT(N2:P2,F2:H4),TRANSPOSE(N2:P2))`

按Ctrl+Shift+Enter输入数组公式，结果放在R2单元格。

组合标准差σ_p（即组合风险）为方差的平方根：

`=SQRT(R2)`

放在S2单元格。

（四）批量生成模拟组合

选中J2到S2的整个模拟组合行，向下拖动填充柄，生成大量（例如数千行）模拟组合数据。每一行代表一个随机的资产配置方案及其对应的预期收益和风险。

四、绘制有效前沿与识别最优组合

（一）绘制散点图

选中所有模拟组合的预期收益率（Q列）和标准差（S列）数据，插入“散点图