实验报告证券投资学院名称专业班级提交日期评阅人 ______________评阅分数 ______________实验五：运用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⅛wChPbeMrd Mgrwneni:A B C D E F H1J K L O I 131⅛A4∣⅛fi⅛rtS中世还祥中孟董令三一甫范梆5平均回抿C-JClα⅛Jβ.7≥5Q.367 3.A⅛'0.3⅛D6⅛*≠0.3M0⅛450駆0.7Gfi33S⅛ C.656S 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.27312D.110gj2.? C.E^E∣D.112 C.22313三1运工 D.153□ 2J7 C.11Ξ D.49E 3 ZM C.1E3U C.1H50 357 C.1SE D.23B 3.M5- C.2D7L5 D.12-⅞0 273 D.2M D.1E33JΠ7 C.43ΠLlS EkKKl1?^M-D⅛C>S- S Et&r≠UALUE∣Q 5^0』0.4555≠Λ M I19F sVAlUEI D≡∣7⅛2C ≡=55B3盗产财和帕血报］用规划車解订a有故组台，可夷空言⅞⅛边畀J可卖空有册CRMd)∣,■ ■ ■ •誰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□lSOlVer ParameterSEqUal To:θMa× ® Mirl O ValUe of; DEy Changing Cells ：D •在规划求解选项卡里面选择“选项”，再选择“非负”再运行一次，比较两次返回的投资比例值的正负。
在实验报告中记录两次得到的最优投资组合，并说明投资比例是负值说明什么？E.（选做）借助连续调用规划求解的 VBA 过程生成有效组合以及资本市场线。
参考实验参考电子书 《金融建模一使用EXCEL 和VBA 》电子书第四章 P83F .对比可卖空和不可卖空的有效前沿图试对比说明其不同?【实验项目步骤与结果】Set Target Cell: 权重Subject to the Constr aints ： IClle - IE$19 ⅛⅛16- 1Add ChangeClBleteQPUonSReSet AlI HeIPJ f⅝ 申』7E3 皐 0[申』[G3^Of ⅜a∙fl⅛⅛⅜⅛" [Q*ipttiE ∣H⅞f h4gςl.∣ - MiCrgTgF⅞ EX¢¢1R<v⅛w Vi ⅛w ⅞dd>[h^Page LJ沖XFdrniult!, D4<Aj⅛ Fl i Gnl AIetMl 舟_i From V⅛⅛ —" JF⅛⅝dλ Otħ⅛r√JFrofll TieiCIΞ□UΓCM TEAj 亦科匚QnrwclionsG<t EItHnIl Di^a\ 切 CWrlfdi⅞π⅛ '-JFtDfierties ⅛^rt⅜ħ ~立j[音Edrt ILinkSCmnrkHllOnS⅛*噩U s *tE CI⅞dΓ ■:J- PeaPP⅛ F■电 I iπ-■/ Ad ∣,a∏Hed一Jr Jg_n ^DdtiVdIiφati^ ■ [⅛Cuπs⅛dff1-!Tt4⅞⅛ F⅛M V ⅞ -CalUmnS- DUVmBbEE ISf WhIlJ Ana⅜⅛ 'Dv⅞a Toak-≠ Gr乜WP 5*⅛5β⅜fftiA (JJ17i4S7⅛0J73635■J* Ur⅞grDup T ■■:EuMortri □ιlUinbtAnalystsA BDE FH1 I J KLi M W=0 ■1 用規知或据苗H 棺羟堀含e 杜巫塀20103∙Λ4⅛Λ⅛^≡Pffi=L≡*中金贅金Z-ffiZ 弼l⅛亦 蠡轴3 平均回抠0.3C1 OW0.克 5> 0.367 <J-⅛⅛ 0.3⅛E0.5M叮認C.E34¢.70635»&.656t⅛方工返甲和収重⅜⅞⅛⅛⅞行中世证弄中"工三一盂工；対冰鶴恥茏爪 玫畫W⅛fi ⅛澜行 0.25C CLITrCllD ⅛153 3.1S5 C.12A c.∆l -LL CP 催匹鼻 0.178 o⅛sG.3≥T 0.23? 0.1⅛7 β∙.275 -C.⅛61L2 CP 金黄全 D.110 OJJ.7 询6D.112DiSiC.228 CJOl 13 S=IX &.153 02J?CllS A 朋D2Ji Clfii C.l^ LISimii D.lβ⅝ 0J⅛7C.l££ 3.H⅞C.2fi7O>.1D7 L5 IfeX^0.1Sd WOlH0.1flj3J0?0.4MC.M2LHi ■岀Ti⅛lfi⅛⅛f.≡≡ιl華≡Ξ1.1KKl 'LLE呱望问?E 0.44555∏.SUJ=J C.4⅞B5&LSi碗3ECl ½LBa3.57⅛E2 D.4S5B3⅛ΛR 7Δ⅛⅛∑S 冋FFJ5SΛ⅛ft可未空有壊辿畀⅜lRNdK⅛∕tragr Π.1-6TCnUrrt:4 SUm- ι.mκ订i⅛rt3证等段责⅛<⅛5EΓ⅛ .. It 2 Windows ∣Eκpkrer ■*-2⅞13⅛ ttltffi型R∙ ∙∙∙H⅛≡⅛ hd⅞ιtVSart 5 FirterG5 0417c aElXB .使用规划求解审闻JT⅛L LHa 碁H 蛊畝歸皓 分列1⅛SK⅛. H⅛ 側S 程合弁计KfflK⅛0Fr .堺 劉亦证■ ■A. 旦回报用规划戒解计算有效担合可卖空■?⅛⅛≡示疇囁蠹：ft⅛S 取洁*沁E 左A Q r I kJnI 輸入.浦岌银行中信证券中金黄金二一重T海峻水混雅戈尔I ［乎均回抿0.3010.500 D.4MO6⅛50.7250.804心&70.7060.44505⅛90J400.656标准差协方差矩阵和权重浦绘银行中信证券Φ⅛S金=一重工权重］浦岌银行0.250 0.17B 0.110 0.1530.1B5 0.1241中信证券0.17S 0.4&90327 0.237 0197 0.273 中金茵金0410 0.327 0.646 0.112 OlBS 0.229三一重工OiS3 02370.1120.493O23B0.163海⅛≡水泥OlBS 0.197 OLlSB O23S 0.359 0.2D71雅戈尔0.124 0,273 0.229 0.1S3 0.207 0430∣k O OM ］输岀育效绢台擁信翹值P■4π ⅛ 旨期望回抿Γ⅛VALUEl O.5SSO3 0.44555 权車具原标灌差r WALUEI S<"ΞE22'435b 3选｝圣求解方法恒求解方法为光滑非线性规划求解问题选择苫RG斗醸性引≡「井为非光滑规划求解冋豎聲滴化弓奚拿.为线性規划茨解问霆齢軽量为m氐澈Qgitnra ■■■■■■■■■■■■■■ IFiuaaaaaBBB ■■ BBiiiriiiaaa ■■・・・・・・・・・・・・・・・菲线性GRG浦意银行口言还券口 ⅛≡金三一海螺班泥平均回报0.301 0.4940.725 D.S67 0.446 OMO 赫隹差0.5000J 6350.β040.7060.5990.655协方差矩陆和权重投资比例为负值说明该证券风险远远大于其收益率，已经不适合投资。
F .对比可卖空和不可卖空的有效前沿图试对比说明其不同?0 2500.178 OIlO 0.153 0.1S5 0.124 0.4O& 0.17SDΛ&3 0.327 0JJ7 0.157 0.273 0,000 Ollo 0.327 0.&46 D.112 0.1S8 0.229 0.275 0.153 0.237 0.112 0.49B 0.238 0.1&3 0.129 OlfiS D.197 Diaa D.23B D.359 0.207 0.114 0.1240.2730.2290.1630.2070.4300.077 中信证券 中金董金Ξ→I 馬i≡7K泥 雅戈尔洼应汙口言证券口金董金三一重工渎堂叹泥雅：⅞尔椁重1.&00警出 期望回报 标准差0.44555 0 588D3 O.W&S 0.462640.57B520.4&5B3权重复原肓效组合 参箕值蜃箕值065OO期望回按通过可卖空和不可卖空有效前沿图的对比可以看到，在相同风险的时候可卖空的情况下期望回报要比不可卖空的情况要高，并且随着风险的增加可卖空曲线的期望回报增加程度明显比不可卖空曲线要大。
【实验项目结论与心得】通过本次实验，学会了用excel进行相关组合最优的计算，同时也画出了风险收益曲线,为将来进行实盘操作打下了坚实的基础。
【教师评语与评分】。