投资组合数字特征计算.doc

实验一： 投资组合数字特征的计算
一、实验目的
通过上机实验，使学生充分理解Excel软件系统管理和基本原理,掌握投资组合数字特征的Excel计算。
二、预备知识
（一）相关的计算机知识： Windows操作系统的常用操作；数据库的基础知识；Excel软件的基本操作。
（二）投资组合数字特征的理论预备知识
假设投资者投资N个风险资产，对该组合作以下定义。
用表示投资在资产i上的比例，则投资组合W的矩阵形式为：
投资组合W的期望收益和方差的矩阵形式为： 式中E(r) 表示各资产期望收益率组成的列矩阵（列向量），S是N个风险资产的方差-协方差矩阵。任意两个投资组合之间的协方差的矩阵形式为：
三、实验内容
利用Excel 计算投资组合的期望、标准差和协方差，并绘制投资组合的标准差-期望收益曲线。
四、实验步骤
本实验通过一个具体的实例展开。已知：3家上市公司2001年12月至2003年12月共25期的股票月末收盘价格。试运用EXCEL计算下列问题：
（1）计算各只股票的月/年收益率、方差、标准差。
（2）计算三只股票的方差-协方差矩阵、相关系数。
（3）构造两个投资组合，其中：组合1 =（0.2 0.4 0.4 ）,
组合2 =（0.5 0.3 0.2）,计算各自的方差，两组合之间的协方差、相关系数。
（4）绘制三家公司股票的标准差-期望收益曲线。

首先新建一个EXCEL 工作表，在B3：D27区域中输入3个公司股票的价格。

A
B
C
D
1

股票价格
2
日期
公司A
公司B
公司C
3
2001年12月
15.7954
25.8483
23.3923
4
2002年1月
18.1096
27.1296
24.0336
5
2002年2月
17.2228
26.2177
23.5039
6
2002年3月
16.3931
24.3938
22.4581
7
2002年4月
15.5634
26.0828
21.3913
8
2002年5月
16.2599
25.8535
22.3458
9
2002年6月
16.8354
24.4777
21.1025
10
2002年7月
18.0153
25.7313
22.7998
11
2002年8月
19.4385
28.0968
22.5169
12
2002年9月
19.6121
27.5198
21.9434
13
2002年10月
19.8209
29.1826
22.2854
14
2002年11月
18.9781
29.0086
20.9745
15
2002年12月
20.1406
28.7765
23.4447
16
2003年1月
18.3827
30.3982
23.6745
17
2003年2月
18.2366
31.04
25.1686
18
2003年3月
19.4056
30.9233
25.0111
19
2003年4月
20.9838
32.7841
25.9374
20
2003年5月
21.8532
33.7225
26.8638
21
2003年6月
23.5568
33.7225
26.3037
22
2003年7月
26.7878
32.502
27.5285
23
2003年8月
26.9548
32.7379
27.4702
24
2003年9月
24.9494
36.3362
29.9358
25
2003年10月
24.0647
38.4158
29.701
26
2003年11月
27.726
41.0243
31.5206
27
2003年12月
25.06
39.3644
34.0269
3家上市公司25期的股票收盘价格
1、 计算各只股票的月/年收益率、方差、标准差。
具体步骤如下：
（1）公司A股票的的每月收益率：选定单元格B30，在编辑栏输入=LN（B4/B3），回车后在B30中出现结果。应用自动填充单元格命令求出各自月收益率所对应单元格区域B30：B53的值。同理可以求出公司B、C的月收益率，分别对应单元格C30：C53、D30：D53中的值。
（2）公司A的股票月收益率：选择单元格B54 ，在编辑栏输入=AVERAGE（B30：B53）。应用自动填充单元格命令可求出公司B、C的月期望收益率， 分别对应单元格C54、D54中的值。
（3）公司A的股票年收益率：选择B55单元格，在编辑栏输入=12*B54。应用自动填充单元格命令可求出公司B、C的股票年期望收益率，分别对应单元格C55、D55中的值。
（4）公司A的股票月收益率方差：选择B56 单元格，在编辑栏输入=VARP（B30：B53）。应用自动填充单元格命令可求出公司B、C的股票月收益率方差，分别对应单元格C56、D56中的值。
（5）公司A的股票年收益率方差：选择B57 单元格，在编辑栏输入=B56*12。应用自动填充单元格命令可求出公司B、C 的股票年收益率方差，分别对应单元格C57、D57中的值。
（6）公司A的股票月标准差：选择B58 单元格，在编辑栏输入=STDEVP（B30：B53）。应用自动填充单元格命令可求出公司B、C的股票月标准差，分别对应单元格C58、D58中的值。
（7）公司A的股票年标准差：选择B59 单元格，在编辑栏输入=SQRT（B57）。应用自动填充单元格可求出公司B、C的股票年标准差，分别对应单元格C59、D59中的值。计算结果如下:

