实验五：运用Excel规划求解进行最优投资组合的求解

实验报告

证券投资

学院名称

专业班级

提交日期

评阅人 ______________

评阅分数 ______________

实验五：运用Excel规划求解进行最优投资组合的求解

【实验目的】

1理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序;

2、进一步理解最优投资组合模型，并据此构建多项资产的最优投资组合；

【实验条件】

1个人计算机一台，预装Windows操作系统和浏览器；

2、计算机通过局域网形式接入互联网；

3、matlab 或者Excel 软件。

【知识准备】理论知识：课本第三章收益与风险，第四章投资组合模型，第五章CAPM

实验参考资料：《金融建模一使用EXCEL 和VBA》电子书第三章，第四章，第五章

【实验项目内容】请打开参考《金融建模一使用EXCEL和VBA》电子书第四章相关章节（4.3）完成以下实验

A .打开“实验五组合优化∙xls”，翻到“用规划求解计算最优组合”子数据表；

B •调用规划求解功能进行求解。

点击“工具”在下拉菜单点击“规划求解”，如没有此选项说明需要加载规划求解后

才能使用，如何加载见实验补充文档“EXCEL规划求解功能的安装”。

C∙

（⅛中总上盟皐0忡空鼻阳皐住∣∙. I：∙nr ■■

H QT kιd⅞ι⅞ P⅛B⅛L0J⅛M Fdimult!- Di财R⅛vi⅛w

ChPbeMrd Mgrwneni:

A B C D E F H1J K L O I 1

3

1⅛A

4∣⅛fi⅛rtS中世还祥中孟董令三一甫范梆

5平均回抿C-JClα⅛Jβ.7≥5Q.367 3.A⅛'0.3⅛D

6⅛*≠0.3M0⅛450駆0.7Gfi33S⅛ C.656

S K>3T≡⅛T51OPΛ

9

.⅛⅛÷8il甲*证漲中≤Λ⅛三Lj E工尿蛋

LD融帳行AlM0-1?S Odlfi CiJSl 3.1±!

Il Ii mlE# D.17Iβ.32?0.23?0.1⅛7, C.273

12

D.110gj2.? C.E^E

∣

D.112 C.223

13三1运工 D.153□ 2J7 C.11Ξ D.49E 3 ZM C.1E3

U C.1H50 357 C.1SE D.23B 3.M5- C.2D7

L5 D.12-⅞0 273 D.2M D.1E33JΠ7 C.43Π

LlS EkKKl

1?^M-D⅛C>S- S E

t&r≠UALUE∣Q 5^0』0.4555≠Λ M I

19F sVAlUEI D≡∣7⅛2C ≡=55B3

盗产财和帕血报］用规划車解订a有故组台，可夷空言⅞⅛边畀J可卖空有册C

RMd)∣,

■ ■ ■ •誰13-

Font

三h

½cUrttyWArntangi Eome■■商k< >E⅝r⅞erv1 his b«en disabled.OPtIaFis...

F弋;I谋件∖⅛≡⅛⅛t揍2M0∖E JT诃文档溶诃2"7tel［Q4章坦合挠忙揆型'茹］魏据和弼盎H辽

∙⅞i□l

SOlVer ParameterS

EqUal To:

θMa× ® Mirl O ValUe of; D

Ey Changing Cells ：

D •在规划求解选项卡里面选择“选项”

，再选择“非负”再运行一次，比较两次返回的投

资比例值的正负。在实验报告中记录两次得到的最优投资组合，

并说明投资比例是负值说明

什么？

E.（选做）借助连续调用规划求解的 VBA 过程生成有效组合以及资本市场线。

参考实验参考电子书 《金融建模一使用EXCEL 和VBA 》电子书第四章 P83

F .对比可卖空和不可卖空的有效前沿图试对比说明其不同?

【实验项目步骤与结果】

Set Target Cell: 权重

Subject to the Constr aints ： IClle - IE$19 ⅛⅛16- 1

Add Change

ClBlete

QPUonS

ReSet AlI HeIP

J f⅝ 申』7E3 皐 0[申』[G3^Of ⅜a∙fl⅛⅛⅜⅛" [Q*ipttiE ∣H⅞f h4gςl.∣ - MiCrgTgF⅞ EX¢¢1

R[h^

Page LJ

沖X

Fdrniult!, D4

j⅛ Fl i Gnl AIetMl 舟

_i From V⅛⅛ —" J

F⅛⅝dλ Otħ⅛r

√J

Frofll TieiCI

Ξ□UΓCM T

EAj 亦科

匚

Qnrwclions

G

\ 切 CWrlfdi⅞π⅛ '-

J

FtDfierties ⅛^rt⅜ħ ~

立j[音

Edrt ILinkS

CmnrkHllOnS

⅛*噩

U s *t

E CI⅞dΓ ■:J- PeaPP⅛ F

■电 I i

π

-

■/ Ad ∣,a∏Hed

一Jr J

g_n ^DdtiVdIiφati^ ■ [⅛Cuπs⅛dff1-!

Tt4⅞⅛ F⅛M V ⅞ -

CalUmnS- DUVmBbEE ISf WhIlJ Ana⅜⅛ '

Dv⅞a Toak

-≠ Gr

乜WP 5

*

⅛5β⅜ffti

A (JJ17i4S7⅛0J73635

■J* Ur⅞grDup T ■■:

EuMortri □ιlUinbt

Analysts

A B

D

E F

H

1 I J K

L

i M W

=0 ■

1 用規知或据苗H 棺羟堀含

e 杜巫塀2010

3

∙Λ

4

⅛Λ⅛^≡Pffi=L≡*

中金贅金Z-ffiZ 弼l⅛亦 蠡轴

3 平均回抠

0.3C1 OW

0.克 5> 0.367

E

0.5M

叮認

C.E34

¢.706

35»

&.656

t⅛

方工返甲和収重

⅜⅞⅛⅛⅞行中世证弄中"工三一盂工；対冰鶴恥茏爪 玫畫

W

⅛fi ⅛澜行 0.25C CLITr

CllD ⅛153 3.1S5 C.12A c.∆l -

LL C

P 催匹鼻 0.178 o⅛s

G.3≥T 0.23? 0.1⅛7 β∙.275 -C.⅛61

L2 C

P 金黄全 D.110 OJJ.7 询6

D.112

DiSi

C.228 CJOl 13 S=IX &.153 02J?

CllS A 朋

D2Ji Clfii C.l^ LI

Simii D.lβ⅝ 0J⅛7

C.l££ 3.H⅞

C.2fi7

O>.1D7 L5 IfeX^

0.1Sd W

OlH

0.1flj

3J0?

0.4M

C.M2

LHi ■岀

Ti⅛lfi⅛

⅛f.≡≡ιl

華≡Ξ

1.1KKl '

L

LE

呱望问?E 0.44555

∏.SUJ=J C.4⅞B5&

LSi

碗3E

Cl ½LBa

3.57⅛E2 D.4S5B3

⅛ΛR 7Δ⅛⅛∑S 冋FF

J5SΛ⅛ft

可未空有壊辿畀

⅜l

RNdK

⅛∕tragr Π.1-6T

CnUrrt:4 SUm- ι.mκ

订i⅛rt

3证等段责⅛<⅛5EΓ⅛ .. It 2 Windows ∣Eκpkrer ■

*-2⅞13⅛ ttltffi

型R∙ ∙∙∙

H⅛≡⅛ hd⅞ιt

V

Sart 5 Firter

G5 0417

c aEl

X