{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Disclaimer","Title","Inputs","Calcs"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":1,"defined_names":49,"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":"Disclaimer","index":0,"n_rows":35,"n_cols":20,"rows_kept":35,"cut":false,"hidden":true,"merged":[]}
[]
[null,"Legal disclaimer"]
[null,"Please fully read this disclaimer before using this model"]
[]
[]
[]
[null,"\n\n"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,"www.lentransolutions.com",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Level 8, Suite 3, 234 George Street, NSW 2000"]
[null,null,null,null,"contact@lentransolutions.com",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"ABN: 66 615 474 523"]

{"sheet":"Title","index":1,"n_rows":35,"n_cols":20,"rows_kept":35,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,{"$f":"Model_Name","v":"Project Finance Ratios"}]
[null,null,"LMS Tutorial"]
[null,null,"Sydney, February 2017"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,"www.lentransolutions.com",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Level 8, Suite 3, 234 George Street, NSW 2000"]
[null,null,null,null,"contact@lentransolutions.com",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"ABN: 66 615 474 523"]

{"sheet":"Inputs","index":2,"n_rows":25,"n_cols":14,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[null,null,"Assumptions"]
[null,null,{"$f":"\"Tutorial: \"&Model_Name","v":"Tutorial: Project Finance Ratios"}]
[]
[]
[]
[null,null,"Tutorial inputs"]
[null,null,"Operations"]
[null,null,"Year",null,1,{"$f":"E8+1","v":2},{"$f":"F8+1","v":3},4,5,6,7,{"$f":"K8+1","v":8},9,10]
[null,null,"Cashflow available for debt service (CFADS)","AUD '000",130,128,115,132,137,126,135]
[]
[null,null,"Debt"]
[null,null,"Remaining loan life","Years",5]
[null,null,"Opening balance","AUD '000",300]
[]
[null,null,"Year",null,1,{"$f":"E15+1","v":2},{"$f":"F15+1","v":3},4,5,6,7,{"$f":"K15+1","v":8},9,10]
[null,null,"Principal repayment","AUD '000",80,70,60,50,40]
[null,null,"All in rate","% p.a",0.06,0.06,0.06,0.06,0.06,0.06,0.06,0.06,0.06,0.06]
[]
[null,null,"Ratios"]
[null,null,"Remaining project life","Years",7]
[null,null,"Discount rate (post loan life)","% p.a",7.0000000000000007E-2]
[]
[null,null,"Technical inputs"]
[null,null,"General"]
[null,null,"Tutorial name","Name","Project Finance Ratios"]

{"sheet":"Calcs","index":3,"n_rows":54,"n_cols":17,"rows_kept":54,"cut":false,"hidden":false,"merged":[]}
[null,null,"Calculations"]
[null,null,{"$f":"\"Tutorial: \"&Model_Name","v":"Tutorial: Project Finance Ratios"}]
[null,null,"Forecast year","Num",null,null,null,{"$f":"G3+1","v":1},{"$f":"H3+1","v":2},3,4,5,6,7,{"$f":"N3+1","v":8},{"$f":"O3+1","v":9},{"$f":"P3+1","v":10}]
[]
[null,null,"Key calculations"]
[null,null,"Debt repayments"]
[null,null,"CFADS","AUD M",null,null,{"$f":"SUM(H7:Q7)","v":300},{"$f":"LOOKUP(H$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)","v":80},{"$f":"LOOKUP(I$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)","v":70},{"$f":"LOOKUP(J$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)","v":60},{"$f":"LOOKUP(K$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)","v":50},{"$f":"LOOKUP(L$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)","v":40},{"$f":"LOOKUP(M$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)","v":0},{"$f":"LOOKUP(N$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)","v":0},{"$f":"LOOKUP(O$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)","v":0},{"$f":"LOOKUP(P$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)","v":0},{"$f":"LOOKUP(Q$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)","v":0}]
[]
[null,null,"Remaining loan life","B.Flag",{"$f":"Inputs!E12","v":5},null,null,{"$f":"IF(H$3<=$E9,1,0)","v":1},{"$f":"IF(I$3<=$E9,1,0)","v":1},1,1,1,0,0,0,0,0]
[]
[null,null,"Balance b/f","AUD M",null,null,null,{"$f":"G13","v":300},{"$f":"H13","v":220},150,90,40,0,0,0,0,0]
[null,null,"Principal repayment","AUD M",null,null,{"$f":"SUM(H12:Q12)","v":-300},{"$f":"-LOOKUP(H$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)*H9","v":-80},{"$f":"-LOOKUP(I$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)*I9","v":-70},{"$f":"-LOOKUP(J$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)*J9","v":-60},{"$f":"-LOOKUP(K$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)*K9","v":-50},{"$f":"-LOOKUP(L$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)*L9","v":-40},{"$f":"-LOOKUP(M$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)*M9","v":0},{"$f":"-LOOKUP(N$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)*N9","v":0},{"$f":"-LOOKUP(O$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)*O9","v":0},{"$f":"-LOOKUP(P$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)*P9","v":0},{"$f":"-LOOKUP(Q$3,Inputs!$E$15:$N$15,Inputs!$E16:$N16)*Q9","v":0}]
[null,null,"Balance c/f","AUD M",null,null,{"$f":"Inputs!E13","v":300},{"$f":"SUM(H11:H12)","v":220},{"$f":"SUM(I11:I12)","v":150},90,40,0,0,0,0,0,0]
[]
[null,null,"Interest"]
[null,null,"All in rate","% p.a",null,null,null,{"$f":"LOOKUP(H$3,Inputs!$E$15:$N$15,Inputs!$E17:$N17)*H9","v":0.06},{"$f":"LOOKUP(I$3,Inputs!$E$15:$N$15,Inputs!$E17:$N17)*I9","v":0.06},{"$f":"LOOKUP(J$3,Inputs!$E$15:$N$15,Inputs!$E17:$N17)*J9","v":0.06},{"$f":"LOOKUP(K$3,Inputs!$E$15:$N$15,Inputs!$E17:$N17)*K9","v":0.06},{"$f":"LOOKUP(L$3,Inputs!$E$15:$N$15,Inputs!$E17:$N17)*L9","v":0.06},{"$f":"LOOKUP(M$3,Inputs!$E$15:$N$15,Inputs!$E17:$N17)*M9","v":0},{"$f":"LOOKUP(N$3,Inputs!$E$15:$N$15,Inputs!$E17:$N17)*N9","v":0},{"$f":"LOOKUP(O$3,Inputs!$E$15:$N$15,Inputs!$E17:$N17)*O9","v":0},{"$f":"LOOKUP(P$3,Inputs!$E$15:$N$15,Inputs!$E17:$N17)*P9","v":0},{"$f":"LOOKUP(Q$3,Inputs!$E$15:$N$15,Inputs!$E17:$N17)*Q9","v":0}]
[null,null,"Interest","AUD M",null,null,{"$f":"SUM(H17:Q17)","v":48},{"$f":"H11*H16","v":18},{"$f":"I11*I16","v":13.2},9,5.3999999999999995,2.4,0,0,0,0,0]
[]
[null,null,"DSCR"]
[null,null,"CFADS","AUD M",null,null,{"$f":"SUM(H20:Q20)","v":903},{"$f":"LOOKUP(H$3,Inputs!$E$8:$N$8,Inputs!$E9:$N9)","v":130},{"$f":"LOOKUP(I$3,Inputs!$E$8:$N$8,Inputs!$E9:$N9)","v":128},{"$f":"LOOKUP(J$3,Inputs!$E$8:$N$8,Inputs!$E9:$N9)","v":115},{"$f":"LOOKUP(K$3,Inputs!$E$8:$N$8,Inputs!$E9:$N9)","v":132},{"$f":"LOOKUP(L$3,Inputs!$E$8:$N$8,Inputs!$E9:$N9)","v":137},{"$f":"LOOKUP(M$3,Inputs!$E$8:$N$8,Inputs!$E9:$N9)","v":126},{"$f":"LOOKUP(N$3,Inputs!$E$8:$N$8,Inputs!$E9:$N9)","v":135},{"$f":"LOOKUP(O$3,Inputs!$E$8:$N$8,Inputs!$E9:$N9)","v":0},{"$f":"LOOKUP(P$3,Inputs!$E$8:$N$8,Inputs!$E9:$N9)","v":0},{"$f":"LOOKUP(Q$3,Inputs!$E$8:$N$8,Inputs!$E9:$N9)","v":0}]
[null,null,"Qualifying CFADS","AUD M",null,null,{"$f":"SUM(H21:Q21)","v":642},{"$f":"H9*H20","v":130},{"$f":"I9*I20","v":128},115,132,137,0,0,0,0,0]
[]
[null,null,"Interest","AUD M",null,null,{"$f":"SUM(H23:Q23)","v":48},{"$f":"H17","v":18},13.2,9,5.3999999999999995,2.4,0,0,0,0,0]
[null,null,"Principal repayment","AUD M",null,null,300,{"$f":"-H12","v":80},70,60,50,40,0,0,0,0,0]
[null,null,"Total debt service","AUD M",null,null,347.99999999999994,{"$f":"SUM(H23:H24)","v":98},{"$f":"SUM(I23:I24)","v":83.2},69,55.4,42.4,0,0,0,0,0]
[null,null,null,null,"Min","Average"]
[null,null,"Debt service cover ratio (DSCR)","x",{"$f":"MIN(IF(H27:Q27<>0,H27:Q27))","v":1.3265306122448979},{"$f":"G21 / G25","v":1.8448275862068968},null,{"$f":"IF(H25=0,0,H20/H25)","v":1.3265306122448979},1.5384615384615383,1.6666666666666667,2.3826714801444044,3.2311320754716983,0,0,0,0,0]
[]
[null,null,"LLCR"]
[null,null,"Qualifying CFADS","AUD M",null,null,{"$f":"SUM(H30:Q30)","v":642},{"$f":"H21","v":130},128,115,132,137,0,0,0,0,0]
[]
[null,null,"All in rate","% p.a",null,null,null,{"$f":"H16","v":0.06},0.06,0.06,0.06,0.06,0,0,0,0,0]
[null,null,"1+r","Factor",null,null,null,{"$f":"1+H32","v":1.06},{"$f":"1+I32","v":1.06},1.06,1.06,1.06,1,1,1,1,1]
[]
[null,null,"NPV of CFADS (start of period)","AUD M",null,null,null,{"$f":"(H30+I35)/H33","v":540.04800453456824},{"$f":"(I30+J35)/I33","v":442.45088480664225},340.99793789504082,246.45781416874328,129.24528301886792,0,0,0,0,0]
[]
[null,null,"Opening debt balance","AUD M",null,null,null,{"$f":"H$11","v":300},{"$f":"I$11","v":220},150,90,40,0,0,0,0,0]
[null,null,null,null,"Min"]
[null,null,"Loan life coverage ratio (LLCR)","x",{"$f":"MIN(IF(H39:Q39<>0,H39:Q39))","v":1.8001600151152275},null,null,{"$f":"IF(H37=0,0,H35/H37)","v":1.8001600151152275},{"$f":"IF(I37=0,0,I35/I37)","v":2.0111403854847376},2.2733195859669388,2.7384201574304807,3.2311320754716979,0,0,0,0,0]
[]
[null,null,"PLCR"]
[null,null,"Project life","B.Flag",{"$f":"Inputs!E20","v":7},null,null,{"$f":"IF(H$3<=$E42,1,0)","v":1},{"$f":"IF(I$3<=$E42,1,0)","v":1},1,1,1,1,1,0,0,0]
[null,null,"Qualifying CFADS","AUD M",null,null,null,{"$f":"H20*H42","v":130},128,115,132,137,126,135,0,0,0]
[]
[null,null,"All in rate","% p.a",null,null,null,{"$f":"H32","v":0.06},{"$f":"I32","v":0.06},0.06,0.06,0.06,0,0,0,0,0]
[null,null,{"$f":"Inputs!C21","v":"Discount rate (post loan life)"},"% p.a",{"$f":"Inputs!E21","v":7.0000000000000007E-2},null,null,{"$f":"IF(H45>0,0,$E46)*H42","v":0},{"$f":"IF(I45>0,0,$E46)*I42","v":0},0,0,0,7.0000000000000007E-2,7.0000000000000007E-2,0,0,0]
[null,null,"Discount rate (applied)","% p.a",null,null,null,{"$f":"SUM(H45:H46)","v":0.06},{"$f":"SUM(I45:I46)","v":0.06},0.06,0.06,0.06,7.0000000000000007E-2,7.0000000000000007E-2,0,0,0]
[null,null,"1+r","Factor",null,null,null,{"$f":"1+H47","v":1.06},{"$f":"1+I47","v":1.06},1.06,1.06,1.06,1.07,1.07,1,1,1]
[]
[null,null,"NPV of CFADS (start of period)","AUD M",null,null,null,{"$f":"(H43+I50)/H48","v":716.15526298746306},{"$f":"(I43+J50)/I48","v":629.1245787667109},538.87205349271358,456.20437670227648,351.57663930441311,235.67123766267792,126.16822429906541,0,0,0]
[]
[null,null,"Debt balance (balance b/f)","AUD M",null,null,null,{"$f":"H$11","v":300},{"$f":"I$11","v":220},150,90,40,0,0,0,0,0]
[null,null,null,null,"Min"]
[null,null,"Project life coverage ratio (PLCR)","x",{"$f":"MIN(IF(H54:Q54<>0,H54:Q54))","v":2.3871842099582103},null,null,{"$f":"IF(H52=0,0,H50/H52)","v":2.3871842099582103},{"$f":"IF(I52=0,0,I50/I52)","v":2.8596571762123224},3.5924803566180903,5.0689375189141828,8.7894159826103273,0,0,0,0,0]