A
B
C
D
E
F
28

月收益率

29
日期
公司A
公司B
公司C

30
2002年1月
0.1367234
0.0483805
0.0270459
<--=LN(D4/D3)

31
2002年2月
-0.050208
-0.034191
-0.022286

32
2002年3月
-0.049374
-0.072106
-0.045515

33
2002年4月
-0.051939
0.0669471
-0.048667

34
2002年5月
0.04378
-0.00883
0.0436541

35
2002年6月
0.0347819
-0.054683
-0.057247

36
2002年7月
0.0677376
0.0499457
0.0773602

37
2002年8月
0.0760342
0.0879475
-0.012486

38
2002年9月
0.0088911
-0.02075
-0.0258

39
2002年10月
0.0105902
0.0586669
0.0154653

40
2002年11月
-0.043451
-0.00598
-0.060624

41
2002年12月
0.059452
-0.008033
0.111337

42
2003年1月
-0.091328
0.0548243
0.0097541

43
2003年2月
-0.007979
0.0208933
0.0611987

44
2003年3月
0.0621311
-0.003767
-0.006277

45
2003年4月
0.078189
0.0584337
0.0363662

46
2003年5月
0.0405967
0.0282216
0.0350937

47
2003年6月
0.0750672
0
-0.02107

48
2003年7月
0.128532
-0.036864
0.0455122

49
2003年8月
0.0062148
0.0072318
-0.00212

50
2003年9月
-0.077312
0.1042811
0.0859533

51
2003年10月
-0.036104
0.0556543
-0.007874

52
2003年11月
0.1416246
0.0656957
0.0594606

53
2003年12月
-0.101098
-0.041303
0.0765101

54
月期望收益
0.0192314
0.0175257
0.0156144
<--=AVERAGE(D30:D53)
55
年期望收益
0.2307771
0.2103085
0.1873722
<--=D54*12
56
月收益率方差
0.0048484
0.0021734
0.0022462
<--=VARP(D30:D53)
57
年收益率方差
0.0581811
0.0260809
0.0269547
<--=D56*12
58
月标准差
0.0696307
0.0466199
0.0473943
<--=STDEVP(D30:D53)
59
年标准差
0.2412077
0.1614959
0.1641788
<--=SQRT(D57)

2、计算3只股票的方差-协方差矩阵，相关系数。
（1）公司A与公司B股票收益率之间的协方差：选择B62 单元格，在编辑栏输入=COVAR（B30：B53，C30：C53）。同理可以求出公司A与公司C、公司B与公司C股票收益率之间的协方差，分别对应单元格C63、D63中的值。
（2）公司A与公司B股票收益率之间的相关系数：选择B63单元格，在编辑栏输入=CORREL（B30：B53，C30：C53）。同理可以求出公司A与公司C、公司B与公司C股票收益率之间的相关系数，分别对单元格C62、D62中的值。
（3）填写方差-协方差矩阵：对角线上的元素为各只股票的年收益率方差，以矩阵左上角的元素为例，选择B64单元格，在编辑栏输入=B57。非对角线元素为3家公司股票收益率之间的协方差，例如单击B65单元格，在编辑栏输入=B62。
（注意：方差-协方差矩阵为对角矩阵）计算结果如下表。

