{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Basic Points","Exercise to Complete","Basic Exercise","DSRA No Cash Sweep","DSRA with  Sweep"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":2,"defined_names":29,"comments":2,"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":"Basic Points","index":0,"n_rows":7,"n_cols":3,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,null,"Find DSRA in Sources and Uses Statement"]
[]
[null,null,"Use OFFSET and SUM for Computing the DSRA Balance"]
[]
[null,null,"Use MAX and MIN in Cash Flow for Withdrawls from DSRA"]

{"sheet":"Exercise to Complete","index":1,"n_rows":74,"n_cols":48,"rows_kept":74,"cut":false,"hidden":false,"merged":[]}
["Inputs"]
[null,"Operating Inputs"]
[null,null,"EPC Cost",null,1300,null,null,"Equity IRR",{"$f":"G74","v":0.11987474167401357}]
[null,null,"Initital Cash Flow",null,125,null,null,"Project IRR"]
[null,null,"Sensitivity Factor",null,{"$f":"F5/100","v":1},100]
[null,null,"Sensitivity Year",null,5,null,null,"Cash Flow Test"]
[null,null,"Life (Periods)",null,40]
[]
[null,"Financial Inputs"]
[null,null,"Debt Commitment",null,800]
[null,null,"Interest Rate (Periodic)",null,0.02]
[null,null,"Debt Term (Periods)",null,34]
[null,null,"Debt Reserve Months",null,12]
[null,null,"Interest Rate on Reserve",null,5.0000000000000001E-3]
[null,null,"Cash Sweep",null,0.5]
[]
["Periods"]
[null,"Semi-Annual Period",null,null,null,null,null,1,{"$f":"H18+1","v":2},{"$f":"I18+1","v":3},4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41]
[null,"Construction Switch",null,null,null,null,null,{"$f":"H18<1","v":false},{"$f":"I18<1","v":false},false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false]
[null,"Operating Switch",null,null,null,null,null,{"$f":"AND(H18>0,H18<=Life)","v":true},true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,false]
[null,"Sensitivity Switch",null,null,null,null,null,{"$f":"H18>=Sensitivity_Year","v":false},false,false,false,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true]
[null,"Debt Repayment",null,null,{"$f":"Debt_Term","v":34},null,null,{"$f":"H18<=$E$22","v":true},{"$f":"I18<=$E$22","v":true},true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,false,false,false,false,false,false,false]
[]
["Working"]
[null,"EBITDA",null,null,null,null,null,{"$f":"H20*Initital_Cash_Flow","v":125},125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,0]
[null,"Sensitivity Factor",null,null,null,null,null,{"$f":"IF(H21,Sensitivity_Factor,1)","v":1},1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1]
[null,"Cash Flow After Sensitivity",null,null,null,null,null,{"$f":"H26*H25","v":125},{"$f":"I26*I25","v":125},125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,0]
[]
["Uses and Sources"]
[null,"Uses"]
[null,null,"EPC Cost",null,null,null,{"$f":"EPC_Cost","v":1300}]
[null,null,"DSRA - From Below",null,null,null,{"$f":"G49","v":77.70237835354942}]
[null,null,null,"Total",null,null,{"$f":"G31+G32","v":1377.7023783535494}]
[null,"Sources"]
[null,null,"Debt",null,null,null,{"$f":"Debt_Commitment","v":800}]
[null,null,"Equity",null,null,null,{"$f":"G33-G35","v":577.70237835354942}]
[null,null,null,"Total",null,null,{"$f":"G35+G36","v":1377.7023783535494}]
[]
["Debt and Reserve Schedule"]
[null,"Debt Balance"]
[null,null,"Opening Balalnce",null,null,null,null,{"$f":"G44","v":800},{"$f":"H44","v":732.17774120688284},663.67030351196649,594.47076484960712,524.57213322351788,453.96734599908666,382.64926907063273,310.61068434760222,237.84312158168649,164.22128655572507,78.062113562189836,-4.0785153032629751E-12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Less: Repayment",null,{"$f":"Debt_Term","v":34},null,null,{"$f":"MIN($E$44/$E$42,H41)","v":23.529411764705884},{"$f":"MIN($E$44/$E$42,I41)","v":23.529411764705884},23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,-4.0785153032629751E-12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Less: Cash Sweep",null,null,null,null,{"$f":"H70","v":44.292847028411266},{"$f":"I70","v":44.978025930210457},45.670126897653489,46.369219861383378,47.075375459725393,47.78866516374805,48.50917295832464,49.2381510012098,50.092423261255576,62.629761228827796,54.532701797482034,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Closing Balance",null,{"$f":"Debt_Commitment","v":800},null,{"$f":"E44","v":800},{"$f":"H41-H42-H43","v":732.17774120688284},{"$f":"I41-I42-I43","v":663.67030351196649},594.47076484960712,524.57213322351788,453.96734599908666,382.64926907063273,310.61068434760222,237.84312158168649,164.22128655572504,78.062113562191399,1.9184653865522705E-12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Interest",null,{"$f":"Interest_Rate","v":0.02},null,null,{"$f":"H41*$E$45","v":16},{"$f":"I41*$E$45","v":14.643554824137658},13.27340607023933,11.889415296992143,10.491442664470357,9.0793469199817327,7.6529853814126545,6.2122136869520448,4.7568624316337296,3.2844257311145015,1.5612422712437968,-8.1570306065259504E-14,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[]
[null,"DSRA Balance"]
[null,null,"Debt Service - FROM DEBT SCHEDULE ABOVE",null,null,null,null,{"$f":"H45+H42","v":39.529411764705884},{"$f":"I45+I42","v":38.172966588843543},36.802817834945216,35.418827061698025,34.020854429176239,32.608758684687615,31.182397146118539,29.741625451657928,28.286274196339612,26.813837495820387,25.090654035949679,-4.1600856093282347E-12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Required DSRA Balance - USE SUM AND OFFSET",null,{"$f":"reserve_months/6","v":2},null,{"$f":"SUM(OFFSET(G48,0,1,1,$E$49))","v":77.70237835354942},{"$f":"SUM(OFFSET(H48,0,1,1,$E$49))*H22","v":74.975784423788753},{"$f":"SUM(OFFSET(I48,0,1,1,$E$49))*I22","v":72.221644896643241},69.439681490874264,66.629613113863854,63.791155830806154,60.924022597776471,58.027899647997543,55.100111692159999,51.904491531770063,25.090654035945519,-4.1600856093282347E-12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Less: Amount Already Funded - OPENING BALANCE",null,null,null,null,{"$f":"H53","v":77.70237835354942},{"$f":"I53","v":74.975784423788753},72.221644896643241,69.439681490874264,66.629613113863854,63.791155830806154,60.924022597776471,58.027899647997543,55.100111692159999,51.904491531770113,25.090654035949871,-8.0042639183375286E-12,1.7290346931986276E-12,2.7207676112084299E-11,-2.0220767282808081E-11,-7.1905930099092079E-11,1.2266553994777496E-10,1.3642917906508959E-10,-5.6646044299668575E-10,2.5153497063001909E-11,2.0324860372511465E-9,-1.8440054816437626E-9,-5.3231104857331953E-9,1.0216253230055371E-8,9.6791552550712351E-9,-4.1653098463712006E-8,-1.1905441965609743E-9,1.5190406301712761E-7,-1.32672283541524E-7,-4.1946791270675014E-7,8.4605004630589038E-7,6.3128084917707434E-7,-3.2052100547730341E-6,2.8548155356134055E-7,1.0529815614006565E-5,-8.3299782912149549E-6,-3.2980761175593909E-5,6.21711847202278E-5,5.7980230316161295E-5,-2.7273764545455471E-4,7.5092456844032268E-5]
[null,null,"Required Net Additional Funding",null,null,null,null,{"$f":"H49-H50","v":-2.7265939297606678},{"$f":"I49-I50","v":-2.7541395271455116},-2.7819634057689768,-2.8100683770104098,-2.8384572830577,-2.8671332330296835,-2.8961229497789276,-2.9277879558375446,-3.1956201603899359,-26.813837495824593,-25.090654035954032,8.0042639183375286E-12,-1.7290346931986276E-12,-2.7207676112084299E-11,2.0220767282808081E-11,7.1905930099092079E-11,-1.2266553994777496E-10,-1.3642917906508959E-10,5.6646044299668575E-10,-2.5153497063001909E-11,-2.0324860372511465E-9,1.8440054816437626E-9,5.3231104857331953E-9,-1.0216253230055371E-8,-9.6791552550712351E-9,4.1653098463712006E-8,1.1905441965609743E-9,-1.5190406301712761E-7,1.32672283541524E-7,4.1946791270675014E-7,-8.4605004630589038E-7,-6.3128084917707434E-7,3.2052100547730341E-6,-2.8548155356134055E-7,-1.0529815614006565E-5,8.3299782912149549E-6,3.2980761175593909E-5,-6.21711847202278E-5,-5.7980230316161295E-5,2.7273764545455471E-4,-7.5092456844032268E-5]
[]
[null,null,"Opening Balance",null,null,null,null,{"$f":"G56","v":77.70237835354942},{"$f":"H56","v":74.975784423788753},72.221644896643241,69.439681490874264,66.629613113863854,63.791155830806154,60.924022597776471,58.027899647997543,55.100111692159999,51.904491531770034,25.090654035952983,7.1196382123162039E-12,-1.0867751143450732E-11,-1.1916938547074097E-11,4.854513235841295E-11,3.19914761348628E-12,-1.5775867723277771E-10,1.191807541545131E-10,4.4258300135879844E-10,-8.4091141161479761E-10,-8.460958156319975E-10,3.7698688970522199E-9,-3.1003722256173237E-10,-1.2272843576965897E-8,1.0523681766017033E-8,3.2525757092116687E-8,-6.0474453107417481E-8,-6.748348596374859E-8,2.7915509846820908E-7,-1.09959606220401E-8,-9.7686303220712016E-7,9.4004742635433959E-7,2.3486964151686615E-6,-4.7924922149888985E-6,-4.272189414251669E-6,1.9190255002285995E-5,1.9264608035030108E-6,-7.4835666688244403E-5,6.5215984047029089E-5,2.0199381881980095E-4,-4.2339528293567209E-4]
[null,null,"Add: Net Addition Funding",null,null,null,null,{"$f":"H51","v":-2.7265939297606678},{"$f":"I51","v":-2.7541395271455116},-2.7819634057689768,-2.8100683770104098,-2.8384572830577,-2.8671332330296835,-2.8961229497789276,-2.9277879558375446,-3.1956201603899359,-26.813837495824593,-25.090654035954032,8.0042639183375286E-12,-1.7290346931986276E-12,-2.7207676112084299E-11,2.0220767282808081E-11,7.1905930099092079E-11,-1.2266553994777496E-10,-1.3642917906508959E-10,5.6646044299668575E-10,-2.5153497063001909E-11,-2.0324860372511465E-9,1.8440054816437626E-9,5.3231104857331953E-9,-1.0216253230055371E-8,-9.6791552550712351E-9,4.1653098463712006E-8,1.1905441965609743E-9,-1.5190406301712761E-7,1.32672283541524E-7,4.1946791270675014E-7,-8.4605004630589038E-7,-6.3128084917707434E-7,3.2052100547730341E-6,-2.8548155356134055E-7,-1.0529815614006565E-5,8.3299782912149549E-6,3.2980761175593909E-5,-6.21711847202278E-5,-5.7980230316161295E-5,2.7273764545455471E-4,-7.5092456844032268E-5]
[null,null,"Less: Withdrawls for Deficit CF - FROM CASH FLOW WATERFALL",null,null,null,null,{"$f":"H68","v":0},{"$f":"I68","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Closing Balance ",null,null,null,{"$f":"G32","v":77.70237835354942},{"$f":"H53+H54-H55","v":74.975784423788753},{"$f":"I53+I54-I55","v":72.221644896643241},69.439681490874264,66.629613113863854,63.791155830806154,60.924022597776471,58.027899647997543,55.100111692159999,51.904491531770063,25.090654035945441,-1.0480505352461478E-12,1.5123902130653732E-11,-1.259678583664936E-11,-3.9124614659158398E-11,6.8765899641221031E-11,7.5105077712578365E-11,-2.8042421718055264E-10,-1.7248424910576484E-11,1.0090434443554842E-9,-8.6606490867779952E-10,-2.8785818528831442E-9,5.6138743786959827E-9,5.0130732631714626E-9,-2.2489096807021269E-8,8.4452651094579805E-10,7.4178855555828693E-8,-5.928390891085651E-8,-2.193875489808762E-7,4.1182738200973308E-7,4.0847195208471006E-7,-1.8229130785130105E-6,3.0876657717726526E-7,5.5539064699416956E-6,-5.0779737685502394E-6,-1.4802005028258234E-5,2.752023329350095E-5,3.4907221979096918E-5,-1.3700685140847222E-4,7.2357537308677936E-6,4.7473146427435566E-4,-4.9848773977970433E-4]
[null,null,"Interest Income",null,{"$f":"E14","v":5.0000000000000001E-3},null,null,{"$f":"$E$57*H53","v":0.38851189176774709},{"$f":"$E$57*I53","v":0.37487892211894375},0.36110822448321622,0.34719840745437131,0.33314806556931925,0.31895577915403078,0.30462011298888236,0.29013949823998775,0.27550055846080002,0.25952245765885018,0.12545327017976493,3.5598191061581018E-14,-5.4338755717253661E-14,-5.9584692735370479E-14,2.4272566179206475E-13,1.59957380674314E-14,-7.8879338616388859E-13,5.9590377077256552E-13,2.2129150067939924E-12,-4.2045570580739883E-12,-4.2304790781599873E-12,1.88493444852611E-11,-1.5501861128086618E-12,-6.1364217884829485E-11,5.2618408830085167E-11,1.6262878546058345E-10,-3.0237226553708741E-10,-3.3741742981874295E-10,1.3957754923410455E-9,-5.49798031102005E-11,-4.8843151610356006E-9,4.7002371317716982E-9,1.1743482075843308E-8,-2.3962461074944494E-8,-2.1360947071258346E-8,9.595127501142997E-8,9.6323040175150539E-9,-3.7417833344122202E-7,3.2607992023514545E-7,1.0099690940990048E-6,-2.1169764146783603E-6]
[]
["Cash Flow Waterfall"]
[null,null,"Operating Cash Flow",null,null,null,null,{"$f":"H27","v":125},{"$f":"I27","v":125},125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,0]
[null,null,"Add: Interest Income",null,null,null,null,{"$f":"H57","v":0.38851189176774709},{"$f":"I57","v":0.37487892211894375},0.36110822448321622,0.34719840745437131,0.33314806556931925,0.31895577915403078,0.30462011298888236,0.29013949823998775,0.27550055846080002,0.25952245765885018,0.12545327017976493,3.5598191061581018E-14,-5.4338755717253661E-14,-5.9584692735370479E-14,2.4272566179206475E-13,1.59957380674314E-14,-7.8879338616388859E-13,5.9590377077256552E-13,2.2129150067939924E-12,-4.2045570580739883E-12,-4.2304790781599873E-12,1.88493444852611E-11,-1.5501861128086618E-12,-6.1364217884829485E-11,5.2618408830085167E-11,1.6262878546058345E-10,-3.0237226553708741E-10,-3.3741742981874295E-10,1.3957754923410455E-9,-5.49798031102005E-11,-4.8843151610356006E-9,4.7002371317716982E-9,1.1743482075843308E-8,-2.3962461074944494E-8,-2.1360947071258346E-8,9.595127501142997E-8,9.6323040175150539E-9,-3.7417833344122202E-7,3.2607992023514545E-7,1.0099690940990048E-6,-2.1169764146783603E-6]
[null,null,null,"CFADS",null,null,null,{"$f":"SUM(H60:H61)","v":125.38851189176775},{"$f":"SUM(I60:I61)","v":125.37487892211894},125.36110822448322,125.34719840745437,125.33314806556932,125.31895577915402,125.30462011298889,125.29013949823998,125.27550055846081,125.25952245765885,125.12545327017976,125.00000000000004,124.99999999999994,124.99999999999994,125.00000000000024,125.00000000000001,124.9999999999992,125.0000000000006,125.00000000000222,124.99999999999579,124.99999999999577,125.00000000001884,124.99999999999845,124.99999999993864,125.00000000005262,125.00000000016263,124.99999999969762,124.99999999966258,125.00000000139578,124.99999999994502,124.99999999511569,125.00000000470024,125.00000001174348,124.99999997603754,124.99999997863905,125.00000009595128,125.0000000096323,124.99999962582167,125.00000032607991,125.0000010099691,-2.1169764146783603E-6]
[null,null,"Less: Scheduled Interest",null,null,null,null,{"$f":"H45","v":16},{"$f":"I45","v":14.643554824137658},13.27340607023933,11.889415296992143,10.491442664470357,9.0793469199817327,7.6529853814126545,6.2122136869520448,4.7568624316337296,3.2844257311145015,1.5612422712437968,-8.1570306065259504E-14,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Less: Scheduled Repayment",null,null,null,null,{"$f":"H42","v":23.529411764705884},{"$f":"I42","v":23.529411764705884},23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,-4.0785153032629751E-12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Net Cash Flow",null,null,null,{"$f":"H62-H63-H64","v":85.859100127061865},{"$f":"I62-I63-I64","v":87.201912333275402},{"$f":"J62-J63-J64","v":88.558290389538001},{"$f":"K62-K63-K64","v":89.928371345756346},{"$f":"L62-L63-L64","v":91.312293636393079},{"$f":"M62-M63-M64","v":92.710197094466409},{"$f":"N62-N63-N64","v":94.122222966870353},{"$f":"O62-O63-O64","v":95.548514046582056},{"$f":"P62-P63-P64","v":96.989226362121187},{"$f":"Q62-Q63-Q64","v":98.445684961838467},{"$f":"R62-R63-R64","v":100.03479923423008},{"$f":"S62-S63-S64","v":125.00000000000421},{"$f":"T62-T63-T64","v":124.99999999999994},{"$f":"U62-U63-U64","v":124.99999999999994},{"$f":"V62-V63-V64","v":125.00000000000024},{"$f":"W62-W63-W64","v":125.00000000000001},{"$f":"X62-X63-X64","v":124.9999999999992},{"$f":"Y62-Y63-Y64","v":125.0000000000006},{"$f":"Z62-Z63-Z64","v":125.00000000000222},{"$f":"AA62-AA63-AA64","v":124.99999999999579},{"$f":"AB62-AB63-AB64","v":124.99999999999577},{"$f":"AC62-AC63-AC64","v":125.00000000001884},{"$f":"AD62-AD63-AD64","v":124.99999999999845},{"$f":"AE62-AE63-AE64","v":124.99999999993864},{"$f":"AF62-AF63-AF64","v":125.00000000005262},{"$f":"AG62-AG63-AG64","v":125.00000000016263},{"$f":"AH62-AH63-AH64","v":124.99999999969762},{"$f":"AI62-AI63-AI64","v":124.99999999966258},{"$f":"AJ62-AJ63-AJ64","v":125.00000000139578},{"$f":"AK62-AK63-AK64","v":124.99999999994502},{"$f":"AL62-AL63-AL64","v":124.99999999511569},{"$f":"AM62-AM63-AM64","v":125.00000000470024},{"$f":"AN62-AN63-AN64","v":125.00000001174348},{"$f":"AO62-AO63-AO64","v":124.99999997603754},{"$f":"AP62-AP63-AP64","v":124.99999997863905},{"$f":"AQ62-AQ63-AQ64","v":125.00000009595128},{"$f":"AR62-AR63-AR64","v":125.0000000096323},{"$f":"AS62-AS63-AS64","v":124.99999962582167},{"$f":"AT62-AT63-AT64","v":125.00000032607991},{"$f":"AU62-AU63-AU64","v":125.0000010099691},{"$f":"AV62-AV63-AV64","v":-2.1169764146783603E-6}]
[null,null,"Add: Interest Income "]
[null,null,"Less: Net Added DSRA Funding",null,null,null,null,{"$f":"H51*H22","v":-2.7265939297606678},{"$f":"I51*I22","v":-2.7541395271455116},{"$f":"J51*J22","v":-2.7819634057689768},{"$f":"K51*K22","v":-2.8100683770104098},{"$f":"L51*L22","v":-2.8384572830577},{"$f":"M51*M22","v":-2.8671332330296835},{"$f":"N51*N22","v":-2.8961229497789276},{"$f":"O51*O22","v":-2.9277879558375446},{"$f":"P51*P22","v":-3.1956201603899359},{"$f":"Q51*Q22","v":-26.813837495824593},{"$f":"R51*R22","v":-25.090654035954032},{"$f":"S51*S22","v":8.0042639183375286E-12},{"$f":"T51*T22","v":-1.7290346931986276E-12},{"$f":"U51*U22","v":-2.7207676112084299E-11},{"$f":"V51*V22","v":2.0220767282808081E-11},{"$f":"W51*W22","v":7.1905930099092079E-11},{"$f":"X51*X22","v":-1.2266553994777496E-10},{"$f":"Y51*Y22","v":-1.3642917906508959E-10},{"$f":"Z51*Z22","v":5.6646044299668575E-10},{"$f":"AA51*AA22","v":-2.5153497063001909E-11},{"$f":"AB51*AB22","v":-2.0324860372511465E-9},{"$f":"AC51*AC22","v":1.8440054816437626E-9},{"$f":"AD51*AD22","v":5.3231104857331953E-9},{"$f":"AE51*AE22","v":-1.0216253230055371E-8},{"$f":"AF51*AF22","v":-9.6791552550712351E-9},{"$f":"AG51*AG22","v":4.1653098463712006E-8},{"$f":"AH51*AH22","v":1.1905441965609743E-9},{"$f":"AI51*AI22","v":-1.5190406301712761E-7},{"$f":"AJ51*AJ22","v":1.32672283541524E-7},{"$f":"AK51*AK22","v":4.1946791270675014E-7},{"$f":"AL51*AL22","v":-8.4605004630589038E-7},{"$f":"AM51*AM22","v":-6.3128084917707434E-7},{"$f":"AN51*AN22","v":3.2052100547730341E-6},{"$f":"AO51*AO22","v":-2.8548155356134055E-7},{"$f":"AP51*AP22","v":0},{"$f":"AQ51*AQ22","v":0},{"$f":"AR51*AR22","v":0},{"$f":"AS51*AS22","v":0},{"$f":"AT51*AT22","v":0},{"$f":"AU51*AU22","v":0},{"$f":"AV51*AV22","v":0}]
[null,null,"Add: Withdrawls from DSRA for Negative Cash Flow - USE MIN AND MAX",null,null,null,null,{"$f":"MIN(H48,MAX(-H65,0))","v":0},{"$f":"MIN(I48,MAX(-I65,0))","v":0},{"$f":"MIN(J48,MAX(-J65,0))","v":0},{"$f":"MIN(K48,MAX(-K65,0))","v":0},{"$f":"MIN(L48,MAX(-L65,0))","v":0},{"$f":"MIN(M48,MAX(-M65,0))","v":0},{"$f":"MIN(N48,MAX(-N65,0))","v":0},{"$f":"MIN(O48,MAX(-O65,0))","v":0},{"$f":"MIN(P48,MAX(-P65,0))","v":0},{"$f":"MIN(Q48,MAX(-Q65,0))","v":0},{"$f":"MIN(R48,MAX(-R65,0))","v":0},{"$f":"MIN(S48,MAX(-S65,0))","v":-4.1600856093282347E-12},{"$f":"MIN(T48,MAX(-T65,0))","v":0},{"$f":"MIN(U48,MAX(-U65,0))","v":0},{"$f":"MIN(V48,MAX(-V65,0))","v":0},{"$f":"MIN(W48,MAX(-W65,0))","v":0},{"$f":"MIN(X48,MAX(-X65,0))","v":0},{"$f":"MIN(Y48,MAX(-Y65,0))","v":0},{"$f":"MIN(Z48,MAX(-Z65,0))","v":0},{"$f":"MIN(AA48,MAX(-AA65,0))","v":0},{"$f":"MIN(AB48,MAX(-AB65,0))","v":0},{"$f":"MIN(AC48,MAX(-AC65,0))","v":0},{"$f":"MIN(AD48,MAX(-AD65,0))","v":0},{"$f":"MIN(AE48,MAX(-AE65,0))","v":0},{"$f":"MIN(AF48,MAX(-AF65,0))","v":0},{"$f":"MIN(AG48,MAX(-AG65,0))","v":0},{"$f":"MIN(AH48,MAX(-AH65,0))","v":0},{"$f":"MIN(AI48,MAX(-AI65,0))","v":0},{"$f":"MIN(AJ48,MAX(-AJ65,0))","v":0},{"$f":"MIN(AK48,MAX(-AK65,0))","v":0},{"$f":"MIN(AL48,MAX(-AL65,0))","v":0},{"$f":"MIN(AM48,MAX(-AM65,0))","v":0},{"$f":"MIN(AN48,MAX(-AN65,0))","v":0},{"$f":"MIN(AO48,MAX(-AO65,0))","v":0},{"$f":"MIN(AP48,MAX(-AP65,0))","v":0},{"$f":"MIN(AQ48,MAX(-AQ65,0))","v":0},{"$f":"MIN(AR48,MAX(-AR65,0))","v":0},{"$f":"MIN(AS48,MAX(-AS65,0))","v":0},{"$f":"MIN(AT48,MAX(-AT65,0))","v":0},{"$f":"MIN(AU48,MAX(-AU65,0))","v":0},{"$f":"MIN(AV48,MAX(-AV65,0))","v":0}]
[null,null,null,"Net Cash Flow",null,null,null,{"$f":"H65-H67+H68+H66","v":88.585694056822533},{"$f":"I65-I67+I68+I66","v":89.956051860420914},91.340253795306978,92.738439722766756,94.150750919450786,95.577330327496099,97.018345916649281,98.476302002419601,100.18484652251112,125.25952245766305,125.12545327018411,124.99999999999204,125.00000000000168,125.00000000002716,124.99999999998002,124.99999999992811,125.00000000012187,125.00000000013702,124.99999999943576,125.00000000002095,125.00000000202824,124.99999999817484,124.99999999467533,125.00000001015489,125.00000000973178,124.99999995850953,124.99999999850708,125.00000015156664,124.99999986872349,124.99999958047711,125.00000084116573,125.0000006359811,124.99999680653343,125.00000026151909,124.99999997863905,125.00000009595128,125.0000000096323,124.99999962582167,125.00000032607991,125.0000010099691,-2.1169764146783603E-6]
[null,null,"Less: Sweep",null,{"$f":"E15","v":0.5},null,null,{"$f":"MIN(H41-H42,MAX(H69,0)*$E$70)","v":44.292847028411266},{"$f":"MIN(I41-I42,MAX(I69,0)*$E$70)","v":44.978025930210457},{"$f":"MIN(J41-J42,MAX(J69,0)*$E$70)","v":45.670126897653489},{"$f":"MIN(K41-K42,MAX(K69,0)*$E$70)","v":46.369219861383378},{"$f":"MIN(L41-L42,MAX(L69,0)*$E$70)","v":47.075375459725393},{"$f":"MIN(M41-M42,MAX(M69,0)*$E$70)","v":47.78866516374805},{"$f":"MIN(N41-N42,MAX(N69,0)*$E$70)","v":48.50917295832464},{"$f":"MIN(O41-O42,MAX(O69,0)*$E$70)","v":49.2381510012098},{"$f":"MIN(P41-P42,MAX(P69,0)*$E$70)","v":50.092423261255561},{"$f":"MIN(Q41-Q42,MAX(Q69,0)*$E$70)","v":62.629761228831526},{"$f":"MIN(R41-R42,MAX(R69,0)*$E$70)","v":54.532701797483952},{"$f":"MIN(S41-S42,MAX(S69,0)*$E$70)","v":0},{"$f":"MIN(T41-T42,MAX(T69,0)*$E$70)","v":0},{"$f":"MIN(U41-U42,MAX(U69,0)*$E$70)","v":0},{"$f":"MIN(V41-V42,MAX(V69,0)*$E$70)","v":0},{"$f":"MIN(W41-W42,MAX(W69,0)*$E$70)","v":0},{"$f":"MIN(X41-X42,MAX(X69,0)*$E$70)","v":0},{"$f":"MIN(Y41-Y42,MAX(Y69,0)*$E$70)","v":0},{"$f":"MIN(Z41-Z42,MAX(Z69,0)*$E$70)","v":0},{"$f":"MIN(AA41-AA42,MAX(AA69,0)*$E$70)","v":0},{"$f":"MIN(AB41-AB42,MAX(AB69,0)*$E$70)","v":0},{"$f":"MIN(AC41-AC42,MAX(AC69,0)*$E$70)","v":0},{"$f":"MIN(AD41-AD42,MAX(AD69,0)*$E$70)","v":0},{"$f":"MIN(AE41-AE42,MAX(AE69,0)*$E$70)","v":0},{"$f":"MIN(AF41-AF42,MAX(AF69,0)*$E$70)","v":0},{"$f":"MIN(AG41-AG42,MAX(AG69,0)*$E$70)","v":0},{"$f":"MIN(AH41-AH42,MAX(AH69,0)*$E$70)","v":0},{"$f":"MIN(AI41-AI42,MAX(AI69,0)*$E$70)","v":0},{"$f":"MIN(AJ41-AJ42,MAX(AJ69,0)*$E$70)","v":0},{"$f":"MIN(AK41-AK42,MAX(AK69,0)*$E$70)","v":0},{"$f":"MIN(AL41-AL42,MAX(AL69,0)*$E$70)","v":0},{"$f":"MIN(AM41-AM42,MAX(AM69,0)*$E$70)","v":0},{"$f":"MIN(AN41-AN42,MAX(AN69,0)*$E$70)","v":0},{"$f":"MIN(AO41-AO42,MAX(AO69,0)*$E$70)","v":0},{"$f":"MIN(AP41-AP42,MAX(AP69,0)*$E$70)","v":0},{"$f":"MIN(AQ41-AQ42,MAX(AQ69,0)*$E$70)","v":0},{"$f":"MIN(AR41-AR42,MAX(AR69,0)*$E$70)","v":0},{"$f":"MIN(AS41-AS42,MAX(AS69,0)*$E$70)","v":0},{"$f":"MIN(AT41-AT42,MAX(AT69,0)*$E$70)","v":0},{"$f":"MIN(AU41-AU42,MAX(AU69,0)*$E$70)","v":0},{"$f":"MIN(AV41-AV42,MAX(AV69,0)*$E$70)","v":0}]
[null,null,null,"Dividends",null,null,null,{"$f":"H69-H70","v":44.292847028411266},{"$f":"I69-I70","v":44.978025930210457},45.670126897653489,46.369219861383378,47.075375459725393,47.78866516374805,48.50917295832464,49.2381510012098,50.092423261255561,62.629761228831526,70.592751472700158,124.99999999999204,125.00000000000168,125.00000000002716,124.99999999998002,124.99999999992811,125.00000000012187,125.00000000013702,124.99999999943576,125.00000000002095,125.00000000202824,124.99999999817484,124.99999999467533,125.00000001015489,125.00000000973178,124.99999995850953,124.99999999850708,125.00000015156664,124.99999986872349,124.99999958047711,125.00000084116573,125.0000006359811,124.99999680653343,125.00000026151909,124.99999997863905,125.00000009595128,125.0000000096323,124.99999962582167,125.00000032607991,125.0000010099691,-2.1169764146783603E-6]
[]
[null,null,null,"Equity CF",null,null,{"$f":"-G36","v":-577.70237835354942},{"$f":"H71","v":44.292847028411266},{"$f":"I71","v":44.978025930210457},45.670126897653489,46.369219861383378,47.075375459725393,47.78866516374805,48.50917295832464,49.2381510012098,50.092423261255561,62.629761228831526,70.592751472700158,124.99999999999204,125.00000000000168,125.00000000002716,124.99999999998002,124.99999999992811,125.00000000012187,125.00000000013702,124.99999999943576,125.00000000002095,125.00000000202824,124.99999999817484,124.99999999467533,125.00000001015489,125.00000000973178,124.99999995850953,124.99999999850708,125.00000015156664,124.99999986872349,124.99999958047711,125.00000084116573,125.0000006359811,124.99999680653343,125.00000026151909,124.99999997863905,125.00000009595128,125.0000000096323,124.99999962582167,125.00000032607991,125.0000010099691,-2.1169764146783603E-6]
[null,null,null,null,null,null,{"$f":"IRR(73:73)","v":0.11987474165116963}]

{"sheet":"Basic Exercise","index":2,"n_rows":74,"n_cols":48,"rows_kept":74,"cut":false,"hidden":false,"merged":[]}
["Inputs"]
[null,"Operating Inputs"]
[null,null,"EPC Cost",null,1300,null,null,"Equity IRR",{"$f":"G74","v":0.11987474167401357}]
[null,null,"Initital Cash Flow",null,125,null,null,"Project IRR"]
[null,null,"Sensitivity Factor",null,{"$f":"F5/100","v":1},100]
[null,null,"Sensitivity Year",null,5,null,null,"Cash Flow Test"]
[null,null,"Life (Periods)",null,40]
[]
[null,"Financial Inputs"]
[null,null,"Debt Commitment",null,800]
[null,null,"Interest Rate (Periodic)",null,0.02]
[null,null,"Debt Term (Periods)",null,34]
[null,null,"Debt Reserve Months",null,12]
[null,null,"Interest Rate on Reserve",null,5.0000000000000001E-3]
[null,null,"Cash Sweep",null,0.5]
[]
["Periods"]
[null,"Semi-Annual Period",null,null,null,null,null,1,{"$f":"H18+1","v":2},{"$f":"I18+1","v":3},4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41]
[null,"Construction Switch",null,null,null,null,null,{"$f":"H18<1","v":false},{"$f":"I18<1","v":false},false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false]
[null,"Operating Switch",null,null,null,null,null,{"$f":"AND(H18>0,H18<=Life)","v":true},true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,false]
[null,"Sensitivity Switch",null,null,null,null,null,{"$f":"H18>=Sensitivity_Year","v":false},false,false,false,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true]
[null,"Debt Repayment",null,null,{"$f":"Debt_Term","v":34},null,null,{"$f":"H18<=$E$22","v":true},{"$f":"I18<=$E$22","v":true},true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,false,false,false,false,false,false,false]
[]
["Working"]
[null,"EBITDA",null,null,null,null,null,{"$f":"H20*Initital_Cash_Flow","v":125},125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,0]
[null,"Sensitivity Factor",null,null,null,null,null,{"$f":"IF(H21,Sensitivity_Factor,1)","v":1},1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1]
[null,"Cash Flow After Sensitivity",null,null,null,null,null,{"$f":"H26*H25","v":125},{"$f":"I26*I25","v":125},125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,0]
[]
["Uses and Sources"]
[null,"Uses"]
[null,null,"EPC Cost",null,null,null,{"$f":"EPC_Cost","v":1300}]
[null,null,"DSRA - From Below",null,null,null,{"$f":"G49","v":77.70237835354942}]
[null,null,null,"Total",null,null,{"$f":"G31+G32","v":1377.7023783535494}]
[null,"Sources"]
[null,null,"Debt",null,null,null,{"$f":"Debt_Commitment","v":800}]
[null,null,"Equity",null,null,null,{"$f":"G33-G35","v":577.70237835354942}]
[null,null,null,"Total",null,null,{"$f":"G35+G36","v":1377.7023783535494}]
[]
["Debt and Reserve Schedule"]
[null,"Debt Balance"]
[null,null,"Opening Balalnce",null,null,null,null,{"$f":"G44","v":800},{"$f":"H44","v":732.17774120688284},663.67030351196649,594.47076484960712,524.57213322351788,453.96734599908666,382.64926907063273,310.61068434760222,237.84312158168649,164.22128655572507,78.062113562189836,-4.0785153032629751E-12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Less: Repayment",null,{"$f":"Debt_Term","v":34},null,null,{"$f":"MIN($E$44/$E$42,H41)","v":23.529411764705884},{"$f":"MIN($E$44/$E$42,I41)","v":23.529411764705884},23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,-4.0785153032629751E-12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Less: Cash Sweep",null,null,null,null,{"$f":"H70","v":44.292847028411266},{"$f":"I70","v":44.978025930210457},45.670126897653489,46.369219861383378,47.075375459725393,47.78866516374805,48.50917295832464,49.2381510012098,50.092423261255576,62.629761228827796,54.532701797482034,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Closing Balance",null,{"$f":"Debt_Commitment","v":800},null,{"$f":"E44","v":800},{"$f":"H41-H42-H43","v":732.17774120688284},{"$f":"I41-I42-I43","v":663.67030351196649},594.47076484960712,524.57213322351788,453.96734599908666,382.64926907063273,310.61068434760222,237.84312158168649,164.22128655572504,78.062113562191399,1.9184653865522705E-12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Interest",null,{"$f":"Interest_Rate","v":0.02},null,null,{"$f":"H41*$E$45","v":16},{"$f":"I41*$E$45","v":14.643554824137658},13.27340607023933,11.889415296992143,10.491442664470357,9.0793469199817327,7.6529853814126545,6.2122136869520448,4.7568624316337296,3.2844257311145015,1.5612422712437968,-8.1570306065259504E-14,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[]
[null,"DSRA Balance"]
[null,null,"Debt Service - FROM DEBT SCHEDULE ABOVE",null,null,null,null,{"$f":"H45+H42","v":39.529411764705884},{"$f":"I45+I42","v":38.172966588843543},36.802817834945216,35.418827061698025,34.020854429176239,32.608758684687615,31.182397146118539,29.741625451657928,28.286274196339612,26.813837495820387,25.090654035949679,-4.1600856093282347E-12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Required DSRA Balance - USE SUM AND OFFSET",null,{"$f":"reserve_months/6","v":2},null,{"$f":"SUM(OFFSET(G48,0,1,1,$E$49))","v":77.70237835354942},{"$f":"SUM(OFFSET(H48,0,1,1,$E$49))*H22","v":74.975784423788753},{"$f":"SUM(OFFSET(I48,0,1,1,$E$49))*I22","v":72.221644896643241},69.439681490874264,66.629613113863854,63.791155830806154,60.924022597776471,58.027899647997543,55.100111692159999,51.904491531770063,25.090654035945519,-4.1600856093282347E-12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Less: Amount Already Funded - OPENING BALANCE",null,null,null,null,{"$f":"H53","v":77.70237835354942},{"$f":"I53","v":74.975784423788753},72.221644896643241,69.439681490874264,66.629613113863854,63.791155830806154,60.924022597776471,58.027899647997543,55.100111692159999,51.904491531770113,25.090654035949871,-8.0042639183375286E-12,1.7290346931986276E-12,2.7207676112084299E-11,-2.0220767282808081E-11,-7.1905930099092079E-11,1.2266553994777496E-10,1.3642917906508959E-10,-5.6646044299668575E-10,2.5153497063001909E-11,2.0324860372511465E-9,-1.8440054816437626E-9,-5.3231104857331953E-9,1.0216253230055371E-8,9.6791552550712351E-9,-4.1653098463712006E-8,-1.1905441965609743E-9,1.5190406301712761E-7,-1.32672283541524E-7,-4.1946791270675014E-7,8.4605004630589038E-7,6.3128084917707434E-7,-3.2052100547730341E-6,2.8548155356134055E-7,1.0529815614006565E-5,-8.3299782912149549E-6,-3.2980761175593909E-5,6.21711847202278E-5,5.7980230316161295E-5,-2.7273764545455471E-4,7.5092456844032268E-5]
[null,null,"Required Net Additional Funding",null,null,null,null,{"$f":"H49-H50","v":-2.7265939297606678},{"$f":"I49-I50","v":-2.7541395271455116},-2.7819634057689768,-2.8100683770104098,-2.8384572830577,-2.8671332330296835,-2.8961229497789276,-2.9277879558375446,-3.1956201603899359,-26.813837495824593,-25.090654035954032,8.0042639183375286E-12,-1.7290346931986276E-12,-2.7207676112084299E-11,2.0220767282808081E-11,7.1905930099092079E-11,-1.2266553994777496E-10,-1.3642917906508959E-10,5.6646044299668575E-10,-2.5153497063001909E-11,-2.0324860372511465E-9,1.8440054816437626E-9,5.3231104857331953E-9,-1.0216253230055371E-8,-9.6791552550712351E-9,4.1653098463712006E-8,1.1905441965609743E-9,-1.5190406301712761E-7,1.32672283541524E-7,4.1946791270675014E-7,-8.4605004630589038E-7,-6.3128084917707434E-7,3.2052100547730341E-6,-2.8548155356134055E-7,-1.0529815614006565E-5,8.3299782912149549E-6,3.2980761175593909E-5,-6.21711847202278E-5,-5.7980230316161295E-5,2.7273764545455471E-4,-7.5092456844032268E-5]
[]
[null,null,"Opening Balance",null,null,null,null,{"$f":"G56","v":77.70237835354942},{"$f":"H56","v":74.975784423788753},72.221644896643241,69.439681490874264,66.629613113863854,63.791155830806154,60.924022597776471,58.027899647997543,55.100111692159999,51.904491531770034,25.090654035952983,7.1196382123162039E-12,-1.0867751143450732E-11,-1.1916938547074097E-11,4.854513235841295E-11,3.19914761348628E-12,-1.5775867723277771E-10,1.191807541545131E-10,4.4258300135879844E-10,-8.4091141161479761E-10,-8.460958156319975E-10,3.7698688970522199E-9,-3.1003722256173237E-10,-1.2272843576965897E-8,1.0523681766017033E-8,3.2525757092116687E-8,-6.0474453107417481E-8,-6.748348596374859E-8,2.7915509846820908E-7,-1.09959606220401E-8,-9.7686303220712016E-7,9.4004742635433959E-7,2.3486964151686615E-6,-4.7924922149888985E-6,-4.272189414251669E-6,1.9190255002285995E-5,1.9264608035030108E-6,-7.4835666688244403E-5,6.5215984047029089E-5,2.0199381881980095E-4,-4.2339528293567209E-4]
[null,null,"Add: Net Addition Funding",null,null,null,null,{"$f":"H51","v":-2.7265939297606678},{"$f":"I51","v":-2.7541395271455116},-2.7819634057689768,-2.8100683770104098,-2.8384572830577,-2.8671332330296835,-2.8961229497789276,-2.9277879558375446,-3.1956201603899359,-26.813837495824593,-25.090654035954032,8.0042639183375286E-12,-1.7290346931986276E-12,-2.7207676112084299E-11,2.0220767282808081E-11,7.1905930099092079E-11,-1.2266553994777496E-10,-1.3642917906508959E-10,5.6646044299668575E-10,-2.5153497063001909E-11,-2.0324860372511465E-9,1.8440054816437626E-9,5.3231104857331953E-9,-1.0216253230055371E-8,-9.6791552550712351E-9,4.1653098463712006E-8,1.1905441965609743E-9,-1.5190406301712761E-7,1.32672283541524E-7,4.1946791270675014E-7,-8.4605004630589038E-7,-6.3128084917707434E-7,3.2052100547730341E-6,-2.8548155356134055E-7,-1.0529815614006565E-5,8.3299782912149549E-6,3.2980761175593909E-5,-6.21711847202278E-5,-5.7980230316161295E-5,2.7273764545455471E-4,-7.5092456844032268E-5]
[null,null,"Less: Withdrawls for Deficit CF - FROM CASH FLOW WATERFALL",null,null,null,null,{"$f":"H68","v":0},{"$f":"I68","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Closing Balance ",null,null,null,{"$f":"G32","v":77.70237835354942},{"$f":"H53+H54-H55","v":74.975784423788753},{"$f":"I53+I54-I55","v":72.221644896643241},69.439681490874264,66.629613113863854,63.791155830806154,60.924022597776471,58.027899647997543,55.100111692159999,51.904491531770063,25.090654035945441,-1.0480505352461478E-12,1.5123902130653732E-11,-1.259678583664936E-11,-3.9124614659158398E-11,6.8765899641221031E-11,7.5105077712578365E-11,-2.8042421718055264E-10,-1.7248424910576484E-11,1.0090434443554842E-9,-8.6606490867779952E-10,-2.8785818528831442E-9,5.6138743786959827E-9,5.0130732631714626E-9,-2.2489096807021269E-8,8.4452651094579805E-10,7.4178855555828693E-8,-5.928390891085651E-8,-2.193875489808762E-7,4.1182738200973308E-7,4.0847195208471006E-7,-1.8229130785130105E-6,3.0876657717726526E-7,5.5539064699416956E-6,-5.0779737685502394E-6,-1.4802005028258234E-5,2.752023329350095E-5,3.4907221979096918E-5,-1.3700685140847222E-4,7.2357537308677936E-6,4.7473146427435566E-4,-4.9848773977970433E-4]
[null,null,"Interest Income",null,{"$f":"E14","v":5.0000000000000001E-3},null,null,{"$f":"$E$57*H53","v":0.38851189176774709},{"$f":"$E$57*I53","v":0.37487892211894375},0.36110822448321622,0.34719840745437131,0.33314806556931925,0.31895577915403078,0.30462011298888236,0.29013949823998775,0.27550055846080002,0.25952245765885018,0.12545327017976493,3.5598191061581018E-14,-5.4338755717253661E-14,-5.9584692735370479E-14,2.4272566179206475E-13,1.59957380674314E-14,-7.8879338616388859E-13,5.9590377077256552E-13,2.2129150067939924E-12,-4.2045570580739883E-12,-4.2304790781599873E-12,1.88493444852611E-11,-1.5501861128086618E-12,-6.1364217884829485E-11,5.2618408830085167E-11,1.6262878546058345E-10,-3.0237226553708741E-10,-3.3741742981874295E-10,1.3957754923410455E-9,-5.49798031102005E-11,-4.8843151610356006E-9,4.7002371317716982E-9,1.1743482075843308E-8,-2.3962461074944494E-8,-2.1360947071258346E-8,9.595127501142997E-8,9.6323040175150539E-9,-3.7417833344122202E-7,3.2607992023514545E-7,1.0099690940990048E-6,-2.1169764146783603E-6]
[]
["Cash Flow Waterfall"]
[null,null,"Operating Cash Flow",null,null,null,null,{"$f":"H27","v":125},{"$f":"I27","v":125},125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,125,0]
[null,null,"Add: Interest Income",null,null,null,null,{"$f":"H57","v":0.38851189176774709},{"$f":"I57","v":0.37487892211894375},0.36110822448321622,0.34719840745437131,0.33314806556931925,0.31895577915403078,0.30462011298888236,0.29013949823998775,0.27550055846080002,0.25952245765885018,0.12545327017976493,3.5598191061581018E-14,-5.4338755717253661E-14,-5.9584692735370479E-14,2.4272566179206475E-13,1.59957380674314E-14,-7.8879338616388859E-13,5.9590377077256552E-13,2.2129150067939924E-12,-4.2045570580739883E-12,-4.2304790781599873E-12,1.88493444852611E-11,-1.5501861128086618E-12,-6.1364217884829485E-11,5.2618408830085167E-11,1.6262878546058345E-10,-3.0237226553708741E-10,-3.3741742981874295E-10,1.3957754923410455E-9,-5.49798031102005E-11,-4.8843151610356006E-9,4.7002371317716982E-9,1.1743482075843308E-8,-2.3962461074944494E-8,-2.1360947071258346E-8,9.595127501142997E-8,9.6323040175150539E-9,-3.7417833344122202E-7,3.2607992023514545E-7,1.0099690940990048E-6,-2.1169764146783603E-6]
[null,null,null,"CFADS",null,null,null,{"$f":"SUM(H60:H61)","v":125.38851189176775},{"$f":"SUM(I60:I61)","v":125.37487892211894},125.36110822448322,125.34719840745437,125.33314806556932,125.31895577915402,125.30462011298889,125.29013949823998,125.27550055846081,125.25952245765885,125.12545327017976,125.00000000000004,124.99999999999994,124.99999999999994,125.00000000000024,125.00000000000001,124.9999999999992,125.0000000000006,125.00000000000222,124.99999999999579,124.99999999999577,125.00000000001884,124.99999999999845,124.99999999993864,125.00000000005262,125.00000000016263,124.99999999969762,124.99999999966258,125.00000000139578,124.99999999994502,124.99999999511569,125.00000000470024,125.00000001174348,124.99999997603754,124.99999997863905,125.00000009595128,125.0000000096323,124.99999962582167,125.00000032607991,125.0000010099691,-2.1169764146783603E-6]
[null,null,"Less: Scheduled Interest",null,null,null,null,{"$f":"H45","v":16},{"$f":"I45","v":14.643554824137658},13.27340607023933,11.889415296992143,10.491442664470357,9.0793469199817327,7.6529853814126545,6.2122136869520448,4.7568624316337296,3.2844257311145015,1.5612422712437968,-8.1570306065259504E-14,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Less: Scheduled Repayment",null,null,null,null,{"$f":"H42","v":23.529411764705884},{"$f":"I42","v":23.529411764705884},23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,23.529411764705884,-4.0785153032629751E-12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Net Cash Flow",null,null,null,{"$f":"H62-H63-H64","v":85.859100127061865},{"$f":"I62-I63-I64","v":87.201912333275402},{"$f":"J62-J63-J64","v":88.558290389538001},{"$f":"K62-K63-K64","v":89.928371345756346},{"$f":"L62-L63-L64","v":91.312293636393079},{"$f":"M62-M63-M64","v":92.710197094466409},{"$f":"N62-N63-N64","v":94.122222966870353},{"$f":"O62-O63-O64","v":95.548514046582056},{"$f":"P62-P63-P64","v":96.989226362121187},{"$f":"Q62-Q63-Q64","v":98.445684961838467},{"$f":"R62-R63-R64","v":100.03479923423008},{"$f":"S62-S63-S64","v":125.00000000000421},{"$f":"T62-T63-T64","v":124.99999999999994},{"$f":"U62-U63-U64","v":124.99999999999994},{"$f":"V62-V63-V64","v":125.00000000000024},{"$f":"W62-W63-W64","v":125.00000000000001},{"$f":"X62-X63-X64","v":124.9999999999992},{"$f":"Y62-Y63-Y64","v":125.0000000000006},{"$f":"Z62-Z63-Z64","v":125.00000000000222},{"$f":"AA62-AA63-AA64","v":124.99999999999579},{"$f":"AB62-AB63-AB64","v":124.99999999999577},{"$f":"AC62-AC63-AC64","v":125.00000000001884},{"$f":"AD62-AD63-AD64","v":124.99999999999845},{"$f":"AE62-AE63-AE64","v":124.99999999993864},{"$f":"AF62-AF63-AF64","v":125.00000000005262},{"$f":"AG62-AG63-AG64","v":125.00000000016263},{"$f":"AH62-AH63-AH64","v":124.99999999969762},{"$f":"AI62-AI63-AI64","v":124.99999999966258},{"$f":"AJ62-AJ63-AJ64","v":125.00000000139578},{"$f":"AK62-AK63-AK64","v":124.99999999994502},{"$f":"AL62-AL63-AL64","v":124.99999999511569},{"$f":"AM62-AM63-AM64","v":125.00000000470024},{"$f":"AN62-AN63-AN64","v":125.00000001174348},{"$f":"AO62-AO63-AO64","v":124.99999997603754},{"$f":"AP62-AP63-AP64","v":124.99999997863905},{"$f":"AQ62-AQ63-AQ64","v":125.00000009595128},{"$f":"AR62-AR63-AR64","v":125.0000000096323},{"$f":"AS62-AS63-AS64","v":124.99999962582167},{"$f":"AT62-AT63-AT64","v":125.00000032607991},{"$f":"AU62-AU63-AU64","v":125.0000010099691},{"$f":"AV62-AV63-AV64","v":-2.1169764146783603E-6}]
[null,null,"Add: Interest Income "]
[null,null,"Less: Net Added DSRA Funding",null,null,null,null,{"$f":"H51*H22","v":-2.7265939297606678},{"$f":"I51*I22","v":-2.7541395271455116},{"$f":"J51*J22","v":-2.7819634057689768},{"$f":"K51*K22","v":-2.8100683770104098},{"$f":"L51*L22","v":-2.8384572830577},{"$f":"M51*M22","v":-2.8671332330296835},{"$f":"N51*N22","v":-2.8961229497789276},{"$f":"O51*O22","v":-2.9277879558375446},{"$f":"P51*P22","v":-3.1956201603899359},{"$f":"Q51*Q22","v":-26.813837495824593},{"$f":"R51*R22","v":-25.090654035954032},{"$f":"S51*S22","v":8.0042639183375286E-12},{"$f":"T51*T22","v":-1.7290346931986276E-12},{"$f":"U51*U22","v":-2.7207676112084299E-11},{"$f":"V51*V22","v":2.0220767282808081E-11},{"$f":"W51*W22","v":7.1905930099092079E-11},{"$f":"X51*X22","v":-1.2266553994777496E-10},{"$f":"Y51*Y22","v":-1.3642917906508959E-10},{"$f":"Z51*Z22","v":5.6646044299668575E-10},{"$f":"AA51*AA22","v":-2.5153497063001909E-11},{"$f":"AB51*AB22","v":-2.0324860372511465E-9},{"$f":"AC51*AC22","v":1.8440054816437626E-9},{"$f":"AD51*AD22","v":5.3231104857331953E-9},{"$f":"AE51*AE22","v":-1.0216253230055371E-8},{"$f":"AF51*AF22","v":-9.6791552550712351E-9},{"$f":"AG51*AG22","v":4.1653098463712006E-8},{"$f":"AH51*AH22","v":1.1905441965609743E-9},{"$f":"AI51*AI22","v":-1.5190406301712761E-7},{"$f":"AJ51*AJ22","v":1.32672283541524E-7},{"$f":"AK51*AK22","v":4.1946791270675014E-7},{"$f":"AL51*AL22","v":-8.4605004630589038E-7},{"$f":"AM51*AM22","v":-6.3128084917707434E-7},{"$f":"AN51*AN22","v":3.2052100547730341E-6},{"$f":"AO51*AO22","v":-2.8548155356134055E-7},{"$f":"AP51*AP22","v":0},{"$f":"AQ51*AQ22","v":0},{"$f":"AR51*AR22","v":0},{"$f":"AS51*AS22","v":0},{"$f":"AT51*AT22","v":0},{"$f":"AU51*AU22","v":0},{"$f":"AV51*AV22","v":0}]
[null,null,"Add: Withdrawls from DSRA for Negative Cash Flow - USE MIN AND MAX",null,null,null,null,{"$f":"MIN(H48,MAX(-H65,0))","v":0},{"$f":"MIN(I48,MAX(-I65,0))","v":0},{"$f":"MIN(J48,MAX(-J65,0))","v":0},{"$f":"MIN(K48,MAX(-K65,0))","v":0},{"$f":"MIN(L48,MAX(-L65,0))","v":0},{"$f":"MIN(M48,MAX(-M65,0))","v":0},{"$f":"MIN(N48,MAX(-N65,0))","v":0},{"$f":"MIN(O48,MAX(-O65,0))","v":0},{"$f":"MIN(P48,MAX(-P65,0))","v":0},{"$f":"MIN(Q48,MAX(-Q65,0))","v":0},{"$f":"MIN(R48,MAX(-R65,0))","v":0},{"$f":"MIN(S48,MAX(-S65,0))","v":-4.1600856093282347E-12},{"$f":"MIN(T48,MAX(-T65,0))","v":0},{"$f":"MIN(U48,MAX(-U65,0))","v":0},{"$f":"MIN(V48,MAX(-V65,0))","v":0},{"$f":"MIN(W48,MAX(-W65,0))","v":0},{"$f":"MIN(X48,MAX(-X65,0))","v":0},{"$f":"MIN(Y48,MAX(-Y65,0))","v":0},{"$f":"MIN(Z48,MAX(-Z65,0))","v":0},{"$f":"MIN(AA48,MAX(-AA65,0))","v":0},{"$f":"MIN(AB48,MAX(-AB65,0))","v":0},{"$f":"MIN(AC48,MAX(-AC65,0))","v":0},{"$f":"MIN(AD48,MAX(-AD65,0))","v":0},{"$f":"MIN(AE48,MAX(-AE65,0))","v":0},{"$f":"MIN(AF48,MAX(-AF65,0))","v":0},{"$f":"MIN(AG48,MAX(-AG65,0))","v":0},{"$f":"MIN(AH48,MAX(-AH65,0))","v":0},{"$f":"MIN(AI48,MAX(-AI65,0))","v":0},{"$f":"MIN(AJ48,MAX(-AJ65,0))","v":0},{"$f":"MIN(AK48,MAX(-AK65,0))","v":0},{"$f":"MIN(AL48,MAX(-AL65,0))","v":0},{"$f":"MIN(AM48,MAX(-AM65,0))","v":0},{"$f":"MIN(AN48,MAX(-AN65,0))","v":0},{"$f":"MIN(AO48,MAX(-AO65,0))","v":0},{"$f":"MIN(AP48,MAX(-AP65,0))","v":0},{"$f":"MIN(AQ48,MAX(-AQ65,0))","v":0},{"$f":"MIN(AR48,MAX(-AR65,0))","v":0},{"$f":"MIN(AS48,MAX(-AS65,0))","v":0},{"$f":"MIN(AT48,MAX(-AT65,0))","v":0},{"$f":"MIN(AU48,MAX(-AU65,0))","v":0},{"$f":"MIN(AV48,MAX(-AV65,0))","v":0}]
[null,null,null,"Net Cash Flow",null,null,null,{"$f":"H65-H67+H68+H66","v":88.585694056822533},{"$f":"I65-I67+I68+I66","v":89.956051860420914},91.340253795306978,92.738439722766756,94.150750919450786,95.577330327496099,97.018345916649281,98.476302002419601,100.18484652251112,125.25952245766305,125.12545327018411,124.99999999999204,125.00000000000168,125.00000000002716,124.99999999998002,124.99999999992811,125.00000000012187,125.00000000013702,124.99999999943576,125.00000000002095,125.00000000202824,124.99999999817484,124.99999999467533,125.00000001015489,125.00000000973178,124.99999995850953,124.99999999850708,125.00000015156664,124.99999986872349,124.99999958047711,125.00000084116573,125.0000006359811,124.99999680653343,125.00000026151909,124.99999997863905,125.00000009595128,125.0000000096323,124.99999962582167,125.00000032607991,125.0000010099691,-2.1169764146783603E-6]
[null,null,"Less: Sweep",null,{"$f":"E15","v":0.5},null,null,{"$f":"MIN(H41-H42,MAX(H69,0)*$E$70)","v":44.292847028411266},{"$f":"MIN(I41-I42,MAX(I69,0)*$E$70)","v":44.978025930210457},{"$f":"MIN(J41-J42,MAX(J69,0)*$E$70)","v":45.670126897653489},{"$f":"MIN(K41-K42,MAX(K69,0)*$E$70)","v":46.369219861383378},{"$f":"MIN(L41-L42,MAX(L69,0)*$E$70)","v":47.075375459725393},{"$f":"MIN(M41-M42,MAX(M69,0)*$E$70)","v":47.78866516374805},{"$f":"MIN(N41-N42,MAX(N69,0)*$E$70)","v":48.50917295832464},{"$f":"MIN(O41-O42,MAX(O69,0)*$E$70)","v":49.2381510012098},{"$f":"MIN(P41-P42,MAX(P69,0)*$E$70)","v":50.092423261255561},{"$f":"MIN(Q41-Q42,MAX(Q69,0)*$E$70)","v":62.629761228831526},{"$f":"MIN(R41-R42,MAX(R69,0)*$E$70)","v":54.532701797483952},{"$f":"MIN(S41-S42,MAX(S69,0)*$E$70)","v":0},{"$f":"MIN(T41-T42,MAX(T69,0)*$E$70)","v":0},{"$f":"MIN(U41-U42,MAX(U69,0)*$E$70)","v":0},{"$f":"MIN(V41-V42,MAX(V69,0)*$E$70)","v":0},{"$f":"MIN(W41-W42,MAX(W69,0)*$E$70)","v":0},{"$f":"MIN(X41-X42,MAX(X69,0)*$E$70)","v":0},{"$f":"MIN(Y41-Y42,MAX(Y69,0)*$E$70)","v":0},{"$f":"MIN(Z41-Z42,MAX(Z69,0)*$E$70)","v":0},{"$f":"MIN(AA41-AA42,MAX(AA69,0)*$E$70)","v":0},{"$f":"MIN(AB41-AB42,MAX(AB69,0)*$E$70)","v":0},{"$f":"MIN(AC41-AC42,MAX(AC69,0)*$E$70)","v":0},{"$f":"MIN(AD41-AD42,MAX(AD69,0)*$E$70)","v":0},{"$f":"MIN(AE41-AE42,MAX(AE69,0)*$E$70)","v":0},{"$f":"MIN(AF41-AF42,MAX(AF69,0)*$E$70)","v":0},{"$f":"MIN(AG41-AG42,MAX(AG69,0)*$E$70)","v":0},{"$f":"MIN(AH41-AH42,MAX(AH69,0)*$E$70)","v":0},{"$f":"MIN(AI41-AI42,MAX(AI69,0)*$E$70)","v":0},{"$f":"MIN(AJ41-AJ42,MAX(AJ69,0)*$E$70)","v":0},{"$f":"MIN(AK41-AK42,MAX(AK69,0)*$E$70)","v":0},{"$f":"MIN(AL41-AL42,MAX(AL69,0)*$E$70)","v":0},{"$f":"MIN(AM41-AM42,MAX(AM69,0)*$E$70)","v":0},{"$f":"MIN(AN41-AN42,MAX(AN69,0)*$E$70)","v":0},{"$f":"MIN(AO41-AO42,MAX(AO69,0)*$E$70)","v":0},{"$f":"MIN(AP41-AP42,MAX(AP69,0)*$E$70)","v":0},{"$f":"MIN(AQ41-AQ42,MAX(AQ69,0)*$E$70)","v":0},{"$f":"MIN(AR41-AR42,MAX(AR69,0)*$E$70)","v":0},{"$f":"MIN(AS41-AS42,MAX(AS69,0)*$E$70)","v":0},{"$f":"MIN(AT41-AT42,MAX(AT69,0)*$E$70)","v":0},{"$f":"MIN(AU41-AU42,MAX(AU69,0)*$E$70)","v":0},{"$f":"MIN(AV41-AV42,MAX(AV69,0)*$E$70)","v":0}]
[null,null,null,"Dividends",null,null,null,{"$f":"H69-H70","v":44.292847028411266},{"$f":"I69-I70","v":44.978025930210457},45.670126897653489,46.369219861383378,47.075375459725393,47.78866516374805,48.50917295832464,49.2381510012098,50.092423261255561,62.629761228831526,70.592751472700158,124.99999999999204,125.00000000000168,125.00000000002716,124.99999999998002,124.99999999992811,125.00000000012187,125.00000000013702,124.99999999943576,125.00000000002095,125.00000000202824,124.99999999817484,124.99999999467533,125.00000001015489,125.00000000973178,124.99999995850953,124.99999999850708,125.00000015156664,124.99999986872349,124.99999958047711,125.00000084116573,125.0000006359811,124.99999680653343,125.00000026151909,124.99999997863905,125.00000009595128,125.0000000096323,124.99999962582167,125.00000032607991,125.0000010099691,-2.1169764146783603E-6]
[]
[null,null,null,"Equity CF",null,null,{"$f":"-G36","v":-577.70237835354942},{"$f":"H71","v":44.292847028411266},{"$f":"I71","v":44.978025930210457},45.670126897653489,46.369219861383378,47.075375459725393,47.78866516374805,48.50917295832464,49.2381510012098,50.092423261255561,62.629761228831526,70.592751472700158,124.99999999999204,125.00000000000168,125.00000000002716,124.99999999998002,124.99999999992811,125.00000000012187,125.00000000013702,124.99999999943576,125.00000000002095,125.00000000202824,124.99999999817484,124.99999999467533,125.00000001015489,125.00000000973178,124.99999995850953,124.99999999850708,125.00000015156664,124.99999986872349,124.99999958047711,125.00000084116573,125.0000006359811,124.99999680653343,125.00000026151909,124.99999997863905,125.00000009595128,125.0000000096323,124.99999962582167,125.00000032607991,125.0000010099691,-2.1169764146783603E-6]
[null,null,null,null,null,null,{"$f":"IRR(73:73)","v":0.11987474165116963}]

{"sheet":"DSRA No Cash Sweep","index":3,"n_rows":46,"n_cols":15,"rows_kept":46,"cut":false,"hidden":false,"merged":[]}
["Contents"]
[null,"Key Points: No Problem without Sweep"]
[null,"If there is a sweep, you do not know how much will be swept next year until the end of the year"]
[null,"You should use the next year's fixed repayment in reducing the DSRA unless the sweep has reduced the opening balance"]
[null,"The key point is that the DSRA account will not in reality be reduced for prospective reductions due to a cash sweep, although it "]
[null,"should be reduced for previous reductions from the cash flow."]
[]
[]
["Case 1 : No Cash Sweep with Level Repayment -- No problem with circularity when use next period debt service. Demonstrates where the problems come from"]
[]
[null,"Assumptions",null,null,null,1,2,3,4,5,6,7,8,9,10]
[null,null,null,"CADS",null,200,200,200,200,200,200,200,200,200,200]
[null,null,null,"Debt",1000]
[null,null,null,"Rate",0.05]
[null,null,null,"Sweep",0.5]
[]
[]
[null,null,"Debt"]
[null,null,null,"Opening Balance",null,{"$f":"E22","v":1000},900,800,700,600,500,400,300,200,100]
[null,null,null,"Less: Fixed Repayment",null,{"$f":"$E$22/10","v":100},100,100,100,100,100,100,100,100,100]
[null,null,null,"Less: Sweep",null,{"$f":"F44","v":0},0,0,0,0,0,0,0,0,0]
[null,null,null,"Closing Balance",{"$f":"E13","v":1000},{"$f":"F19-F20-F21","v":900},800,700,600,500,400,300,200,100,0]
[]
[null,null,null,"Int Rate",null,{"$f":"$E$14","v":0.05},0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05]
[null,null,null,"Interest Expense",null,{"$f":"F24*F19","v":50},45,40,35,30,25,20,15,10,5]
[]
[null,null,"DSRA Balance"]
[null,null,null,"This Period DS",null,{"$f":"F25+F20+F21","v":150},145,140,135,130,125,120,115,110,105]
[null,null,null,"Next Period DS",{"$f":"F28","v":150},145,140,135,130,125,120,115,110,105,0]
[null,null,null,"OB",{"$f":"E33","v":0},150,145,140,135,130,125,120,115,110,105]
[null,null,null,"Required Additional DSRA Balance",{"$f":"E29-E30","v":150},-5,-5,-5,-5,-5,-5,-5,-5,-5,-105]
[]
[null,null,null,"OB",null,{"$f":"E35","v":150},145,140,135,130,125,120,115,110,105]
[null,null,null,"Top-up from Above",null,{"$f":"F31","v":-5},-5,-5,-5,-5,-5,-5,-5,-5,-105]
[null,null,null,"CB",{"$f":"E31","v":150},{"$f":"F33+F34","v":145},140,135,130,125,120,115,110,105,0]
[]
[]
[null,null,"Cash Flow"]
[null,null,null,"CADS",null,{"$f":"F12","v":200},200,200,200,200,200,200,200,200,200]
[null,null,null,"Add: Top-up",null,{"$f":"F34","v":-5},-5,-5,-5,-5,-5,-5,-5,-5,-105]
[null,null,null,"Less: Interest",null,{"$f":"F25","v":50},45,40,35,30,25,20,15,10,5]
[null,null,null,"Less: Fixed Repayment",null,{"$f":"F20","v":100},100,100,100,100,100,100,100,100,100]
[null,null,null,"Cash Flow for Sweep",null,{"$f":"F39-F40-F41-F42","v":55},60,65,70,75,80,85,90,95,200]
[null,null,null,"Cash  Flow Sweep"]
[]
[null,null,"Fixed",null,null,{"$f":"F42","v":100},100,100,100,100,100,100,100,100,100]

{"sheet":"DSRA with  Sweep","index":4,"n_rows":615,"n_cols":15,"rows_kept":615,"cut":false,"hidden":false,"merged":[]}
[]
[]
["Case 2: Cash Sweep Included with Level Payment -- circularity can be avoided making a separate interest and repayment calculation"]
[]
[null,null,"Debt"]
[null,null,null,"OB",null,{"$f":"E9","v":1000},872.5,741.125,606.36874999999998,468.1590625,326.40779687500003,181.02421015625004,31.91522574218753,0,0]
[null,null,null,"Less: Fixed",null,{"$f":"MIN('DSRA No Cash Sweep'!$E$22/10,F6)","v":100},{"$f":"MIN('DSRA No Cash Sweep'!$E$22/10,G6)","v":100},{"$f":"MIN('DSRA No Cash Sweep'!$E$22/10,H6)","v":100},{"$f":"MIN('DSRA No Cash Sweep'!$E$22/10,I6)","v":100},{"$f":"MIN('DSRA No Cash Sweep'!$E$22/10,J6)","v":100},{"$f":"MIN('DSRA No Cash Sweep'!$E$22/10,K6)","v":100},{"$f":"MIN('DSRA No Cash Sweep'!$E$22/10,L6)","v":100},{"$f":"MIN('DSRA No Cash Sweep'!$E$22/10,M6)","v":31.91522574218753},{"$f":"MIN('DSRA No Cash Sweep'!$E$22/10,N6)","v":0},{"$f":"MIN('DSRA No Cash Sweep'!$E$22/10,O6)","v":0}]
[null,null,null,"Less: Sweep",null,{"$f":"F36","v":27.5},31.375,34.756249999999994,38.209687500000001,41.751265625000002,45.383586718749996,49.108984414062505,0,0,0]
[null,null,null,"CB",{"$f":"'DSRA No Cash Sweep'!E13","v":1000},{"$f":"F6-F7-F8","v":872.5},741.125,606.36874999999998,468.1590625,326.40779687500003,181.02421015625004,31.91522574218753,0,0,0]
[]
[null,null,null,"Interest Rate",null,{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05}]
[null,null,null,"Interest Expense on OB",null,{"$f":"F11*F6","v":50},43.625,37.056249999999999,30.318437500000002,23.407953125000002,16.320389843750004,9.0512105078125025,1.5957612871093767,0,0]
[]
[null,null,"Debt Service Calculation"]
[null,null,null,"This Period DS",null,{"$f":"F12+F7+F8","v":177.5},175,171.8125,168.52812500000002,165.15921875000001,161.7039765625,158.16019492187502,33.510987029296906,0,0]
[null,null,null,"Current Interest",null,{"$f":"F9*F11+F7*F11","v":48.625},42.056249999999999,35.318437500000002,28.407953125000002,21.320389843750004,14.051210507812502,6.5957612871093767,1.5957612871093767,0,0]
[null,null,null,"Next Interest",{"$f":"E9*F11","v":50},{"$f":"(F6-F7)*F11","v":45},38.625,32.056249999999999,25.318437500000002,18.407953125000002,11.320389843750002,4.0512105078125016,0,0,0]
[null,null,null,"Next Fixed Repayments",{"$f":"F7","v":100},{"$f":"MIN('DSRA No Cash Sweep'!G46,F7)","v":100},{"$f":"MIN('DSRA No Cash Sweep'!H46,G7)","v":100},{"$f":"MIN('DSRA No Cash Sweep'!I46,H7)","v":100},{"$f":"MIN('DSRA No Cash Sweep'!J46,I7)","v":100},{"$f":"MIN('DSRA No Cash Sweep'!K46,J7)","v":100},{"$f":"MIN('DSRA No Cash Sweep'!L46,K7)","v":100},{"$f":"MIN('DSRA No Cash Sweep'!M46,L7)","v":100},{"$f":"MIN('DSRA No Cash Sweep'!N46,M7)","v":31.91522574218753},{"$f":"MIN('DSRA No Cash Sweep'!O46,N7)","v":0},{"$f":"MIN('DSRA No Cash Sweep'!P46,O7)","v":0}]
[]
[null,null,"DSRA Required Balance"]
[null,null,null,"Next Period DS",{"$f":"E17+E18","v":150},145,138.625,132.05625000000001,125.3184375,118.40795312500001,111.32038984375001,104.0512105078125,31.91522574218753,0,0]
[null,null,null,"OB",{"$f":"E25","v":0},150,145,138.625,132.05625000000001,125.3184375,118.40795312500001,111.32038984375001,104.0512105078125,31.91522574218753,0]
[null,null,null,"Required Balance",{"$f":"E21-E22","v":150},-5,-6.375,-6.5687499999999943,-6.737812500000004,-6.9104843749999958,-7.0875632812499987,-7.2691793359375083,-72.135984765624968,-31.91522574218753,0]
[]
[null,null,null,"OB",null,{"$f":"E27","v":150},145,138.625,132.05625000000001,125.3184375,118.40795312500001,111.32038984375001,104.0512105078125,31.91522574218753,0]
[null,null,null,"Top-up",null,{"$f":"F23","v":-5},-6.375,-6.5687499999999943,-6.737812500000004,-6.9104843749999958,-7.0875632812499987,-7.2691793359375083,-72.135984765624968,-31.91522574218753,0]
[null,null,null,"CB",{"$f":"E23","v":150},{"$f":"F25+F26","v":145},138.625,132.05625000000001,125.3184375,118.40795312500001,111.32038984375001,104.0512105078125,31.91522574218753,0,0]
[]
[]
[null,null,"Cash Flow"]
[null,null,null,"CADS",null,{"$f":"'DSRA No Cash Sweep'!F12","v":200},{"$f":"'DSRA No Cash Sweep'!G12","v":200},{"$f":"'DSRA No Cash Sweep'!H12","v":200},{"$f":"'DSRA No Cash Sweep'!I12","v":200},{"$f":"'DSRA No Cash Sweep'!J12","v":200},{"$f":"'DSRA No Cash Sweep'!K12","v":200},{"$f":"'DSRA No Cash Sweep'!L12","v":200},{"$f":"'DSRA No Cash Sweep'!M12","v":200},{"$f":"'DSRA No Cash Sweep'!N12","v":200},{"$f":"'DSRA No Cash Sweep'!O12","v":200}]
[null,null,null,"Less: Top-up",null,{"$f":"F26","v":-5},-6.375,-6.5687499999999943,-6.737812500000004,-6.9104843749999958,-7.0875632812499987,-7.2691793359375083,-72.135984765624968,-31.91522574218753,0]
[null,null,null,"Less: Interest",null,{"$f":"F12","v":50},43.625,37.056249999999999,30.318437500000002,23.407953125000002,16.320389843750004,9.0512105078125025,1.5957612871093767,0,0]
[null,null,null,"Less: Fixed Repayment",null,{"$f":"F7","v":100},100,100,100,100,100,100,31.91522574218753,0,0]
[null,null,null,"Cash Flow for Sweep",null,{"$f":"F31-F32-F33-F34","v":55},62.75,69.512499999999989,76.419375000000002,83.502531250000004,90.767173437499991,98.217968828125009,238.62499773632805,231.91522574218754,200]
[null,null,null,"Cash  Flow Sweep",null,{"$f":"MIN(F35*'DSRA No Cash Sweep'!$E$15,F6-F7)","v":27.5},{"$f":"MIN(G35*'DSRA No Cash Sweep'!$E$15,G6-G7)","v":31.375},{"$f":"MIN(H35*'DSRA No Cash Sweep'!$E$15,H6-H7)","v":34.756249999999994},{"$f":"MIN(I35*'DSRA No Cash Sweep'!$E$15,I6-I7)","v":38.209687500000001},{"$f":"MIN(J35*'DSRA No Cash Sweep'!$E$15,J6-J7)","v":41.751265625000002},{"$f":"MIN(K35*'DSRA No Cash Sweep'!$E$15,K6-K7)","v":45.383586718749996},{"$f":"MIN(L35*'DSRA No Cash Sweep'!$E$15,L6-L7)","v":49.108984414062505},{"$f":"MIN(M35*'DSRA No Cash Sweep'!$E$15,M6-M7)","v":0},{"$f":"MIN(N35*'DSRA No Cash Sweep'!$E$15,N6-N7)","v":0},{"$f":"MIN(O35*'DSRA No Cash Sweep'!$E$15,O6-O7)","v":0}]
[]
[]
[]
[null,"Repayment = CADS/DSCR - Int"]
[]
["Case 3: Sculpted without cash sweep and without top-up problem -- demonstrates that cannot solve top-up problem unless use the backwards menthod"]
[]
[null,null,"Target DSCR",null,1.6]
[]
[]
[null,null,"EBITDA",null,null,{"$f":"F31","v":200},{"$f":"F47*1.02","v":204},208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[null,null,"Less: DSRA Top-up"]
[null,null,"Total CADS",null,null,{"$f":"F47-F48","v":200},204,208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[]
[null,null,"Target DSCR",null,null,{"$f":"E44","v":1.6},{"$f":"F51","v":1.6},1.6,1.6,1.6,1.6,1.6,1.6,1.6,1.6]
[null,null,"Required Debt Service",null,null,{"$f":"F49/F51","v":125},127.5,130.05000000000001,132.65099999999998,135.30402000000001,138.0101004,140.77030240799999,143.58570845616001,146.45742262528321,149.38657107778886]
[]
[null,null,"Alternative",null,null,{"$f":"F49/F51","v":125},{"$f":"G47/G51","v":127.5},130.05000000000001,132.65099999999998,135.30402000000001,138.0101004,140.77030240799999,143.58570845616001,146.45742262528321,149.38657107778886]
[]
[null,null,"Debt"]
[null,null,null,"OB",null,{"$f":"E60","v":1048.513206554115},975.93886688182067,897.23581022591168,812.04760073720729,719.99898077406772,620.69490981277113,513.71955490340974,398.63523024058026,274.98128329644925,142.2729248359885]
[null,null,null,"Less: Fixed",null,{"$f":"F52-F63","v":72.574339672294258},78.703056655908966,85.188209488704416,92.048619963139615,99.304070961296617,106.97535490936144,115.0843246628295,123.65394694413099,132.70835846046074,142.27292483598944]
[null,null,null,"Less: Sweep"]
[null,null,null,"CB",1048.513206554115,{"$f":"F57-F58-F59","v":975.93886688182067},897.23581022591168,812.04760073720729,719.99898077406772,620.69490981277113,513.71955490340974,398.63523024058026,274.98128329644925,142.2729248359885,-9.3791641120333225E-13]
[]
[null,null,null,"Interest Rate",null,{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05}]
[null,null,null,"Interest Expense on OB",null,{"$f":"F62*F57","v":52.425660327705749},48.796943344091034,44.861790511295588,40.602380036860367,35.999949038703384,31.034745490638556,25.68597774517049,19.931761512029013,13.749064164822464,7.113646241799426]
[]
[null,null,"Debt Service Calculation"]
[null,null,null,"This Period DS",null,{"$f":"F63+F58+F59","v":125},127.5,130.05000000000001,132.65099999999998,135.30402000000001,138.0101004,140.77030240799999,143.58570845616001,146.45742262528321,149.38657107778886]
[null,null,null,"Current Interest",null,{"$f":"F60*F62+F58*F62","v":52.425660327705749},48.796943344091034,44.861790511295588,40.602380036860367,35.999949038703392,31.034745490638564,25.68597774517049,19.931761512029013,13.749064164822464,7.1136462417994251]
[null,null,null,"Next Interest",{"$f":"E60*F62","v":52.425660327705749},{"$f":"(F57-F58)*F62","v":48.796943344091034},44.861790511295588,40.602380036860367,35.999949038703384,31.034745490638556,25.68597774517049,19.931761512029013,13.749064164822464,7.113646241799426,-4.6895820560166616E-14]
[null,null,null,"Next Fixed Repayments",{"$f":"F58","v":72.574339672294258},{"$f":"MIN(F57,G58)","v":78.703056655908966},85.188209488704416,92.048619963139615,99.304070961296617,106.97535490936144,115.0843246628295,123.65394694413099,132.70835846046074,142.27292483598944,142.2729248359885]
[]
[null,null,"DSRA Required Balance"]
[null,null,null,"Next Period DS",{"$f":"E68+E69","v":125},127.5,130.05000000000001,132.65099999999998,135.30402000000001,138.0101004,140.77030240799999,143.58570845616001,146.45742262528321,149.38657107778886,142.27292483598845]
[null,null,null,"OB",{"$f":"E76","v":0},125,127.5,130.05000000000001,132.65099999999998,135.30402000000001,138.0101004,140.77030240799999,143.58570845616001,146.45742262528321,149.38657107778886]
[null,null,null,"Required Change",{"$f":"E72-E73","v":125},2.5,2.5500000000000114,2.6009999999999707,2.6530200000000264,2.7060803999999905,2.7602020079999932,2.8154060481600141,2.8717141691232086,2.9291484525056433,-7.1136462418004101]
[]
[null,null,null,"OB",null,{"$f":"E78","v":125},127.5,130.05000000000001,132.65099999999998,135.30402000000001,138.0101004,140.77030240799999,143.58570845616001,146.45742262528321,149.38657107778886]
[null,null,null,"Top-up",null,{"$f":"F74","v":2.5},2.5500000000000114,2.6009999999999707,2.6530200000000264,2.7060803999999905,2.7602020079999932,2.8154060481600141,2.8717141691232086,2.9291484525056433,-7.1136462418004101]
[null,null,null,"CB",{"$f":"E74","v":125},{"$f":"F76+F77","v":127.5},130.05000000000001,132.65099999999998,135.30402000000001,138.0101004,140.77030240799999,143.58570845616001,146.45742262528321,149.38657107778886,142.27292483598845]
[]
[]
[null,null,"Cash Flow"]
[null,null,null,"EBITDA",null,{"$f":"F47","v":200},204,208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[null,null,null,"Less: Top-up",null,{"$f":"F77","v":2.5},2.5500000000000114,2.6009999999999707,2.6530200000000264,2.7060803999999905,2.7602020079999932,2.8154060481600141,2.8717141691232086,2.9291484525056433,-7.1136462418004101]
[null,null,null,"Less: Interest",null,{"$f":"F63","v":52.425660327705749},48.796943344091034,44.861790511295588,40.602380036860367,35.999949038703384,31.034745490638556,25.68597774517049,19.931761512029013,13.749064164822464,7.113646241799426]
[null,null,null,"Less: Fixed Repayment",null,{"$f":"F58","v":72.574339672294258},78.703056655908966,85.188209488704416,92.048619963139615,99.304070961296617,106.97535490936144,115.0843246628295,123.65394694413099,132.70835846046074,142.27292483598944]
[null,null,null,"Cash Flow for Sweep",null,{"$f":"F82-F83-F84-F85","v":72.5},73.949999999999989,75.42900000000003,76.937579999999983,78.476331600000009,80.045858232000029,81.646775396639995,83.279710904572781,84.94530512266428,96.745588888473748]
[null,null,null,"Cash  Flow Sweep"]
[]
[null,null,null,"DSCR Check"]
[null,null,null,"EBiTDA",null,{"$f":"F82","v":200},204,208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[null,null,null,"Less: Top-up"]
[null,null,null,"CADS",null,{"$f":"F90-F91","v":200},204,208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[]
[null,null,null,"Debt Service",null,{"$f":"F66","v":125},127.5,130.05000000000001,132.65099999999998,135.30402000000001,138.0101004,140.77030240799999,143.58570845616001,146.45742262528321,149.38657107778886]
[]
[null,null,null,"DSCR",null,{"$f":"F92/F94","v":1.6},1.6,1.5999999999999999,1.6000000000000003,1.5999999999999999,1.6,1.6000000000000003,1.6,1.5999999999999999,1.6]
[null,null,null,"DSCR Check",null,{"$f":"F96=F51","v":true},true,true,true,true,true,true,true,true,true]
[]
[]
[]
["Case 4: Sculpted with cash sweep and without top-up problem - requires two step process with sculpting mode first"]
[]
[null,null,"Target DSCR",null,1.5]
[null,null,"Cash Sweep",null,0.5]
[]
[null,null,"EBITDA",null,null,{"$f":"F47","v":200},204,208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[null,null,"Less: DSRA Top-up"]
[null,null,"Total CADS",null,null,{"$f":"F106-F107","v":200},204,208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[]
[null,null,"Target DSCR",null,null,{"$f":"$E$44","v":1.6},1.6,1.6,1.6,1.6,1.6,1.6,1.6,1.6,1.6]
[null,null,"Required Debt Service",null,null,{"$f":"F108/F110","v":125},127.5,130.05000000000001,132.65099999999998,135.30402000000001,138.0101004,140.77030240799999,143.58570845616001,146.45742262528321,149.38657107778886]
[]
[null,null,"Alternative",null,null,{"$f":"F108/F110","v":125},{"$f":"G106/G110","v":127.5},130.05000000000001,132.65099999999998,135.30402000000001,138.0101004,140.77030240799999,143.58570845616001,146.45742262528321,149.38657107778886]
[]
[null,null,"Debt for sculpting mode: No sweep included"]
[null,null,"Debt"]
[null,null,null,"OB",null,{"$f":"E120","v":1048.513206554115},975.93886688182067,897.23581022591168,812.04760073720729,719.99898077406772,620.69490981277113,513.71955490340974,398.63523024058026,274.98128329644925,142.2729248359885]
[null,null,null,"Less: Fixed",null,{"$f":"F113-F123","v":72.574339672294258},78.703056655908966,85.188209488704416,92.048619963139615,99.304070961296617,106.97535490936144,115.0843246628295,123.65394694413099,132.70835846046074,142.27292483598944]
[null,null,null,"Less: Sweep"]
[null,null,null,"CB",{"$f":"E60","v":1048.513206554115},{"$f":"F117-F118","v":975.93886688182067},897.23581022591168,812.04760073720729,719.99898077406772,620.69490981277113,513.71955490340974,398.63523024058026,274.98128329644925,142.2729248359885,-9.3791641120333225E-13]
[]
[null,null,null,"Interest Rate",null,{"$f":"F132","v":0.05},0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05]
[null,null,null,"Interest Expense",null,{"$f":"F122*F117","v":52.425660327705749},48.796943344091034,44.861790511295588,40.602380036860367,35.999949038703384,31.034745490638556,25.68597774517049,19.931761512029013,13.749064164822464,7.113646241799426]
[]
[null,null,"Debt used in cash flow; include cash sweep"]
[null,null,"Debt"]
[null,null,null,"OB",null,{"$f":"E130","v":1118.414086991057},1011.3372693296865,895.68161078524724,771.81623126331294,639.32610781249525,497.75601163996737,346.62760453000504,185.43876710450542,13.662440310619395,0]
[null,null,null,"Less: Fixed",null,{"$f":"MIN(F118,F127)","v":72.574339672294258},78.703056655908966,85.188209488704416,92.048619963139615,99.304070961296617,106.97535490936144,115.0843246628295,123.65394694413099,13.662440310619395,0]
[null,null,null,"Less: Sweep",null,{"$f":"F158","v":34.502477989076439},36.952601888530268,38.677170033229871,40.441503487678112,42.266025211231266,44.153052200600897,46.104512762670147,48.122379849755028,0,0]
[null,null,null,"CB",1118.414086991057,{"$f":"F127-F128-F129","v":1011.3372693296865},895.68161078524724,771.81623126331294,639.32610781249525,497.75601163996737,346.62760453000504,185.43876710450542,13.662440310619395,0,0]
[]
[null,null,null,"Interest Rate",null,{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05}]
[null,null,null,"Interest Expense on OB",null,{"$f":"F132*F127","v":55.920704349552857},50.566863466484328,44.784080539262362,38.590811563165651,31.966305390624765,24.88780058199837,17.331380226500254,9.2719383552252719,0.68312201553096985,0]
[]
[null,null,"Debt Service Calculation"]
[null,null,null,"This Period DS",null,{"$f":"F133+F128+F129","v":162.99752201092355},166.22252201092357,168.64946006119663,171.08093501398338,173.53640156315265,176.01620769196069,178.5202176519999,181.04826514911127,14.345562326150365,0]
[null,null,null,"Current Interest",null,{"$f":"F127*F132","v":55.920704349552857},{"$f":"G130*G132+G128*G132","v":48.719233372057808},42.850222037600872,36.568736388781744,29.853004130063201,22.680147971968328,15.026154588366747,6.8658193627375201,0.68312201553096985,0]
[null,null,null,"Next Interest",{"$f":"E130*F132","v":55.920704349552857},{"$f":"(F127-F128)*F132","v":52.291987365938148},46.631710633688876,40.524670064827149,33.988380565008669,27.001101842559933,19.539032836530296,11.577163993358779,3.0892410080187211,0,0]
[null,null,null,"Next Fixed Repayments",{"$f":"F118","v":72.574339672294258},78.703056655908966,85.188209488704416,92.048619963139615,99.304070961296617,106.97535490936144,115.0843246628295,123.65394694413099,132.70835846046074,142.27292483598944,0]
[]
[null,null,"DSRA Required Balance"]
[null,null,null,"Next Period DS",{"$f":"E138+E139","v":128.49504402184712},130.99504402184712,131.81992012239328,132.57329002796678,133.29245152630529,133.97645675192138,134.6233574993598,135.23111093748977,135.79759946847946,142.27292483598944,0]
[null,null,null,"OB",{"$f":"E146","v":0},128.49504402184712,130.99504402184712,131.81992012239328,132.57329002796678,133.29245152630529,133.97645675192138,134.6233574993598,135.23111093748977,135.79759946847946,142.27292483598944]
[null,null,null,"Required Change",{"$f":"E142-E143","v":128.49504402184712},2.5,0.82487610054616312,0.75336990557349282,0.71916149833850795,0.68400522561609023,0.64690074743842274,0.60775343812997562,0.56648853098968743,6.4753253675099813,-142.27292483598944]
[]
[null,null,null,"OB",null,{"$f":"E148","v":128.49504402184712},130.99504402184712,131.81992012239328,132.57329002796678,133.29245152630529,133.97645675192138,134.6233574993598,135.23111093748977,135.79759946847946,142.27292483598944]
[null,null,null,"Top-up",null,{"$f":"F144","v":2.5},0.82487610054616312,0.75336990557349282,0.71916149833850795,0.68400522561609023,0.64690074743842274,0.60775343812997562,0.56648853098968743,6.4753253675099813,-142.27292483598944]
[null,null,null,"CB",{"$f":"E144","v":128.49504402184712},{"$f":"F146+F147","v":130.99504402184712},131.81992012239328,132.57329002796678,133.29245152630529,133.97645675192138,134.6233574993598,135.23111093748977,135.79759946847946,142.27292483598944,0]
[]
[null,null,"Cash Sweep Percent",null,null,{"$f":"E104","v":0.5},{"$f":"F150","v":0.5},0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5]
[]
[null,null,"Cash Flow"]
[null,null,null,"EBITDA",null,{"$f":"F106","v":200},204,208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[null,null,null,"Less: Top-up",null,{"$f":"F147","v":2.5},0.82487610054616312,0.75336990557349282,0.71916149833850795,0.68400522561609023,0.64690074743842274,0.60775343812997562,0.56648853098968743,6.4753253675099813,-142.27292483598944]
[null,null,null,"Less: Interest",null,{"$f":"F133","v":55.920704349552857},50.566863466484328,44.784080539262362,38.590811563165651,31.966305390624765,24.88780058199837,17.331380226500254,9.2719383552252719,0.68312201553096985,0]
[null,null,null,"Less: Fixed Repayment",null,{"$f":"F128","v":72.574339672294258},78.703056655908966,85.188209488704416,92.048619963139615,99.304070961296617,106.97535490936144,115.0843246628295,123.65394694413099,13.662440310619395,0]
[null,null,null,"Cash Flow for Sweep",null,{"$f":"F153-F154-F155-F156","v":69.004955978152879},73.905203777060535,77.354340066459741,80.883006975356224,84.532050422462532,88.306104401201793,92.209025525340294,96.244759699510055,213.51098850679278,381.29143856045164]
[null,null,null,"Cash  Flow Sweep",null,{"$f":"MIN(F157*'DSRA No Cash Sweep'!$E$15,F127-F128)","v":34.502477989076439},{"$f":"MIN(G157*'DSRA No Cash Sweep'!$E$15,G127-G128)","v":36.952601888530268},{"$f":"MIN(H157*'DSRA No Cash Sweep'!$E$15,H127-H128)","v":38.677170033229871},{"$f":"MIN(I157*'DSRA No Cash Sweep'!$E$15,I127-I128)","v":40.441503487678112},{"$f":"MIN(J157*'DSRA No Cash Sweep'!$E$15,J127-J128)","v":42.266025211231266},{"$f":"MIN(K157*'DSRA No Cash Sweep'!$E$15,K127-K128)","v":44.153052200600897},{"$f":"MIN(L157*'DSRA No Cash Sweep'!$E$15,L127-L128)","v":46.104512762670147},{"$f":"MIN(M157*'DSRA No Cash Sweep'!$E$15,M127-M128)","v":48.122379849755028},{"$f":"MIN(N157*'DSRA No Cash Sweep'!$E$15,N127-N128)","v":0},{"$f":"MIN(O157*'DSRA No Cash Sweep'!$E$15,O127-O128)","v":0}]
[]
[null,null,null,"DSCR Check"]
[null,null,null,"EBiTDA",null,{"$f":"F153","v":200},204,208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[null,null,null,"Less: Top-up"]
[null,null,null,"CADS",null,{"$f":"F161-F162","v":200},204,208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[]
[null,null,null,"Debt Service",null,{"$f":"E142","v":128.49504402184712},130.99504402184712,131.81992012239328,132.57329002796678,133.29245152630529,133.97645675192138,134.6233574993598,135.23111093748977,135.79759946847946,142.27292483598944]
[]
[null,null,null,"DSCR",null,{"$f":"IF(F165,F163/F165)","v":1.5564802636745694},1.557310824415443,1.5785171149155615,1.6009378658040918,1.6241462252442433,1.6481713727425715,1.6730565039864729,1.6988482305381001,1.7255966019844473,1.6799999999999995]
[null,null,null,"DSCR Check",null,{"$f":"F167=F110","v":false},false,false,false,false,false,false,false,false,false]
[]
[]
[]
["Case 5: Sculpted with cash sweep and with top-up problem - requires two step process with sculpting mode first and also copy and paste macro"]
[]
[null,null,"Target DSCR",null,{"$f":"K174/100","v":1.7},null,null,null,null,null,170]
[null,null,"Cash Sweep",null,0]
[]
[null,null,"EBITDA",null,null,{"$f":"F106","v":200},204,208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[null,null,"Less: DSRA Top-up",null,null,{"$f":"F231","v":2.6666666666666572},2.7199999999999989,2.7744000000000142,2.8298879999999826,2.8864857600000278,2.9442154751999965,3.0030997847039771,3.0631617803981044,3.124425016006029,0]
[null,null,"Total CADS",null,null,{"$f":"F177-F178","v":197.33333333333334},201.28,205.3056,209.41171200000002,213.59994623999998,217.87194516480002,222.22938406809604,226.67397174945791,231.20745118444711,239.0185137244622]
[]
[null,null,"Target DSCR",null,null,{"$f":"$E$174","v":1.7},1.7,1.7,1.7,1.7,1.7,1.7,1.7,1.7,1.7]
[null,null,"Required Debt Service",null,null,{"$f":"F179/F181","v":116.07843137254903},118.4,120.768,123.18336000000002,125.6470272,128.15996774400003,130.72316709888003,133.33763044085759,136.00438304967477,140.59912572027187]
[]
[null,null,"Alternative",null,null,{"$f":"F177/F181","v":117.64705882352942},120,122.4,124.84800000000001,127.34496000000001,129.89185920000003,132.48969638400001,135.13949031168002,137.84228011791362,140.59912572027187]
[]
[null,null,"Debt for sculpting mode: No sweep included"]
[null,null,"Debt"]
[null,null,null,"OB",null,{"$f":"E191","v":1118.4140869910568},1056.6877325170803,989.52211914293434,916.59822510008109,837.58013635508519,752.11418317283938,659.82803313148133,560.32973840405543,453.20673501257818,338.02479164529348]
[null,null,null,"Less: Fixed",null,{"$f":"F184-F194","v":61.726354473976578},67.165613374145977,72.923894042853277,79.018088744995964,85.465953182245755,92.286150041358056,99.498294727425943,107.12300339147725,115.1819433672847,123.69788613800719]
[null,null,null,"Less: Sweep"]
[null,null,null,"CB",1118.4140869910568,{"$f":"F188-F189","v":1056.6877325170803},989.52211914293434,916.59822510008109,837.58013635508519,752.11418317283938,659.82803313148133,560.32973840405543,453.20673501257818,338.02479164529348,214.32690550728628]
[]
[null,null,null,"Interest Rate",null,{"$f":"F205","v":0.05},0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05]
[null,null,null,"Interest Expense",null,{"$f":"F193*F188","v":55.920704349552842},52.834386625854023,49.476105957146721,45.829911255004056,41.879006817754259,37.605709158641972,32.99140165657407,28.016486920202773,22.660336750628911,16.901239582264676]
[null,null,null,"Fixed Repayment"]
[]
[]
[null,null,"Debt used in cash flow; include cash sweep"]
[null,null,"Debt"]
[null,null,null,"OB",null,{"$f":"E203","v":1118.4140869910568},1056.6877325170803,989.52211914293434,916.59822510008109,837.58013635508519,752.11418317283938,659.82803313148133,560.32973840405543,453.20673501257818,338.02479164529348]
[null,null,null,"Less: Fixed",null,{"$f":"MIN(F189,F200)","v":61.726354473976578},67.165613374145977,72.923894042853277,79.018088744995964,85.465953182245755,92.286150041358056,99.498294727425943,107.12300339147725,115.1819433672847,123.69788613800719]
[null,null,null,"Less: Sweep",null,{"$f":"F235","v":0},0,0,0,0,0,0,0,0,0]
[null,null,null,"CB",{"$f":"debt_bal3","v":1118.4140869910568},{"$f":"F200-F201-F202","v":1056.6877325170803},989.52211914293434,916.59822510008109,837.58013635508519,752.11418317283938,659.82803313148133,560.32973840405543,453.20673501257818,338.02479164529348,214.32690550728628]
[]
[null,null,null,"Interest Rate",null,{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05},{"$f":"'DSRA No Cash Sweep'!$E$14","v":0.05}]
[null,null,null,"Interest Expense on OB",null,{"$f":"F205*F200","v":55.920704349552842},52.834386625854023,49.476105957146721,45.829911255004056,41.879006817754259,37.605709158641972,32.99140165657407,28.016486920202773,22.660336750628911,16.901239582264676]
[]
[null,null,"Debt Service Calculation"]
[null,null,null,"This Period DS",null,{"$f":"F206+F201+F202","v":117.64705882352942},120,122.4,124.84800000000001,127.34496000000001,129.89185920000003,132.48969638400001,135.13949031168002,137.84228011791362,140.59912572027187]
[null,null,null,"Current Interest",null,{"$f":"F200*F205","v":55.920704349552842},{"$f":"G203*G205+G201*G205","v":52.834386625854023},49.476105957146721,45.829911255004056,41.879006817754259,37.605709158641972,32.99140165657407,28.016486920202773,22.660336750628911,16.901239582264676]
[null,null,null,"Next Interest",{"$f":"E203*F205","v":55.920704349552842},{"$f":"(F200-F201)*F205","v":52.834386625854023},49.476105957146721,45.829911255004056,41.879006817754259,37.605709158641972,32.99140165657407,28.016486920202773,22.660336750628911,16.901239582264676,10.716345275364315]
[null,null,null,"Next Fixed Repayments",{"$f":"F189","v":61.726354473976578},67.165613374145977,72.923894042853277,79.018088744995964,85.465953182245755,92.286150041358056,99.498294727425943,107.12300339147725,115.1819433672847,123.69788613800719,0]
[]
[null,null,"DSRA Required Balance"]
[null,null,null,"Next Period DS",{"$f":"E211+E212","v":117.64705882352942},120,122.4,124.84800000000001,127.34496000000001,129.89185920000003,132.48969638400001,135.13949031168002,137.84228011791362,140.59912572027187,10.716345275364315]
[null,null,null,"Fixed DS next Period",133.33333333333334,136,138.72,141.49440000000001,144.324288,147.21077376000002,150.15498923520002,153.158089019904,156.2212508003021,159.34567581630813,9.9475983006414035E-15]
[null,null,null,"Difference",{"$f":"ABS(E215-E216)","v":15.686274509803923},16,16.319999999999993,16.6464,16.979327999999981,17.318914559999996,17.665292851200007,18.018598708223976,18.378970682388484,18.746550096036259,10.716345275364304]
[]
[null,null,null,"Total Difference",{"$f":"SUM(E217:O217)","v":182.47667468301694}]
[]
[null,null,null,"OB",{"$f":"E224","v":0},133.33333333333334,136,138.72,141.49440000000001,144.324288,147.21077376000002,150.15498923520002,153.158089019904,156.2212508003021,159.34567581630813]
[null,null,null,"Required Change",{"$f":"E216-E221","v":133.33333333333334},2.6666666666666572,2.7199999999999989,2.7744000000000142,2.8298879999999826,2.8864857600000278,2.9442154751999965,3.0030997847039771,3.0631617803981044,3.124425016006029,-159.34567581630813]
[]
[null,null,null,"OB",null,{"$f":"E226","v":133.33333333333334},136,138.72,141.49440000000001,144.324288,147.21077376000002,150.15498923520002,153.158089019904,156.2212508003021,159.34567581630813]
[null,null,null,"Top-up",null,{"$f":"F222","v":2.6666666666666572},2.7199999999999989,2.7744000000000142,2.8298879999999826,2.8864857600000278,2.9442154751999965,3.0030997847039771,3.0631617803981044,3.124425016006029,-159.34567581630813]
[null,null,null,"CB",{"$f":"E222","v":133.33333333333334},{"$f":"F224+F225","v":136},138.72,141.49440000000001,144.324288,147.21077376000002,150.15498923520002,153.158089019904,156.2212508003021,159.34567581630813,0]
[]
[]
[null,null,"Cash Flow"]
[null,null,null,"EBITDA",null,{"$f":"F177","v":200},204,208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[null,null,null,"Less: Top-up",null,{"$f":"F225","v":2.6666666666666572},2.7199999999999989,2.7744000000000142,2.8298879999999826,2.8864857600000278,2.9442154751999965,3.0030997847039771,3.0631617803981044,3.124425016006029,-159.34567581630813]
[null,null,null,"Less: Interest",null,{"$f":"F206","v":55.920704349552842},52.834386625854023,49.476105957146721,45.829911255004056,41.879006817754259,37.605709158641972,32.99140165657407,28.016486920202773,22.660336750628911,16.901239582264676]
[null,null,null,"Less: Fixed Repayment",null,{"$f":"F201","v":61.726354473976578},67.165613374145977,72.923894042853277,79.018088744995964,85.465953182245755,92.286150041358056,99.498294727425943,107.12300339147725,115.1819433672847,123.69788613800719]
[null,null,null,"Cash Flow for Sweep",null,{"$f":"F230-F231-F232-F233","v":79.686274509803923},81.28,82.905599999999993,84.56371200000001,86.254986239999965,87.980085964800011,89.739687684096012,91.534481437777885,93.365171066533492,257.76506382049843]
[null,null,null,"Cash  Flow Sweep",null,{"$f":"MIN(F234*$E$175,F200-F201)","v":0},0,0,0,0,0,0,0,0,0]
[]
[null,null,null,"DSCR Check"]
[null,null,null,"EBiTDA",null,{"$f":"F230","v":200},204,208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[null,null,null,"Less: Top-up",null,{"$f":"F231","v":2.6666666666666572},2.7199999999999989,2.7744000000000142,2.8298879999999826,2.8864857600000278,2.9442154751999965,3.0030997847039771,3.0631617803981044,3.124425016006029,-159.34567581630813]
[null,null,null,"CADS",null,{"$f":"F238-F239","v":197.33333333333334},201.28,205.3056,209.41171200000002,213.59994623999998,217.87194516480002,222.22938406809604,226.67397174945791,231.20745118444711,398.36418954077033]
[]
[null,null,null,"Debt Service",null,{"$f":"E215","v":117.64705882352942},120,122.4,124.84800000000001,127.34496000000001,129.89185920000003,132.48969638400001,135.13949031168002,137.84228011791362,140.59912572027187]
[]
[null,null,null,"DSCR",null,{"$f":"IF(F242,F240/F242)","v":1.6773333333333333},1.6773333333333333,1.6773333333333333,1.6773333333333333,1.6773333333333329,1.6773333333333331,1.6773333333333336,1.6773333333333329,1.6773333333333333,2.8333333333333335]
[null,null,null,"DSCR Check",null,{"$f":"F244=F181","v":false},false,false,false,false,false,false,false,false,false]
[]
[null,null,null,null,null,{"$f":"E174","v":1.7}]
[]
[]
[]
["Artificial Case with only Interest in DSRA used to derive fromulas"]
[]
[null,null,"Repay  =  (EBIT - CB x Rate x DSCR)/ (DSCR + Rate x DSCR - Rate)"]
[]
[null,null,"DSCR",null,1.6]
[null,null,"Interest Rate",null,0.05]
[null,null,"EBITDA",null,null,200,{"$f":"F257*1.05","v":210},220.5,231.52500000000001,243.10125000000002,255.25631250000004,268.01912812500007,281.4200845312501,295.49108875781263,310.26564319570326]
[null,null,"Less: Top-up of DSRA",null,null,{"$f":"F289","v":-3.3069422132798323},-3.8002037468684762,-4.3350529725132887,-4.914442803352685,-5.5415188835257325,-6.2196315517077068,-6.9523485229248756,-7.7434683307613241,-8.5970345744961865,-9.5173510182731054]
[null,null,"CADS",null,null,{"$f":"F257-F258","v":203.30694221327983},213.80020374686848,224.8350529725133,236.43944280335268,248.64276888352575,261.47594405170776,274.97147664792493,289.16355286201144,304.08812333230884,319.78299421397639]
[]
[null,null,"DSCR",null,null,{"$f":"E255","v":1.6},{"$f":"F261","v":1.6},1.6,1.6,1.6,1.6,1.6,1.6,1.6,1.6]
[]
[null,null,"Required Debt Service",null,null,{"$f":"F259/F261","v":127.06683888329988},133.62512734179279,140.52190810782079,147.77465175209542,155.40173055220359,163.42246503231735,171.85717290495307,180.72722053875714,190.055077082693,199.86437138373523]
[]
[null,null,"EBIT - CB x Rate x DSCR",null,null,{"$f":"F257-F272*F274*F261","v":107.80631615292259},123.88664214791214,141.32272690393341,160.2108353892977,180.65351560293891,202.75998858567124,226.64656184735108,252.43706758281922,280.26332712857567,310.26564319570326]
[null,null,"DSCR + Rate x DSCR + Rate",null,null,{"$f":"F261+F261*F274-F274","v":1.6300000000000001},1.6300000000000001,1.6300000000000001,1.6300000000000001,1.6300000000000001,1.6300000000000001,1.6300000000000001,1.6300000000000001,1.6300000000000001,1.6300000000000001]
[null,null,"Repay",null,null,{"$f":"F265/F266","v":66.138844265596674},76.00407493736941,86.701059450265888,98.2888560670538,110.83037767051466,124.39263103415412,139.04697045849758,154.86936661522651,171.94069148992372,190.3470203654621]
[]
[null,null,"Debt"]
[null,null,null,"OB",null,{"$f":"F272+F271","v":1218.5598923540642},1152.4210480884676,1076.4169731510981,989.71591370083229,891.42705763377853,780.59667996326391,656.20404892910983,517.1570784706123,362.28771185538585,190.3470203654621]
[null,null,null,"Less: Fixed",null,{"$f":"F267","v":66.138844265596674},76.00407493736941,86.701059450265888,98.2888560670538,110.83037767051466,124.39263103415412,139.04697045849758,154.86936661522651,171.94069148992372,190.3470203654621]
[null,null,null,"CB",{"$f":"F270","v":1218.5598923540642},1152.4210480884676,1076.4169731510981,989.71591370083229,891.42705763377853,780.59667996326391,656.20404892910983,517.1570784706123,362.28771185538585,190.3470203654621,0]
[]
[null,null,null,"Interest Rate",{"$f":"E256","v":0.05},{"$f":"E274","v":0.05},0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05]
[null,null,null,"Interest Expense on OB",null,{"$f":"F274*F270","v":60.927994617703213},57.621052404423381,53.820848657554905,49.485795685041616,44.571352881688931,39.029833998163198,32.810202446455492,25.857853923530616,18.114385592769292,9.5173510182731054]
[]
[null,null,"Debt Service Calculation"]
[null,null,null,"This Period DS",null,{"$f":"F275+F271","v":127.06683888329988},133.62512734179279,140.52190810782079,147.77465175209542,155.40173055220359,163.42246503231732,171.85717290495307,180.72722053875714,190.055077082693,199.8643713837352]
[null,null,null,"Current Interest",null,{"$f":"F270*F274","v":60.927994617703213},{"$f":"G272*G274+G271*G274","v":57.621052404423374},53.820848657554912,49.485795685041623,44.571352881688931,39.029833998163198,32.810202446455492,25.85785392353062,18.114385592769292,9.5173510182731054]
[null,null,null,"Next Interest",{"$f":"E272*F274","v":60.927994617703213},{"$f":"F272*F274","v":57.621052404423381},53.820848657554905,49.485795685041616,44.571352881688931,39.029833998163198,32.810202446455492,25.857853923530616,18.114385592769292,9.5173510182731054,0]
[null,null,null,"Next Fixed Repayments"]
[]
[null,null,"DSRA Required Balance"]
[null,null,null,"Next Period DS",{"$f":"E280","v":60.927994617703213},57.621052404423381,53.820848657554905,49.485795685041616,44.571352881688931,39.029833998163198,32.810202446455492,25.857853923530616,18.114385592769292,9.5173510182731054,0]
[null,null,null,"OB",{"$f":"E288","v":0},60.927994617703213,57.621052404423381,53.820848657554905,49.485795685041616,44.571352881688931,39.029833998163198,32.810202446455492,25.857853923530616,18.114385592769292,9.5173510182731054]
[null,null,null,"Required Change",{"$f":"E284-E285","v":60.927994617703213},-3.3069422132798323,-3.8002037468684762,-4.3350529725132887,-4.914442803352685,-5.5415188835257325,-6.2196315517077068,-6.9523485229248756,-7.7434683307613241,-8.5970345744961865,-9.5173510182731054]
[]
[null,null,null,"OB",null,{"$f":"E290","v":60.927994617703213},57.621052404423381,53.820848657554905,49.485795685041616,44.571352881688931,39.029833998163198,32.810202446455492,25.857853923530616,18.114385592769292,9.5173510182731054]
[null,null,null,"Top-up",null,{"$f":"F286","v":-3.3069422132798323},-3.8002037468684762,-4.3350529725132887,-4.914442803352685,-5.5415188835257325,-6.2196315517077068,-6.9523485229248756,-7.7434683307613241,-8.5970345744961865,-9.5173510182731054]
[null,null,null,"CB",{"$f":"E286","v":60.927994617703213},{"$f":"F288+F289","v":57.621052404423381},53.820848657554905,49.485795685041616,44.571352881688931,39.029833998163198,32.810202446455492,25.857853923530616,18.114385592769292,9.5173510182731054,0]
[]
[null,null,null,"DSCR Check"]
[null,null,null,"EBIT ",null,{"$f":"F257","v":200},210,220.5,231.52500000000001,243.10125000000002,255.25631250000004,268.01912812500007,281.4200845312501,295.49108875781263,310.26564319570326]
[null,null,null,"Less: Top-up",null,{"$f":"F289","v":-3.3069422132798323},-3.8002037468684762,-4.3350529725132887,-4.914442803352685,-5.5415188835257325,-6.2196315517077068,-6.9523485229248756,-7.7434683307613241,-8.5970345744961865,-9.5173510182731054]
[null,null,null,"CADS",null,{"$f":"F293-F294","v":203.30694221327983},213.80020374686848,224.8350529725133,236.43944280335268,248.64276888352575,261.47594405170776,274.97147664792493,289.16355286201144,304.08812333230884,319.78299421397639]
[]
[null,null,null,"Debt Service",null,{"$f":"F271+F275","v":127.06683888329988},133.62512734179279,140.52190810782079,147.77465175209542,155.40173055220359,163.42246503231732,171.85717290495307,180.72722053875714,190.055077082693,199.8643713837352]
[]
[null,null,null,"DSCR",null,{"$f":"IF(F297,F295/F297)","v":1.6},1.6,1.6000000000000003,1.6,1.6,1.6000000000000003,1.6,1.6,1.6,1.6000000000000003]
[null,null,null,"DSCR Check",null,{"$f":"F299=F233","v":false},false,false,false,false,false,false,false,false,false]
[]
[]
[null,null,null,"CADS = DS x DSCR"]
[null,null,null,"DS = Repay + Int"]
[]
[null,null,null,"Int = CB x Rate + Repay x Rate"]
[]
[null,null,null,"CADS = EBIT + DSRA Chg "]
[null,null,null,"DSRA Chg = Int1 - Int"]
[]
[null,null,null,"DSRA Chg = CB x Repay - (CB x Rate - Repay x Rate)"]
[]
[null,null,null,"DSRA Chg = - Repay x Rate"]
[]
[null,null,null,"CADS = EBIT - Repay x Rate"]
[]
[null,null,null,"EBIT - Repay x Rate = DS x DSCR"]
[]
[null,null,null,"EBIT - Repay x Rate = (Repay + Int ) x DSCR"]
[]
[null,null,null,"EBIT - Repay x Rate = (Repay + CB x Rate + Repay x Rate ) x DSCR"]
[]
[null,null,null,"EBIT - Repay x Rate = Repay x DSCR + CB x Rate x DSCR + Repay x Rate x DSCR"]
[]
[null,null,null,"EBIT - Repay x Rate = Repay x DSCR + CB x Rate x DSCR + Repay x Rate x DSCR"]
[]
[null,null,null,"EBIT  = Repay x DSCR + CB x Rate x DSCR + Repay x Rate x DSCR + Repay x Rate"]
[]
[null,null,null,"EBIT - CB x Rate x DSCR = Repay x DSCR + Repay x Rate x DSCR + Repay x Rate"]
[]
[null,null,null,"Repay x (DSCR + Rate x DSCR - Rate) = EBIT - CB x Rate x DSCR"]
[]
[null,null,null,"Repay  =  (EBIT - CB x Rate x DSCR)/ (DSCR + Rate x DSCR - Rate)"]
[]
[]
[]
["Sculpting Analysis with Top-up and Backwards method and assuming DSRA is 100% of next year Debt Service"]
[]
[null,"Repayment formula (see derivation below)"]
[null,null," Repay = (EBIT - Repay 1 - CB x Rate x DSCR) / (Rate x DSCR + DSCR - 1 - Rate) "]
[]
[]
[null,"Inputs"]
[null,null,"DSCR",null,1.5]
[null,null,"Interest Rate",null,0.1]
[null,null,"Growth Rate",null,0]
[]
[null,"Required Debt Service"]
[null,null,"EBITDA",null,null,200,{"$f":"F349*(1+E346)","v":200},{"$f":"G349*(1+E346)","v":200},{"$f":"H349*(1+E346)","v":200},{"$f":"I349*(1+E346)","v":200},{"$f":"J349*(1+E346)","v":200},{"$f":"K349*(1+E346)","v":200},{"$f":"L349*(1+E346)","v":200},{"$f":"M349*(1+E346)","v":200},{"$f":"N349*(1+E346)","v":200}]
[null,null,"Less: Top-up of DSRA",null,null,{"$f":"F385","v":204800.00000000026},-102400.00000000013,51200.000000000058,-25600.000000000022,12800.000000000011,-6400.0000000000055,3200.0000000000027,-1600.0000000000011,800.00000000000023,-400]
[null,null,"CADS",null,null,{"$f":"F349-F350","v":-204600.00000000026},102600.00000000013,-51000.000000000058,25800.000000000022,-12600.000000000011,6600.0000000000055,-3000.0000000000027,1800.0000000000011,-600.00000000000023,600]
[]
[null,null,"DSCR",null,null,{"$f":"E344","v":1.5},{"$f":"F353","v":1.5},1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5]
[]
[null,null,"Required Debt Service",null,null,{"$f":"F351/F353","v":-136400.00000000017},68400.000000000087,-34000.000000000036,17200.000000000015,-8400.0000000000073,4400.0000000000036,-2000.0000000000018,1200.0000000000007,-400.00000000000017,400]
[]
[null,"Formula for Repayment"]
[null,null,"EBIT - Repay 1 - CB x Rate x DSCR",null,null,{"$f":"F349-G360-F367*F369*F353","v":-70373.493898851812},35229.156711263153,-17567.9276176106,8835.2796206283692,-4361.1924173088064,2242.6883409603183,-1053.042824943652,601.65289256198366,-218.18181818181824,200]
[null,null,"DSCR + Rate x DSCR - Rate + 1",null,null,{"$f":"F369*F353+F353-1-F369","v":0.54999999999999993},0.54999999999999993,0.54999999999999993,0.54999999999999993,0.54999999999999993,0.54999999999999993,0.54999999999999993,0.54999999999999993,0.54999999999999993,0.54999999999999993]
[null,null,"Repay",null,null,{"$f":"F358/F359","v":-127951.80708882149},64053.012202296646,-31941.686577473822,16064.144764778855,-7929.4407587432852,4077.6151653823977,-1914.6233180793674,1093.9143501126978,-396.69421487603324,363.63636363636368]
[]
[null,null,"Computed DSCR as Check",null,null,{"$f":"F395","v":1.5},1.5,1.4999999999999998,1.4999999999999998,1.5,1.5,1.5000000000000004,1.5,1.4999999999999998,1.4999999999999998]
[]
[null,null,"Debt"]
[null,null,null,"OB",null,{"$f":"F367+F366","v":-84481.929111787031},43469.877977034455,-20583.134225262191,11358.552352211629,-4705.5924125672263,3223.8483461760584,-853.76681920633928,1060.8564988730282,-33.05785123966956,363.63636363636368]
[null,null,null,"Less: Fixed",null,{"$f":"F360","v":-127951.80708882149},64053.012202296646,-31941.686577473822,16064.144764778855,-7929.4407587432852,4077.6151653823977,-1914.6233180793674,1093.9143501126978,-396.69421487603324,363.63636363636368]
[null,null,null,"CB",{"$f":"F365","v":-84481.929111787031},43469.877977034455,-20583.134225262191,11358.552352211629,-4705.5924125672263,3223.8483461760584,-853.76681920633928,1060.8564988730282,-33.05785123966956,363.63636363636368,0]
[]
[null,null,null,"Interest Rate",{"$f":"E345","v":0.1},{"$f":"E369","v":0.1},0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1]
[null,null,null,"Interest Expense on OB",null,{"$f":"F369*F365","v":-8448.1929111787031},4346.9877977034457,-2058.3134225262193,1135.855235221163,-470.55924125672266,322.38483461760586,-85.376681920633928,106.08564988730282,-3.305785123966956,36.363636363636367]
[]
[null,null,"Debt Service Calculation for DSRA"]
[null,null,null,"This Period DS",null,{"$f":"F370+F366","v":-136400.00000000017},68400.000000000087,-34000.000000000044,17200.000000000018,-8400.0000000000073,4400.0000000000036,-2000.0000000000014,1200.0000000000007,-400.00000000000023,400.00000000000006]
[null,null,null,"Current Interest",null,{"$f":"F365*F369","v":-8448.1929111787031},{"$f":"G367*G369+G366*G369","v":4346.9877977034457},-2058.3134225262193,1135.8552352211632,-470.55924125672266,322.38483461760586,-85.376681920633928,106.08564988730282,-3.3057851239669631,36.363636363636367]
[null,null,null,"Next Period Interest",{"$f":"E367*F369","v":-8448.1929111787031},{"$f":"F367*F369","v":4346.9877977034457},-2058.3134225262193,1135.855235221163,-470.55924125672266,322.38483461760586,-85.376681920633928,106.08564988730282,-3.305785123966956,36.363636363636367,0]
[null,null,null,"Next Fixed Repayments",{"$f":"F366","v":-127951.80708882149},64053.012202296646,-31941.686577473822,16064.144764778855,-7929.4407587432852,4077.6151653823977,-1914.6233180793674,1093.9143501126978,-396.69421487603324,363.63636363636368,0]
[]
[null,null,"DSRA Required Balance"]
[null,null,null,"Next Period DS",{"$f":"E375+E376","v":-136400.00000000017},68400.000000000087,-34000.000000000044,17200.000000000018,-8400.0000000000073,4400.0000000000036,-2000.0000000000014,1200.0000000000007,-400.00000000000023,400.00000000000006,0]
[null,null,null,"OB",{"$f":"E384","v":0},-136400.00000000017,68400.000000000087,-34000.000000000044,17200.000000000015,-8400.0000000000073,4400.0000000000036,-2000.0000000000018,1200.0000000000009,-400.00000000000023,400]
[null,null,null,"Required Change",{"$f":"E379-E380","v":-136400.00000000017},204800.00000000026,-102400.00000000013,51200.000000000058,-25600.000000000022,12800.000000000011,-6400.0000000000055,3200.0000000000027,-1600.0000000000011,800.00000000000023,-400]
[]
[null,null,"DSRA Balance"]
[null,null,null,"OB",null,{"$f":"E386","v":-136400.00000000017},68400.000000000087,-34000.000000000044,17200.000000000015,-8400.0000000000073,4400.0000000000036,-2000.0000000000018,1200.0000000000009,-400.00000000000023,400]
[null,null,null,"Top-up",null,{"$f":"F381","v":204800.00000000026},-102400.00000000013,51200.000000000058,-25600.000000000022,12800.000000000011,-6400.0000000000055,3200.0000000000027,-1600.0000000000011,800.00000000000023,-400]
[null,null,null,"CB",{"$f":"E381","v":-136400.00000000017},{"$f":"F384+F385","v":68400.000000000087},-34000.000000000044,17200.000000000015,-8400.0000000000073,4400.0000000000036,-2000.0000000000018,1200.0000000000009,-400.00000000000023,400,0]
[]
[null,null,null,"DSCR Check"]
[null,null,null,"EBIT ",null,{"$f":"F349","v":200},200,200,200,200,200,200,200,200,200]
[null,null,null,"Less: Top-up",null,{"$f":"F385","v":204800.00000000026},-102400.00000000013,51200.000000000058,-25600.000000000022,12800.000000000011,-6400.0000000000055,3200.0000000000027,-1600.0000000000011,800.00000000000023,-400]
[null,null,null,"CADS",null,{"$f":"F389-F390","v":-204600.00000000026},102600.00000000013,-51000.000000000058,25800.000000000022,-12600.000000000011,6600.0000000000055,-3000.0000000000027,1800.0000000000011,-600.00000000000023,600]
[]
[null,null,null,"Debt Service",null,{"$f":"F366+F370","v":-136400.00000000017},68400.000000000087,-34000.000000000044,17200.000000000018,-8400.0000000000073,4400.0000000000036,-2000.0000000000014,1200.0000000000007,-400.00000000000023,400.00000000000006]
[]
[null,null,null,"DSCR",null,{"$f":"IF(F393,F391/F393)","v":1.5},1.5,1.4999999999999998,1.4999999999999998,1.5,1.5,1.5000000000000004,1.5,1.4999999999999998,1.4999999999999998]
[null,null,null,"DSCR Check",null,{"$f":"F395=F318","v":false},false,false,false,false,false,false,false,false,false]
[]
[]
[null,null,null,"CADS = DS x DSCR"]
[null,null,null,"DS = Repay + Int"]
[]
[null,null,null,"Int = CB x Rate + Repay x Rate"]
[]
[null,null,null,"CADS = EBIT - DSRA Chg "]
[]
[null,null,null,"DSRA Chg = DS 1 - DS"]
[null,null,null,"DSRA Chg = CB x Repay - (CB x Rate + Repay x Rate) + Repay 1 - Repay"]
[]
[null,null,null,"DSRA Chg = Repay  1 - Repay - Repay x Rate"]
[]
[null,null,null,"CADS = EBIT - DSRA Chg"]
[]
[null,null,null,"CADS = EBIT - Repay 1 + Repay + Repay x Rate"]
[]
[null,null,null,"DS x DSCR = EBIT - Repay 1 + Repay + Repay x Rate"]
[]
[null,null,null,"(CB x Rate + Reapy x Rate + Repay) x DSCR = EBIT - Repay 1 + Repay + Repay x Rate"]
[]
[null,null,null,"(CB x Rate + Reapy x Rate + Repay) x DSCR - Repay - Repay x Rate= EBIT - Repay 1 "]
[]
[null,null,null,"(Reapy x Rate + Repay) x DSCR - Repay - Repay x Rate= EBIT - Repay 1 - CB x Rate x DSCR"]
[]
[null,null,null,"(Rate x DSCR + DSCR - 1 - Rate) x Repay = EBIT - Repay 1 - CB x Rate x DSCR"]
[]
[null,null,null," Repay = (EBIT - Repay 1 - CB x Rate x DSCR) / (Rate x DSCR + DSCR - 1 - Rate) "]
[]
[]
[]
[]
[]
["Sculpting Analysis with Top-up and Backwards method and DSRA Percentage"]
[]
[null,"Repayment formula (see derivation below)"]
[null,null," Repay = (EBIT - Repay 1 x DSRA Pct - CB x Rate x DSCR) / (Rate x DSCR + DSCR - DSRA Pct - Rate x DSRA Pct) "]
[]
[]
[null,"Inputs"]
[null,null,"DSCR",null,1.5,null,"Debt Issued",{"$f":"E463","v":2351.6062794927257}]
[null,null,"Interest Rate",null,0.05]
[null,null,"Growth Rate",null,0.2]
[null,null,"DSRA Pct",null,0.5]
[]
[null,"Required Debt Service"]
[null,null,"EBITDA",null,null,200,{"$f":"F444*(1+E440)","v":240},{"$f":"G444*(1+E440)","v":288},{"$f":"H444*(1+E440)","v":345.59999999999997},{"$f":"I444*(1+E440)","v":414.71999999999997},{"$f":"J444*(1+E440)","v":497.66399999999993},{"$f":"K444*(1+E440)","v":597.19679999999994},{"$f":"L444*(1+E440)","v":716.6361599999999},{"$f":"M444*(1+E440)","v":859.96339199999989},{"$f":"N444*(1+E440)","v":1031.9560703999998}]
[null,null,"Less: Top-up of DSRA",null,null,{"$f":"F481","v":12.625971200000002},14.748057600000024,18.503884799999923,20.592230400000091,27.935539199999909,27.072921600000058,45.386956799999979,28.665446400000036,85.996339199999909,-343.98535679999992]
[null,null,"CADS",null,null,{"$f":"F444-F445","v":187.37402879999999},225.25194239999996,269.49611520000008,325.00776959999985,386.78446080000003,470.5910783999999,551.80984319999993,687.97071359999984,773.96705279999992,1375.9414271999997]
[]
[null,null,"DSCR",null,null,{"$f":"E438","v":1.5},{"$f":"F448","v":1.5},1.5,1.5,1.5,1.5,1.5,1.5,1.5,1.5]
[]
[null,null,"Required Debt Service",null,null,{"$f":"F446/F448","v":124.91601919999999},150.16796159999998,179.66407680000006,216.67184639999991,257.8563072,313.72738559999993,367.87322879999994,458.64714239999989,515.97803519999991,917.29428479999979]
[]
[null,"Formula for Repayment"]
[null,null,"DSRA Pct",null,null,{"$f":"E441","v":0.5},{"$f":"F453","v":0.5},0.5,0.5,0.5,0.5,0.5,0.5,0.5,0]
[null,null,"EBIT - Repay 1 x DSRA Pct - CB x Rate x DSCR",null,null,{"$f":"F444-G456*F453-F463*F465*F448","v":7.7024904866318877},34.602154530963475,67.303183217511702,109.52650045838715,158.24650932130666,224.82346710737184,292.91777582274051,402.87627389387751,483.2175250285714,1031.9560703999998]
[null,null,"DSCR + Rate x DSCR - Rate x DSRA Pct  - DSRA Pct",null,null,{"$f":"F465*F448+F448-F453-F465*F453","v":1.05},1.05,1.05,1.05,1.05,1.05,1.05,1.05,1.05,1.575]
[null,null,"Repay",null,null,{"$f":"F454/F455","v":7.3357052253637027},32.954432886631878,64.098269730963523,104.31095281751156,150.7109612583873,214.11758772130651,278.96931030737193,383.69168942274047,460.20716669387753,655.21020342857128]
[]
[null,null,"Computed DSCR as Check",null,null,{"$f":"F491","v":1.5000000000000002},1.5,1.5000000000000002,1.5000000000000002,1.4999999999999998,1.5000000000000002,1.4999999999999998,1.4999999999999998,1.4999999999999998,2]
[]
[null,null,"Debt"]
[null,null,null,"OB",null,{"$f":"F463+F462","v":2351.6062794927257},2344.270574267362,2311.3161413807302,2247.2178716497665,2142.9069188322551,1992.1959575738676,1778.0783698525611,1499.1090595451892,1115.4173701224488,655.21020342857128]
[null,null,null,"Less: Fixed",null,{"$f":"F456","v":7.3357052253637027},32.954432886631878,64.098269730963523,104.31095281751156,150.7109612583873,214.11758772130651,278.96931030737193,383.69168942274047,460.20716669387753,655.21020342857128]
[null,null,null,"CB",{"$f":"F461","v":2351.6062794927257},2344.270574267362,2311.3161413807302,2247.2178716497665,2142.9069188322551,1992.1959575738676,1778.0783698525611,1499.1090595451892,1115.4173701224488,655.21020342857128,0]
[]
[null,null,null,"Interest Rate",{"$f":"E439","v":0.05},{"$f":"E465","v":0.05},0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05]
[null,null,null,"Interest Expense on OB",null,{"$f":"F465*F461","v":117.58031397463628},117.21352871336811,115.56580706903651,112.36089358248833,107.14534594161276,99.609797878693385,88.903918492628065,74.955452977259469,55.77086850612244,32.760510171428564]
[]
[null,null,"Debt Service Calculation for DSRA"]
[null,null,null,"This Period DS",null,{"$f":"F466+F462","v":124.91601919999998},150.16796159999998,179.66407680000003,216.67184639999988,257.85630720000006,313.72738559999988,367.87322879999999,458.64714239999995,515.97803520000002,687.97071359999984]
[null,null,null,"Current Interest",null,{"$f":"F461*F465","v":117.58031397463628},{"$f":"G463*G465+G462*G465","v":117.21352871336811},115.56580706903651,112.36089358248834,107.14534594161275,99.609797878693399,88.903918492628065,74.955452977259469,55.77086850612244,32.760510171428564]
[null,null,null,"Next Period Interest",{"$f":"E463*F465","v":117.58031397463628},{"$f":"F463*F465","v":117.21352871336811},115.56580706903651,112.36089358248833,107.14534594161276,99.609797878693385,88.903918492628065,74.955452977259469,55.77086850612244,32.760510171428564,0]
[null,null,null,"Next Fixed Repayments",{"$f":"F462","v":7.3357052253637027},32.954432886631878,64.098269730963523,104.31095281751156,150.7109612583873,214.11758772130651,278.96931030737193,383.69168942274047,460.20716669387753,655.21020342857128,0]
[]
[null,null,"DSRA Required Balance"]
[null,null,null,"Next Period DS",{"$f":"(E471+E472)*$E$441","v":62.45800959999999},75.083980799999992,89.832038400000016,108.33592319999994,128.92815360000003,156.86369279999994,183.9366144,229.32357119999998,257.98901760000001,343.98535679999992,0]
[null,null,null,"OB",{"$f":"E480","v":0},62.45800959999999,75.083980799999992,89.832038400000016,108.33592319999994,128.92815360000003,156.86369279999994,183.9366144,229.32357119999998,257.98901760000001,343.98535679999992]
[null,null,null,"Required Change",{"$f":"E475-E476","v":62.45800959999999},12.625971200000002,14.748057600000024,18.503884799999923,20.592230400000091,27.935539199999909,27.072921600000058,45.386956799999979,28.665446400000036,85.996339199999909,-343.98535679999992]
[]
[null,null,"DSRA Balance"]
[null,null,null,"OB",null,{"$f":"E482","v":62.45800959999999},75.083980799999992,89.832038400000016,108.33592319999994,128.92815360000003,156.86369279999994,183.9366144,229.32357119999998,257.98901760000001,343.98535679999992]
[null,null,null,"Top-up",null,{"$f":"F477","v":12.625971200000002},14.748057600000024,18.503884799999923,20.592230400000091,27.935539199999909,27.072921600000058,45.386956799999979,28.665446400000036,85.996339199999909,-343.98535679999992]
[null,null,null,"CB",{"$f":"E477","v":62.45800959999999},{"$f":"F480+F481","v":75.083980799999992},89.832038400000016,108.33592319999994,128.92815360000003,156.86369279999994,183.9366144,229.32357119999998,257.98901760000001,343.98535679999992,0]
[]
[null,null,null,"DSCR Check"]
[null,null,null,"EBIT ",null,{"$f":"F444","v":200},240,288,345.59999999999997,414.71999999999997,497.66399999999993,597.19679999999994,716.6361599999999,859.96339199999989,1031.9560703999998]
[null,null,null,"Less: Top-up",null,{"$f":"F481","v":12.625971200000002},14.748057600000024,18.503884799999923,20.592230400000091,27.935539199999909,27.072921600000058,45.386956799999979,28.665446400000036,85.996339199999909,-343.98535679999992]
[null,null,null,"CADS",null,{"$f":"F485-F486","v":187.37402879999999},225.25194239999996,269.49611520000008,325.00776959999985,386.78446080000003,470.5910783999999,551.80984319999993,687.97071359999984,773.96705279999992,1375.9414271999997]
[]
[null,null,null,"Debt Service",null,{"$f":"F462+F466","v":124.91601919999998},150.16796159999998,179.66407680000003,216.67184639999988,257.85630720000006,313.72738559999988,367.87322879999999,458.64714239999995,515.97803520000002,687.97071359999984]
[]
[null,null,null,"DSCR",null,{"$f":"IF(F489,F487/F489)","v":1.5000000000000002},1.5,1.5000000000000002,1.5000000000000002,1.4999999999999998,1.5000000000000002,1.4999999999999998,1.4999999999999998,1.4999999999999998,2]
[null,null,null,"DSCR Check",null,{"$f":"F491=F412","v":false},false,false,false,false,false,false,false,false,false]
[]
[]
[null,null,null,"CADS = DS x DSCR"]
[null,null,null,"DS = Repay + Int"]
[]
[null,null,null,"Int = CB x Rate + Repay x Rate"]
[]
[null,null,null,"CADS = EBIT - DSRA Chg "]
[]
[null,null,null,"DSRA Chg = (DS 1 - DS) x DSRA Pct"]
[null,null,null,"DSRA Chg = (CB x Repay - (CB x Rate + Repay x Rate) + Repay 1 - Repay) x DSRA Pct"]
[]
[null,null,null,"DSRA Chg = (Repay  1 - Repay - Repay x Rate) x DSRA Pct"]
[]
[null,null,null,"CADS = EBIT - DSRA Chg"]
[]
[null,null,null,"CADS = EBIT - (Repay 1 + Repay + Repay x Rate) x DSRA Pct"]
[]
[null,null,null,"DS x DSCR = EBIT - Repay 1 x DSRA Pct + Repay x DSRA Pct + Repay x Rate x DSRA Pct"]
[]
[null,null,null,"(CB x Rate + Reapy x Rate + Repay) x DSCR = EBIT - Repay 1 x DSRA Pct + Repay x DSRA Pct + Repay x Rate x DSRA Pct"]
[]
[null,null,null,"(CB x Rate + Reapy x Rate + Repay) x DSCR - Repay x DSRA Pct - Repay x Rate x DSRA Pct = EBIT - Repay 1  x DSRA Pct"]
[]
[null,null,null,"(Reapy x Rate + Repay) x DSCR - Repay x DSRA Pct - Repay x Rate x DSRA Pct = EBIT - Repay 1 x DSRA Pct - CB x Rate x DSCR"]
[]
[null,null,null,"(Rate x DSCR + DSCR - DSRA Pct - Rate x DSRA Pct) x Repay = EBIT - Repay 1 - CB x Rate x DSCR"]
[]
[null,null,null," Repay = (EBIT - Repay 1 - CB x Rate x DSCR) / (Rate x DSCR + DSCR - DSRA Pct - Rate x DSRA Pct) "]
[]
[]
[]
[]
[]
["Case 3: Sculpted without cash sweep and without top-up problem -- demonstrates that cannot solve top-up problem unless use the backwards menthod"]
[]
[null,null,"Target DSCR",null,1.6]
[null,null,"Interest Rate",null,0.05]
[null,null,"Interest Income Rate",null,0.02]
[null,null,"DSRA Percent",null,0.5]
[]
[null,null,"Repay = (EBIT/DSCR + Int x Inc Rate/DSCR  - Int) /   ( 1  - Int Inc Rate/DSCR)"]
[]
[]
[null,null,"EBITDA",null,null,200,{"$f":"F537*1.02","v":204},208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[null,null,"Less: DSRA Top-up"]
[null,null,"Add Interest Income",null,null,{"$f":"F582","v":1.2578616352201257},1.2830188679245282,1.3086792452830189,1.3348528301886793,1.3615498867924527,1.3887808845283018,1.416556502218868,1.4448876322632453,1.4737853849085101,1.5032610926066801]
[null,null,"Total CADS",null,null,{"$f":"F537+F539","v":201.25786163522014},{"$f":"G537-G538","v":204},208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[]
[null,null,"Target DSCR",null,null,{"$f":"E529","v":1.6},{"$f":"F542","v":1.6},1.6,1.6,1.6,1.6,1.6,1.6,1.6,1.6]
[null,null,"Required Debt Service",null,null,{"$f":"F540/F542","v":125.78616352201259},127.5,130.05000000000001,132.65099999999998,135.30402000000001,138.0101004,140.77030240799999,143.58570845616001,146.45742262528321,149.38657107778886]
[]
[null,null,"DSRA Percent",null,null,{"$f":"E532","v":0.5},{"$f":"F545","v":0.5},0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5]
[null,null,"Income Rate",null,null,{"$f":"E531","v":0.02},{"$f":"F546","v":0.02},0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02]
[null,null,"Income Rate",null,null,{"$f":"F545*F546","v":0.01},0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01]
[null,null,"(EBIT/DSCR + Int x Inc Rate/DSCR  - Int) ",null,null,{"$f":"(F537/F542+F561*F547/F542-F561)","v":72.242531695536599},78.35465828031343,84.8223911943291,91.664510754045551,98.900756291747825,106.55187450633521,114.63967023965198,123.18705979979457,132.2181269589075,141.75818175935854]
[null,null,"( 1  - Int Inc Rate/DSCR)",null,null,{"$f":"(1-F547/F542)","v":0.99375000000000002},0.99375000000000002,0.99375000000000002,0.99375000000000002,0.99375000000000002,0.99375000000000002,0.99375000000000002,0.99375000000000002,0.99375000000000002,0.99375000000000002]
[null,null,"Repayment",null,null,{"$f":"F548/F549","v":72.69688724079154},78.847454873271374,85.355865352783994,92.241017110989233,99.522773626916049,107.22201208184676,115.3606744549957,123.96182118218321,133.04968750581887,142.64974265092681]
[]
[null,null,"Alternative",null,null,{"$f":"F540/F542","v":125.78616352201259},{"$f":"G537/G542","v":127.5},130.05000000000001,132.65099999999998,135.30402000000001,138.0101004,140.77030240799999,143.58570845616001,146.45742262528321,149.38657107778886]
[]
[null,null,"Debt Balance"]
[null,null,null,"OB",null,{"$f":"E558","v":1061.7855256244206},989.08863838362913,910.24118351035781,824.88531815757381,732.64430104658459,633.1215274196685,525.89951533782175,410.53884088282604,286.57701970064284,153.52733219482397]
[null,null,null,"Less: Fixed",null,{"$f":"F550","v":72.69688724079154},78.847454873271374,85.355865352783994,92.241017110989233,99.522773626916049,107.22201208184676,115.3606744549957,123.96182118218321,133.04968750581887,142.64974265092681]
[null,null,null,"Less: Sweep"]
[null,null,null,"CB",1061.7855256244206,{"$f":"F555-F556-F557","v":989.08863838362913},910.24118351035781,824.88531815757381,732.64430104658459,633.1215274196685,525.89951533782175,410.53884088282604,286.57701970064284,153.52733219482397,10.877589543897159]
[]
[null,null,null,"Interest Rate",null,{"$f":"E530","v":0.05},{"$f":"F560","v":0.05},0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05]
[null,null,null,"Interest Expense on OB",null,{"$f":"F560*F555","v":53.089276281221032},49.454431919181459,45.512059175517891,41.244265907878692,36.632215052329229,31.656076370983428,26.294975766891088,20.526942044141304,14.328850985032142,7.6763666097411987]
[]
[null,null,"Debt Service Calculation"]
[null,null,null,"This Period DS",null,{"$f":"F561+F556+F557","v":125.78616352201257},128.30188679245282,130.8679245283019,133.48528301886793,136.15498867924526,138.87808845283018,141.65565022188679,144.48876322632452,147.37853849085101,150.32610926066801]
[null,null,null,"Current Interest",null,{"$f":"F558*F560+F556*F560","v":53.089276281221039},49.454431919181459,45.512059175517891,41.244265907878692,36.632215052329229,31.656076370983428,26.294975766891088,20.526942044141304,14.328850985032142,7.6763666097411996]
[null,null,null,"Next Interest",{"$f":"E558*F560","v":53.089276281221032},{"$f":"(F555-F556)*F560","v":49.454431919181459},45.512059175517891,41.244265907878692,36.632215052329229,31.656076370983428,26.294975766891088,20.526942044141304,14.328850985032142,7.6763666097411987,0.54387947719485796]
[null,null,null,"Next Fixed Repayments",{"$f":"F556","v":72.69688724079154},78.847454873271374,85.355865352783994,92.241017110989233,99.522773626916049,107.22201208184676,115.3606744549957,123.96182118218321,133.04968750581887,142.64974265092681,0]
[]
[null,null,"DSRA Required Balance"]
[null,null,null,"DSRA Pct",{"$f":"E532","v":0.5},{"$f":"E570","v":0.5},0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5]
[null,null,null,"Next Period DS  ",{"$f":"E566+E567","v":125.78616352201257},128.30188679245282,130.8679245283019,133.48528301886793,136.15498867924526,138.87808845283018,141.65565022188679,144.48876322632452,147.37853849085101,150.32610926066801,0.54387947719485796]
[null,null,null,"Next Period DS x DSRA Pct",{"$f":"E570*E571","v":62.893081761006286},64.15094339622641,65.433962264150949,66.742641509433966,68.077494339622632,69.439044226415092,70.827825110943394,72.244381613162261,73.689269245425507,75.163054630334003,0.27193973859742898]
[]
[null,null,null,"OB",{"$f":"E577","v":0},62.893081761006286,64.15094339622641,65.433962264150949,66.742641509433966,68.077494339622632,69.439044226415092,70.827825110943394,72.244381613162261,73.689269245425507,75.163054630334003]
[null,null,null,"Required Top-up",{"$f":"E572-E574","v":62.893081761006286},1.2578616352201237,1.2830188679245396,1.3086792452830167,1.334852830188666,1.36154988679246,1.3887808845283018,1.4165565022188673,1.4448876322632458,1.4737853849084956,-74.891114891736578]
[]
[null,null,null,"OB",null,{"$f":"E579","v":62.893081761006286},64.15094339622641,65.433962264150949,66.742641509433966,68.077494339622632,69.439044226415092,70.827825110943394,72.244381613162261,73.689269245425507,75.163054630334003]
[null,null,null,"Top-up",null,{"$f":"F575","v":1.2578616352201237},1.2830188679245396,1.3086792452830167,1.334852830188666,1.36154988679246,1.3887808845283018,1.4165565022188673,1.4448876322632458,1.4737853849084956,-74.891114891736578]
[null,null,null,"CB",{"$f":"E575","v":62.893081761006286},{"$f":"F577+F578","v":64.15094339622641},65.433962264150949,66.742641509433966,68.077494339622632,69.439044226415092,70.827825110943394,72.244381613162261,73.689269245425507,75.163054630334003,0.27193973859742471]
[]
[null,null,null,"Interest Income Rate",{"$f":"E531","v":0.02},{"$f":"E581","v":0.02},0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02]
[null,null,null,"Interest Income  ",null,{"$f":"F577*F581","v":1.2578616352201257},1.2830188679245282,1.3086792452830189,1.3348528301886793,1.3615498867924527,1.3887808845283018,1.416556502218868,1.4448876322632453,1.4737853849085101,1.5032610926066801]
[]
[null,null,"Cash Flow"]
[null,null,null,"EBITDA",null,{"$f":"F537","v":200},204,208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[null,null,null,"Less: Top-up",null,{"$f":"F578","v":1.2578616352201237},1.2830188679245396,1.3086792452830167,1.334852830188666,1.36154988679246,1.3887808845283018,1.4165565022188673,1.4448876322632458,1.4737853849084956,-74.891114891736578]
[null,null,null,"Less: Interest",null,{"$f":"F561","v":53.089276281221032},49.454431919181459,45.512059175517891,41.244265907878692,36.632215052329229,31.656076370983428,26.294975766891088,20.526942044141304,14.328850985032142,7.6763666097411987]
[null,null,null,"Less: Fixed Repayment",null,{"$f":"F556","v":72.69688724079154},78.847454873271374,85.355865352783994,92.241017110989233,99.522773626916049,107.22201208184676,115.3606744549957,123.96182118218321,133.04968750581887,142.64974265092681]
[null,null,null,"Add: Interest Income",null,{"$f":"F582","v":1.2578616352201257},1.2830188679245282,1.3086792452830189,1.3348528301886793,1.3615498867924527,1.3887808845283018,1.416556502218868,1.4448876322632453,1.4737853849085101,1.5032610926066801]
[null,null,null,"Cash Flow for Sweep",null,{"$f":"F585-F586-F587-F588","v":72.95597484276729},74.415094339622613,75.903396226415126,77.421464150943436,78.96989343396227,80.549291302641549,82.160277128694347,83.803482671268242,85.479552324693628,163.5835193555308]
[null,null,null,"Cash  Flow Sweep"]
[]
[null,null,null,"DSCR Check"]
[null,null,null,"EBiTDA",null,{"$f":"F585","v":200},204,208.08,212.24160000000001,216.48643200000001,220.81616064000002,225.23248385280002,229.73713352985601,234.33187620045314,239.0185137244622]
[null,null,null,"Less: Top-up"]
[null,null,null,"Add: Interest Income",null,{"$f":"F589","v":1.2578616352201257},1.2830188679245282,1.3086792452830189,1.3348528301886793,1.3615498867924527,1.3887808845283018,1.416556502218868,1.4448876322632453,1.4737853849085101,1.5032610926066801]
[null,null,null,"CADS",null,{"$f":"F594+F596","v":201.25786163522014},205.28301886792454,209.38867924528304,213.57645283018869,217.84798188679247,222.20494152452832,226.64904035501888,231.18202116211924,235.80566158536166,240.52177481706889]
[]
[null,null,null,"Debt Service",null,{"$f":"F564","v":125.78616352201257},128.30188679245282,130.8679245283019,133.48528301886793,136.15498867924526,138.87808845283018,141.65565022188679,144.48876322632452,147.37853849085101,150.32610926066801]
[]
[null,null,null,"DSCR",null,{"$f":"F597/F599","v":1.6},1.6000000000000003,1.6,1.5999999999999999,1.6000000000000003,1.6000000000000003,1.6,1.6,1.6000000000000003,1.6000000000000005]
[null,null,null,"DSCR Check",null,{"$f":"F601=F542","v":true},true,true,true,true,true,true,true,true,true]
[]
[null,"DSCR = (EBIT + Int Income)/(Int + Repay)"]
[null,"Int + Repay = (EBIT + Int Income)/DSCR"]
[]
[null,"Int Income = DSRA OB x Int Inc Rate"]
[null,"Int Income = DS x Int Inc Rate"]
[null,"Int Income = (Int + Repay) x Int Inc Rate"]
[]
[null,"DSCR = (EBIT + (Int + Repay) x Int Inc Rate)/(Int + Repay)"]
[null,"Int + Repay = EBIT/DSCR + Int x Inc Rate/DSCR + Repay x Int Inc Rate/DSCR"]
[null,"Repay - Repay x Int Inc Rate/DSCR = EBIT/DSCR + Int x Inc Rate/DSCR  - Int"]
[null,"Repay x  ( 1  - Int Inc Rate/DSCR) = EBIT/DSCR + Int x Inc Rate/DSCR  - Int"]
[null,"Repay = (EBIT/DSCR + Int x Inc Rate/DSCR  - Int) /   ( 1  - Int Inc Rate/DSCR)"]