{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["LLCR Theory","LLCR Example with Sumproduct","Rael","No Construction Period"],"not_rendered":{"charts":1,"images":2,"macros":0,"external_links":0,"defined_names":19,"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":"LLCR Theory","index":0,"n_rows":17,"n_cols":2,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,"LLCR Formulas"]
[]
[null,"LLCR = PV(CF)/PV(Net Debt) ; DSCR with NPV's around it"]
[]
[null,"LLCR = PV(CF)/(Debt - DSRA)"]
[]
[null,"LLCR is a break even formula"]
[null,"LLCR measures pct reduction in cash flow before cannot repay debt over loan life"]
[]
[null,"Break even percent reduction in CFADS = (LLCR-1)/LLCR"]
[]
[null,"Problems in what discount rate gives the correct answer -- in terms of the break-even theory"]
[]
[null,"Rolling LLCR"]
[null,"PV of CFADS = (Sumproduct of CF/Interest Rate Index) * Last Period Interest Rate Index"]

{"sheet":"LLCR Example with Sumproduct","index":1,"n_rows":50,"n_cols":23,"rows_kept":50,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,null,null,null,null,null,null,null,"Create an Interest Rate Index"]
[null,"Period",null,1,4,6,9,12,null,"LLCR can be computed with Sumproduct"]
[null,"Rate",null,0.04,{"$f":"D4+0.25%","v":4.2500000000000003E-2},{"$f":"E4+0.25%","v":4.5000000000000005E-2},4.7500000000000007E-2,5.000000000000001E-2]
[null,null,null,null,null,null,null,null,null,"Multuiply by prior value or Interest Rate Index"]
[null,"Loan",null,1000]
[null,"Tenor",null,20]
[]
[null,null,null,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20]
[]
[null,"Base CF",null,200,{"$f":"D11*1.01","v":202},204.02,206.06020000000001,208.120802,210.20201001999999,212.3040301202,214.42707042140199,216.57134112561602,218.73705453687217,220.9244250822409,223.13366933306332,225.36500602639396,227.61865608665789,229.89484264752448,232.19379107399973,234.51572898473972,236.86088627458713,239.229495137333,241.62179008870635]
[null,"Pct Reduction",1,{"$f":"D11*$C$12","v":200},202,204.02,206.06020000000001,208.120802,210.20201001999999,212.3040301202,214.42707042140199,216.57134112561602,218.73705453687217,220.9244250822409,223.13366933306332,225.36500602639396,227.61865608665789,229.89484264752448,232.19379107399973,234.51572898473972,236.86088627458713,239.229495137333,241.62179008870635]
[null,"Cash Flow Realised",null,{"$f":"D12","v":200},{"$f":"E12","v":202},204.02,206.06020000000001,208.120802,210.20201001999999,212.3040301202,214.42707042140199,216.57134112561602,218.73705453687217,220.9244250822409,223.13366933306332,225.36500602639396,227.61865608665789,229.89484264752448,232.19379107399973,234.51572898473972,236.86088627458713,239.229495137333,241.62179008870635]
[]
[null,"Target DS",1.5,{"$f":"D13/$C$15","v":133.33333333333334},134.66666666666666,136.01333333333335,137.37346666666667,138.74720133333332,140.13467334666666,141.53602008013334,142.95138028093467,144.38089408374401,145.82470302458145,147.28295005482727,148.75577955537554,150.24333735092929,151.74577072443859,153.26322843168299,154.79586071599982,156.34381932315981,157.90725751639141,159.48633009155535,161.08119339247091]
[]
[null,"Interest Rate",null,{"$f":"LOOKUP(D9,3:3,4:4)","v":0.04},0.04,0.04,4.2500000000000003E-2,4.2500000000000003E-2,4.5000000000000005E-2,4.5000000000000005E-2,4.5000000000000005E-2,4.7500000000000007E-2,4.7500000000000007E-2,4.7500000000000007E-2,5.000000000000001E-2,5.000000000000001E-2,5.000000000000001E-2,5.000000000000001E-2,5.000000000000001E-2,5.000000000000001E-2,5.000000000000001E-2,5.000000000000001E-2,5.000000000000001E-2]
[null,"Interest Rate Index",1,{"$f":"C18*(1+D17)","v":1.04},1.0816000000000001,1.1248640000000001,1.1726707200000002,1.2225092256000001,1.2775221407520001,1.3350106370858401,1.3950861157547028,1.4613527062530514,1.5307669598000715,1.6034783903905749,1.6836523099101037,1.7678349254056089,1.8562266716758895,1.9490380052596841,2.0464899055226682,2.1488144007988015,2.2562551208387416,2.3690678768806785,2.4875212707247125]
[]
[null,"NPV of DS",{"$f":"SUMPRODUCT(D15:W15/D18:W18)","v":1892.5455646886339}]
[]
[]
[null,"Opening Balance",null,{"$f":"C25","v":1892.5455646886339},{"$f":"D25","v":1834.9140539428458},1773.6439494338931,1708.5763740779155,1643.8174033095602,1574.9324416168831,1505.6697281429763,1431.888845829277,1353.3724636106599,1273.2767615484222,1187.9327046973908,1097.0765581156895,1003.1746064660985,903.08999943847402,796.49872868595912,683.0604366885741,562.41759780700295,434.1946583741933,297.99713377651153,153.41066037378175]
[null,"Repayment",null,{"$f":"D15-D26","v":57.631510745787978},{"$f":"E15-E26","v":61.270104508952826},65.067575355977624,64.758970768355255,68.88496169267701,69.262713473906913,73.780882313699394,78.516382218617196,80.095702062237649,85.344056851031382,90.856146581701196,93.901951649591055,100.08460702762437,106.59127075251487,113.43829199738502,120.64283888157111,128.22293943280965,136.19752459768173,144.58647340272978,153.41066037378181]
[null,"Closing Balance",{"$f":"C20","v":1892.5455646886339},{"$f":"D23-D24","v":1834.9140539428458},{"$f":"E23-E24","v":1773.6439494338931},1708.5763740779155,1643.8174033095602,1574.9324416168831,1505.6697281429763,1431.888845829277,1353.3724636106599,1273.2767615484222,1187.9327046973908,1097.0765581156895,1003.1746064660985,903.08999943847402,796.49872868595912,683.0604366885741,562.41759780700295,434.1946583741933,297.99713377651153,153.41066037378175,0]
[null,"Interest",null,{"$f":"D23*D17","v":75.701822587545365},{"$f":"E23*E17","v":73.396562157713831},70.945757977355726,72.614495898311418,69.862239640656313,70.871959872759746,67.755137766433947,64.434998062317476,64.285192021506361,60.480646173550063,56.426803473126071,54.853827905784485,50.15873032330493,45.154499971923713,39.82493643429796,34.153021834428714,28.120879890350153,21.709732918709669,14.899856688825579,7.6705330186890892]
[]
[null,"NPV of Cash Flow with Sumproduct",{"$f":"SUMPRODUCT(D13:W13/D18:W18)","v":2838.81834703295}]
["Wrong","NPV with discount factor"]
[null,"Discount factor",null,{"$f":"1/(1+D17)^D9","v":0.96153846153846145},{"$f":"1/(1+E17)^E9","v":0.92455621301775137},0.88899635867091487,0.84663407814083602,0.81211901979936307,0.76789573827816682,0.73482845768245619,0.70318512696885782,0.65858785138708187,0.62872348581105664,0.60021335160960043,0.5568374181775595,0.53032135064529462,0.50506795299551888,0.48101709809097021,0.45811152199140021,0.43629668761085727,0.41552065486748313,0.39573395701665059,0.37688948287300061]
[null,"Sumproduct",{"$f":"SUMPRODUCT(D30:W30,D13:W13)","v":2747.0348114147787}]
[null,"NPV of DS",{"$f":"SUMPRODUCT(D15:W15/D18:W18)","v":1892.5455646886339}]
[]
[null,"Rolling Forward DS",null,{"$f":"IFERROR(SUMPRODUCT(E13:$W13/E18:$W18)*D18,0)","v":2752.3710809142681},{"$f":"IFERROR(SUMPRODUCT(F13:$W13/F18:$W18)*E18,0)","v":2660.4659241508384},{"$f":"IFERROR(SUMPRODUCT(G13:$W13/G18:$W18)*F18,0)","v":2562.8645611168727},{"$f":"IFERROR(SUMPRODUCT(H13:$W13/H18:$W18)*G18,0)","v":2465.7261049643403},{"$f":"IFERROR(SUMPRODUCT(I13:$W13/I18:$W18)*H18,0)","v":2362.3986624253243},{"$f":"IFERROR(SUMPRODUCT(J13:$W13/J18:$W18)*I18,0)","v":2258.5045922144641},{"$f":"IFERROR(SUMPRODUCT(K13:$W13/K18:$W18)*J18,0)","v":2147.8332687439151},{"$f":"IFERROR(SUMPRODUCT(L13:$W13/L18:$W18)*K18,0)","v":2030.0586954159887},{"$f":"IFERROR(SUMPRODUCT(M13:$W13/M18:$W18)*L18,0)","v":1909.9151423226326},{"$f":"IFERROR(SUMPRODUCT(N13:$W13/N18:$W18)*M18,0)","v":1781.8990570460858},{"$f":"IFERROR(SUMPRODUCT(O13:$W13/O18:$W18)*N18,0)","v":1645.6148371735339},{"$f":"IFERROR(SUMPRODUCT(P13:$W13/P18:$W18)*O18,0)","v":1504.7619096991477},{"$f":"IFERROR(SUMPRODUCT(Q13:$W13/Q18:$W18)*P18,0)","v":1354.6349991577108},{"$f":"IFERROR(SUMPRODUCT(R13:$W13/R18:$W18)*Q18,0)","v":1194.7480930289387},{"$f":"IFERROR(SUMPRODUCT(S13:$W13/S18:$W18)*R18,0)","v":1024.5906550328612},{"$f":"IFERROR(SUMPRODUCT(T13:$W13/T18:$W18)*S18,0)","v":843.62639671050454},{"$f":"IFERROR(SUMPRODUCT(U13:$W13/U18:$W18)*T18,0)","v":651.29198756129006},{"$f":"IFERROR(SUMPRODUCT(V13:$W13/V18:$W18)*U18,0)","v":446.99570066476741},{"$f":"IFERROR(SUMPRODUCT(W13:$W13/W18:$W18)*V18,0)","v":230.11599056067271},{"$f":"IFERROR(SUMPRODUCT($W13:X13/$W18:X18)*W18,0)","v":0}]
[null,"Rolling Forward Cash Flow",null,{"$f":"IFERROR(SUMPRODUCT(E13:$W13/E18:$W18)*D18,0)","v":2752.3710809142681},{"$f":"IFERROR(SUMPRODUCT(F13:$W13/F18:$W18)*E18,0)","v":2660.4659241508384},{"$f":"IFERROR(SUMPRODUCT(G13:$W13/G18:$W18)*F18,0)","v":2562.8645611168727},{"$f":"IFERROR(SUMPRODUCT(H13:$W13/H18:$W18)*G18,0)","v":2465.7261049643403},{"$f":"IFERROR(SUMPRODUCT(I13:$W13/I18:$W18)*H18,0)","v":2362.3986624253243},{"$f":"IFERROR(SUMPRODUCT(J13:$W13/J18:$W18)*I18,0)","v":2258.5045922144641},{"$f":"IFERROR(SUMPRODUCT(K13:$W13/K18:$W18)*J18,0)","v":2147.8332687439151},{"$f":"IFERROR(SUMPRODUCT(L13:$W13/L18:$W18)*K18,0)","v":2030.0586954159887},{"$f":"IFERROR(SUMPRODUCT(M13:$W13/M18:$W18)*L18,0)","v":1909.9151423226326},{"$f":"IFERROR(SUMPRODUCT(N13:$W13/N18:$W18)*M18,0)","v":1781.8990570460858},{"$f":"IFERROR(SUMPRODUCT(O13:$W13/O18:$W18)*N18,0)","v":1645.6148371735339},{"$f":"IFERROR(SUMPRODUCT(P13:$W13/P18:$W18)*O18,0)","v":1504.7619096991477},{"$f":"IFERROR(SUMPRODUCT(Q13:$W13/Q18:$W18)*P18,0)","v":1354.6349991577108},{"$f":"IFERROR(SUMPRODUCT(R13:$W13/R18:$W18)*Q18,0)","v":1194.7480930289387},{"$f":"IFERROR(SUMPRODUCT(S13:$W13/S18:$W18)*R18,0)","v":1024.5906550328612},{"$f":"IFERROR(SUMPRODUCT(T13:$W13/T18:$W18)*S18,0)","v":843.62639671050454},{"$f":"IFERROR(SUMPRODUCT(U13:$W13/U18:$W18)*T18,0)","v":651.29198756129006},{"$f":"IFERROR(SUMPRODUCT(V13:$W13/V18:$W18)*U18,0)","v":446.99570066476741},{"$f":"IFERROR(SUMPRODUCT(W13:$W13/W18:$W18)*V18,0)","v":230.11599056067271},{"$f":"IFERROR(SUMPRODUCT($W13:X13/$W18:X18)*W18,0)","v":0}]
[]
[null,"Rolling LLCR",null,{"$f":"IF(D25,D35/D25)","v":1.4999999999999996},{"$f":"IF(E25,E35/E25)","v":1.4999999999999993},1.4999999999999998,1.5,1.4999999999999998,1.4999999999999998,1.4999999999999998,1.4999999999999991,1.4999999999999996,1.4999999999999996,1.4999999999999998,1.5,1.4999999999999998,1.5,1.5,1.5000000000000002,1.5000000000000002,1.5000000000000004,1.5000000000000007,false]
[]
[]
["Cash Flow Sweep Proof of LLCR Formula"]
[null,"Cash Flow",{"$f":"(D37-1)/D37","v":0.33333333333333315},{"$f":"D13*(1-$C$41)","v":133.33333333333337},{"$f":"E13*(1-$C$41)","v":134.66666666666671},136.01333333333338,137.3734666666667,138.74720133333338,140.13467334666669,141.53602008013337,142.9513802809347,144.38089408374404,145.82470302458148,147.2829500548273,148.7557795553756,150.24333735092935,151.74577072443864,153.26322843168302,154.79586071599988,156.34381932315986,157.90725751639147,159.48633009155537,161.08119339247094]
[]
[null,"Debt Balance"]
[null,"Opening Balance",null,{"$f":"C46","v":1892.5455646886339},{"$f":"D46","v":1834.9140539428458},1773.6439494338929,1708.5763740779153,1643.8174033095599,1574.9324416168829,1505.6697281429761,1431.8888458292765,1353.3724636106592,1273.2767615484215,1187.9327046973901,1097.0765581156888,1003.1746064660977,903.08999943847323,796.49872868595821,683.06043668857308,562.41759780700181,434.19465837419204,297.99713377651017,153.4106603737803]
[null,"Less: Cash Sweep",null,{"$f":"MIN(D44,D50)","v":57.631510745788006},{"$f":"MIN(E44,E50)","v":61.270104508952883},65.067575355977667,64.758970768355297,68.884961692677081,69.262713473906942,73.780882313699436,78.516382218617252,80.09570206223772,85.344056851031439,90.856146581701253,93.90195164959114,100.08460702762446,106.59127075251497,113.43829199738511,120.64283888157121,128.22293943280977,136.19752459768188,144.58647340272987,153.4106603737803]
[null,"Closing Balance",{"$f":"C20","v":1892.5455646886339},{"$f":"D44-D45","v":1834.9140539428458},{"$f":"E44-E45","v":1773.6439494338929},1708.5763740779153,1643.8174033095599,1574.9324416168829,1505.6697281429761,1431.8888458292765,1353.3724636106592,1273.2767615484215,1187.9327046973901,1097.0765581156888,1003.1746064660977,903.08999943847323,796.49872868595821,683.06043668857308,562.41759780700181,434.19465837419204,297.99713377651017,153.4106603737803,0]
[]
[null,"Interest",null,{"$f":"D44*D17","v":75.701822587545365},{"$f":"E44*E17","v":73.396562157713831},70.945757977355711,72.614495898311404,69.862239640656298,70.871959872759746,67.755137766433933,64.434998062317447,64.285192021506319,60.480646173550035,56.426803473126043,54.853827905784456,50.158730323304894,45.15449997192367,39.824936434297918,34.153021834428664,28.120879890350096,21.709732918709605,14.899856688825512,7.6705330186890164]
[]
[null,"Cash Flow After Interest",null,{"$f":"D41-D48","v":57.631510745788006},{"$f":"E41-E48","v":61.270104508952883},65.067575355977667,64.758970768355297,68.884961692677081,69.262713473906942,73.780882313699436,78.516382218617252,80.09570206223772,85.344056851031439,90.856146581701253,93.90195164959114,100.08460702762446,106.59127075251497,113.43829199738511,120.64283888157121,128.22293943280977,136.19752459768188,144.58647340272987,153.41066037378192]

{"sheet":"Rael","index":2,"n_rows":43,"n_cols":13,"rows_kept":43,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Discounting"]
[null,null,"Period","[yr]",null,null,0,{"$f":"+G12+1","v":1},{"$f":"+H12+1","v":2},{"$f":"+I12+1","v":3},{"$f":"+J12+1","v":4},{"$f":"+K12+1","v":5},6]
[null,null,"One period discount rate","[%]",null,null,null,0.05,5.5E-2,0.06,6.5000000000000002E-2,7.0000000000000007E-2,7.1999999999999995E-2]
[null,null,"Individual Discount Factor ","[#]",null,null,1,{"$f":"1+H13","v":1.05},1.0549999999999999,1.06,1.0649999999999999,1.07,1.0720000000000001]
[null,null,"Compounded Discounted Factor","[#]",null,null,{"$f":"+G14","v":1},{"$f":"+G15*(1+H13)","v":1.05},1.10775,1.174215,1.250538975,1.3380767032500001,1.4344182258840001]
[null,null,"CFADS","[$m]",{"$f":"SUM(G16:M16)","v":36.1},null,0,6.5,5.5,5.7,6.7,6,5.7]
[null,null,"Discounted CFADS (Long Formula)","[$m]",{"$f":"SUM(G17:M17)","v":29.825276499226337},null,null,{"$f":"H16/H14","v":6.1904761904761898},{"$f":"I16/I14/H14","v":4.9650191830286614},{"$f":"J16/J14/I14/H14","v":4.8543069199422595},{"$f":"K16/K14/J14/I14/H14","v":5.357689871281301},{"$f":"L16/L14/K14/J14/I14/H14","v":4.4840478766477618},{"$f":"M16/M14/L14/K14/J14/I14/H14","v":3.9737364578501611}]
[null,null,"Discounted CFADS (Compounded Discount Factor)","[$m]",{"$f":"SUM(G18:M18)","v":29.825276499226337},null,null,{"$f":"H16/H15","v":6.1904761904761898},4.9650191830286614,4.8543069199422595,5.357689871281301,4.4840478766477618,3.9737364578501619]
[]
[null,"[i]","Present Value Sumproduct",null,null,null,{"$f":"SUMPRODUCT(G16:$M$16/G15:$M$15)","v":29.825276499226337},{"$f":"SUMPRODUCT(H16:$M$16/H15:$M$15)","v":29.825276499226337},{"$f":"SUMPRODUCT(I16:$M$16/I15:$M$15)","v":23.634800308750144},{"$f":"SUMPRODUCT(J16:$M$16/J15:$M$15)","v":18.669781125721485},{"$f":"SUMPRODUCT(K16:$M$16/K15:$M$15)","v":13.815474205779225},{"$f":"SUMPRODUCT(L16:$M$16/L15:$M$15)","v":8.4577843344979229},{"$f":"SUMPRODUCT(M16:$M$16/M15:$M$15)","v":3.9737364578501619}]
[null,"[ii]","Present Value (Summation of Discounted CFADS)",null,null,null,{"$f":"SUM(G17:$M$17)","v":29.825276499226337},{"$f":"SUM(H17:$M$17)","v":29.825276499226337},{"$f":"SUM(I17:$M$17)","v":23.634800308750144},{"$f":"SUM(J17:$M$17)","v":18.669781125721485},{"$f":"SUM(K17:$M$17)","v":13.815474205779225},{"$f":"SUM(L17:$M$17)","v":8.4577843344979229},{"$f":"SUM(M17:$M$17)","v":3.9737364578501611}]
[null,null,"Outstaning debt at Period End","[$m]",null,null,20.100000000000001,15.87,13.44,10.210000000000001,7.03,3.47]
[null,null,"LLCR","[#]",null,null,{"$f":"+G21/G22","v":1.483844601951559},1.879349495855472,1.7585416896391477,1.8285779750951503,1.9652168144778412,2.437401825503724]
[]
[null,"[iii]","Present Vaue of CFADS (formula as per Sheet)","[$m]",null,null,{"$f":"(H16+H25)/H14","v":29.825276499226334},{"$f":"(I16+I25)/I14","v":24.816540324187653},{"$f":"(J16+J25)/J14","v":20.681450042017971},{"$f":"(K16+K25)/K14","v":16.222337044539053},{"$f":"(L16+L25)/L14","v":10.576788952434089},{"$f":"(M16+N17)/M14","v":5.317164179104477}]
[null,null,"Outstaning debt at Period End","[$m]",null,null,20.100000000000001,15.87,13.44,10.210000000000001,7.03,3.47]
[null,null,"LLCR","[#]",null,null,{"$f":"+G25/G26","v":1.4838446019515588},1.563739150862486,1.5387983662215754,1.5888674872222381,1.5045218993505105,1.5323239709234804]
[]
[null,null,"Compound Interest Rate Table"]
[null,null,null,null,null,null,{"$f":"G12","v":0},1,2,3,4,5,6]
[null,null,null,null,null,0,{"$f":"IF(G$30<=$F31,1,F31*(1+G$13))","v":1},1.05,1.10775,1.174215,1.250538975,1.3380767032500001,1.4344182258840001]
[null,null,null,null,null,1,1,1,1.0549999999999999,1.1183000000000001,1.1909894999999999,1.2743587649999999,1.36611259608]
[null,null,null,null,null,2,1,1,1,1.06,1.1289,1.2079230000000001,1.2948934560000001]
[null,null,null,null,null,3,1,1,1,1,1.0649999999999999,1.1395500000000001,1.2215976000000002]
[null,null,null,null,null,4,1,1,1,1,1,1.07,1.1470400000000001]
[null,null,null,null,null,5,1,1,1,1,1,1,1.0720000000000001]
[]
[null,null,"Forward LLCR"]
[null,null,"CFADS",null,null,null,{"$f":"G16","v":0},6.5,5.5,5.7,6.7,6,5.7]
[null,null,"Discounted with Vintage Rate",null,null,null,{"$f":"SUMPRODUCT(G39:M39/G31:M31)","v":29.825276499226337},{"$f":"SUMPRODUCT(H39:M39/H32:M32)","v":31.316540324187653},{"$f":"SUMPRODUCT(I39:M39/I33:M33)","v":26.181450042017975},{"$f":"SUMPRODUCT(J39:M39/J34:M34)","v":21.922337044539052},{"$f":"SUMPRODUCT(K39:M39/K35:M35)","v":17.276788952434089},{"$f":"SUMPRODUCT(L39:M39/L36:M36)","v":11.317164179104477}]
[]
[null,null,"Original",null,null,null,{"$f":"G21","v":29.825276499226337},29.825276499226337,23.634800308750144,18.669781125721485,13.815474205779225,8.4577843344979229,3.9737364578501611]
[null,null,"Adjusted with Multiplying by Discount Factor",null,null,null,{"$f":"G42*G31","v":29.825276499226337},31.316540324187656,26.181450042017971,21.922337044539052,17.276788952434092,11.317164179104477,5.6999999999999993]

{"sheet":"No Construction Period","index":3,"n_rows":25,"n_cols":18,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,null,1,{"$f":"D2+1","v":2},{"$f":"E2+1","v":3},4,5,6,7,8,9,10,11,12,13,14,15]
[]
[null,null,"Cash Flow",230,{"$f":"D4*0.95","v":218.5},{"$f":"E4*0.95","v":207.57499999999999},197.19624999999999,187.33643749999999,177.96961562499999,169.07113484374997,160.61757810156246,152.58669919648432,144.95736423666008,137.70949602482708,130.8240212235857,124.28282016240641,118.06867915428609,112.16524519657177]
[]
[null,null,"Debt Balance",{"$f":"D7>0","v":true},{"$f":"E7>0","v":true},true,true,true,true,true,true,true,true,false,false,false,false,false]
[null,null,"Opening",1000,{"$f":"D9","v":900},{"$f":"E9","v":800},700,600,500,400,300,200,100,0,0,0,0,0]
[null,null,"Less: Repayment",{"$f":"100","v":100},{"$f":"100","v":100},{"$f":"100","v":100},{"$f":"100","v":100},{"$f":"100","v":100},{"$f":"100","v":100},{"$f":"100","v":100},{"$f":"100","v":100},{"$f":"100","v":100},{"$f":"100","v":100}]
[null,null,"Closing Balance",{"$f":"D7-D8","v":900},{"$f":"E7-E8","v":800},{"$f":"F7-F8","v":700},600,500,400,300,200,100,0,0,0,0,0,0]
[]
[null,null,"Interest Rate",0.1,0.1,0.1,0.08,0.08,0.08,0.08,0.08,0.08,0.08,0.08,0.08,0.08,0.08,0.08]
[null,null,"Interest Expense",{"$f":"D7*D11","v":100},{"$f":"E7*E11","v":90},80,56,48,40,32,24,16,8,0,0,0,0,0]
[]
[null,null,"Debt Service",{"$f":"D12+D8","v":200},{"$f":"E12+E8","v":190},{"$f":"F12+F8","v":180},156,148,140,132,124,116,108,0,0,0,0,0]
[]
[null,null,"DSCRA",{"$f":"IF(D14,D4/D14,\"\")","v":1.1499999999999999},1.1499999999999999,1.1531944444444444,1.2640785256410256,1.2657867398648648,1.2712115401785713,1.2808419306344696,1.2953030492061488,1.3154025792800372,1.342197817006112,"","","","",""]
[]
[null,null,"IRR on Debt",9.1546835309757185E-2,8.896442269939836E-2,8.5312523561774906E-2,7.9999999999999974E-2,7.9999999999999974E-2,7.9999999999999918E-2,8.0000000000000016E-2,7.9999999999999946E-2,7.9999999999999918E-2,8.0000001960282952E-2,0.1,0.1,0.1,0.1,0.1]
[null,null,"NPV on Debt",{"$f":"NPV(D18,-D7,D14:R14)","v":-6.6396930212310389E-13},{"$f":"NPV(E18,-E7,E14:S14)","v":-2.8057241357754034E-13},{"$f":"NPV(F18,-F7,F14:T14)","v":2.6187580824304634E-14},{"$f":"NPV(G18,-G7,G14:U14)","v":-9.2107391672605572E-14},-3.9474596431116676E-14,2.2368937977632786E-13,-3.9474596431116676E-14,7.8949192862233364E-14,3.9474596431116682E-14,-1.6806265977987188E-7,0,0,0,0,0]
[]
[null,null,"PV Factor",{"$f":"1/(1+D18)^D2","v":0.91613109731221176},0.84328158671371123,0.78223196108131399,0.73502985279645328,0.68058319703375303,0.6301696268831054,0.58349039526213387,0.54026888450197663,0.50024896713145994,0.46319347967736707,0.3504938994813922,0.31863081771035656,0.28966437973668779,0.26333125430607973,0.23939204936916339]
[]
[null,null,"PV of Debt Service",{"$f":"NPV(D18,D14:R14*D6:R6)","v":999.99999999999932},{"$f":"NPV(E18,E14:S14*E6:S6)","v":899.99999999999977},{"$f":"NPV(F18,F14:T14*F6:T6)","v":800},{"$f":"NPV(G18,G14:U14*G6:U6)","v":699.99999999999989},{"$f":"NPV(H18,H14:V14*H6:V6)","v":600},{"$f":"NPV(I18,I14:W14*I6:W6)","v":500.00000000000017},{"$f":"NPV(J18,J14:X14*J6:X6)","v":400},{"$f":"NPV(K18,K14:Y14*K6:Y6)","v":300.00000000000011},{"$f":"NPV(L18,L14:Z14*L6:Z6)","v":200.00000000000003},{"$f":"NPV(M18,M14:AA14*M6:AA6)","v":99.999999818492327},{"$f":"NPV(N11,N14:AA14*N6:AA6)","v":0},{"$f":"NPV(O11,O14:AB14*O6:AB6)","v":0},{"$f":"NPV(P11,P14:AC14*P6:AC6)","v":0},{"$f":"NPV(Q11,Q14:AD14*Q6:AD6)","v":0},{"$f":"NPV(R11,R14:AE14*R6:AE6)","v":0}]
[null,null,"PV of Cash Flow",{"$f":"NPV(D11,D4:Q4*D6:Q6)","v":1179.3801861127001},{"$f":"NPV(E11,E4:R4*E6:R6)","v":1067.3182047239702},{"$f":"NPV(F11,F4:S4*F6:S6)","v":955.55002519636753},{"$f":"NPV(G11,G4:T4*G6:T6)","v":898.80062871634073},{"$f":"NPV(H11,H4:U4*H6:U6)","v":773.50842901364797},{"$f":"NPV(I11,I4:V4*I6:V6)","v":648.05266583473986},{"$f":"NPV(J11,J4:W4*J6:W6)","v":521.92726347651922},{"$f":"NPV(K11,K4:X4*K6:X6)","v":394.6103097108907},{"$f":"NPV(L11,L4:Y4*L6:Y6)","v":265.56155638619953},{"$f":"NPV(M11,M4:Z4*M6:Z6)","v":134.21978170061118},{"$f":"NPV(N11,N4:AA4*N6:AA6)","v":0},{"$f":"NPV(O11,O4:AB4*O6:AB6)","v":0},{"$f":"NPV(P11,P4:AC4*P6:AC6)","v":0},{"$f":"NPV(Q11,Q4:AD4*Q6:AD6)","v":0},{"$f":"NPV(R11,R4:AE4*R6:AE6)","v":0}]
[null,null,"LLCR",{"$f":"IF(D23,D24/D23,\"\")","v":1.1793801861127009},{"$f":"IF(E23,E24/E23,\"\")","v":1.1859091163599671},1.1944375314954594,1.2840008981662012,1.2891807150227466,1.2961053316694793,1.3048181586912981,1.3153676990363019,1.3278077819309975,1.3421978194423039,"","","","",""]