{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["PLEASE READ","22.A.9","22.A.21","22.A.23","22.A.28","22.A.29"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":1,"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":"PLEASE READ","index":0,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["The content in this spreadsheet is for informational purposes only. You should not construe any such information or other material as legal, tax, investment, financial, or other advice. You alone assume the sole responsibility of evaluating the merits and risks associated with the use of any information or other Content on the Site before making any decisions based on such information or other Content. You agree not to hold CFA Institute or its subsidiaries liable for any possible claim arising from any decision you make based on information or content made available to you through this spreadsheet."]

{"sheet":"22.A.9","index":1,"n_rows":7,"n_cols":4,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
[null,"External Cash Flows"]
["Dates","& Ending Valuation",null,"Explanation"]
["2020-09-01",-1000000,null,"Contribution"]
["2020-09-10",-75000,null,"Contribution"]
["2020-09-30",1100000,null,"Portfolio Value as of 30 September 2020"]
[null,{"$f":"XIRR(B3:B5,A3:A5,0.1)","v":0.34411035180091853},"=XIRR(B3:B5,A3:A5,0.1)","Annualized return using XIRR"]
[null,{"$f":"(1+B6)^((A5-A3)/365)-1","v":2.377476107721721E-2},"=(1+B6)^((A5-A3)/365)-1","Non-annualized return"]

{"sheet":"22.A.21","index":2,"n_rows":37,"n_cols":6,"rows_kept":37,"cut":false,"hidden":false,"merged":[]}
["Modified Dietz method:"]
[null,null,null,"Day of CF/",null,"Weighted"]
["Date","Value or Cash Flow","Cash Flow","Valuation","Weight","CF"]
["2020-05-31","Beginning Value (BV)",100000]
["2020-06-06","Cash Flow (CF)",-2000,{"$f":"A5-$A$4","v":6},{"$f":"(30-D5)/30","v":0.8},{"$f":"C5*E5","v":-1600}]
["2020-06-11","Cash Flow (CF)",20000,{"$f":"A6-$A$4","v":11},{"$f":"(30-D6)/30","v":0.6333333333333333},{"$f":"C6*E6","v":12666.666666666666}]
["2020-06-30","Ending Value (EV)",135000]
["Totals",null,null,null,null,{"$f":"SUM(F4:F7)","v":11066.666666666666}]
[]
["RModified Dietz"]
["Numerator",null,{"$f":"(C7-C4-C5-C6)","v":17000},"=(C7-C4-C5-C6)"]
["Denominator",null,{"$f":"C4+F8","v":111066.66666666667},"=C4+F8"]
["Cumulative Return",null,{"$f":"C11/C12","v":0.15306122448979592},"=C11/C12"]
[]
[]
["Modified Dietz method with sub-period returns due to large cash flows:"]
[]
[null,null,null,"Day of CF/",null,"Weighted"]
["Date","Value or Cash Flow","Cash Flow","Valuation","Weight","CF"]
["2020-05-31","Beginning Value (BV)",100000]
["2020-06-06","Cash Flow (CF)",-2000,{"$f":"A21-$A$4","v":6},{"$f":"(11-D21)/11","v":0.45454545454545453},{"$f":"C21*E21","v":-909.09090909090901}]
["2020-06-11","Cash Flow (CF)",20000,{"$f":"A22-$A$4","v":11},{"$f":"(11-D22)/11","v":0},{"$f":"C22*E22","v":0}]
["2020-06-11","Ending Value (EV)",125000]
["2020-06-30","Ending Value (EV)",135000]
["Totals",null,null,null,null,{"$f":"SUM(F20:F24)","v":-909.09090909090901}]
[]
["RModified Dietz (sub-period 1)"]
["Numerator",null,{"$f":"(C23-C20-C21-C22)","v":7000},"=(C23-C20-C21-C22)"]
["Denominator",null,{"$f":"C20+F21+F22","v":99090.909090909088},"=C20+F21+F22"]
["Cumulative Return",null,{"$f":"C28/C29","v":7.0642201834862389E-2},"=C28/C29"]
[]
["RModified Dietz (sub-period 2)"]
["Numerator",null,{"$f":"(C24-C23)","v":10000},"=(C24-C23)"]
["Denominator",null,{"$f":"C23","v":125000},"=C23"]
["Cumulative Return",null,{"$f":"C33/C34","v":0.08},"=C33/C34"]
[]
["RModified Dietz (sub-period 1 & 2 linked)",null,{"$f":"(1+C30)*(1+C35)-1","v":0.15629357798165144},"=(1+C30)*(1+C35)-1"]

{"sheet":"22.A.23","index":3,"n_rows":25,"n_cols":5,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["Modified Dietz method:"]
[]
[null,"Terminal",null,"Day of CF/","Weighted"]
["Date","Value","Cash Flow","Valuation","CF"]
["2016-12-31",null,2000000,{"$f":"A5-$A$5","v":0},{"$f":"C5*($D$18-D5)/$D$18","v":2000000}]
["2017-01-08",null,200000,{"$f":"A6-$A$5","v":8},{"$f":"C6*($D$18-D6)/$D$18","v":198904.85968514715}]
["2017-12-24",null,-50000,358,{"$f":"C7*($D$18-D7)/$D$18","v":-37748.117727583849}]
["2018-02-20",null,-200000,416,-143052.70362765228]
["2018-03-06",null,150000,430,105852.15605749487]
["2018-12-11",null,-20000,710,-10280.62970568104]
["2019-06-25",null,100000,906,37987.679671457903]
["2019-07-03",null,30000,914,11232.032854209445]
["2019-08-14",null,-50000,956,-17282.683093771389]
["2020-03-21",null,-200000,1176,-39014.373716632443]
["2020-06-04",null,80000,1251,11498.973305954825]
["2020-11-22",null,-50000,1422,-1334.7022587268993]
["2020-12-03",null,150000,1433,2874.7433264887063]
["2020-12-31",2300000,null,1461,0]
["Totals",null,{"$f":"SUM(C5:C18)","v":2140000},null,{"$f":"SUM(E5:E18)","v":2119637.2347707055}]
[]
["RModified Dietz"]
["Numerator",{"$f":"B18-C19","v":160000},"=B18-C19"]
["Denominator",{"$f":"E19","v":2119637.2347707055},"=E19"]
["Cumulative Return",{"$f":"B22/B23","v":7.5484614713945714E-2},"=B22/B23"]
["Annualized Return",{"$f":"(1+B24)^(1/4)-1","v":1.8359338986573936E-2},"=(1+B24)^(1/4)-1"]

{"sheet":"22.A.28","index":4,"n_rows":39,"n_cols":7,"rows_kept":39,"cut":false,"hidden":false,"merged":[]}
["Beginning Assets Weighting method:"]
[]
["Portfolio","BMV","Portfolio Weight","Portfolio Return","Weighted Return"]
["A",450000,{"$f":"B4/$B$7","v":0.17077798861480076},0.12,{"$f":"C4*D4","v":2.049335863377609E-2}]
["B",785000,{"$f":"B5/$B$7","v":0.29791271347248577},0.14000000000000001,{"$f":"C5*D5","v":4.170777988614801E-2}]
["C",1400000,0.53130929791271342,0.11,5.8444022770398479E-2]
["Total",{"$f":"SUM(B4:B6)","v":2635000},{"$f":"SUM(C4:C6)","v":1},null,{"$f":"SUM(E4:E6)","v":0.12064516129032257}]
[]
["RBMV"]
["Numerator",{"$f":"(B4*D4)+(B5*D5)+(B6*D6)","v":317900},"=(B4*D4)+(B5*D5)+(B6*D6)"]
["Denominator",{"$f":"(B4+B5+B6)","v":2635000},"=(B4+B5+B6)"]
["Return",{"$f":"B10/B11","v":0.12064516129032259},"=B10/B11"]
[]
["Beginning Assets Plus Weighted External Cash Flow method:"]
[]
[null,null,"Weighted","BMV plus","BMV plus","Portfolio","Weighted"]
["Portfolio","BMV","Cash Flows","Wtd CFs ($)","Wtd CFs (%)","Return","Return"]
["A",450000,75000,{"$f":"B18+C18","v":525000},{"$f":"D18/$D$21","v":0.18953068592057762},0.12,{"$f":"E18*F18","v":2.2743682310469315E-2}]
["B",785000,120000,{"$f":"B19+C19","v":905000},{"$f":"D19/$D$21","v":0.3267148014440433},0.14000000000000001,{"$f":"E19*F19","v":4.5740072202166068E-2}]
["C",1400000,-60000,1340000,0.48375451263537905,0.11,5.3212996389891694E-2]
["Total",{"$f":"SUM(B18:B20)","v":2635000},{"$f":"SUM(C18:C20)","v":135000},{"$f":"SUM(D18:D20)","v":2770000},{"$f":"SUM(E18:E20)","v":1},null,{"$f":"SUM(G18:G20)","v":0.12169675090252707}]
[]
["RBMV + CF"]
["Numerator",{"$f":"((B18+C18)*F18)+((B19+C19)*F19)+((B20+C20)*F20)","v":337100},"=((B18+C18)*F18)+((B19+C19)*F19)+((B20+C20)*F20)"]
["Denominator",{"$f":"(B18+C18+B19+C19+B20+C20)","v":2770000},"=(B18+C18+B19+C19+B20+C20)"]
["Return",{"$f":"B24/B25","v":0.12169675090252707},"=B24/B25"]
[]
["Aggregate Return method (using Modified Dietz method):"]
[]
["Portfolio","BMV","EMV","Cash Flows","Weighted CFs","Portfolio Return"]
["A",450000,665000,150000,{"$f":"C18","v":75000},0.12]
["B",785000,1140000,240000,{"$f":"C19","v":120000},0.14000000000000001]
["C",1400000,1440000,-120000,-60000,0.11]
["Total",{"$f":"SUM(B31:B33)","v":2635000},{"$f":"SUM(C31:C33)","v":3245000},{"$f":"SUM(D31:D33)","v":270000},{"$f":"SUM(E31:E33)","v":135000}]
[]
["RAggregate"]
["Numerator",{"$f":"(C34-B34-D34)","v":340000},"=(C34-B34-D34)"]
["Denominator",{"$f":"(B34+E34)","v":2770000},"=(B34+E34)"]
["Return",{"$f":"B37/B38","v":0.12274368231046931},"=B37/B38"]

{"sheet":"22.A.29","index":5,"n_rows":19,"n_cols":7,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,null,null,"Combined",null,"Combined"]
["Date","CF or TV","Portfolio 1","Portfolio 2","Portfolio 1 & 2","Portfolio 3","Portfolio 1, 2 & 3"]
["2018-12-31","Cash Flow",-1000000,null,{"$f":"SUM(C4:D4)","v":-1000000},null,{"$f":"C4+D4+F4","v":-1000000}]
["2019-01-15","Cash Flow",-10000,null,{"$f":"SUM(C5:D5)","v":-10000},null,{"$f":"C5+D5+F5","v":-10000}]
["2019-12-31","Terminal Value",1090000]
["2020-02-15","Cash Flow",null,-5000000,-5000000,null,-5000000]
["2020-06-30","Cash Flow",null,-1000000,-1000000,null,-1000000]
["2020-12-31","Terminal Value",1100000,6500000,7600000]
["2021-02-12","Cash Flow",null,null,null,-4000000,-4000000]
["2021-03-15","Cash Flow",500000,null,null,null,500000]
["2021-12-31","Terminal Value",900000,6700000,null,4200000,11800000]
[]
[]
[]
["Year","SI-IRR Calc","Formula"]
[2019,{"$f":"XIRR(C4:C6,A4:A6,0.1)","v":7.9241356253623968E-2},"=XIRR(C4:C6,A4:A6,0.1)"]
[2020,{"$f":"XIRR(E4:E9,A4:A9,0.1)","v":8.4726986289024364E-2},"=XIRR(E4:E9,A4:A9,0.1)"]
[2021,{"$f":"XIRR(G4:G12,A4:A12,0.1)","v":7.3318746685981775E-2},"=XIRR(G4:G12,A4:A12,0.1)"]