{"text_format":"1","workbook":"xlsx","n_sheets":11,"sheets":["PLEASE READ","2.A.12","2.A.24 End of Day","2.A.24 Beg of Day","2.A.24 End of Day LCF","2.A.29","2.A.36 Comp. Calcs BMV","2.A.36 Comp. Calcs BMV+Wtd CF","2.A.36 Comp. Calcs Agg Method","2.A.39","2.A.50"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":4,"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":"2.A.12","index":1,"n_rows":15,"n_cols":6,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["Going from an annualized XIRR to an unannualized SI-IRR"]
[null,null,null,null,"Check calculation"]
[null,"External Cash Flows & ",null,null,"CF's Invested"]
["Dates","Ending Valuation","Explanation",null,"at IRR"]
["2020-09-01",-1000000,"Contribution",null,{"$f":"B5*(1+B11)^((A7-A5)/(A7-A5))","v":-1023774.7610772172},"=B5*(1+B11)^((A7-A5)/(A7-A5))"]
["2020-09-10",-75000,"Contribution",null,{"$f":"B6*(1+B11)^((A7-A6)/(A7-A5))","v":-76225.23879473962},"=B6*(1+B11)^((A7-A6)/(A7-A5))"]
["2020-09-30",1100000,"Portfolio value as of 30 September 2020",null,{"$f":"B7","v":1100000},"=B7"]
[null,{"$f":"XIRR(B5:B7,A5:A7)","v":0.34411035180091853},"Calculated annualized return using XIRR",null,{"$f":"SUM(E5:E7)","v":1.2804311700165272E-4},"=SUM(E5:E7)"]
[]
[]
[null,{"$f":"(1+B8)^((A7-A5)/365)-1","v":2.377476107721721E-2},"Unannualized return"]
[]
[]
[]
[" "]

{"sheet":"2.A.24 End of Day","index":2,"n_rows":14,"n_cols":7,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
["Modified Dietz - End of Day Cash Flow Assumption"]
[]
[null,null,null,"Day Wtg (EOD)",null,"Day Wtd CF"]
["2019-05-31","BMV",100000]
["2019-06-06","CF",-2000,{"$f":"(A7-A5)/(A7-A4)","v":0.8},"=(A7-A5)/(A7-A4)",{"$f":"C5*D5","v":-1600},"=C5*D5"]
["2019-06-11","CF",20000,{"$f":"(A7-A6)/(A7-A4)","v":0.6333333333333333},"=(A7-A6)/(A7-A4)",{"$f":"C6*D6","v":12666.666666666666},"=C6*D6"]
["2019-06-30","EMV",135000]
[]
["Total Cash Flow",null,{"$f":"C5+C6","v":18000},"=C5+C6"]
["Weighted Cash Flow",null,{"$f":"F5+F6","v":11066.666666666666},"=F5+F6"]
[]
["Numerator",null,{"$f":"C7-C4-C5-C6","v":17000},"=C7-C4-C5-C6"]
["Denominator",null,{"$f":"C4+F5+F6","v":111066.66666666667},"=C4+F5+F6"]
["Return",null,{"$f":"C12/C13","v":0.15306122448979592},"=C12/C13"]

{"sheet":"2.A.24 Beg of Day","index":3,"n_rows":14,"n_cols":7,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
["Modified Dietz - Beginning of Day Cash Flow Assumption"]
[]
[null,null,null,"Day Wtg (BOD)",null,"Day Wtd CF"]
["2019-05-31","BMV",100000]
["2019-06-06","CF",-2000,{"$f":"(A7-A5+1)/(A7-A4)","v":0.83333333333333337},"=(A7-A5+1)/(A7-A4)",{"$f":"C5*D5","v":-1666.6666666666667},"=C5*D5"]
["2019-06-11","CF",20000,{"$f":"(A7-A6+1)/(A7-A4)","v":0.66666666666666663},"=(A7-A6+1)/(A7-A4)",{"$f":"C6*D6","v":13333.333333333332},"=C6*D6"]
["2019-06-30","EMV",135000]
[]
["Total Cash Flow",null,{"$f":"C5+C6","v":18000},"=C5+C6"]
["Weighted Cash Flow",null,{"$f":"F5+F6","v":11666.666666666666},"=F5+F6"]
[]
["Numerator",null,{"$f":"C7-C4-C5-C6","v":17000},"=C7-C4-C5-C6"]
["Denominator",null,{"$f":"C4+F5+F6","v":111666.66666666666},"=C4+F5+F6"]
["Return",null,{"$f":"C12/C13","v":0.15223880597014927},"=C12/C13"]

{"sheet":"2.A.24 End of Day LCF","index":4,"n_rows":20,"n_cols":7,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Modified Dietz - End of Day Cash Flow Assumption with  Large Cash Flow"]
[]
[null,null,null,"Day Wtg (EOD)",null,"Day Wtd CF"]
["2019-05-31","BMV",100000]
["2019-06-06","CF",-2000,{"$f":"(A7-A5)/(A7-A4)","v":0.45454545454545453},"=(A7-A5)/(A7-A4)",{"$f":"C5*D5","v":-909.09090909090901},"=C5*D5"]
["2019-06-11","CF",20000,{"$f":"(A7-A6)/(A7-A4)","v":0},"=(A7-A6)/(A7-A4)",{"$f":"C6*D6","v":0},"=C6*D6"]
["2019-06-11","MV",125000]
["2019-06-30","EMV",135000]
[]
["Sub-period 1 Calculation"]
["Numerator",null,{"$f":"C7-C4-C5-C6","v":7000},"=C7-C4-C5-C6"]
["Denominator",null,{"$f":"C4+F5+F6","v":99090.909090909088},"=C4+F5+F6"]
["Return",null,{"$f":"C11/C12","v":7.0642201834862389E-2},"=C11/C12"]
[]
["Sub-period 2 Calculation"]
["Numerator",null,{"$f":"C8-C7","v":10000},"=C8-C7"]
["Denominator",null,{"$f":"C7","v":125000},"=C7"]
["Return",null,{"$f":"C16/C17","v":0.08},"=C16/C17"]
[]
["Total sub-periods 1 & 2",null,{"$f":"(1+C13)*(1+C18)-1","v":0.15629357798165144},"=(1+C13)*(1+C18)-1"]

{"sheet":"2.A.29","index":5,"n_rows":25,"n_cols":16,"rows_kept":25,"cut":false,"hidden":false,"merged":[[3,12,3,15],[3,0,3,7]]}
[]
[]
[]
["SI-Modified Dietz",null,null,null,null,null,null,null,null,null,null,null,"SI-IRR check calculation"]
["Dates","Terminal Value","Cash Flows (CF)","Day of CF/Valuation","Wtd CF","Numerator","Denominator","Return",null,null,null,null,"Dates of Values & CF's","Values & CF's","NPV","Days invested"]
["2016-12-31",null,2000000,{"$f":"A6-$A$6","v":0},{"$f":"C6*($D$19-$D$6)/$D$19","v":2000000},null,null,null,null,null,null,null,"2016-12-31",-2000000,{"$f":"N6*(1+$N$20)^(P6/365)","v":-2150908.3563857372},{"$f":"$A$19-M6","v":1461}]
["2017-01-08",null,200000,8,{"$f":"C7*($D$19-$D$7)/$D$19","v":198904.85968514715},null,null,null,null,null,null,null,"2017-01-08",-200000,-215005.17786638267,1453]
["2017-12-24",null,-50000,358,{"$f":"C8*($D$19-$D$8)/$D$19","v":-37748.117727583849},null,null,null,null,null,null,null,"2017-12-24",50000,52822.713602418269,1103]
["2018-02-20",null,-200000,416,{"$f":"C9*($D$19-$D$9)/$D$19","v":-143052.70362765228},null,null,null,null,null,null,null,"2018-02-20",200000,210681.56569388838,1045]
["2018-03-06",null,150000,430,{"$f":"C10*($D$19-$D$10)/$D$19","v":105852.15605749487},null,null,null,null,null,null,null,"2018-03-06",-150000,-157901.06957170111,1031]
["2018-12-11",null,-20000,710,{"$f":"C11*($D$19-$D$11)/$D$19","v":-10280.62970568104},null,null,null,null,null,null,null,"2018-12-11",20000,20762.002197854468,751]
["2019-06-25",null,100000,906,{"$f":"C12*($D$19-$D$12)/$D$19","v":37987.679671457903},null,null,null,null,null,null,null,"2019-06-25",-100000,-102801.8745738202,555]
["2019-07-03",null,30000,914,{"$f":"C13*($D$19-$D$13)/$D$19","v":11232.032854209445},null,null,null,null,null,null,null,"2019-07-03",-30000,-30828.280426901514,547]
["2019-08-14",null,-50000,956,{"$f":"C14*($D$19-$D$14)/$D$19","v":-17282.683093771389},null,null,null,null,null,null,null,"2019-08-14",50000,51273.134024553838,505]
["2020-03-21",null,-200000,1176,{"$f":"C15*($D$19-$D$15)/$D$19","v":-39014.373716632443},null,null,null,null,null,null,null,"2020-03-21",200000,202858.25662096718,285]
["2020-06-04",null,80000,1251,{"$f":"C16*($D$19-$D$16)/$D$19","v":11498.973305954825},null,null,null,null,null,null,null,"2020-06-04",-80000,-80840.858847436568,210]
["2020-11-22",null,-50000,1422,{"$f":"C17*($D$19-$D$17)/$D$19","v":-1334.7022587268993},null,null,null,null,null,null,null,"2020-11-22",50000,50097.184657792961,39]
["2020-12-03",null,150000,1433,{"$f":"C18*($D$19-$D$18)/$D$19","v":2874.7433264887063},null,null,null,null,null,null,null,"2020-12-03",-150000,-150209.26347196213,28]
["2020-12-31",2300000,null,1461,null,{"$f":"(B19-SUM(C6:C18))","v":160000},{"$f":"SUM(E6:E18)","v":2119637.2347707055},{"$f":"F19/G19","v":7.5484614713945714E-2},"Cumulative","=F19/G19",null,null,"2020-12-31",2300000,{"$f":"N19","v":2300000},0]
["Total",null,{"$f":"SUM(C6:C19)","v":2140000},null,{"$f":"SUM(E6:E19)","v":2119637.2347707055},null,null,{"$f":"(1+H19)^(1/H25)-1","v":1.8346658128064686E-2},"Annualized","=(1+H19)^(1/H25)-1","(Day count used in the Explanation)",null,"XIRR",{"$f":"XIRR(N6:N19,M6:M19,0.1)","v":1.8339458107948313E-2},{"$f":"SUM(O6:O19)","v":-2.4346465244889259E-2}]
[null,null,null,null,null,null,null,{"$f":"(1+H19)^(1/4)-1","v":1.8359338986573936E-2},"Annualized","=(1+H19)^(1/4)-1","(formula using years)"]
[null,null,null,null,null,null,null,{"$f":"RATE(4,,-1,(1+H19))","v":1.8359338986916621E-2},"Annualized","=RATE(4,,-1,(1+H19))","(Excel's Rate formula)"]
[]
[null,null,null,null,null,null,null,{"$f":"A19-A6","v":1461},"Day Count"]
[null,null,null,null,null,null,null,{"$f":"H24/365","v":4.0027397260273974},"Implied # of years"]

{"sheet":"2.A.36 Comp. Calcs BMV","index":6,"n_rows":8,"n_cols":6,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["Beginning Assets Weighting Method"]
[]
[null,null,null,"Portfolio",null,"Weighted Return"]
["Portfolio","BMV","Portfolio Weight","Return","Weighted Return","Formula"]
["A",450000,{"$f":"B5/B8","v":0.17077798861480076},0.12,{"$f":"C5*D5","v":2.049335863377609E-2},"=C5*D5"]
["B",785000,{"$f":"B6/B8","v":0.29791271347248577},0.14000000000000001,{"$f":"C6*D6","v":4.170777988614801E-2},"=C6*D6"]
["C",1400000,{"$f":"B7/B8","v":0.53130929791271342},0.11,{"$f":"C7*D7","v":5.8444022770398479E-2},"=C7*D7"]
["Total",{"$f":"SUM(B5:B7)","v":2635000},{"$f":"SUM(C5:C7)","v":1},null,{"$f":"SUM(E5:E7)","v":0.12064516129032257},"=SUM(E5:E7)"]

{"sheet":"2.A.36 Comp. Calcs BMV+Wtd CF","index":7,"n_rows":8,"n_cols":11,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["Beginning Assets Plus Weighted External Cash Flow method"]
[]
[null,null,"Weighted ","BMV plus","BMV plus","Portfolio",null,null,null,"Return"]
["Portfolio","BMV","Cash Flows","Wtd CFs","Wtd CFs (%)","Return","Weighted Return",null,null,"Formula"]
["A",450000,75000,{"$f":"B5+C5","v":525000},{"$f":"D5/D8","v":0.18953068592057762},0.12,{"$f":"E5*F5","v":2.2743682310469315E-2},null,"Numerator",{"$f":"(D5*F5)+(D6*F6)+(D7*F7)","v":337100},"=(D5*F5)+(D6*F6)+(D7*F7)"]
["B",785000,120000,{"$f":"B6+C6","v":905000},{"$f":"D6/D8","v":0.3267148014440433},0.14000000000000001,{"$f":"E6*F6","v":4.5740072202166068E-2},null,"Denominator",{"$f":"D8","v":2770000},"=D8"]
["C",1400000,-60000,{"$f":"B7+C7","v":1340000},{"$f":"D7/D8","v":0.48375451263537905},0.11,{"$f":"E7*F7","v":5.3212996389891694E-2},null,"Return",{"$f":"J5/J6","v":0.12169675090252707},"=J5/J6"]
["Total",{"$f":"SUM(B5:B7)","v":2635000},{"$f":"SUM(C5:C7)","v":135000},{"$f":"SUM(D5:D7)","v":2770000},{"$f":"SUM(E5:E7)","v":1},null,{"$f":"SUM(G5:G7)","v":0.12169675090252707}]

{"sheet":"2.A.36 Comp. Calcs Agg Method","index":8,"n_rows":8,"n_cols":11,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["Aggregate Return method (using Modified Dietz)"]
[]
[null,null,null,null,null,"Portfolio","BMV plus",null,null,"Return"]
["Portfolio","BMV","EMV","Cash Flows","Wtd CFs","Return","Wtd CFs (%)",null,null,"Formula"]
["A",450000,665000,150000,75000,0.12,{"$f":"E5/E8","v":0.55555555555555558},null,"Numerator",{"$f":"(C8-B8-D8)","v":340000},"=(C8-B8-D8)"]
["B",785000,1140000,240000,120000,0.14000000000000001,{"$f":"E6/E8","v":0.88888888888888884},null,"Denominator",{"$f":"(B8+E8)","v":2770000},"=(B8+E8)"]
["C",1400000,1440000,-120000,-60000,0.11,{"$f":"E7/E8","v":-0.44444444444444442},null,"Return",{"$f":"J5/J6","v":0.12274368231046931},"=J5/J6"]
["Total",{"$f":"SUM(B5:B7)","v":2635000},{"$f":"SUM(C5:C7)","v":3245000},{"$f":"SUM(D5:D7)","v":270000},{"$f":"SUM(E5:E7)","v":135000},null,{"$f":"SUM(G5:G7)","v":1}]

{"sheet":"2.A.39","index":9,"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","Fund 1","Fund 2","Fund 1 & 2","Fund 3","Fund 1, 2 & 3"]
["2018-12-31","CF",-1000000,null,{"$f":"SUM(C4:D4)","v":-1000000},null,{"$f":"C4+D4+F4","v":-1000000}]
["2019-01-15","CF",-10000,null,{"$f":"SUM(C5:D5)","v":-10000},null,{"$f":"C5+D5+F5","v":-10000}]
["2019-12-31","Terminal Value",1090000]
["2020-02-15","CF",null,-5000000,-5000000,null,-5000000]
["2020-06-30","CF",null,-1000000,-1000000,null,-1000000]
["2020-12-31","Terminal Value",1100000,6500000,7600000]
["2021-02-12","CF",null,null,null,-4000000,-4000000]
["2021-03-15","CF",500000,null,null,null,500000]
["2021-12-31","Terminal Value",900000,6700000,null,4200000,11800000]
[]
[]
[]
["Year","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)"]

{"sheet":"2.A.50","index":10,"n_rows":13,"n_cols":4,"rows_kept":13,"cut":false,"hidden":false,"merged":[[6,8,8,8],[6,9,8,9],[5,12,7,16]]}
[null,null,"Net MWR","Net MWR"]
[null,null,"with","without "]
[null,null,"Subscription","Subscription"]
[null,null,"Line of Credit","Line of Credit"]
[]
["Date","Transaction Type","Cash Flow","Cash Flow"]
["2020-01-06","Subscription LOC Drawdown",null,-1000000]
["2020-07-01","Subscription LOC Drawdown",null,-500000]
["2020-10-01","Subscription LOC Drawdown",null,-500000]
["2020-12-15","Capital Call",-2000000]
["2021-10-01","Capital Call",-1000000,-1000000]
["2021-12-31","Ending Fair Value",3500000,3500000]
[null,"SI-MWR",{"$f":"XIRR(C10:C12,A10:A12,0.1)","v":0.21474853157997134},{"$f":"XIRR(D7:D12,A7:A12,0.1)","v":0.13283923268318176}]