A
B
C
D
E
F
G
61

公司A&B
公司B&C
公司A&C

62
协方差
0.000306
0.000603
0.000703
<--=COVAR(B30:B53,D30:D53)
63
相关系数
0.094315
0.273038
0.212905
<--=CORREL(B30:B53,D30:D53)
64

0.058181
0.000306
0.000703

65
方差—协方差矩阵
0.000306
0.026081
0.000603

66

0.000703
0.000603
0.026955

3、计算组合1和组合2的方差以及两组合之间的协方差、相关系数。
具体步骤如下：
（1）组合1的转置矩阵：选定单元格区域B171：D71，在编辑栏输入=TRANSPOSE（B68：B70），用[Ctrl+Shift+Enter]组合键完成输入。同理可以求出组合2的矩阵。
（2）组合1的期望收益：单击B73 单元格，在编辑栏输入=MMULT（B55：D55，C68：C70），用[Ctrl+Shift+Enter]组合键完成输入。
（3）组合1的方差：单击B74 单元格，在编辑栏输入=MMULT（B72：D72，MMULT（B64：D66，C68：C70）），用[Ctrl+Shift+Enter]组合键完成输入。
同理可以求得组合2的期望收益和方差，分别对应单元格C73、C74中的值。
（4）两组合的协方差：单击B75 单元格，在编辑栏输入=MMULT（B71：D71，MMULT（B64：D66，B68：B70）），用[Ctrl+Shift+Enter]组合键完成输入。
结果如下表。

A
B
C
D
E
F
G
67

组合1
组合2

68

0.2
0.5

69

0.4
0.3

70

0.4
0.2

71
组合1的转置
0.2
0.4
0.4
<--=TRANSPOSE(B68:B70)
72
组合2的转置
0.5
0.3
0.2

73
组合期望收益
0.205228
0.215956
<--=MMULT(B55:D55,C68:C70)

74
组合的方差
0.011167
0.018276
<--=MMULT(B72:D72,MMULT(B64:D66,C68:C70))
75
组合的协方差
0.011473

<--=MMULT(B71:D71,MMULT(B64:D66,C68:C70))
构造组合指标的计算
4、绘制三家公司股票的标准差-期望收益曲线。
（1）任意给定组合1的投资比例为0.4（对应组合2 的比例为0.6） .

(2) 组合的期望收益：单击B78单元格，在编辑栏输入=B77*B73+（1-B77）*C73

（3）组合的方差：单击B79单元格，在编辑栏输入
=B77^2*B74+(1-B77)^2*C74+2*B77*(1-B77)*B75
(4) 组合的标准差：单击B80单元格，在编辑栏输入=SQRT（B79）。
在组合1的投资比例为0.4对应的组合数字特征基础上，运用模拟运算表给出其它比例下的数字特征。
（5）单击B82单元格，在编辑栏输入=B80。
（6）单击C82单元格，在编辑栏输入=B78。
（7）选择A82：C100单元格，单击[数据]/[模拟运算表]，在[模拟运算表]对话框中选择输入的列对应的B77单元格。
（8）完成后单击[确定]按钮。计算结果如图所示。
（9）绘制标准差与收益曲线。

A
B
C
D
E
F
G
77
组合1的投资比例
0.4

78
期望收益
0.211664
<--=B77*B73+(1-B77)*C73

79
方差
0.013873
<--=B77^2*B74+(1-B77)^2*C74
+2*B77*(1-B77)*B75
80
标准差
0.117784
<--=SQRT(B79)

81

不同投资比例下的标准差和期望收益

82
B82=-->
0.117784
0.211664
<--=B78

