{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Pages 228-232","Page 233","Page 235","Page 236","Page 237","Page 241","Page 241-244"],"not_rendered":{"charts":3,"images":0,"macros":1,"external_links":0,"defined_names":30,"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":"Pages 228-232","index":0,"n_rows":65,"n_cols":8,"rows_kept":65,"cut":false,"hidden":false,"merged":[[0,0,0,5],[1,0,1,3]]}
["CALCULATING THE FRONTIER"]
["Variance-covariance, S",null,null,null,null,"Mean returns E(r)","E(r) minus constant"]
[0.1,0.01,0.03,0.05,null,0.06,{"$f":"F3-$B$8","v":1.9999999999999997E-2},{"$f":"getformula(G3)","v":"<--  =F3-$B$8"}]
[{"$f":"B3","v":0.01},0.3,0.06,-0.04,null,0.08,{"$f":"F4-$B$8","v":0.04}]
[{"$f":"C3","v":0.03},{"$f":"C4","v":0.06},0.4,0.02,null,0.1,6.0000000000000005E-2]
[{"$f":"D3","v":0.05},{"$f":"D4","v":-0.04},{"$f":"D5","v":0.02},0.5,null,0.15,0.10999999999999999]
[]
["Constant, c",0.04]
[]
["Computing an envelope portfolio with constant = 0"]
["z",null,null,null,null,"Envelope portfolio\n x"]
[{"$f":"MMULT(MINVERSE(A3:D6),F3:F6)","v":0.38610628324643165},{"$f":"getformula(A12)","v":"<--  {=MMULT(MINVERSE(A3:D6),F3:F6)}"},null,null,null,{"$f":"A12/SUM($A$12:$A$15)","v":0.35527263160809713},{"$f":"getformula(F12)","v":"<--  =A12/SUM($A$12:$A$15)"}]
[0.25673204974832409,null,null,null,null,{"$f":"A13/SUM($A$12:$A$15)","v":0.23622995763063889}]
[0.16877337125401595,null,null,null,null,0.15529547783223951]
[0.27517700080506213,null,null,null,null,0.25320193292902443]
[null,null,null,null,"Sum",{"$f":"SUM(F12:F15)","v":1},{"$f":"getformula(F16)","v":"<--  =SUM(F12:F15)"}]
[]
[{"$f":"\"Computing an envelope portfolio with constant = \"&TEXT(B8,\"0.00%\")","v":"Computing an envelope portfolio with constant = 4.00%"}]
["z",null,null,null,null,"Envelope portfolio\n y"]
[{"$f":"MMULT(MINVERSE(A3:D6),G3:G6)","v":4.0396060462421612E-2},{"$f":"getformula(A20)","v":"<--  {=MMULT(MINVERSE(A3:D6),G3:G6)}"},null,null,null,{"$f":"A20/SUM($A$20:$A$23)","v":7.8206024587775952E-2},{"$f":"getformula(F20)","v":"<--  =A20/SUM($A$20:$A$23)"}]
[0.13863620973748958,null,null,null,null,{"$f":"A21/SUM($A$20:$A$23)","v":0.2683971333682923}]
[0.11505240427661033,null,null,null,null,0.22273932296218615]
[0.22244919456169257,null,null,null,null,0.4306575190817456]
[null,null,null,null,"Sum",{"$f":"SUM(F20:F23)","v":1},{"$f":"getformula(F24)","v":"<--  =SUM(F20:F23)"}]
[]
["E(x)",{"$f":"MMULT(TRANSPOSE(F12:F15),F3:F6)","v":9.3724592229514556E-2},null,null,"E(y)",{"$f":"MMULT(TRANSPOSE(F20:F23),F3:F6)","v":0.1130366923032104},{"$f":"getformula(F26)","v":"<--  {=MMULT(TRANSPOSE(F20:F23),F3:F6)}"}]
["Var(x)",{"$f":"MMULT(MMULT(TRANSPOSE(F12:F15),A3:D6),F12:F15)","v":8.6239939551885511E-2},null,null,"Var(y)",{"$f":"MMULT(MMULT(TRANSPOSE(F20:F23),A3:D6),F20:F23)","v":0.14139768305843081},{"$f":"getformula(F27)","v":"<--  {=MMULT(MMULT(TRANSPOSE(F20:F23),A3:D6),F20:F23)}"}]
["Sigma(x)",{"$f":"SQRT(B27)","v":0.29366637456795341},null,null,"Sigma(y)",{"$f":"SQRT(F27)","v":0.37602883274880772},{"$f":"getformula(F28)","v":"<--  =SQRT(F27)"}]
[]
["Cov(x,y)",{"$f":"MMULT(MMULT(TRANSPOSE(F12:F15),A3:D6),F20:F23)","v":0.10400981513477467},{"$f":"getformula(B30)","v":"<--  {=MMULT(MMULT(TRANSPOSE(F12:F15),A3:D6),F20:F23)}"}]
["Corr(x,y)",{"$f":"B30/(B28*F28)","v":0.94188733267960922},{"$f":"getformula(B31)","v":"<--  =B30/(B28*F28)"}]
[]
[]
["A single portfolio calculation"]
["Proportion of x",0.3]
["E(rp)",{"$f":"B35*B26+(1-B35)*F26","v":0.10724306228110164},{"$f":"getformula(B36)","v":"<--  =B35*B26+(1-B35)*F26"}]
["sp2",{"$f":"B35^2*B27+(1-B35)^2*F27+2*B35*(1-B35)*B30","v":0.12073058161490614},{"$f":"getformula(B37)","v":"<--  =B35^2*B27+(1-B35)^2*F27+2*B35*(1-B35)*B30"}]
["sp",{"$f":"SQRT(B37)","v":0.34746306510894959},{"$f":"getformula(B38)","v":"<--  =SQRT(B37)"}]
[]
["Data table:  We vary the proportion of x to produce a graph of the frontier"]
["Proportion\nof x","Sigma","Return"]
[null,{"$f":"B38","v":0.34746306510894959},{"$f":"B36","v":0.10724306228110164},{"$f":"getformula(C42)&\", data table header\"","v":"<--  =B36, data table header"}]
[-1.5,0.54562053626684992,0.14200484241375419]
[-1.2,0.50929213134105078,0.13621121239164544]
[-1,0.48558357802391755,0.13234879237690622]
[-0.8,0.46235677440086403,0.12848637236216706]
[-0.6,0.43968807330821968,0.12462395234742792]
[-0.4,0.41766835668012781,0.12076153233268871]
[-0.2,0.39640578946519428,0.11689911231794957]
[0,0.37602883274880772,0.1130366923032104]
[0.25,0.35203675037970206,0.10820866728478644]
[0.5,0.33002168598437048,0.10338064226636248]
[0.75,0.31040457771210145,9.8552617247938512E-2]
[0.8,0.30681038033070501,9.7587012244253715E-2]
[1,0.29366637456795341,9.3724592229514556E-2]
[1.2,0.28269543507520695,8.9862172214775396E-2]
[1.4,0.27415856335357625,8.5999752200036222E-2]
[1.6,0.26828821460342733,8.2137332185297035E-2]
[1.8,0.26526148178232306,7.8274912170557889E-2]
[2,0.26517575440992747,7.4412492155818716E-2]
[2.2000000000000002,0.26803385443569622,7.0550072141079528E-2]
[2.4,0.27374358971069834,6.6687652126340397E-2]
[2.6,0.2821318806498192,6.2825232111601209E-2]
[2.8,0.29296874003478501,5.8962812096862049E-2]
[3,0.30599412834790946,5.5100392082122862E-2]

{"sheet":"Page 233","index":1,"n_rows":17,"n_cols":6,"rows_kept":17,"cut":false,"hidden":false,"merged":[[1,0,1,3],[0,0,0,6],[10,1,13,5]]}
["FINDING ENVELOPE PORTFOLIOS IN ONE STEP"]
["Variance-covariance, S",null,null,null,null,"Mean returns E(r)"]
[0.1,0.01,0.03,0.05,null,0.06]
[{"$f":"B3","v":0.01},0.3,0.06,-0.04,null,0.08]
[{"$f":"C3","v":0.03},{"$f":"C4","v":0.06},0.4,0.02,null,0.1]
[{"$f":"D3","v":0.05},{"$f":"D4","v":-0.04},{"$f":"D5","v":0.02},0.5,null,0.15]
[]
["Constant",0.04]
[]
["Envelope portfolio"]
[{"$f":"MMULT(MINVERSE(A3:D6),F3:F6-B8)/SUM(MMULT(MINVERSE(A3:D6),F3:F6-B8))","v":7.8206024587775952E-2},{"$f":"getformula(A11)","v":"<--  {=MMULT(MINVERSE(A3:D6),F3:F6-B8)/SUM(MMULT(MINVERSE(A3:D6),F3:F6-B8))}"}]
[0.2683971333682923]
[0.22273932296218615]
[0.4306575190817456]
[]
["Portfolio mean",{"$f":"SUMPRODUCT(A11:A14,F3:F6)","v":0.1130366923032104},{"$f":"getformula(B16)","v":"<--  =SUMPRODUCT(A11:A14,F3:F6)"}]
["Portfolio sigma",{"$f":"SQRT(MMULT(MMULT(TRANSPOSE(A11:A14),A3:D6),A11:A14))","v":0.37602883274880772},{"$f":"getformula(B17)","v":"<--  {=SQRT(MMULT(MMULT(TRANSPOSE(A11:A14),A3:D6),A11:A14))}"}]

{"sheet":"Page 235","index":2,"n_rows":17,"n_cols":6,"rows_kept":17,"cut":false,"hidden":false,"merged":[[0,0,0,6],[1,0,1,3],[10,1,13,5]]}
["FINDING ENVELOPE PORTFOLIOS IN ONE STEP"]
["Variance-covariance, S",null,null,null,null,"Mean returns E(r)"]
[0.1,0.01,0.03,0.05,null,0.06]
[{"$f":"B3","v":0.01},0.3,0.06,-0.04,null,0.08]
[{"$f":"C3","v":0.03},{"$f":"C4","v":0.06},0.4,0.02,null,0.1]
[{"$f":"D3","v":0.05},{"$f":"D4","v":-0.04},{"$f":"D5","v":0.02},0.5,null,0.15]
[]
["Constant",0.04]
[]
["Envelope portfolio"]
[{"$f":"MMULT(MINVERSE(varcov),means-B8)/SUM(MMULT(MINVERSE(varcov),means-B8))","v":7.8206024587775952E-2},{"$f":"getformula(A11)","v":"<--  {=MMULT(MINVERSE(varcov),means-B8)/SUM(MMULT(MINVERSE(varcov),means-B8))}"}]
[0.2683971333682923]
[0.22273932296218615]
[0.4306575190817456]
[]
["Portfolio mean",{"$f":"SUMPRODUCT(portx,means)","v":0.1130366923032104},{"$f":"getformula(B16)","v":"<--  =SUMPRODUCT(portx,means)"}]
["Portfolio sigma",{"$f":"SQRT(MMULT(MMULT(TRANSPOSE(portx),varcov),portx))","v":0.37602883274880772},{"$f":"getformula(B17)","v":"<--  {=SQRT(MMULT(MMULT(TRANSPOSE(portx),varcov),portx))}"}]

{"sheet":"Page 236","index":3,"n_rows":26,"n_cols":6,"rows_kept":26,"cut":false,"hidden":false,"merged":[[16,4,17,6],[0,0,0,6],[1,0,1,3],[10,4,13,6]]}
["CALCULATING THE ENVELOPE\nAll constants c lead to the same envelope"]
["Variance-covariance, S",null,null,null,null,"Mean returns E(r)"]
[0.1,0.01,0.03,0.05,null,0.06]
[{"$f":"B3","v":0.01},0.3,0.06,-0.04,null,0.08]
[{"$f":"C3","v":0.03},{"$f":"C4","v":0.06},0.4,0.02,null,0.1]
[{"$f":"D3","v":0.05},{"$f":"D4","v":-0.04},{"$f":"D5","v":0.02},0.5,null,0.15]
[]
["Constant",0,0.04,0.06]
[]
[null,"Portfolio\nx","Portfolio\ny","Portfolio\nz"]
[null,{"$f":"MMULT(MINVERSE(varcov),means-B8)/SUM(MMULT(MINVERSE(varcov),means-B8))","v":0.35527263160809713},{"$f":"MMULT(MINVERSE(varcov),means-C8)/SUM(MMULT(MINVERSE(varcov),means-C8))","v":7.8206024587775952E-2},{"$f":"MMULT(MINVERSE(varcov),means-D8)/SUM(MMULT(MINVERSE(varcov),means-D8))","v":-0.57240863571335665},{"$f":"getformula(D11)","v":"<--  {=MMULT(MINVERSE(varcov),means-D8)/SUM(MMULT(MINVERSE(varcov),means-D8))}"}]
[null,0.23622995763063889,0.2683971333682923,0.34393289114319164]
[null,0.15529547783223951,0.22273932296218615,0.38111262843022503]
[null,0.25320193292902443,0.4306575190817456,0.84736311613994009]
[]
["Portfolio mean",{"$f":"SUMPRODUCT(portfoliox,means)","v":9.3724592229514556E-2},{"$f":"SUMPRODUCT(portfolioy,means)","v":0.1130366923032104},{"$f":"SUMPRODUCT(portfolioz,means)","v":0.15838584341266745},{"$f":"getformula(D16)","v":"<--  =SUMPRODUCT(portfolioz,means)"}]
["Portfolio sigma",{"$f":"SQRT(MMULT(MMULT(TRANSPOSE(portfoliox),varcov),portfoliox))","v":0.29366637456795341},{"$f":"SQRT(MMULT(MMULT(TRANSPOSE(portfolioy),varcov),portfolioy))","v":0.37602883274880772},{"$f":"SQRT(MMULT(MMULT(TRANSPOSE(portfolioz),varcov),portfolioz))","v":0.65204651123986135},{"$f":"getformula(D17)","v":"<--  {=SQRT(MMULT(MMULT(TRANSPOSE(portfolioz),varcov),portfolioz))}"}]
[]
[]
["Show:  portfolio z is a linear proportion of portfolio x and portfolio y"]
["Proportion",{"$f":"(D11-C11)/(B11-C11)","v":-2.3482247366367539},{"$f":"getformula(B21)","v":"<--  =(D11-C11)/(B11-C11)"}]
["Check"]
["z1",{"$f":"$B$21*B11+(1-$B$21)*C11","v":-0.57240863571335676},{"$f":"getformula(B23)","v":"<--  =$B$21*B11+(1-$B$21)*C11"}]
["z2",{"$f":"$B$21*B12+(1-$B$21)*C12","v":0.3439328911431917},{"$f":"getformula(B24)","v":"<--  =$B$21*B12+(1-$B$21)*C12"}]
["z3",0.38111262843022509,{"$f":"getformula(B25)","v":"<--  =$B$21*B13+(1-$B$21)*C13"}]
["z4",0.84736311613994009,{"$f":"getformula(B26)","v":"<--  =$B$21*B14+(1-$B$21)*C14"}]

{"sheet":"Page 237","index":4,"n_rows":44,"n_cols":6,"rows_kept":44,"cut":false,"hidden":false,"merged":[[0,0,0,6],[1,0,1,3]]}
["SOME c's CAN LEAD TO INEFFICIENT PORTFOLIOS"]
["Variance-covariance, S",null,null,null,null,"Mean returns E(r)"]
[0.1,0.01,0.03,0.05,null,0.06]
[{"$f":"B3","v":0.01},0.3,0.06,-0.04,null,0.08]
[{"$f":"C3","v":0.03},{"$f":"C4","v":0.06},0.4,0.02,null,0.1]
[{"$f":"D3","v":0.05},{"$f":"D4","v":-0.04},{"$f":"D5","v":0.02},0.5,null,0.15]
[]
["Constant",0.11,0.04]
[]
[null,"Portfolio\nx","Portfolio\ny"]
[null,{"$f":"MMULT(MINVERSE(varcov),means-B8)/SUM(MMULT(MINVERSE(varcov),means-B8))","v":1.172793198299575},{"$f":"MMULT(MINVERSE(varcov),means-C8)/SUM(MMULT(MINVERSE(varcov),means-C8))","v":7.8206024587775952E-2}]
[null,0.14131657914478624,0.2683971333682923]
[null,-4.3706239059764972E-2,0.22273932296218615]
[null,-0.27040353838459619,0.4306575190817456]
[]
["Portfolio mean",{"$f":"SUMPRODUCT(portfoliox,means)","v":3.6741763565891476E-2},{"$f":"SUMPRODUCT(portfolioy,means)","v":0.1130366923032104}]
["Portfolio sigma",{"$f":"SQRT(MMULT(MMULT(TRANSPOSE(portfoliox),varcov),portfoliox))","v":0.39009441414571544},{"$f":"SQRT(MMULT(MMULT(TRANSPOSE(portfolioy),varcov),portfolioy))","v":0.37602883274880772}]
["Cov(x,y)",{"$f":"MMULT(MMULT(TRANSPOSE(portfoliox),varcov),portfolioy)","v":-6.3078853670822527E-3}]
[]
["Single portfolio calculation"]
["Proportion of x",0.6]
["Mean",{"$f":"B21*B16+(1-B21)*C16","v":6.7259735060819045E-2},{"$f":"getformula(B22)","v":"<--  =B21*B16+(1-B21)*C16"}]
["Sigma",{"$f":"SQRT(B21^2*B17^2+(1-B21)^2*C17^2+2*B21*(1-B21)*B18)","v":0.27272396120311371},{"$f":"getformula(B23)","v":"<--  =SQRT(B21^2*B17^2+(1-B21)^2*C17^2+2*B21*(1-B21)*B18)"}]
[]
[]
["Data table to determine the frontier"]
["Proportion of x","Sigma","Mean"]
[null,{"$f":"B23","v":0.27272396120311371},{"$f":"B22","v":6.7259735060819045E-2}]
[-1,0.861972114194967,0.18933162104052931]
[-0.8,0.75742084749037231,0.17407263529306552]
[-0.6,0.65488298438388892,0.15881364954560176]
[-0.4,0.55547463913061479,0.14355466379813794]
[-0.2,0.46122380105350957,0.12829567805067418]
[0,0.37602883274880772,0.1130366923032104]
[0.2,0.30751087772278396,9.7777706555746621E-2]
[0.4,0.26874367943537919,8.2518720808282833E-2]
[0.6,0.27272396120311371,6.7259735060819045E-2]
[0.8,0.31784984072890748,5.2000749313355257E-2]
[1,0.39009441414571544,3.6741763565891476E-2]
[1.2,0.47729838791180595,2.1482777818427699E-2]
[1.4,0.57266815759037215,6.2237920709639141E-3]
[1.6,0.67273981210562883,-9.0351936764998877E-3]
[1.8,0.77569572599383629,-2.4294179423963669E-2]
[2,0.88052474827089078,-3.9553165171427443E-2]

{"sheet":"Page 241","index":5,"n_rows":47,"n_cols":14,"rows_kept":47,"cut":false,"hidden":false,"merged":[[0,0,0,6],[1,0,1,3]]}
["IF c = rf  AND THE OPTIMIZING PORTFOLIO IS EFFICIENT, THEN THE ENVELOPE PORTFOLIO IS OPTIMAL\nThe portfolio x determined by the constant c = 4% is optimal"]
["Variance-covariance matrix",null,null,null,null,"Expected returns"]
[0.4,0.03,0.02,0,null,0.06]
[{"$f":"B3","v":0.03},0.2,1E-3,-0.06,null,0.05]
[{"$f":"C3","v":0.02},{"$f":"C4","v":1E-3},0.3,0.03,null,7.0000000000000007E-2]
[{"$f":"D3","v":0},{"$f":"D4","v":-0.06},{"$f":"D5","v":0.03},0.1,null,0.08]
[]
["Constant",0.04]
[]
[{"$f":"\"Computing an envelope portfolio with constant = \"&TEXT(B8,\"0.00\")","v":"Computing an envelope portfolio with constant = 0.04"}]
["z",null,null,null,null,"Envelope portfolio\nx"]
[{"$f":"MMULT(MINVERSE(A3:D6),F3:F6-B8)","v":3.2970145650599497E-2},{"$f":"getformula(A12)","v":"<--  {=MMULT(MINVERSE(A3:D6),F3:F6-B8)}"},null,null,null,{"$f":"A12:A15/SUM(A12:A15)","v":4.2317528124238286E-2},{"$f":"getformula(F12)","v":"<--  {=A12:A15/SUM(A12:A15)}"}]
[0.19586384485394703,null,null,null,null,0.25139330141168598]
[4.6801319707089437E-2,null,null,null,null,6.0069985251042193E-2]
[0.50347791100024131,null,null,null,null,0.64621918521303356]
[null,null,null,null,"Sum",{"$f":"SUM(F12:F15)","v":1},{"$f":"getformula(F16)","v":"<--  =SUM(F12:F15)"}]
[]
[]
["E(rx)",{"$f":"SUMPRODUCT(F12:F15,F3:F6)","v":7.1011150542654236E-2},null,null,null,null,null,null,null,null,null,"Sigma","Return"]
["sx",{"$f":"SQRT(MMULT(MMULT(TRANSPOSE(F12:F15),A3:D6),F12:F15))","v":0.19950723629490036},null,null,null,null,null,null,null,null,null,0.21993805158731042,7.3377156127424029E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.2164321629484347,7.3039155329599786E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.21310770581613184,7.2701154531775516E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.20997329805252407,7.2363153733951274E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.2070375714913964,7.202515293612699E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.20430909088980331,7.1687152138302762E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.2017962629201607,7.1349151340478506E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19950723629490036,7.1011150542654236E-2,{"$f":"B19","v":7.1011150542654236E-2}]
[null,null,null,null,null,null,null,null,null,null,null,0.19844909819197518,7.0842150143742122E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19744979472432397,7.067314974482998E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19651022346035232,7.0504149345917852E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19563124504150151,7.0335148947005724E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19481367963474455,7.0166148548093596E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19405830343858063,6.9997148149181468E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19336584527362127,6.982814775026934E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19273698328964869,6.9659147351357212E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19217234182133772,6.9490146952445084E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19167248842463094,6.9321146553532956E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19086911590759811,6.89831457557087E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19033017231371338,6.8645144957884457E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.1900579071644993,6.8307144160060174E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19005346657110175,6.7969143362235931E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19031686928067681,6.7631142564411661E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19084700628269075,6.7293141766587419E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19164166399427479,6.6955140968763163E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19269756990446321,6.6617140170938921E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19401045851406651,6.6279139373114665E-2]
[null,null,null,null,null,null,null,null,null,null,null,0.19557515454615695,6.5941138575290409E-2]

{"sheet":"Page 241-244","index":6,"n_rows":43,"n_cols":8,"rows_kept":43,"cut":false,"hidden":false,"merged":[[0,0,0,5],[15,5,17,6],[23,3,25,6],[40,0,40,4]]}
["ILLUSTRATING PROPOSITIONS 3-5"]
["Dates","Asset 1","Asset 2","Asset 3","Asset 4",null,"Efficient portfolio w"]
[1,-6.6255605887467039E-2,-2.4904679180075117E-2,-4.269514666027549E-2,0.1172,null,{"$f":"MMULT(B3:E9,B23:B26)","v":-1.8222825757319806E-2},{"$f":"getformula(G3)","v":"<--  {=MMULT(B3:E9,B23:B26)}"}]
[2,8.5300000000000001E-2,2.4400000000000002E-2,-3.1543618535442949E-2,-8.3299999999999999E-2,null,9.9087550844717976E-3]
[3,1.7937188329115412E-2,4.4606698040520781E-2,1.9195027491194547E-2,0.19180375767263116,null,5.4706257461176799E-2]
[4,7.2484350433373146E-2,0.17896441688028816,-6.5257342301863905E-2,-7.4061437799726737E-2,null,3.6503513841059508E-2]
[5,7.545292033896033E-3,-8.2229955942229177E-2,-1.757117827535767E-2,-1.443594623294148E-2,null,-2.5107488636137654E-2]
[6,-1.5696373666933345E-2,8.3192591965755795E-3,0.1288,-5.9221112814114754E-2,null,2.1615229585562404E-2]
[7,-2.0984886675167722E-2,5.1431364020888035E-2,0.1340676294208841,-4.5686250612152913E-3,null,4.1386712004944544E-2]
[]
["Mean",{"$f":"AVERAGE(B3:B9)","v":1.1475709223830927E-2},{"$f":"AVERAGE(C3:C9)","v":2.8655300430852609E-2},{"$f":"AVERAGE(D3:D9)","v":1.7856481591305519E-2},{"$f":"AVERAGE(E3:E9)","v":1.0488090823518989E-2},{"$f":"getformula(E11)","v":"<--  =AVERAGE(E3:E9)"},{"$f":"AVERAGE(G3:G9)","v":1.7255736226251088E-2}]
[]
["Variance-covariance matrix"]
[null,"Asset 1","Asset 2","Asset 3","Asset 4"]
["Asset 1",{"$f":"MMULT(TRANSPOSE(B3:E9-B11:E11),B3:E9-B11:E11)/7","v":2.4376355465752187E-3},1.910205859138172E-3,-1.5213980341143869E-3,-2.3892178138495862E-3]
["Asset 2",1.910205859138172E-3,5.5659857568364493E-3,-6.7128169135941441E-4,-1.6134761521794001E-3,{"$f":"\"cells \"&CHAR(COLUMN(B15)+64)&ROW(B15)&\":\"&CHAR(COLUMN(E18)+64)&ROW(E18)&\" contain the formula \"&ggetformula(B15)","v":"cells B15:E18 contain the formula  {=MMULT(TRANSPOSE(B3:E9-B11:E11),B3:E9-B11:E11)/7}"}]
["Asset 3",-1.5213980341143869E-3,-6.7128169135941441E-4,5.7264547491596863E-3,-4.5129243537122291E-4]
["Asset 4",-2.3892178138495862E-3,-1.6134761521794001E-3,-4.5129243537122291E-4,9.4164166805685689E-3]
[]
["Finding an efficient portfolio w"]
["Constant",5.0000000000000001E-3]
[]
["Asset 1",{"$f":"MMULT(MINVERSE(B15:E18),TRANSPOSE(B11:E11)-B21)/SUM(MMULT(MINVERSE(B15:E18),TRANSPOSE(B11:E11)-B21))","v":0.31285942027940788}]
["Asset 2",0.24641147896469962,null,{"$f":"{\"Cells B23:B26 contain the formula {=MMULT(MINVERSE(B15:E18),TRANSPOSE(B11:E11)-B21)/SUM(MMULT(MINVERSE(B15:E18),TRANSPOSE(B11:E11)-B21))}\"}","v":"Cells B23:B26 contain the formula {=MMULT(MINVERSE(B15:E18),TRANSPOSE(B11:E11)-B21)/SUM(MMULT(MINVERSE(B15:E18),TRANSPOSE(B11:E11)-B21))}"}]
["Asset 3",0.26899369984741706]
["Asset 4",0.17173540090847547]
[]
[]
["Implementing propositions 3-5—finding the SML"]
["Step 1:  Regress each asset's returns on those of the efficient portfolio w"]
[null,"Asset 1","Asset 2","Asset 3","Asset 4"]
["Alpha",{"$f":"INTERCEPT(B3:B9,$G$3:$G$9)","v":2.3580904874729788E-3},{"$f":"INTERCEPT(C3:C9,$G$3:$G$9)","v":-4.6507055937546719E-3},{"$f":"INTERCEPT(D3:D9,$G$3:$G$9)","v":-2.4508742435548972E-4},{"$f":"INTERCEPT(E3:E9,$G$3:$G$9)","v":2.7610113655335599E-3},{"$f":"getformula(E32)","v":"<--  =INTERCEPT(E3:E9,$G$3:$G$9)"}]
["Beta",{"$f":"SLOPE(B3:B9,$G$3:$G$9)","v":0.52838190250540273},{"$f":"SLOPE(C3:C9,$G$3:$G$9)","v":1.9301411187509332},{"$f":"SLOPE(D3:D9,$G$3:$G$9)","v":1.049017484871098},{"$f":"SLOPE(E3:E9,$G$3:$G$9)","v":0.44779772689328967},{"$f":"getformula(E33)","v":"<--  =SLOPE(E3:E9,$G$3:$G$9)"}]
["R-squared",{"$f":"RSQ(B3:B9,$G$3:$G$9)","v":8.9689683964278413E-2},{"$f":"RSQ(C3:C9,$G$3:$G$9)","v":0.52414498455428704},{"$f":"RSQ(D3:D9,$G$3:$G$9)","v":0.15048562636313398},{"$f":"RSQ(E3:E9,$G$3:$G$9)","v":1.6676061990681626E-2},{"$f":"getformula(E34)","v":"<--  =RSQ(E3:E9,$G$3:$G$9)"}]
[]
["Step 2:  Regress the asset mean returns on their betas"]
["Intercept",{"$f":"INTERCEPT(B11:E11,B33:E33)","v":5.0000000000000062E-3},{"$f":"getformula(B37)","v":"<--  =INTERCEPT(B11:E11,B33:E33)"}]
["Slope",{"$f":"SLOPE(B11:E11,B33:E33)","v":1.2255736226251082E-2},{"$f":"getformula(B38)","v":"<--  =SLOPE(B11:E11,B33:E33)"}]
["R-squared",{"$f":"RSQ(B11:E11,B33:E33)","v":0.99999999999999978},{"$f":"getformula(B39)","v":"<--  =RSQ(B11:E11,B33:E33)"}]
[]
["Check Propositions 3 & 4:  Step 2 coefficients should be:\nIntercept = c, Slope = E(rw) - c"]
["Intercept = c ?",{"$f":"IF(ROUND(B37-B21,10)=0,\"yes\",\"no\")","v":"yes"},{"$f":"getformula(B42)","v":"<--  =IF(ROUND(B37-B21,10)=0,\"yes\",\"no\")"}]
["Slope = E(rw) - c ?",{"$f":"IF(B38=G11-B21,\"yes\",\"no\")","v":"yes"},{"$f":"getformula(B43)","v":"<--  =IF(B38=G11-B21,\"yes\",\"no\")"}]