{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Info","via Formulas","via Macro"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":81,"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":"Info","index":0,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"via Formulas","index":1,"n_rows":21,"n_cols":16,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Total Investments","Total Distributions","Total Loss",null,"Receiver Assets"]
[null,null,{"$f":"SUM(C15:C1048576)","v":680000},{"$f":"SUM(D15:D1048576)","v":240000},{"$f":"C10-D10","v":440000},null,50000]
[]
[null,null,null,null,null,null,"Total Distributions"]
[null,null,null,null,null,null,{"$f":"SUM(G15:G1048576)","v":50000}]
[]
[null,"Name","Investment","Prior Distributions","Prior % Recovery","Breakpts","Distribution Amounts","Final Recovery",null,"Do Not Use","Rate Differentials (must have at least as many columns as the table has rows; copy formulas right as necessary)"]
[null,"Alan",10000,0,{"$f":"D16/C16","v":0},{"$f":"D16/C16 * SUM(C$15:C16) - SUM(D$15:D16)","v":0},{"$f":"MMULT(Rates, (Recovered > Breakpts) * (Recovered - Breakpts))","v":3661.9718309859213},{"$f":"ROUND((D16+G16)/C16, 6)","v":0.36619699999999999},null,null,{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C16/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J16:relW))","v":1},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C16/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J16:relW))","v":-0.91666666666666663},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C16/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J16:relW))","v":-3.455284552845532E-2},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C16/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J16:relW))","v":-2.0611473720371008E-2},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C16/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J16:relW))","v":-1.0150996066489025E-2},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C16/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J16:relW))","v":-3.3121356650768416E-3}]
[null,"Barb",110000,10000,9.0909090909090912E-2,{"$f":"D17/C17 * SUM(C$15:C17) - SUM(D$15:D17)","v":909.09090909090992},{"$f":"MMULT(Rates, (Recovered > Breakpts) * (Recovered - Breakpts))","v":30281.690140845061},0.36619699999999999,null,null,{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C17/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J17:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C17/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J17:relW))","v":0.91666666666666663},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C17/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J17:relW))","v":-0.38008130081300806},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C17/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J17:relW))","v":-0.22672621092408113},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C17/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J17:relW))","v":-0.11166095673137924},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C17/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J17:relW))","v":-3.6433492315845251E-2}]
[null,"Cain",85000,20000,0.23529411764705882,{"$f":"D18/C18 * SUM(C$15:C18) - SUM(D$15:D18)","v":18235.294117647056},{"$f":"MMULT(Rates, (Recovered > Breakpts) * (Recovered - Breakpts))","v":11126.760563380281},0.36619699999999999,null,null,{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C18/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J18:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C18/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J18:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C18/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J18:relW))","v":0.41463414634146339},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C18/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J18:relW))","v":-0.17519752662315355},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C18/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J18:relW))","v":-8.6283466565156702E-2},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C18/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J18:relW))","v":-2.8153153153153143E-2}]
[null,"Dana",150000,50000,0.33333333333333331,{"$f":"D19/C19 * SUM(C$15:C19) - SUM(D$15:D19)","v":38333.333333333328},{"$f":"MMULT(Rates, (Recovered > Breakpts) * (Recovered - Breakpts))","v":4929.5774647887347},0.36619699999999999,null,null,{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C19/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J19:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C19/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J19:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C19/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J19:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C19/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J19:relW))","v":0.42253521126760563},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C19/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J19:relW))","v":-0.15226494099733534},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C19/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J19:relW))","v":-4.9682034976152645E-2}]
[null,"Eric",200000,80000,0.4,{"$f":"D20/C20 * SUM(C$15:C20) - SUM(D$15:D20)","v":62000},{"$f":"MMULT(Rates, (Recovered > Breakpts) * (Recovered - Breakpts))","v":0},0.4,null,null,{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C20/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J20:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C20/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J20:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C20/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J20:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C20/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J20:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C20/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J20:relW))","v":0.36036036036036034},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C20/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J20:relW))","v":-6.6242713301536804E-2}]
[null,"Fran",125000,80000,0.64,{"$f":"D21/C21 * SUM(C$15:C21) - SUM(D$15:D21)","v":195200},{"$f":"MMULT(Rates, (Recovered > Breakpts) * (Recovered - Breakpts))","v":0},0.64,null,null,{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C21/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J21:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C21/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J21:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C21/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J21:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C21/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J21:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C21/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J21:relW))","v":0},{"$f":"IF(COLUMNS($K$16:Me) <= ROWS($K$16:Me) - 1, 0, $C21/SUBTOTAL(9, OFFSET($C$15, 1, 0, COLUMNS($K$16:Me))) - SUM($J21:relW))","v":0.18382352941176472}]

