{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Portfolio Frontier (二資產)","Portfolio Frontier (三資產)","歷史資料估計E(r), s.d., and correl."],"not_rendered":{"charts":2,"images":0,"macros":0,"external_links":0,"defined_names":68,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Portfolio Frontier (二資產)","index":0,"n_rows":35,"n_cols":12,"rows_kept":35,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,null,null,null,"E(r_i) = ",0.1,0.06,"(derived according to the annualized adjusted (total) returns in the historical data)"]
[null,"coef. 1","coef.2",null,"Result",null,null,null,"sigma(r_i) =",0.2,0.1]
["Objectinve Function",null,null,null,{"$f":"(B7*B7*B9+C7*C7*C10+2*B7*C7*C9)^0.5","v":0.2384887314292857},null,null,null,"correlation =",1,0.5]
["C1",0.1,0.06,null,{"$f":"SUMPRODUCT(B4:D4,$B$7:$D$7)","v":0.11000099999999999},"=",0.11,null,null,null,1]
["C2",1,1,null,{"$f":"SUMPRODUCT(B5:D5,$B$7:$D$7)","v":1},"=",1]
[]
["Solution Value (weights)",1.2500249999999995,-0.2500249999999995,null,null,"※ 使用 \"資料\" => \"規劃求解\"按鈕解此受限制portfolio standard deviation極小化問題"]
[null,null,null,null,null,"※ 如何叫出\"規劃求解\"按鈕：\"檔案\" => \"選項\" => \"增益集\" => \"Excel增益集\" => 執行 => 選擇 \"規劃求解增益集\""]
["variance-covariance",{"$f":"0.2^2","v":4.0000000000000008E-2},{"$f":"0.5*0.2*0.1","v":1.0000000000000002E-2}]
[null,{"$f":"C9","v":1.0000000000000002E-2},{"$f":"0.1^2","v":1.0000000000000002E-2}]
[]
[]
[]
[null,"E(r_p)","sigma(r_p)"]
[null,0,0.27838821814178705]
[null,0.01,0.23848873142928584]
[null,0.02,0.20000000000026549]
[null,0.03,0.16393596310730715]
[null,0.04,0.13228756555347029]
[null,0.05,0.10897247358863862]
[null,0.06,0.1]
[null,7.0000000000000007E-2,0.1089724735883951]
[null,0.08,0.13228756555302887]
[null,0.09,0.16393596310730713]
[null,0.1,0.19999999999973458]
[null,0.11,0.23848086929301068]
[null,0.12,0.27838417702655083]
[null,0.13,0.31917863336980151]
[null,0.14000000000000001,0.36055512754616326]
[null,0.15,0.40233692348548011]
[null,0.16,0.44440972086548081]
[null,0.17,0.48669382060875821]
[null,0.18,0.52915026221261707]
[null,0.19,0.57172983130173516]
[null,0.2,0.61441028637252781]