83
-0.8
0.182530
0.224538

84
-0.65
0.172811
0.222929

85
-0.5
0.163408
0.221319

86
-0.35
0.154380
0.219710

87
-0.2
0.145796
0.218101

88
-0.05
0.137739
0.216492

89
0.1
0.130307
0.214883

90
0.25
0.123614
0.213274

91
0.4
0.117784
0.211664

92
0.55
0.112952
0.210055

93
0.7
0.109250
0.208446

94
0.85
0.106796
0.206837

95
1
0.105676
0.205228

96
1.15
0.105933
0.203619

97
1.3
0.107558
0.202009

98
1.45
0.110489
0.200400

99
1.6
0.114627
0.198791

100
1.75
0.119847
0.197182

组合的标准差-期望收益曲线

第二部分 投资组合优化模型

投资组合优化，就是确定一组投资项目的最优投资比例。这里所说的“最优”，可以是指在一定期望投资回报水平下使得风险最小，或者是指在一定风险水平下使得投资回报最大，本章只讨论前者，后者放在第10章再讨论。在20世纪50年代，Harry Markowitz研究了一定期望投资回报水平下使得方差最小的最优投资比例问题，Harry Markowitz在该问题上取得的研究成果以及关于投资的其他研究成果，使他荣获1991年诺贝尔经济奖。下面通过例子说明投资组合优化问题的建模与VBA求解方法。

大部分投资者的目标是获得大的投资回报和承担小的投资风险。投资组合优化模型就是确定一组投资项目的最优投资比例（或者各项目的最优投资额），在该投资组合的总回报率的方差不超过某个可接受的值的约束下（即在可接受的风险水平下），使得总回报率的期望值最大（即投资回报最大）；或者在投资组合的总回报率的期望值不低于某个所要求的值的约束下（即在所要求的投资回报水平下），使得总回报率的方差最小（即投资风险最小）。由于总回报率的方差通常总是投资比例的非线性函数，所以该规划是一个非线性规划。
例如，对于目标函数为风险最小的投资组合优化模型，由（4-2）式可得到投资总回报率R的方差估计量，又由（4-1）式可以得到投资总回报率R的期望值。该模型的形式如下：
o.b, min R的方差= （4-3）
s.t. R的期望值=≥P

≥0
（4-3）式中，R为投资组合的总回报率；
第1至第m个项目的投资比例（决策变量）；
第1至第m个项目的单项回报率的方差；
第1至第m个项目的单项回报率的标准方差；
为第i个投资项目与第j个投资项目的相关系数；
为第1至第m个项目的单项期望回报率；
P为投资者所要求的回报率水平。
下面通过例2说明投资组合优化问题的建模与求解方法。
例2 投资组合优化问题
计算例1中对三个投资项目的最优投资比例，要求在总投资回报率不低于0.13的前提下，使得投资的风险最小。
解：这是以投资总风险最小为目标，以总回报率不低于要求值为约束条件的优化问题，该问题可以用（4-3）式建立非线性规划模型来求解。该问题的Spreadsheet如表4-5所示。其步骤如下：
第一步：输入已知数据
首先在Spreadsheet上输入已知数据。在A4：D23输入三个投资项目在各历史年份的回报率，以及所要求的总回报率期望值。
表4-5 已知数据表

A
B
C
D
E
F
G
1
例
投资组合优化模型

2
历史数据

3
时期
股票1
股票2
债券

4
1
0
0.07
0.06

5
2
0.04
0.13
0.07

6
3
0.13
0.14
0.05

7
4
0.19
0.43
0.04

8
5
-0.15
0.67
0.07

9
6
-0.27
0.64
0.08

10
7
0.37
0
0.06

11
8
0.24
-0.22
0.04

12
9
-0.07
0.18
0.05

13
10
0.07
0.31
0.07

14
11
0.19
0.59
0.1

15
12
0.33
0.99
0.11