{"sheet":"via Macro","index":2,"n_rows":34,"n_cols":22,"rows_kept":34,"cut":false,"hidden":false,"merged":[[4,18,5,21]]}
[]
[null,null,null,"Total Investments","Total Distributions",null,null,"5. Receiver Assets"]
[null,null,null,{"$f":"SUM(tbl D:D)","v":5036223.1999999983},{"$f":"SUM(tbl E:E)","v":852605.81999999983},null,null,300000]
[]
[null,null,null,null,"Total Loss",null,null,"Total Distribution",null,null,null,null,null,null,null,null,null,null,"Random Data for Test\nCopy from below and paste into table at left"]
[null,null,null,null,{"$f":"D3-E3","v":4183617.3799999985},null,null,{"$f":"SUM(H8:H1048576)","v":300000}]
[]
[null,"1. ID","2. Name","3. Investment","4. Prior Distributions","Prior % Recovery","Distribution Breakpoints","Distribution Amount","Final Recovery",null,null,null,null,null,null,null,null,null,"1. ID","2. Name","3. Investment","4. Prior Distributions"]
[null,117909436,"Alan",608055.21,177.13,{"$f":"E9/D9","v":2.9130578455203109E-4},0,61069.913178808027,{"$f":"MROUND((E9 + H9) / D9, 1/65536)","v":0.1007232666015625},null,null,null,null,null,null,null,null,null,117909436,"Alan",{"$f":"ROUND(10^(2 * RAND() + 4), 2)","v":33814.92},{"$f":"ROUND(10 ^ (RAND()^0.25 * LOG(U9)), 2)","v":16647.3}]
[null,126364890,"Barb",19158.82,900.52,4.7002894750302997E-2,114808.3,1029.2736951138011,0.1007232666015625,null,null,null,null,null,null,null,null,null,126364890,"Barb",{"$f":"ROUND(10^(2 * RAND() + 4), 2)","v":18721.05},4914.87]
[null,150464948,"Cain",37188.9,9930.08,0.26701730892820169,948244.76,0,0.2670135498046875,null,null,null,null,null,null,null,null,null,150464948,"Cain",502789.35,1178.17]
[null,175395345,"Dana",239146.35,6451.14,2.6975699190056632E-2,50687.08,17637.145032652152,0.1007232666015625,null,null,null,null,null,null,null,null,null,175395345,"Dana",41160.379999999997,10961.06]
[null,181338965,"Eric",76912.990000000005,7906.21,0.10279420940467923,307555.27,0,0.1027984619140625,null,null,null,null,null,null,null,null,null,181338965,"Eric",182734.88,163261.99]
[null,250893382,"Fran",12981.7,8700.08,0.67018033077331929,2689809.75,0,0.6701812744140625,null,null,null,null,null,null,null,null,null,250893382,"Fran",18629.53,883.49]
[null,257621544,"Gary",209646.78,20927.689999999999,9.982356991125739E-2,296702.71999999997,189.21799335537642,0.1007232666015625,null,null,null,null,null,null,null,null,null,257621544,"Gary",44762.16,1386.98]
[null,315034233,"Hana",821843.02,26167.17,3.1839620661376425E-2,63136.1,56613.891972423815,0.1007232666015625,null,null,null,null,null,null,null,null,null,315034233,"Hana",21332.32,659.62]
[null,325047634,"Ivan",40537.65,39275.379999999997,0.96886178651204491,4027437.59,0,0.9688568115234375,null,null,null,null,null,null,null,null,null,325047634,"Ivan",174693.63,25.55]
[null,370048693,"Jane",26609.22,987.15,3.7098043460123971E-2,80916.42,1693.0936546633498,0.1007232666015625,null,null,null,null,null,null,null,null,null,370048693,"Jane",194846.75,144560.71]
[null,405998977,"Kent",468100.01,418136.95,0.89326413387600656,3659658.39,0,0.8932647705078125,null,null,null,null,null,null,null,null,null,405998977,"Kent",995346.31,123768.51]
[null,441507976,"Leah",123101.55,119907.59,0.97405426657909666,4052948.88,0,0.97406005859375,null,null,null,null,null,null,null,null,null,441507976,"Leah",454648.26,320929.46000000002]
[null,441881808,"Mark",21227.83,4287.7700000000004,0.20198814480801852,679765.71,0,0.2019805908203125,null,null,null,null,null,null,null,null,null,441881808,"Mark",934470.01,3361.16]
[null,495836196,"Nina",646663.46,7186.46,1.1113137581640998E-2,15863.09,57949.444796091593,0.1007232666015625,null,null,null,null,null,null,null,null,null,495836196,"Nina",134902.57,12568.92]
[null,515511571,"Otto",17154.169999999998,6425.25,0.37455907222558715,1410476.95,0,0.3745574951171875,null,null,null,null,null,null,null,null,null,515511571,"Otto",46818.16,26010.79]
[null,527754303,"Peri",21667.96,17821.73,0.82249228815264563,3350933.39,0,0.8224945068359375,null,null,null,null,null,null,null,null,null,527754303,"Peri",15747.08,4186.1000000000004]
[null,749317591,"Quin",17215.03,5918.28,0.34378563383276128,1277829.48,0,0.343780517578125,null,null,null,null,null,null,null,null,null,749317591,"Quin",77060.14,26266.639999999999]
[null,784691205,"Rene",90416.66,18460.080000000002,0.20416679846391142,688354.88,0,0.20416259765625,null,null,null,null,null,null,null,null,null,784691205,"Rene",12601.25,3927.89]
[null,788378351,"Seth",38233.379999999997,50.2,1.3129888071627465E-3,797.35,3800.9003107872018,0.1007232666015625,null,null,null,null,null,null,null,null,null,788378351,"Seth",365521.86,2998.82]
[null,833311421,"Tina",891008.15,993.77,1.1153321100373773E-3,501.05,88754.030622569393,0.1007232666015625,null,null,null,null,null,null,null,null,null,833311421,"Tina",288792.99,251328.34]
[null,844807967,"Ulis",16549.37,640.87,3.8724736953732984E-2,86460.06,1026.0838880875008,0.1007232666015625,null,null,null,null,null,null,null,null,null,844807967,"Ulis",92152.52,54731.03]
[null,847088600,"Vera",203879.27,41274.15,0.20244407388745311,681489.68,0,0.2024383544921875,null,null,null,null,null,null,null,null,null,847088600,"Vera",30680.5,5740.11]
[null,875003034,"Wade",180547.19,44466.74,0.24628874035646856,860022.95,0,0.2462921142578125,null,null,null,null,null,null,null,null,null,875003034,"Wade",802781.22,213924.41]
[null,888046363,"Xana",29797.02,3819.58,0.12818664416777248,402273.66,0,0.1281890869140625,null,null,null,null,null,null,null,null,null,888046363,"Xana",155489.35999999999,13744.92]
[null,979376477,"Yuri",42226.77,38296.370000000003,0.90692160447033965,3725628.85,0,0.90692138671875,null,null,null,null,null,null,null,null,null,979376477,"Yuri",17254.02,4842.9399999999996]
[null,997809152,"Zuni",136354.74,3497.48,2.5649860063537214E-2,47610.71,10237.004855447805,0.1007232666015625,null,null,null,null,null,null,null,null,null,997809152,"Zuni",12074.27,1622.38]