计算方差EXCEL在投资组合理论中的应用

EXCEL在投资组合理论中的应用

教学内容:

一、计算投资组合的数字特征;

二、在没有卖空限制下计算有效前沿组合

(1) 计算有效前沿; (2) 绘制资本市场线;

(3) 绘制证券市场线;

三、不允许卖空条件下计算有效前沿组合,并比较两种条件下的有效前沿组合的区别

四、EGP法计算前沿组合在EXCEL中的实现。

一 计算期望收益率、标准差、协方差矩阵和相关系数;

1.一个简单的两资产组合的例子（表1）

假如有两只股票12个月度的价格数据:股票A 和股票B,资料如下:

月份

股票Ａ股票Ｂ025.0045.00124.8844.74224.4146.90323.5945.36426.4650.77526.8753.22627.9153.31728.6462.65829.7265.60932.9866.761036.2278.601137.2478.141237.0368.53

股票价格

1.1.收益率与期望收益 1）收益率的计算

以股票A 为例,计算该股票的月收益率.股票A 在第t 月的收益率为在第t 月

月末与第(t-1)月末价格之比的自然对数,计算公式为:

1

ln()At

At At P r P -=

注意:

对数收益率是对普通收益率泰勒级数展开得到的，t 期的对数收益率是ln(Pt)-ln(Pt-1)，对数收益率一般适用于时间间隔比较短的时候（因为是一阶泰勒级数逼近的，所以时间间隔大了误差比较大）。对数收益率的好处是可以直接相加，比如t 期到t+n 期的对数收益率可以由Rt+R(t+1)+R(t+2)+...得到。

(1) 这个公式采用的是连续收益率计算公式,而离散收益率计算公式为

,,1

1A t At A t P r P -=

-

(2) 如果在第t 月末获得股利收入,记为t Div ,则收益率为

,,1

ln

A t t

At A t P Div r P -+=.

(3) 在考虑股利收

入下,股票的离散型

收益率为

,,1

,1

A t t A t At A t P Div P r P --+-=

.

本例中的收益率的计算采用连续收益率形式,并忽略股利收入.

具体步骤是:使用EXCEL 中的LN 函数计算股票的收益率.调用Ln 函数的方法是:单击EXCEL 工具栏下的[]x f ，或者选择[插入]菜单中的[函数]命令，弹出[粘贴菜单]对话框，在[函数分类]中选择[数学与三角函数]。在[函数名]中选择[LN]函数，单击[确定]按钮即可。

2）期望收益的计算

期望收益是指持有股票的投资者在下一个时期所能获得的收益预期。单个证券的期望收益可以通过计算历史数据的样本均值来估计。

在EXCEL 中可以通过[统计]中的[A VERAGE]函数实现对期望收益的计算（见表1）。具体操作步骤如下：

（1） 股票A 每月的收益率：单击C4单元格，在编辑栏输入=LN

（B4/B3），应用自动填充单元格命令即可求出各月收益率对应的C4：C15单元格区域的值。同样可求出股票B 的月收益率。

（2） 股票A 的月期望收益率：选择C16单元格，在编辑栏中输入

=A VERAGE （C4：C15）。股票B 的月期望收益率：选择E16单元格，在编辑栏中输入=A VERAGE （E4：E15）。

（3） 股票A 的年期望收益率：选择C17单元格，在编辑栏中输入

=12*C16。同样的方法可得股票B 的年期望收益率。

1.2 方差与标准差

方差与标准差刻画证券收益率变动，是风险的常用度量指标，在

EXCEL 中方差，样本方差，标准差，样本标准差分别用V AR （计算基于给定样本的方差），V ARP （计算基于给定的样本总体的方差），STEDV ，STDEVP 来表示，公式如下：

22

11

11(),()1n n

i i i i i i VAR r Er VARP r Er n n ===-=--∑∑（无偏估计）

,STDEV VAR STDEVP VARP

==

VAR:

计算基于给定样本的方差。函数 VAR 假设其参数是样本总体中的一个样本。

VARA:

计算基于给定样本的方差。不仅数字，文本值和逻辑值（如 TRUE 和 FALSE ）也将计算在内.函数 VARA 假设参数为总体的一个样本。如果数据代表的是样本总体，则必须使用函数 VARPA 来计算方差。

VARP:

计算基于整个样本总体的方差。函数 V ARP 假设其参数为样本总体。如果数据只是代表样本总体中的一个样本，使用函数 V AR 计算方差。

计算基于整个样本总体的方差。函数 VARP 假设其参数为样本总体。如果数据只是代表样本总体中的一个样本，使用函数 VAR 计算方差。

沿用上面求出的12个月的收益率，通过EXCEL 中[工具栏]下的

[x f ]/[统计]中的V ARP 函数和[x f ]/[统计]中的STDEVP 函数，计算收益率的方差、标准差。 1.3协方差

协方差是度量两种风险资产收益之间线性关联程度的统计指针。正的协方差表示资产收益同向变动；负的协方差表示它们反向变动。

可以通过EXCEL 工具栏中[x f ]/[统计]/[COV AR]直接求协方差，具体步骤如下：

（1） 单击一空白单元格，选择[x f ]/[统计]/[COV AR]命令，出现[COV AR]函数对话框。

（2）

COV AR 函数对话框中，[Array1]选择相应单元格区域，[Array2]选择相应单元格区域。 （3） 完成后单击确定。

1.4 相关系数

相关系数刻画两个随机变量的线性关联程度。有两种计算方法：

（1） 根据定义式计算。(,)

A B AB A B

Cov r r ρσσ=

（2） 可以通过EXCEL 工具栏中[x f ]/[统计]/[CORREL]直接求得。

表1