16
13
-0.05
-0.25
0.15

17
14
0.22
0.04
0.11

18
15
0.23
-0.11
0.09

19
16
0.06
-0.15
0.1

20
17
0.32
-0.12
0.08

21
18
0.19
0.16
0.06

22
19
0.05
0.22
0.05

23
20
0.17
-0.02
0.07

24

25
统计量计算

26
单项期望值
0.1130
0.1850
0.0755

27
单项方差
0.0274
0.1102
0.0008

28
标准方差
0.1656
0.3319
0.0278

29

30
相关系数

31

股票1
股票2
债券

32
股票1
1.0000
-0.1959
-0.0289

33
股票2
-0.1959
1.0000
-0.0134

34
债券
-0.0289
-0.0134
1.0000

35

36
模型

37

38
决策变量

39

股票1
股票2
债券
投资比例之和

40
投资比例
0.5063
0.3243
0.1693
1
=
1
41
投资比例的平方
0.2564
0.1052
0.0287

42

43
总回报率期望值

43

实际值

要求值

44

0.1300
>=
0.13

45

46

47

48
总回报率方差
0.0151

49

50
总回报率方差
0.1228

第二步：计算各投资项目的单项回报率期望值、单项回报率方差，及各投资项目之间的相关系数。
根据前面介绍的Excel的公式，可计算出各投资项目的各个统计量。其计算方法与例1同，这里不再重复。计算公式见表4-6。
表4-6 投资组合优化模型中统计量的计算公式
25
统计量计算