{"sheet":"Portfolio Frontier (三資產)","index":1,"n_rows":36,"n_cols":13,"rows_kept":36,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,null,null,null,"E(r_i) = ",0.1,0.06,0.2,"(derived according to the annualized adjusted (total) returns in the historical data)"]
[null,"coef. 1","coef.2","coef. 3","Result",null,null,null,"sigma(r_i) =",0.2,0.1,0.3]
["Objectinve Function",null,null,null,{"$f":"(B7*B7*B9+C7*C7*C10+D7*D7*D11+2*B7*C7*C9+2*B7*D7*D9+2*C7*D7*D10)^0.5","v":0.29538200380310259},null,null,null,"correlation =",1,0.5,0.8]
["C1",0.1,0.06,0.2,{"$f":"SUMPRODUCT(B4:D4,$B$7:$D$7)","v":0.19999999960140605},"=",0.2,null,null,null,1,0.7]
["C2",1,1,1,{"$f":"SUMPRODUCT(B5:D5,$B$7:$D$7)","v":1},"=",1,null,null,null,null,1]
[]
["Solution Value (weights)",-0.37737839554107716,0.26955599966215488,1.1078223958789224,null,"※ 使用 \"資料\" => \"規劃求解\"按鈕解此受限制portfolio standard deviation極小化問題"]
[null,null,null,null,null,"※ 如何叫出\"規劃求解\"按鈕：\"檔案\" => \"選項\" => \"增益集\" => \"Excel增益集\" => 執行 => 選擇 \"規劃求解增益集\""]
["variance-covariance",{"$f":"0.2^2","v":4.0000000000000008E-2},{"$f":"0.5*0.2*0.1","v":1.0000000000000002E-2},{"$f":"0.8*0.2*0.3","v":4.8000000000000008E-2}]
[null,{"$f":"C9","v":1.0000000000000002E-2},{"$f":"0.1^2","v":1.0000000000000002E-2},{"$f":"0.7*0.1*0.3","v":2.0999999999999998E-2}]
[null,{"$f":"D9","v":4.8000000000000008E-2},{"$f":"D10","v":2.0999999999999998E-2},{"$f":"0.3^2","v":0.09}]
[]
[]
[]
[null,"E(r_p)","sigma(r_p)"]
[null,0,9.0681003164502463E-2]
[null,0.01,8.4249980738170888E-2]
[null,0.02,8.0668538908602067E-2]
[null,0.03,8.031880195252096E-2]
[null,0.04,8.3241669337317831E-2]
[null,0.05,8.9115688260062567E-2]
[null,0.06,9.7408318340964459E-2]
[null,7.0000000000000007E-2,0.10756162548744402]
[null,0.08,0.11910075383616477]
[null,0.09,0.13166180537443323]
[null,0.1,0.14497941358510164]
[null,0.11,0.15886339864255555]
[null,0.12,0.1731776147762672]
[null,0.13,0.18782371726750957]
[null,0.14000000000000001,0.20272978598519031]
[null,0.15,0.21784246277174515]
[null,0.16,0.23312156994327679]
[null,0.17,0.24853641418644526]
[null,0.18,0.26406322600824739]
[null,0.19,0.27968335770046787]
[null,0.2,0.29538200520139157]

{"sheet":"歷史資料估計E(r), s.d., and correl.","index":2,"n_rows":12,"n_cols":4,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["daily adjusted (total) return","r1","r2","r3"]
[null,1E-3,-0.03,3.0000000000000001E-3]
[null,-0.03,7.0000000000000001E-3,-3.5000000000000003E-2]
[null,0.04,7.0000000000000001E-3,1.4999999999999999E-2]
[null,1.4999999999999999E-2,3.0000000000000001E-3,6.0000000000000001E-3]
[null,-0.04,0.02,1E-3]
[]
["annual E(r_i)",{"$f":"AVERAGE(B2:B6)*250","v":-0.69999999999999984},{"$f":"AVERAGE(C2:C6)*250","v":0.34999999999999992},{"$f":"AVERAGE(D2:D6)*250","v":-0.50000000000000011}]
["annual sigma_i",{"$f":"STDEV(B2:B6)*SQRT(250)","v":0.51761472158353461},{"$f":"STDEV(C2:C6)*SQRT(250)","v":0.29550803711574408},{"$f":"STDEV(D2:D6)*SQRT(205)","v":0.2750363612324741}]
["correlation_ij",{"$f":"CORREL(B2:B6,B2:B6)","v":1},{"$f":"CORREL(B2:B6,C2:C6)","v":-0.28414419428577098},{"$f":"CORREL(B2:B6,D2:D6)","v":0.66589387716095161}]
[null,{"$f":"C10","v":-0.28414419428577098},{"$f":"CORREL(C2:C6,C2:C6)","v":0.99999999999999978},{"$f":"CORREL(C2:C6,D2:D6)","v":-0.1239502865413068}]
[null,{"$f":"D10","v":0.66589387716095161},{"$f":"D11","v":-0.1239502865413068},{"$f":"CORREL(D2:D6,D2:D6)","v":1}]