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

1、实验报告证券投资专业班级提交日期评阅人评阅分数实验五：运用Excel规划求解进行最优投资组合的求解【实验目的】1、理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序:2、进一步理解最优投资组合模型，并据此构建多项资产的最优投资组合；【实验条件】1、个人计算机一台，预装Windows操作系统和浏览器；2、计算机通过局域网形式接入互联网；3、matlab 或者 Excel 软件。【知识准备】理论知识：课本第三章收益与风险，第四章投资组合模型，第五章CAPM实验参考资料：金融建模一使用EXCEL和VBA电子书第三章，第四章，第五章【实验项目容】 请打开参考金融建模一使用EXCEL和V

2、BA电子书第四章相关章ij- (4.3)完成以下实 验A. 打开实验五组合优化.xls”，翻到“用规划求解计算最优组合”子数据表：B. 调用规划求解功能进行求解。点击“工具”在下拉菜单点击“规划求解”，如没有此选项说明需要加载规划求解后 才能使用，如何加载见实验补充文档“EXCEL规划求解功能的安装”。K0 tLL2C10冏皆LsiiDriCipboard -FontMionmcneG4SMesOeiece Fornat HQ中兰冷（S3 曼 0 忸合比化模里必!Conx；3iibiity Mode） Microsoft xelHome Iniert Page layout9 WurWySom

3、e adire conUnt hw been esobkd1闭圾划畑计算有处俎合2_ANurbeiCflfxiriiorwl roimtt Ce11冷盛Sort ft Hnd&NW 5K*cfEdttM如9行中硕证并中金資金三一武工险療加U奖戈尔份方魏坏砂整中话证悴中金；!全三一至工O25CC.1730110015301BSC0q憶it券0178OW032702刃01970.273中金衣金0.11QC.327Q20.1120.IB80.229三一HX0153G.2J7Q1U0 2弐O.leS和0*&0.1P0.1B30.23803活0.X70124C.273022901G30 237G.A30

4、OOCO_1II92：2425C免旳CMSMIUE057862C.45832ID-P M? 3i 3432OO0血C.创07250367oYC.珂0.疝C.C550.8工0.70C空？5C.05Cm 9FFFU塗和翦跋罰用现划求録itR右绘纽可茨空曲攵迥見C.I Close jhn) M虧中。 O 晏 0申 0 冷(3 0円合优化*型8 s Kontibii* Mode Microsoft xel -Hone IniertPage LayoutForrrMksiDdURe,ievVieww Frore 丸“俗、Fron Wet rromOthe txtstmgQ Fre T5 Sources

5、ConnexionsGet Erlemal Dsts勺Connexion:Refreih l Eflt Linksipcitie?Q HtappConnertions%rt& Filter.3 ZData ualWKion SpConsdKfekTed to Remove .95E cgse; fi? h3t HDsti Tookb Group % StiverJ Unflrouo SubtotalOutlineSolver Parameters5et Target Cell:殖IE! 1Equ-al To; O Max MinO Value of:。By Changing Cells:Jx

6、I 0.41744S700279635IS19202122Z5550 583C.555C.5?Et2C.455S3收丟負阪4r25 2435 04 333J8 -0Q -32212934170 301曲 0J07 3M2曲曲 startis Q f尬逍券冷歿労畅次骇.卜2*曲叭（：如 *9 3姐台忧化3尖0Me“X：0】67 Count e SLZ| ABCDEFGH1用以刘敦解计算林俎合3A4杀么三一筆工3呦:.逞鲨曲5巧均卫気Q.3Q1C.C*O.72S0.3670.45C.3405050CC.6I507060 5SC.6$一 t：.O25GQ.178011001530153G.12Cn中

7、区还界0.178C上甜0 327023701970.273CK1n0.110C.32?0理011201680.229C3C1D三一車工0.15,C.2570.X120.C980.258C.1C5G.114:厂C.15701K023S0 3590.X7C1C?1501MC.273022901630 28C.-3002lii.OCCD在规划求解选项卡里而选择“选项S再选择“非负”再运行一次，比较两次返回的投 资比例值的正负。在实验报告中记录两次得到的最优投资组合，并说明投资比例是负值说明 什么？E.（选做）借助连续调用规划求解的VBA过程生成有效组合以及资本市场线。参考实验参考电子书金融建模一使用

8、EXCEL和VBA电子书第四章P83F.对比可卖空和不可卖空的有效前沿图试对比说明其不同？【实验项目步骤与结果】蔓回报用规!1求解计算有效组台可卖空军B.使用规划求解*.游V5S骼回昶吕和甌瞎湘 沁酝 *fe 菽启镒台祥计舁晝刃分托了公宝参22SIR1 瓏 1：gia总如a台驱cs八Rc.1平均回協标准差0.3010.5000.4940.6850.7250.8040.3670.7060.4460.5990.3400.656协方差矩眸和权重浦左银行中信证券中金苗金二一重丁每蠟水泥雜戈尔权重浦发银行0.2500.1780.1100.1530.1S50.124中信证券0.1780.4690.3270

9、.2370.1970.273中金黄金0.1100.3270.64&0.1120.1S80.229三一重工0.1530.2370.1120.4980.2380.163海螺水泥0.1850.1370.18&0.2380.3590.207雅戈尔0.1240.2730.2290.1630.2070.4300.0001输出有效组台参照值参照值4n fft-Q rFj期望回报PVALUE!0.588030.44555干乂里臭原标准差LVALUE!0.578620.48583输入浦发银行中信讦券 中金苗金=一重T海蛭水泥 雅龙尔i.On0.8-权56重 0.4 -0.2-0.0-11111*I*1藝 & 衣

10、. 乡 皋0 匠束变S为非员数画: 厂磐求解方法;非经性GRG甥方法为光滑非线性规划求解问题迄GRG非线性引奚 为线性规划求韓问遂鋅 挚并为非光滑规划求解问题选择滴化引奚浦发银行口信证券口金罡金三一重工海塚水泥雅戈尔平均回报0.3010.4940.7250.3670.4460.3400.5000.6850.&40.70S0.5990.656协方差矩阵和权重滝发银行中信还券中金堇金三一重工海舞水泥惟戈尔权重0.2500.1780.1100.1530.1850.1240.40&中信证券0.1780.46903270.2370.1970.2730.000口金董金0.1100.3270.6460.1120.1880.2290.275三一丸0.1530.2370.1120.4980.2380.1630.129海摟水泥0.1550.1970.1580.2380.3590.2070.114雅戈尔0.1240.2730.2250.1630.2070.4300.0771.000输出有效组合参頤值参照值期望回报0.445550.588030.44555TA 里 SJM标准差0.462640.578620.48583投资比例为负值说明该证券风险远远大于其收益率，已经不适合投资。权重F.对比可卖空和不可卖空的有效前沿图试对比说明其不同？1.2期望回很组合的凤脸和回