26
期望值
=AVERAGE(B4:B23)
=AVERAGE(C4:C23)
=AVERAGE(D4:D23)
27
方差
=VAR(B4:B23)
=VAR(C4:C23)
=VAR(D4:D23)
28
标准方差
=STDEV(B4:B23)
=STDEV(C4:C23)
=STDEV(D4:D23
29

30
相关系数

31

股票1
股票2
债券
32
股票1
1
=CORREL(B4:B23,C4:C23)
=CORREL(B4:B23,D4:D23)
33
股票2
=C32
1
=CORREL(C4:C23,D4:D23)
34
债券
=D32
=D33
1

第三步：建立非线性规划模型

本问题的决策变量是各投资项目的投资比例。用单元格B40:D40分别表示股票1、股票2和债券的投资比例。如表4-5所示。
本问题的目标函数是投资组合的风险最小，即投资组合总回报率的方差最小。由（4-2）式可知，投资组合总回报率R的方差的计算公式如下：
R的方差=
本题中有三个投资项目，所以上式变为：
R的方差=

= （4-4）
用单元格C48表示投资组合总回报率R的方差（即目标函数）。根据（4-3）式，在单元格C48中输入：
=SUMPRODUCT(B41:D41,B27:D27)+2*B40*C40*C32*B28*C28+2*B40*D40*D32*B28*D28+2*C40*D40*D33*C28*D28
上式中，单元格B41：D41中的三个值分别为三个项目投资比例的平方；单元格B27：D27中的三个值分别为三个项目的单项回报率的方差；单元格B28：D28中的三个值分别为三个项目的单项回报率的标准方差；单元格C32、D32、D33中的值分别为股票1与股票2、股票1与债券、股票2与债券之间的相关系数。
如果我们建立了如下的协方差矩阵，则上述表达式可大大简化。

A
B
C
D
30
协方差矩阵

31

股票1
股票2
债券
32
股票1
=B27
=COVAR(B4:B23,C4:C23)
=COVAR(B4:B23,D4:D23)
33
股票2
=C32
=C27
=COVAR(C4:C23,D4:D23)
34
债券
=D32
=D33
=D27
这时，投资组合总回报率R的方差（即目标函数），可以用如下更简单的方法表示：
=SUMPRODUCT(MMULT(B40:D40,B32:D34),B40:D40)
本问题有三个约束条件。第一个约束条件是投资组合总回报率的期望值约束。由（4-1）式可知，投资组合总回报率R的期望值的计算公式如下：
R的期望值=
本题中有三个投资项目，所以上式变为：
R的期望值= （4-5）
用单元格B45表示实际投资组合总回报率R的期望值。根据上式，在单元格B45输入：
=sumproduct (B26:D26,B40:D40)
上式中，单元格B26：D26中的三个值分别为三个项目的单项投资回报率期望值，单元格B40：D40中的值分别是三个项目的投资比例。（4-5）式计算得到的投资组合总回报率的期望值必须不小于要求达到的值，本题中要求达到的期望值为0.13，用单元格D45表示。
第二个约束条件是投资比例之和应等于1。用单元格 E40表示投资比例之和，可用下式计算：
=sum(B40:D40)
它应等于1。
第三个约束是条件非负约束。

第四步：利用“规划求解”功能求出非线性规划的解。
在规划求解参数框中输入目标单元格（目标函数地址）、可变单元格（可变变量地址）和约束条件。其规划求解参数框如图4-3所示。

图4-3 规划求解参数对话框
然后在规划求解参数框中选择“假定非负”（注意：本问题是非线性规划问题，所以不选择“采用线性模型”），最后在规划求解参数对话框中单击“求解”得到本问题的解。

第五步：判断用“规划求解”功能求出是否是本问题的最优解。
本问题的模型如公式（4-3）所示：
o.b, min R的方差= （4-4）
s.t. R的期望值=≥P

≥0
本问题为最小化问题，其目标函数是总风险（总方差）最小。由公式（4-4）可知，总风险是决策变量的幂函数。
由凸函数的定义可知，幂函数Y=cxa，a≥1，c≥0，x≥0是凸函数。
可见公式4-4的目标函数是若干个凸函数之和，因此，本问题的目标函数是一个凸函数。此外，本问题的约束条件均为线性，所以本问题满足运用“规划求解”功能正确求解非线性规划问题的条件，所求得的解是整体最优解。
模型运行结果见表4-5。由该表可得本问题的最优解如下：股票1、股票2、债券的投资比例为0.5063:0.3243:0.1693。这时，投资组合的总回报率期望值达到所要求的0.13，而投资组合的总回报率的方差最小，为0.0151。
第六步：置信区间分析
当投资组合总回报率服从正态分布时，根据正态分析的统计理论，总回报率的置信区间如下。
（1）总回报率的值落在区间[总回报率期望值-总回报率标准方差，总回报率期望值+总回报率标准方差]的概率是68%；
（2）总回报率的值落在区间[总回报率期望值-2总回报率标准方差，总回报率期望值+2总回报率标准方差]的概率是95%；
（3）总回报率的值落在区间[总回报率期望值-3总回报率标准方差，总回报率期望值+3总回报率标准方差]的概率是99.7%。
本题中，总回报率期望值=0.13，总回报率的标准方差===0.1228，所以当总回报率服从正态分布时，有：总回报率以68%的概率落在区间[0.0072，0.2528]（即[0.13-0.1228，0.13+0.1228]）；以95%的概率落在区间[-0.1156，0.3756]（即[0.13-2*0.1228，0.13+2*0.1228]）；以99.7%的概率落在区间[-0.2384，0.4984]（即[0.13-3*0.1228，0.13+3*0.1228]）。
上面的计算是对单元格的操作来实现的，这是很麻烦的，稍不小心，就很容易出错，为避免出错，我们编制了一个VBA程序，来简化上述的操作。VBA程序如下：
Sub js()
Range("b26") = Application.Average(Range("b4:b23"))
Range("c26") = Application.Average(Range("c4:c23"))
Range("d26") = Application.Average(Range("d4:d23"))

Range("b27") = Application.Var(Range("b4:b23"))
Range("c27") = Application.Var(Range("c4:c23"))
Range("d27") = Application.Var(Range("d4:d23"))

Range("b28") = Application.StDev(Range("b4:b23"))
Range("c28") = Application.StDev(Range("c4:c23"))
Range("d28") = Application.StDev(Range("d4:d23"))

Range("b32") = 1
Range("c32") = Application.Correl(Range("b4:b23"), Range("c4:c23"))
Range("d32") = Application.Correl(Range("b4:b23"), Range("d4:d23"))

Range("b33") = Range("c32")
Range("c33") = 1
Range("d33") = Application.Correl(Range("c4:c23"), Range("d4:d23"))

Range("b34") = Range("d32")
Range("c34") = Range("d33")
Range("d34") = 1
Cells(40, 5) = "=SUM(B40:D40)"
Cells(41, 2) = "=B40^2"
Cells(41, 3) = "=c40^2"
Cells(41, 4) = "=d40^2"
Cells(45, 2) = "=SUMPRODUCT(B26:D26,B40:D40)"
Cells(48, 3) = "=SUMPRODUCT(B41:D41,B27:D27)+2*B40*C40*C32*B28*C28+2*B40*D40*D32*B28*D28+2*C40*D40*D33*C28*D28"
Cells(50, 3) = "=SQRT(C48)"
SolverReset
SolverOk setcell:="$c$50", MaxminVal:=2, ValueOf:="0", byChange:="$b$40:$d$40"
SolverAdd CellRef:="$e$40", Relation:=2, FormulaText:="100%"
SolverAdd CellRef:="$b$45", Relation:=3, FormulaText:="13%"
SolverSolve (True)
End Sub
需要注意的是：在使用规划求解函数如SolverOk（）、SolverAdd（）等编制程序进行求解之前，首先必须建立对规划求解加载宏的引用，方法是：在Visual Basic编辑器界面下，单击[工具]菜单中的[引用]命令，如图4-4所示，打开[引用-VBAProject]对话框，如图4-5所示，然后选中[可使用的引用]列表框中的“SOLVER ”复选框。

图4-4 执行引用命令

图4-5 [引用-VBAProject]对话框
如果“SOLVER ”未出现在[可使用的引用]列表框中，则需要单击[浏览]按钮，并打开“\Office\Library\ SOLVER”子文件夹中的“Solver.xla”。

4.5通用投资组合优化决策模型及其信息化实现

4.5.1最优投资组合的确定

考虑一个投资组合X，它由n个证券组成，每个证券的预期收益率为，方差记为，证券之间的协方差记为，i、j=1，2，…，n。于是证券投资组合的收益率和风险可以表示成

在给定预期收益率之下，如何选择证券组合的权重，使证券组合X具有最小方差呢？
记，为确定最小方差集合，我们考虑如下优化模型，即一般的马柯维茨模型
，
这是一个等式约束的极值问题，

在有些情形下，投资者把不进行卖空作为一种投资策略，因此，讨论在不允许卖空的约束下如何确定最小（或最优）方差集合是必要的。这时在约束条件中需要加入xi大于0，i=1,…,n。模型为
，
这是二次规划模型。利用Kuhn-Tucker条件，可得类似结论。对于上面的二次规划模型，我们可以通过如下的VBA程序来进行求解。

4.5.2通用投资组合风险的最优化模型的VBA实现

（1）首先建立一个名字为“给定最低预期收益率的最优投资组合规划求解模型.xls”的工作簿，在当前的sheet1上设计模型的结构，如图4-6所示。

图4-6 给定最低预期收益率的最优投资组合规划求解模型
是否允许卖空的下拉列表框控件插入在单元格B5的位置，此下拉列表框控件的单元格链接为“$B$5”，数据区域为“$C$5:$C$6”，并在单元格C5和C6中分别输入“允许卖空”和“不允许卖空”，且此两个单元格的字体颜色设置为白色，以便使模型界面美观。
（2）模型由以下三部分组成： 已知数据区域、命令按钮区域（包括一个[准备数据]按钮、一个[开始计算]按钮、一个[