使用ExcelSolver求解最优风险资产组合.doc

《使用ExcelSolver求解最优风险资产组合.doc》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、UsingExcel'sSolvertosolvequestion4&7It’smoreconvenientbyusingexceltoworkouttheminimum-varianceportfolioandtheoptimalriskyportfoliosetStep1：Inputtheexpectedreturnandstandarddeviationofeachstock,thencalculatethecovariancematrix.Step2:Setupaworkingareatocomputetheminimum-varianceportfolioandtheoptimal

2、riskyportfolioset.Temporarily,Wecaninputtheweightofeachstockarbitrarily.Andtheformulasusedinthecellsare:C12=(C11^2)*C7;D12=B12*D11*D7;C13=B13*C11*C8;D13=D11^2*D8;B14==SUM(B12:B13)C15=SUM(C12:D13);C16=SQRT(C15);C17=B12*C2+B13*C3C18==(C17-C4)/C16Step3:Workouttheoptimalriskyportfolioset.Runsolver,sett

3、hetargetcellasC18.Inordertogettheoptimalriskyportfolio,weoughttomaximizethesharperatio,sothetargetshouldbesetas“maximize”.Thechangeablevariablesshouldbesetas“$B$12:$B$13”whichdenotetheweightofeachstock.Finally,addaconstraintwhichdemandsthat“B14=1”andrunit.Then,wecanobtaintheoptimalriskyportfolioSte

4、p4:Workouttheminimum-varianceportfolioset.Usingthesimilarmethod,wecangettheminimum-varianceportfolio.