{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Points","Curved DSCR","Test","Flat","Sculpt"],"not_rendered":{"charts":5,"images":0,"macros":1,"external_links":0,"defined_names":52,"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":"Points","index":0,"n_rows":5,"n_cols":2,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,"Use Interpolate"]
[]
[null,"Idea of LLCR Factor to Make Curved"]

{"sheet":"Curved DSCR","index":1,"n_rows":65,"n_cols":18,"rows_kept":65,"cut":false,"hidden":false,"merged":[]}
["Inputs"]
[null,null,null,null,null,null,"EIRR","Avg Loan Life",null,null,null,null,null,null,null,null,null,"Basic Idea: Increase the Slope and Longer Average Life"]
[null,null,"Min DSCR",1.2,null,"Sloped DSCR",{"$f":"D44","v":0.19095647657089221},{"$f":"F32","v":8.4999999296127662}]
[null,null,"Debt Percent",0.82,null,"Flat DSCR",{"$f":"Flat!D23","v":0.15893897627174258},{"$f":"Flat!D20","v":7.8549714107745263},null,null,null,null,null,null,null,null,null,"Just find until meets criteria"]
[null,null,"Project Cost",900,null,null,null,null,null,null,null,null,null,null,null,null,null,"Not need to be actual"]
[null,null,"Target Debt Amount",{"$f":"D5*D4","v":738}]
[null,null,"PV of CFADS",{"$f":"NPV(D23,E19:P19)","v":1028.5714285714284},null,null,null,null,null,null,null,null,null,null,null,null,null,"Demonstrate with solver"]
[null,null,"LLCR",{"$f":"D7/D6","v":1.3937282229965156},null,null,null,null,null,"Difference"]
[null,null,"Target Avg Debt Life",8.5,{"$f":"F32","v":8.4999999296127662},null,"Average Life",null,null,{"$f":"D9-E9","v":7.0387233819246831E-8},null,null,null,null,null,null,null,"Input boundaries: LLCR Factor"]
[null,null,"LLCR Factor",1.3072768136809128,null,null,"Debt Balance",null,null,{"$f":"D24-D6","v":-6.8222518621041672E-6}]
[null,null,"Tenure",12]
[null,null,"Max LLCR Factor",10]
[null,null,"CFADS",null,{"$f":"E19","v":90},94.5,99.225000000000009,104.18625000000002,109.39556250000003,114.86534062500003,120.60860765625004,126.63903803906256,132.97098994101569,139.61953943806648,146.6005164099698,153.93054223046829]
[null,null,"Period",1,9.1892574895706556,"last date",null,null,null,null,null,null,null,null,null,null,null,"Start with Most Extreme Slope"]
[null,null,"Max/Min DSCR",{"$f":"LLCR*LLCR_Fac","v":1.8219885904960456},{"$f":"Min_DSCR","v":1.2},null,null,null,null,null,null,null,null,null,null,null,null,"Goal Seek on End Date to Find"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"If too much debt in extreme case, increase the LLCR or exit function"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Increment the LLCR until projected debt is below target"]
["Target Debt Service",null,null,null,1,2,3,4,5,6,7,8,9,10,11,12,null,"Once the computed debt is below the target debt, make a dynamic goal seek until the computed = target"]
[null,null,"CFADS",null,90,{"$f":"E19*1.05","v":94.5},{"$f":"F19*1.05","v":99.225000000000009},104.18625000000002,109.39556250000003,114.86534062500003,120.60860765625004,126.63903803906256,132.97098994101569,139.61953943806648,146.6005164099698,153.93054223046829,null,"Check the Average loan life.  If the computed loan life is more than the target, then reduce the LLCR Factor and repeat the process"]
[null,null,"DSCR Target",null,{"$f":"interpolate_look_up(E18,14:14,15:15)","v":1.8219885904960456},1.7314064417011188,1.6453276831706023,1.5635284239486793,1.4857959040624502,1.411927941132981,1.3417324044986918,1.2750267154832702,1.2116373725083134,1.2,1.2,1.2]
[null,null,"Target Debt Service",{"$f":"MAX(D8,D3)","v":1.3937282229965156},{"$f":"E19/E20","v":49.396577162701689},{"$f":"F19/F20","v":54.579905517247063},60.307135785128267,66.63534119634258,73.627583843037684,81.353543108459206,89.89021003879887,99.322654577526151,109.74487330787854,116.34961619838874,122.16709700830818,128.27545185872359]
[]
[null,null,"Interest Rate",0.05]
[null,null,"PV of Debt Service",{"$f":"NPV(D23,E21:P21)","v":737.99999317774814},null,null,null,null,null,null,null,null,null,null,null,null,null,"Allow with flat or growth interpolate"]
[]
["Debt Balance"]
[null,null,"Opening Balance",null,{"$f":"D29","v":738},{"$f":"E29","v":725.50342283729833},707.19868846191616,682.25148709988366,649.7287202585353,608.58757242842444,557.66340794138648,495.65636829965695,421.11653213711361,332.42748543609076,232.69924350950657,122.16710867667373]
[null,null,"Less: Repayment",null,{"$f":"E21-E30","v":12.496577162701691},{"$f":"F21-F30","v":18.304734375382147},24.947201362032459,32.522766841348393,41.141147830110917,50.924164487037984,62.007039641729548,74.53983616254331,88.689046701022846,99.728241926584204,110.53213483283284,122.16709642488991]
[null,null,"Closing Balance",{"$f":"D6","v":738},{"$f":"E27-E28","v":725.50342283729833},{"$f":"F27-F28","v":707.19868846191616},682.25148709988366,649.7287202585353,608.58757242842444,557.66340794138648,495.65636829965695,421.11653213711361,332.42748543609076,232.69924350950657,122.16710867667373,1.2251783829242413E-5,null,"Demonstrate with "]
[null,null,"Interest",{"$f":"D23","v":0.05},{"$f":"$D$30*E27","v":36.9},{"$f":"$D$30*F27","v":36.275171141864917},35.359934423095808,34.112574354994187,32.486436012926767,30.429378621421222,27.883170397069325,24.782818414982849,21.055826606855682,16.621374271804537,11.63496217547533,6.1083554338336867]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Outside is LLCR Factor"]
[null,null,"Average Loan Life with Sumproduct",null,null,{"$f":"SUMPRODUCT(E28:P28,E18:P18)/D24","v":8.4999999296127662},null,null,null,null,null,null,null,null,null,null,null,"Inside is last date"]
[]
[null,null,"Sum of Opening Balance/Initial",null,{"$f":"SUM(E27:P27)","v":6273.0000370864855},{"$f":"SUM(E27:P27)/D29","v":8.5000000502526909},null,null,null,null,null,null,null,null,null,null,null,"Start with high LLCR Factor"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Move down until hit Averge loan life or 1"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Each time re-run the debt sizing function"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"If 1 then stop"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"If hit, then reverse the process"]
[]
[]
[]
[null,null,"Equity CF",{"$f":"D29-D5","v":-162},{"$f":"E19-E28-E30","v":40.603422837298304},39.920094482752944,38.917864214871742,37.550908803657428,35.767978656962349,33.511797516540824,30.718397617451171,27.316383461536397,23.22611663313716,23.269923239677741,24.433419401661631,25.655090371744699]
[null,null,"IRR",{"$f":"IRR(43:43)","v":0.19095647657089221}]
[]
[null,null,"Average DSCR",{"$f":"AVERAGE(20:20)","v":1.4240309564168461}]
[]
[]
[null,null,"PV Factor",{"$f":"D23","v":0.05},{"$f":"1/(1+$D$49)^E18","v":0.95238095238095233},{"$f":"1/(1+$D$49)^F18","v":0.90702947845804982},0.86383759853147601,0.82270247479188197,0.78352616646845896,0.74621539663662761,0.71068133013012147,0.67683936202868722,0.64460891621779726,0.61391325354075932,0.5846792890864374,0.5568374181775595]
[]
[{"$f":"D8","v":1.3937282229965156},null,"Debt PV",null,{"$f":"E49*E27","v":702.85714285714278},{"$f":"F49*F27","v":658.05299123564464},610.90481672555109,561.28998686751606,509.07945342862791,454.13741674779908,396.3209725206811,335.47974010539582,271.45547138230199,204.08163915044386,136.0544282660901,68.027217381736335]
[null,null,"Weighting",null,{"$f":"E51/SUM(51:51)","v":0.14317331712343404},{"$f":"F51/SUM(51:51)","v":0.13404662747712137},0.12444245597580585,0.11433582215771078,0.10370043865596136,9.2508642825111453E-2,8.0731324790568867E-2,6.8337851733776322E-2,5.5295988216212844E-2,4.1571812334979086E-2,2.7714541997829061E-2,1.3857271660679033E-2]
[null,null,"Weighted Average",null,{"$f":"SUMPRODUCT(52:52,20:20)","v":1.5233819530016359}]
[]
[null,null,"CFADS PV",null,{"$f":"E49*E19","v":85.714285714285708},{"$f":"F49*F19","v":85.714285714285708},85.714285714285708,85.714285714285722,85.714285714285722,85.714285714285737,85.714285714285737,85.714285714285751,85.714285714285737,85.714285714285751,85.714285714285737,85.714285714285751]
[null,null,"Weighting",null,{"$f":"E55/SUM(55:55)","v":8.3333333333333301E-2},{"$f":"F55/SUM(55:55)","v":8.3333333333333301E-2},8.3333333333333301E-2,8.3333333333333315E-2,8.3333333333333315E-2,8.3333333333333329E-2,8.3333333333333329E-2,8.3333333333333343E-2,8.3333333333333329E-2,8.3333333333333343E-2,8.3333333333333329E-2,8.3333333333333343E-2]
[null,null,"Weighted Average",null,{"$f":"SUMPRODUCT(56:56,20:20)","v":1.4240309564168463}]
[]
[null,null,null,null,{"$f":"Flat!E22","v":25.424999999999997},{"$f":"Flat!F22","v":26.696249999999992},{"$f":"Flat!G22","v":28.03106249999999},{"$f":"Flat!H22","v":29.432615624999997},{"$f":"Flat!I22","v":30.90424640625},{"$f":"Flat!J22","v":32.449458726562497},{"$f":"Flat!K22","v":34.071931662890634},{"$f":"Flat!L22","v":35.775528246035165},{"$f":"Flat!M22","v":37.564304658336923},{"$f":"Flat!N22","v":39.442519891253767},{"$f":"Flat!O22","v":41.414645885816455},{"$f":"Flat!P22","v":43.485378180107276}]
[]
[null,null,"LLCR",{"$f":"D7/D24","v":1.393728235880479}]
[]
[null,null,"Debt Service",null,{"$f":"E21*E49","v":47.044359202573034},{"$f":"F21*F49","v":49.505583235598237},52.095571350936844,54.821060110832484,57.689138514870365,60.707266438473873,63.883294036049577,67.225482159248472,70.742523843451053,71.428571428571473,71.428571428571459,71.428571428571473]
[null,null,"Weight",null,{"$f":"E63/SUM(63:63)","v":6.3745744766209422E-2},{"$f":"F63/SUM(63:63)","v":6.7080736711707195E-2},7.0590205735123299E-2,7.4283279969663579E-2,7.8169565105911679E-2,8.2259169376242058E-2,8.6562729846344597E-2,9.1091440082245539E-2,9.5857079264786149E-2,9.6786683047255545E-2,9.6786683047255517E-2,9.6786683047255545E-2]
[null,null,"Weighted Average",{"$f":"SUMPRODUCT(64:64,20:20)","v":1.3937282358804794}]

{"sheet":"Test","index":2,"n_rows":11,"n_cols":12,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
[]
[" LLCR Factor",8,"Average Life",9.3418295515498908," Increment ",1," Reducing "," Prior Increment ",1,"Reduce: Base, Not Reversed ",0]
[" LLCR Factor",7,"Average Life",9.3338016395124495," Increment ",1," Reducing "," Prior Increment ",1,"Reduce: Base, Not Reversed ",0]
[" LLCR Factor",6,"Average Life",9.3245068974937286," Increment ",1," Reducing "," Prior Increment ",1,"Reduce: Base, Not Reversed ",0]
[" LLCR Factor",5,"Average Life",9.3128446925103958," Increment ",1," Reducing "," Prior Increment ",1,"Reduce: Base, Not Reversed ",0]
[" LLCR Factor",4,"Average Life",9.2977329831620743," Increment ",1," Reducing "," Prior Increment ",1,"Reduce: Base, Not Reversed ",0]
[" LLCR Factor",3,"Average Life",9.2701335040064077," Increment ",1," Reducing "," Prior Increment ",1,"Reduce: Base, Not Reversed ",0]
[" LLCR Factor",2,"Average Life",9.2129920434003605," Increment ",1," Reducing "," Prior Increment ",1,"Reduce: Base, Not Reversed ",0]
[" LLCR Factor",1.5,"Average Life",9.0664788294246748," Increment ",0.5," Reducing "," Prior Increment ",1,"Reduce: Cut Difference ",1]
[" LLCR Factor",1.625,"Average Life",8.8240384988400908," Increment ",0.125," Increasing "," Prior Increment ",0.5,"Increase: Reversed Base , LLCR Reduce True",2,4]
[" LLCR Factor",1.75,"Average Life",8.9136075201030618," Increment ",0.125," Increasing "," Prior Increment ",0.125,"Increase: Reversed Base , LLCR Reduce True",2,4]

{"sheet":"Flat","index":3,"n_rows":23,"n_cols":16,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,"Min DSCR",{"$f":"Min_DSCR","v":1.2}]
[]
[null,null,"Project",900]
[null,null,"Max Debt",{"$f":"Max_Debt","v":738}]
[null,null,"PV of CFADS",{"$f":"NPV(D13,E11:P11)","v":1028.5714285714284}]
[null,null,"LLCR",{"$f":"D6/D5","v":1.3937282229965156}]
[null,null,"Target Avg Loan Life",9]
[]
[null,null,null,null,1,2,3,4,5,6,7,8,9,10,11,12]
[null,null,"CFADS",null,{"$f":"'Curved DSCR'!E19","v":90},{"$f":"E11*1.05","v":94.5},{"$f":"F11*1.05","v":99.225000000000009},104.18625000000002,109.39556250000003,114.86534062500003,120.60860765625004,126.63903803906256,132.97098994101569,139.61953943806648,146.6005164099698,153.93054223046829]
[null,null,"Target",{"$f":"MAX(D7,D2)","v":1.3937282229965156},{"$f":"E11/$D$12","v":64.575000000000003},{"$f":"F11/$D$12","v":67.803750000000008},71.193937500000018,74.753634375000019,78.491316093750029,82.415881898437533,86.53667599335941,90.86350979302739,95.406685282678765,100.17701954681272,105.18587052415334,110.44516405036102]
[null,null,"Interest",{"$f":"'Curved DSCR'!D23","v":0.05},{"$f":"NPV(D13,E12:P12)","v":738.00000000000011}]
[]
[null,null,"Open",null,{"$f":"D17","v":738},{"$f":"E17","v":710.32500000000005},678.03750000000002,640.74543749999998,598.02907499999992,549.43921265624988,494.49529139062486,432.68337996679668,363.45403917210911,286.22005584803583,200.35403909362492,105.18587052415282]
[null,null,"Less Repayment",null,{"$f":"E12-E18","v":27.675000000000004},{"$f":"F12-F18","v":32.287500000000001},37.292062500000014,42.716362500000017,48.589862343750028,54.943921265625036,61.811911423828164,69.229340794687559,77.233983324073307,85.866016754410921,95.168168569472101,105.18587052415337]
[null,null,"Closing",{"$f":"D5","v":738},{"$f":"E15-E16","v":710.32500000000005},{"$f":"F15-F16","v":678.03750000000002},640.74543749999998,598.02907499999992,549.43921265624988,494.49529139062486,432.68337996679668,363.45403917210911,286.22005584803583,200.35403909362492,105.18587052415282,-5.5422333389287814E-13]
[null,null,"Interest",{"$f":"D13","v":0.05},{"$f":"$D$18*E15","v":36.9},{"$f":"$D$18*F15","v":35.516250000000007},33.901875000000004,32.037271875000002,29.901453749999998,27.471960632812497,24.724764569531246,21.634168998339835,18.172701958605455,14.311002792401792,10.017701954681247,5.2592935262076415]
[]
[null,null,"Avg Loan Life",{"$f":"SUMPRODUCT(E16:P16,E10:P10)/D17","v":7.8549714107745263}]
[]
[null,null,"Equity CF",{"$f":"D17-D4","v":-162},{"$f":"E11-E16-E18","v":25.424999999999997},{"$f":"F11-F16-F18","v":26.696249999999992},28.03106249999999,29.432615624999997,30.90424640625,32.449458726562497,34.071931662890634,35.775528246035165,37.564304658336923,39.442519891253767,41.414645885816455,43.485378180107276]
[null,null,"IRR",{"$f":"IRR(22:22)","v":0.15893897627174258}]

{"sheet":"Sculpt","index":4,"n_rows":24,"n_cols":16,"rows_kept":24,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,"Min DSCR",{"$f":"Min_DSCR","v":1.2}]
[]
[null,null,"Project",900]
[null,null,"Max Debt",{"$f":"Max_Debt","v":738}]
[null,null,"PV of CFADS",{"$f":"NPV(D13,E11:P11)","v":1028.5714285714284}]
[null,null,"LLCR",{"$f":"D6/D5","v":1.3937282229965156}]
[null,null,"Target Avg Loan Life",9]
[]
[null,null,null,null,1,2,3,4,5,6,7,8,9,10,11,12]
[null,null,"CFADS",null,{"$f":"'Curved DSCR'!E19","v":90},{"$f":"E11*1.05","v":94.5},{"$f":"F11*1.05","v":99.225000000000009},104.18625000000002,109.39556250000003,114.86534062500003,120.60860765625004,126.63903803906256,132.97098994101569,139.61953943806648,146.6005164099698,153.93054223046829]
[null,null,"Target",{"$f":"D2","v":1.2},{"$f":"E11/$D$12","v":75},{"$f":"F11/$D$12","v":78.75},82.687500000000014,86.82187500000002,91.162968750000019,95.721117187500028,100.50717304687504,105.53253169921879,110.80915828417974,116.34961619838874,122.16709700830818,128.27545185872359]
[null,null,"Interest",{"$f":"'Curved DSCR'!D23","v":0.05},{"$f":"NPV(D13,E12:P12)","v":857.14285714285711}]
[]
[null,null,"Open",null,{"$f":"D17","v":857.14285714285711},{"$f":"E17","v":825},787.5,744.1875,694.57499999999993,638.14078124999992,574.32670312499988,502.53586523437485,422.13012679687483,332.42747485253881,232.69923239677701,122.16709700830768]
[null,null,"Less Repayment",null,{"$f":"E12-E18","v":32.142857142857139},{"$f":"F12-F18","v":37.5},43.312500000000014,49.612500000000018,56.434218750000021,63.81407812500003,71.790837890625042,80.405738437500048,89.702651944335997,99.728242455761801,110.53213538846933,122.1670970083082]
[null,null,"Closing",{"$f":"E13","v":857.14285714285711},{"$f":"E15-E16","v":825},{"$f":"F15-F16","v":787.5},744.1875,694.57499999999993,638.14078124999992,574.32670312499988,502.53586523437485,422.13012679687483,332.42747485253881,232.69923239677701,122.16709700830768,-5.2580162446247414E-13]
[null,null,"Interest",{"$f":"D13","v":0.05},{"$f":"$D$18*E15","v":42.857142857142861},{"$f":"$D$18*F15","v":41.25},39.375,37.209375000000001,34.728749999999998,31.907039062499997,28.716335156249997,25.126793261718745,21.106506339843744,16.62137374262694,11.634961619838851,6.1083548504153846]
[]
[null,null,"Sumproduct",{"$f":"SUMPRODUCT(E16:P16,E10:P10)","v":6732.8326378067359}]
[null,null,"Avg Loan Life",{"$f":"SUMPRODUCT(E16:P16,E10:P10)/D17","v":7.8549714107745254}]
[]
[null,null,"Equity CF",{"$f":"D17-D4","v":-42.85714285714289},{"$f":"E11-E16-E18","v":15},{"$f":"F11-F16-F18","v":15.75},16.537499999999994,17.364374999999995,18.232593750000007,19.144223437500003,20.101434609375005,21.106506339843762,22.161831656835947,23.269923239677741,24.43341940166162,25.655090371744702]
[null,null,"IRR",{"$f":"IRR(23:23)","v":0.3876718378393651}]