{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Model","Macros"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":79,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Model","index":0,"n_rows":109,"n_cols":26,"rows_kept":109,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Simple Project Finance Example (Solar) with VBA Macro for Debt Sizing"]
[null,"($ in Millions)"]
[]
[]
[null,"Assumptions:",null,"Units:",null,null,null,null,null,{"$f":"$D$6","v":"Units:"}]
[]
[null,null,"Project Capacity:","MW",1600,null,"Initial PPA Rate:",null,null,"$ / MWh",65]
[null,null,"Net Capacity Factor:","%",0.25,null,"Escalation Factor:",null,null,"%",0.03]
[null,null,"Annual Degradation Factor:","%",5.0000000000000001E-3,null,"Hours in Year:",null,null,"# Hours",{"$f":"24*365","v":8760}]
[]
[null,null,"Units:","#",1000000,null,"Operating & Maintenance Expense:",null,null,"$ / MW / Yr",20000]
[null,null,null,null,null,null,"Escalation Factor:",null,null,"%",2.5000000000000001E-2]
[null,null,"Loan Life Coverage Ratio (LLCR) Target:","x",1.5]
[null,null,"Fixed Interest Rate on Debt:","%",0.1,null,"Useful Life of Maintenance CapEx:",null,null,"# Years",15]
[null,null,"Tax Rate:","%",0.25,null,"Working Capital % Revenue:",null,null,"#",0.05]
[]
[null,null,"Present Value of CFADS Over Debt Tenor:","$ M",{"$f":"NPV(Interest_Rate,OFFSET(G49,0,0,1,Tenor))","v":1184.5421007371726},null,"Asset Useful Life:",null,null,"# Years",20]
[null,null,"Initial Debt Balance:","$ M",{"$f":"E18/Min_LLCR","v":789.69473382478179},null,"EBITDA Purchase Multiple:",null,null,"x",8]
[null,null,"Debt Tenor:","# Years",10]
[null,null,null,null,null,null,"Purchase Enterprise Value:",null,null,"$ M",{"$f":"G43*K19","v":1604.9586880000002}]
[null,null,null,null,null,null,"(-) Debt Used:",null,null,"$ M",{"$f":"-E19","v":-789.69473382478179}]
[null,null,null,null,null,null,"Investor Equity:",null,null,"$ M",{"$f":"SUM(K21:K22)","v":815.26395417521837}]
[]
[null,null,null,null,null,0,{"$f":"F25+1","v":1},{"$f":"G25+1","v":2},{"$f":"H25+1","v":3},4,5,6,7,8,9,{"$f":"O25+1","v":10},{"$f":"P25+1","v":11},{"$f":"Q25+1","v":12},{"$f":"R25+1","v":13},{"$f":"S25+1","v":14},{"$f":"T25+1","v":15},{"$f":"U25+1","v":16},{"$f":"V25+1","v":17},{"$f":"W25+1","v":18},{"$f":"X25+1","v":19},20]
[null,null,null,null,null,null,"Projected Years:"]
[null,"Cash Flow Projections:",null,{"$f":"$D$6","v":"Units:"},"Total:",{"$f":"\"Year \"&F25","v":"Year 0"},{"$f":"\"Year \"&G25","v":"Year 1"},"Year 2","Year 3","Year 4","Year 5","Year 6","Year 7","Year 8","Year 9","Year 10","Year 11","Year 12","Year 13","Year 14","Year 15","Year 16","Year 17","Year 18","Year 19","Year 20"]
[]
[null,null,"Capacity Post-Availability and Degradation:","MW",null,{"$f":"Capacity*Capacity_Factor*(1-F25*Degradation)","v":400},398,396,394,392,390,388,386,384,382,380,378,376,374,372,370,368,366,364,362,360]
[null,null,"Electricity Generation:","MWh",null,null,{"$f":"G29*Hrs_in_Year","v":3486480},3468960,3451440,3433920,3416400,3398880,3381360,3363840,3346320,3328800,3311280,3293760,3276240,3258720,3241200,3223680,3206160,3188640,3171120,3153600]
[]
[null,null,"PPA Rate:","$ / MWh",null,{"$f":"Initial_PPA*(1+PPA_Escalation)^F25","v":65},66.95,68.958500000000001,71.027254999999997,73.158072649999994,75.352814829499991,77.613399274384989,79.941801252616543,82.340055290195039,84.810256948900886,87.354564657367916,89.975201597088954,92.67445764500161,95.454691374351654,98.318332115582209,101.26788207904968,104.30591854142116,107.43509609766379,110.65814898059371,113.97789345001152,117.39723025351186]
[null,null,"O&M Expense:","$ / MW",null,{"$f":"OM_Expense*(1+OM_Escalation)^F25","v":20000},20500,21012.5,21537.812499999996,22076.257812499996,22628.164257812492,23193.868364257803,23773.715073364248,24368.057950198356,24977.259398953309,25601.690883927142,26241.733156025319,26897.776484925951,27570.220897049097,28259.476419475322,28965.963329962211,29690.112413211264,30432.365223541539,31193.174354130078,31973.003712983333,32772.328805807912]
[]
[null,null,"Revenue:","$ M",{"$f":"SUM(G35:Z35)","v":5941.8601476463946},null,{"$f":"G32*G30/Units","v":233.419836},239.21427815999999,245.14630899719998,251.21896883428798,257.43535658350379,263.79863052572165,270.31200908354748,276.97877158736969,283.80225903324606,290.78587483144634,297.93308554440875,305.24742161280045,312.73247806830585,320.39191523169006,328.22945939461584,336.24890348360861,344.45410770448569,352.84900016548033,361.43757747720053,370.22390532747499]
[null,null,"(-) Cash Operating Expenses:","$ M",{"$f":"SUM(G36:Z36)","v":-837.86476966100065},null,{"$f":"-G33*Capacity/Units","v":-32.799999999999997},-33.619999999999997,-34.460499999999989,-35.322012499999992,-36.205062812499982,-37.110189382812486,-38.037944117382793,-38.988892720317374,-39.963615038325294,-40.962705414283427,-41.986773049640504,-43.036442375881521,-44.112353435278557,-45.215162271160516,-46.345541327939543,-47.50417986113802,-48.691784357666464,-49.909078966608121,-51.156805940773332,-52.435726089292658]
[null,null,"EBITDA:","$ M",{"$f":"SUM(E35:E36)","v":5103.995377985394},null,{"$f":"SUM(G35:G36)","v":200.61983600000002},{"$f":"SUM(H35:H36)","v":205.59427815999999},210.68580899719998,215.89695633428798,221.2302937710038,226.68844114290917,232.2740649661647,237.98987886705231,243.83864399492077,{"$f":"SUM(P35:P36)","v":249.82316941716292},{"$f":"SUM(Q35:Q36)","v":255.94631249476825},262.21097923691894,268.62012463302727,275.17675296052954,281.8839180666763,288.74472362247059,295.76232334681924,302.93992119887218,310.28077153642721,317.78817923818235]
[null,null,"(-) Depreciation of Initial Purchase:","$ M",{"$f":"SUM(G38:Z38)","v":-1604.9586880000004},null,{"$f":"-$K$21/$K$18","v":-80.247934400000005},-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005,-80.247934400000005]
[null,null,"(-) Depreciation of Maint. CapEx:","$ M",{"$f":"SUM(G39:Z39)","v":-47.105346318816672},null,{"$f":"SUM($F44:F44)/$K$15","v":0},{"$f":"SUM($F44:G44)/$K$15","v":0},{"$f":"SUM($F44:H44)/$K$15","v":0},{"$f":"SUM($F44:I44)/$K$15","v":0},{"$f":"SUM($F44:J44)/$K$15","v":0},{"$f":"SUM($F44:K44)/$K$15","v":-1.4748686251400254},{"$f":"SUM($F44:L44)/$K$15","v":-1.4748686251400254},{"$f":"SUM($F44:M44)/$K$15","v":-1.4748686251400254},{"$f":"SUM($F44:N44)/$K$15","v":-1.4748686251400254},{"$f":"SUM($F44:O44)/$K$15","v":-1.4748686251400254},{"$f":"SUM($F44:P44)/$K$15","v":-3.9731003193116545},{"$f":"SUM($F44:Q44)/$K$15","v":-3.9731003193116545},{"$f":"SUM($F44:R44)/$K$15","v":-3.9731003193116545},{"$f":"SUM($F44:S44)/$K$15","v":-3.9731003193116545},{"$f":"SUM($F44:T44)/$K$15","v":-3.9731003193116545},{"$f":"SUM($F44:U44)/$K$15","v":-3.9731003193116545},{"$f":"SUM($F44:V44)/$K$15","v":-3.9731003193116545},{"$f":"SUM($F44:W44)/$K$15","v":-3.9731003193116545},{"$f":"SUM($F44:X44)/$K$15","v":-3.9731003193116545},{"$f":"SUM($F44:Y44)/$K$15","v":-3.9731003193116545}]
[null,null,"(-) Interest Expense:","$ M",{"$f":"SUM(G40:Z40)","v":-501.78606825808487},null,{"$f":"-G59","v":-78.969473382478185},{"$f":"-H59","v":-74.960241347825402},-69.609417050188995,-63.558531949736803,-56.743299520691352,-50.568768053775592,-42.107790014294466,-32.662919457850322,-22.145646046334658,-10.459981434909086,0,0,0,0,0,0,0,0,0,0]
[null,null,"Pre-Tax Income:","$ M",{"$f":"SUM(E37:E40)","v":2950.1452754084917},null,{"$f":"SUM(G37:G40)","v":41.40242821752183},{"$f":"SUM(H37:H40)","v":50.38610241217458},60.828457547010998,72.090489984551184,84.239059850312429,94.396870063993561,108.44347192673018,123.60415638406194,139.97019492344609,{"$f":"SUM(P37:P40)","v":157.64038495711381},171.72527777545662,177.98994451760728,184.39908991371561,190.95571824121788,197.66288334736464,204.52368890315893,211.54128862750758,218.71888647956052,226.05973681711555,233.56714451887069]
[]
[null,null,"EBITDA:","$ M",{"$f":"E37","v":5103.995377985394},null,{"$f":"G37","v":200.61983600000002},205.59427815999999,210.68580899719998,215.89695633428798,221.2302937710038,226.68844114290917,232.2740649661647,237.98987886705231,243.83864399492077,{"$f":"P37","v":249.82316941716292},255.94631249476825,262.21097923691894,268.62012463302727,275.17675296052954,281.8839180666763,288.74472362247059,295.76232334681924,302.93992119887218,310.28077153642721,317.78817923818235]
[null,null,"(-) Maintenance CapEx & Other:","$ M",{"$f":"SUM(G44:Z44)","v":-107.26473167540217},null,0,0,0,0,-22.12302937710038,0,0,0,0,-37.473475412574437,0,0,0,0,0,0,0,0,0,-47.66822688572735]
[null,null,"(+/-) Change in Working Capital:","$ M",{"$f":"SUM(G45:Z45)","v":-18.511195266373754},null,{"$f":"(F35-G35)*WC_Pct_Rev","v":-11.670991800000001},-0.28972210799999942,-0.29660154185999943,-0.30363299185439985,-0.31081938746079063,-0.31816369711089298,-0.32566892789129159,-0.33333812519111067,-0.34117437229381836,-0.34918078991001383,-0.35736053564812098,-0.36571680341958485,-0.37425282277527006,-0.38297185816921059,-0.39187720814628907,-0.40097220444963855,-0.41026021104385396,-0.41974462304973204,-0.42942886558601007,-0.43931639251372306]
[null,null,"(-) Cash Taxes:","$ M",{"$f":"SUM(G46:Z46)","v":-737.53631885212303},null,{"$f":"-G41*Tax_Rate","v":-10.350607054380458},-12.596525603043645,-15.20711438675275,-18.022622496137796,-21.059764962578107,-23.59921751599839,-27.110867981682546,-30.901039096015484,-34.992548730861522,-39.410096239278452,-42.931319443864155,-44.497486129401821,-46.099772478428903,-47.73892956030447,-49.41572083684116,-51.130922225789732,-52.885322156876896,-54.679721619890131,-56.514934204278887,-58.391786129717673]
[null,null,"Cash Flow Available for Debt Service (CFADS):","$ M",{"$f":"SUM(E43:E46)","v":4240.6831321914951},null,178.59823714561958,192.70803044895632,195.18209306858725,197.57070084629578,177.73668004386454,202.77105992979989,204.83752805659088,206.7555016458457,208.50492089176544,{"$f":"SUM(P43:P46)","v":172.59041697540002},212.65763251525595,217.34777630409758,222.14609933182311,227.05485154205587,232.07632002168884,237.2128291922312,242.46674097889849,247.84045495593233,253.33640846656229,211.28884983022363]
[]
[null,null,"Pasted CFADS from Macro:","$ M",{"$f":"SUM(G49:Z49)","v":4240.6491662630706},null,{"$f":"Macros!G9","v":178.59269059350891},{"$f":"Macros!H9","v":192.70272648628415},{"$f":"Macros!I9","v":195.17740208206635},{"$f":"Macros!J9","v":197.56628436028697},{"$f":"Macros!K9","v":177.7329212847734},{"$f":"Macros!L9","v":202.76782267288036},{"$f":"Macros!M9","v":204.83474336810383},{"$f":"Macros!N9","v":206.75348035951046},{"$f":"Macros!O9","v":208.50343824088557},{"$f":"Macros!P9","v":172.58969367600127},{"$f":"Macros!Q9","v":212.65763251525595},{"$f":"Macros!R9","v":217.34777630409758},{"$f":"Macros!S9","v":222.14609933182311},{"$f":"Macros!T9","v":227.05485154205587},{"$f":"Macros!U9","v":232.07632002168884},{"$f":"Macros!V9","v":237.2128291922312},{"$f":"Macros!W9","v":242.46674097889849},{"$f":"Macros!X9","v":247.84045495593233},{"$f":"Macros!Y9","v":253.33640846656229},{"$f":"Macros!Z9","v":211.28884983022363}]
[null,null,"Present Value of CFADS Until Maturity:","$ M",null,{"$f":"IF(F25<Tenor,NPV(Interest_Rate,OFFSET(G49,0,0,1,Tenor-F25)),\"N/A\")","v":1184.5421007371726},1124.4036202173813,1044.1412557528354,953.37797924605263,851.14949281037082,758.5315208066346,631.61685021441781,489.94379186775575,332.18469069502095,156.89972152363751,"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"]
[]
[null,null,"Maximum Debt Service Allowed:","$ M",{"$f":"SUM(G52:Z52)","v":2827.099444175381},null,{"$f":"G49/Min_LLCR","v":119.06179372900594},128.46848432418943,130.11826805471091,131.7108562401913,118.48861418984893,135.17854844858689,136.55649557873588,137.83565357300697,139.00229216059037,{"$f":"P49/Min_LLCR","v":115.05979578400085},141.77175501017064,144.89851753606504,148.09739955454873,151.36990102803725,154.7175466811259,158.14188612815414,161.64449398593231,165.22696997062155,168.8909389777082,140.85923322014909]
[null,null,"Maximum Debt Amortization Allowed:","$ M",{"$f":"SUM(G53:Z53)","v":2325.3133759172956},null,{"$f":"MAX(0,G52-G59)","v":40.092320346527757},{"$f":"MAX(0,H52-H59)","v":53.508242976364031},60.508851004521915,68.152324290454501,61.74531466915758,84.60978039481131,94.448705564441411,105.17273411515664,116.85664611425571,{"$f":"MAX(0,P52-P59)","v":104.59981434909177},141.77175501017064,144.89851753606504,148.09739955454873,151.36990102803725,154.7175466811259,158.14188612815414,161.64449398593231,165.22696997062155,168.8909389777082,140.85923322014909]
[]
[null,null,"Beginning Debt Balance:","$ M",{"$f":"E19","v":789.69473382478179},null,{"$f":"F57","v":789.69473382478179},{"$f":"G57","v":749.60241347825399},696.09417050188995,635.585319497368,567.43299520691346,505.68768053775591,421.0779001429446,326.62919457850319,221.45646046334656,{"$f":"O57","v":104.59981434909085},{"$f":"P57","v":0},{"$f":"Q57","v":0},{"$f":"R57","v":0},{"$f":"S57","v":0},{"$f":"T57","v":0},{"$f":"U57","v":0},{"$f":"V57","v":0},{"$f":"W57","v":0},{"$f":"X57","v":0},{"$f":"Y57","v":0}]
[null,null,"(-) Debt Amortization (Sculpted):","$ M",{"$f":"SUM(G56:Z56)","v":-789.69473382478168},null,{"$f":"-MIN(G53,G55)","v":-40.092320346527757},{"$f":"-MIN(H53,H55)","v":-53.508242976364031},-60.508851004521915,-68.152324290454501,-61.74531466915758,-84.60978039481131,-94.448705564441411,-105.17273411515664,-116.85664611425571,{"$f":"-MIN(P53,P55)","v":-104.59981434909085},0,0,0,0,0,0,0,0,0,0]
[null,null,"Ending Debt Balance:","$ M",{"$f":"SUM(E55:E56)","v":0},{"$f":"E19","v":789.69473382478179},{"$f":"SUM(G55:G56)","v":749.60241347825399},{"$f":"SUM(H55:H56)","v":696.09417050188995},635.585319497368,567.43299520691346,505.68768053775591,421.0779001429446,326.62919457850319,221.45646046334656,104.59981434909085,{"$f":"SUM(P55:P56)","v":0},0,0,0,0,0,0,0,0,0,0]
[]
[null,null,"(+) Debt Interest Expense:","$ M",{"$f":"SUM(G59:Z59)","v":501.78606825808487},null,{"$f":"G55*Interest_Rate","v":78.969473382478185},74.960241347825402,69.609417050188995,63.558531949736803,56.743299520691352,50.568768053775592,42.107790014294466,32.662919457850322,22.145646046334658,{"$f":"P55*Interest_Rate","v":10.459981434909086},0,0,0,0,0,0,0,0,0,0]
[null,null,"(+) Debt Amortization (Sculpted):","$ M",{"$f":"SUM(G60:Z60)","v":789.69473382478168},null,{"$f":"-G56","v":40.092320346527757},{"$f":"-H56","v":53.508242976364031},60.508851004521915,68.152324290454501,61.74531466915758,84.60978039481131,94.448705564441411,105.17273411515664,116.85664611425571,{"$f":"-P56","v":104.59981434909085},0,0,0,0,0,0,0,0,0,0]
[null,null,"Total Debt Service:","$ M",{"$f":"SUM(E59:E60)","v":1291.4808020828666},null,{"$f":"SUM(G59:G60)","v":119.06179372900594},{"$f":"SUM(H59:H60)","v":128.46848432418943},130.11826805471091,131.7108562401913,118.48861418984893,135.17854844858689,136.55649557873588,137.83565357300697,139.00229216059037,{"$f":"SUM(P59:P60)","v":115.05979578399993},0,0,0,0,0,0,0,0,0,0]
[]
[null,null,"Cash Flow Post-Debt Service:","$ M",{"$f":"SUM(G63:Z63)","v":2949.2023301086278},null,{"$f":"G47-G61","v":59.536443416613636},{"$f":"H47-H61","v":64.239546124766889},65.06382501387634,65.859844606104474,59.248065854015607,67.592511481212995,68.281032477855007,68.919848072838732,69.502628731175065,{"$f":"P47-P61","v":57.530621191400087},212.65763251525595,217.34777630409758,222.14609933182311,227.05485154205587,232.07632002168884,237.2128291922312,242.46674097889849,247.84045495593233,253.33640846656229,211.28884983022363]
[]
[null,null,"Debt Service Coverage Ratio (DSCR):","x",{"$f":"IFERROR(E47/E61,\"N/A\")","v":3.2835820132612361},null,1.50004658549092,1.5000412860998562,1.500036051713499,1.500033531677911,1.5000317225339905,1.5000239480077031,1.5000203922081865,1.5000146644665793,1.5000106663772002,{"$f":"IFERROR(P47/P61,\"N/A\")","v":1.5000062862913599},"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"]
[null,null,"Loan Life Coverage Ratio (LLCR):","x",null,{"$f":"IFERROR(F50/F57,\"N/A\")","v":1.5},{"$f":"IFERROR(G50/G57,\"N/A\")","v":1.5000000000000004},{"$f":"IFERROR(H50/H57,\"N/A\")","v":1.5000000000000007},1.5000000000000009,1.5000000000000011,1.5000000000000016,1.5000000000000022,1.5000000000000029,1.5000000000000051,1.5000000000000118,{"$f":"IFERROR(P50/P57,\"N/A\")","v":"N/A"},"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"]
[null,null,"EBITDA Margin:","x",{"$f":"IFERROR(E43/E35,\"N/A\")","v":0.85898948328615854},null,{"$f":"IFERROR(G43/G35,\"N/A\")","v":0.85948066555920299},0.8594565497569796,0.85942884418303189,0.85939751021229804,0.85936250834777539,0.85932379819843663,0.85928133845645727,0.85923508687372885,0.85918500023763467,0.85913103434605476,0.85907314398157997,0.85901128288489759,0.85894540372732342,0.85887545808244448,0.85880139639684094,0.85872316795985104,0.85864072087234289,0.85855400201445486,0.85846295701226505,0.85836753020334666]
[null,null,"Cash Flow Yield:","x",null,null,{"$f":"IFERROR(G63/$K$23,\"N/A\")","v":7.3027199487612729E-2},7.8796009311801835E-2,7.9807067000404477E-2,8.0783461931336328E-2,7.267347654779531E-2,8.290874524140418E-2,8.3753282759732914E-2,8.4536851801038079E-2,8.5251688579178128E-2,7.0566864751922392E-2,0.26084513049567637,0.26659804495340744,0.27248365169850125,0.27850471050417214,0.28466402670344293,0.29096445142385002,0.2974088817948487,0.3040002611260621,0.31074157905442568,0.25916618629849658]
[]
[null,null,null,null,null,null,{"$f":"$G$26","v":"Projected Years:"}]
[null,"Returns Calculations:",null,{"$f":"$D$27","v":"Units:"},{"$f":"$E$27","v":"Total:"},{"$f":"$F$27","v":"Year 0"},{"$f":"$G$27","v":"Year 1"},{"$f":"$H$27","v":"Year 2"},{"$f":"$I$27","v":"Year 3"},{"$f":"$J$27","v":"Year 4"},{"$f":"$K$27","v":"Year 5"},{"$f":"$L$27","v":"Year 6"},{"$f":"$M$27","v":"Year 7"},{"$f":"$N$27","v":"Year 8"},{"$f":"$O$27","v":"Year 9"},{"$f":"$P$27","v":"Year 10"},{"$f":"$Q$27","v":"Year 11"},{"$f":"$R$27","v":"Year 12"},{"$f":"$S$27","v":"Year 13"},{"$f":"$T$27","v":"Year 14"},{"$f":"$U$27","v":"Year 15"},{"$f":"$V$27","v":"Year 16"},{"$f":"$W$27","v":"Year 17"},{"$f":"$X$27","v":"Year 18"},{"$f":"$Y$27","v":"Year 19"},{"$f":"$Z$27","v":"Year 20"}]
[]
[null,null,"(-) Upfront Investor Equity:","$ M",{"$f":"SUM(F73:Z73)","v":-815.26395417521837},{"$f":"-K23","v":-815.26395417521837},0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"(+) Cash Flow Post-Debt Service:","$ M",{"$f":"SUM(F74:Z74)","v":2949.2023301086278},null,{"$f":"G63","v":59.536443416613636},{"$f":"H63","v":64.239546124766889},65.06382501387634,65.859844606104474,59.248065854015607,67.592511481212995,68.281032477855007,68.919848072838732,69.502628731175065,57.530621191400087,212.65763251525595,217.34777630409758,222.14609933182311,227.05485154205587,232.07632002168884,237.2128291922312,242.46674097889849,247.84045495593233,253.33640846656229,211.28884983022363]
[null,null,"(+) Exit Value (Terminal Value):","$ M",0,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Total Cash Flows to Equity:","$ M",{"$f":"SUM(E73:E75)","v":2133.9383759334096},{"$f":"SUM(F73:F75)","v":-815.26395417521837},{"$f":"SUM(G73:G75)","v":59.536443416613636},64.239546124766889,65.06382501387634,65.859844606104474,59.248065854015607,67.592511481212995,68.281032477855007,68.919848072838732,69.502628731175065,57.530621191400087,212.65763251525595,217.34777630409758,222.14609933182311,227.05485154205587,232.07632002168884,237.2128291922312,242.46674097889849,247.84045495593233,253.33640846656229,211.28884983022363]
[]
[null,null,"Equity IRR:","%",{"$f":"IRR(F76:Z76)","v":0.11466556583856558}]
[]
[null,null,"Total Returns to Equity:","$",{"$f":"SUMIF(F76:Z76,\">0\",F76:Z76)","v":2949.2023301086278}]
[null,null,"Invested Equity:","$",{"$f":"-SUMIF(F76:Z76,\"<=0\",F76:Z76)","v":815.26395417521837}]
[null,null,"Cash-on-Cash Multiple:","x",{"$f":"E80/E81","v":3.6174815714651092}]
[]
[null,null,null,null,null,null,{"$f":"$G$26","v":"Projected Years:"}]
[null,"Payback Period Calculations:",null,{"$f":"$D$27","v":"Units:"},{"$f":"$E$27","v":"Total:"},{"$f":"$F$27","v":"Year 0"},{"$f":"$G$27","v":"Year 1"},{"$f":"$H$27","v":"Year 2"},{"$f":"$I$27","v":"Year 3"},{"$f":"$J$27","v":"Year 4"},{"$f":"$K$27","v":"Year 5"},{"$f":"$L$27","v":"Year 6"},{"$f":"$M$27","v":"Year 7"},{"$f":"$N$27","v":"Year 8"},{"$f":"$O$27","v":"Year 9"},{"$f":"$P$27","v":"Year 10"},{"$f":"$Q$27","v":"Year 11"},{"$f":"$R$27","v":"Year 12"},{"$f":"$S$27","v":"Year 13"},{"$f":"$T$27","v":"Year 14"},{"$f":"$U$27","v":"Year 15"},{"$f":"$V$27","v":"Year 16"},{"$f":"$W$27","v":"Year 17"},{"$f":"$X$27","v":"Year 18"},{"$f":"$Y$27","v":"Year 19"},{"$f":"$Z$27","v":"Year 20"}]
[]
[null,null,"Levered Payback Period:"]
[null,null,"Upfront Investor Equity:","$ M",{"$f":"K23","v":815.26395417521837}]
[null,null,"Positive Cash Flow to Equity:","$ M",null,null,{"$f":"G76","v":59.536443416613636},{"$f":"H76","v":64.239546124766889},65.06382501387634,65.859844606104474,59.248065854015607,67.592511481212995,68.281032477855007,68.919848072838732,69.502628731175065,57.530621191400087,212.65763251525595,217.34777630409758,222.14609933182311,227.05485154205587,232.07632002168884,237.2128291922312,242.46674097889849,247.84045495593233,253.33640846656229,211.28884983022363]
[null,null,"Unrecovered Equity:","$ M",null,null,{"$f":"MAX(0,$E88-SUM($G89:G89))","v":755.72751075860469},{"$f":"MAX(0,$E88-SUM($G89:H89))","v":691.48796463383781},{"$f":"MAX(0,$E88-SUM($G89:I89))","v":626.42413961996158},{"$f":"MAX(0,$E88-SUM($G89:J89))","v":560.56429501385708},{"$f":"MAX(0,$E88-SUM($G89:K89))","v":501.31622915984144},{"$f":"MAX(0,$E88-SUM($G89:L89))","v":433.72371767862842},{"$f":"MAX(0,$E88-SUM($G89:M89))","v":365.44268520077344},{"$f":"MAX(0,$E88-SUM($G89:N89))","v":296.52283712793474},{"$f":"MAX(0,$E88-SUM($G89:O89))","v":227.0202083967597},{"$f":"MAX(0,$E88-SUM($G89:P89))","v":169.48958720535961},{"$f":"MAX(0,$E88-SUM($G89:Q89))","v":0},{"$f":"MAX(0,$E88-SUM($G89:R89))","v":0},{"$f":"MAX(0,$E88-SUM($G89:S89))","v":0},{"$f":"MAX(0,$E88-SUM($G89:T89))","v":0},{"$f":"MAX(0,$E88-SUM($G89:U89))","v":0},{"$f":"MAX(0,$E88-SUM($G89:V89))","v":0},{"$f":"MAX(0,$E88-SUM($G89:W89))","v":0},{"$f":"MAX(0,$E88-SUM($G89:X89))","v":0},{"$f":"MAX(0,$E88-SUM($G89:Y89))","v":0},{"$f":"MAX(0,$E88-SUM($G89:Z89))","v":0}]
[null,null,"Breakeven Point or Beyond?","Y / N",null,null,{"$f":"IF(G90=0,1,0)","v":0},{"$f":"IF(H90=0,1,0)","v":0},0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1]
[null,null,"Year Fraction for Recovery:","#",null,null,{"$f":"IF(AND(F91=0,G91=1),F90/G89,\"N/A\")","v":"N/A"},{"$f":"IF(AND(G91=0,H91=1),G90/H89,\"N/A\")","v":"N/A"},"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A",0.79700683770755565,"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"]
[null,null,"Total Cash Flow to Equity:","$ M",null,{"$f":"F76","v":-815.26395417521837},{"$f":"G76","v":59.536443416613636},64.239546124766889,65.06382501387634,65.859844606104474,59.248065854015607,67.592511481212995,68.281032477855007,68.919848072838732,69.502628731175065,57.530621191400087,212.65763251525595,217.34777630409758,222.14609933182311,227.05485154205587,232.07632002168884,237.2128291922312,242.46674097889849,247.84045495593233,253.33640846656229,211.28884983022363]
[]
[null,null,"Levered Payback Period:","# Years",{"$f":"COUNTIF(G91:Z91,0)+SUM(G92:Z92)","v":10.797006837707556}]
[null,null,"Levered IRR (Equity IRR):","%",{"$f":"IRR(F93:Z93)","v":0.11466556583856558}]
[null,null,"Levered Cash-on-Cash Multiple:","x",{"$f":"-SUMIF(F93:Z93,\">0\",F93:Z93)/SUMIF(F93:Z93,\"<=0\",F93:Z93)","v":3.6174815714651092}]
[]
[null,null,"Unlevered Payback Period:"]
[null,null,"Upfront Total Capital:","$ M",{"$f":"K21","v":1604.9586880000002}]
[null,null,"Positive Unlevered Cash Flow:","$ M",null,null,{"$f":"(G41-G40)*(1-Tax_Rate)+G44+G45-G39-G38","v":158.85586880000002},{"$f":"(H41-H40)*(1-Tax_Rate)+H44+H45-H39-H38","v":173.96797011199999},{"$f":"(I41-I40)*(1-Tax_Rate)+I44+I45-I39-I38","v":177.77973880604},{"$f":"(J41-J40)*(1-Tax_Rate)+J44+J45-J39-J38","v":181.68106785886158},{"$f":"(K41-K40)*(1-Tax_Rate)+K44+K45-K39-K38","v":163.55085516369166},{"$f":"(L41-L40)*(1-Tax_Rate)+L44+L45-L39-L38","v":190.128867916356},{"$f":"(M41-M40)*(1-Tax_Rate)+M44+M45-M39-M38","v":194.31058055301722},{"$f":"(N41-N40)*(1-Tax_Rate)+N44+N45-N39-N38","v":198.58977178138309},{"$f":"(O41-O40)*(1-Tax_Rate)+O44+O45-O39-O38","v":202.96850938018179},{"$f":"(P41-P40)*(1-Tax_Rate)+P44+P45-P39-P38","v":169.97542161667275},{"$f":"(Q41-Q40)*(1-Tax_Rate)+Q44+Q45-Q39-Q38","v":212.65763251525601},{"$f":"(R41-R40)*(1-Tax_Rate)+R44+R45-R39-R38","v":217.34777630409752},{"$f":"(S41-S40)*(1-Tax_Rate)+S44+S45-S39-S38","v":222.14609933182305},{"$f":"(T41-T40)*(1-Tax_Rate)+T44+T45-T39-T38","v":227.05485154205581},{"$f":"(U41-U40)*(1-Tax_Rate)+U44+U45-U39-U38","v":232.07632002168884},{"$f":"(V41-V40)*(1-Tax_Rate)+V44+V45-V39-V38","v":237.2128291922312},{"$f":"(W41-W40)*(1-Tax_Rate)+W44+W45-W39-W38","v":242.46674097889849},{"$f":"(X41-X40)*(1-Tax_Rate)+X44+X45-X39-X38","v":247.84045495593233},{"$f":"(Y41-Y40)*(1-Tax_Rate)+Y44+Y45-Y39-Y38","v":253.33640846656232},{"$f":"(Z41-Z40)*(1-Tax_Rate)+Z44+Z45-Z39-Z38","v":211.28884983022363}]
[null,null,"Unrecovered Capital:","$ M",null,null,{"$f":"MAX(0,$E$100-SUM($G101:G101))","v":1446.1028192000001},{"$f":"MAX(0,$E$100-SUM($G101:H101))","v":1272.134849088},{"$f":"MAX(0,$E$100-SUM($G101:I101))","v":1094.3551102819602},{"$f":"MAX(0,$E$100-SUM($G101:J101))","v":912.67404242309863},{"$f":"MAX(0,$E$100-SUM($G101:K101))","v":749.1231872594069},{"$f":"MAX(0,$E$100-SUM($G101:L101))","v":558.99431934305085},{"$f":"MAX(0,$E$100-SUM($G101:M101))","v":364.68373879003366},{"$f":"MAX(0,$E$100-SUM($G101:N101))","v":166.09396700865045},{"$f":"MAX(0,$E$100-SUM($G101:O101))","v":0},{"$f":"MAX(0,$E$100-SUM($G101:P101))","v":0},{"$f":"MAX(0,$E$100-SUM($G101:Q101))","v":0},{"$f":"MAX(0,$E$100-SUM($G101:R101))","v":0},{"$f":"MAX(0,$E$100-SUM($G101:S101))","v":0},{"$f":"MAX(0,$E$100-SUM($G101:T101))","v":0},{"$f":"MAX(0,$E$100-SUM($G101:U101))","v":0},{"$f":"MAX(0,$E$100-SUM($G101:V101))","v":0},{"$f":"MAX(0,$E$100-SUM($G101:W101))","v":0},{"$f":"MAX(0,$E$100-SUM($G101:X101))","v":0},{"$f":"MAX(0,$E$100-SUM($G101:Y101))","v":0},{"$f":"MAX(0,$E$100-SUM($G101:Z101))","v":0}]
[null,null,"Breakeven Point or Beyond?","Y / N",null,null,{"$f":"IF(G102=0,1,0)","v":0},{"$f":"IF(H102=0,1,0)","v":0},0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,1]
[null,null,"Year Fraction for Recovery:","#",null,null,{"$f":"IF(AND(F103=0,G103=1),F102/G101,\"N/A\")","v":"N/A"},{"$f":"IF(AND(G103=0,H103=1),G102/H101,\"N/A\")","v":"N/A"},"N/A","N/A","N/A","N/A","N/A","N/A",0.81832382528630898,"N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A","N/A"]
[null,null,"Total Unlevered Cash Flow:","$ M",null,{"$f":"-E100","v":-1604.9586880000002},{"$f":"G101","v":158.85586880000002},{"$f":"H101","v":173.96797011199999},177.77973880604,181.68106785886158,163.55085516369166,190.128867916356,194.31058055301722,198.58977178138309,202.96850938018179,169.97542161667275,212.65763251525601,217.34777630409752,222.14609933182305,227.05485154205581,232.07632002168884,237.2128291922312,242.46674097889849,247.84045495593233,253.33640846656232,211.28884983022363]
[]
[null,null,"Unlevered Payback Period:","# Years",{"$f":"COUNTIF(G103:Z103,0)+SUM(G104:Z104)","v":8.8183238252863099}]
[null,null,"Unlevered IRR:","%",{"$f":"IRR(F105:Z105)","v":0.10286291632938571}]
[null,null,"Unlevered Cash-on-Cash Multiple:","x",{"$f":"-SUMIF(F105:Z105,\">0\",F105:Z105)/SUMIF(F105:Z105,\"<=0\",F105:Z105)","v":2.5640763503110011}]

{"sheet":"Macros","index":1,"n_rows":11,"n_cols":26,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Macro Output for Copy/Paste VBA Code"]
[null,{"$f":"Model!$B$3","v":"($ in Millions)"}]
[]
[null,null,null,null,null,null,{"$f":"Model!$G$26","v":"Projected Years:"}]
[null,"Cash Flow Projections:",null,{"$f":"Model!$D$27","v":"Units:"},{"$f":"Model!$E$27","v":"Total:"},{"$f":"Model!F27","v":"Year 0"},{"$f":"Model!G27","v":"Year 1"},{"$f":"Model!H27","v":"Year 2"},{"$f":"Model!I27","v":"Year 3"},{"$f":"Model!J27","v":"Year 4"},{"$f":"Model!K27","v":"Year 5"},{"$f":"Model!L27","v":"Year 6"},{"$f":"Model!M27","v":"Year 7"},{"$f":"Model!N27","v":"Year 8"},{"$f":"Model!O27","v":"Year 9"},{"$f":"Model!P27","v":"Year 10"},{"$f":"Model!Q27","v":"Year 11"},{"$f":"Model!R27","v":"Year 12"},{"$f":"Model!S27","v":"Year 13"},{"$f":"Model!T27","v":"Year 14"},{"$f":"Model!U27","v":"Year 15"},{"$f":"Model!V27","v":"Year 16"},{"$f":"Model!W27","v":"Year 17"},{"$f":"Model!X27","v":"Year 18"},{"$f":"Model!Y27","v":"Year 19"},{"$f":"Model!Z27","v":"Year 20"}]
[]
[null,null,"CFADS Link:","$ M",{"$f":"SUM(CFADS_Links)","v":4240.6831321914951},null,{"$f":"Model!G47","v":178.59823714561958},{"$f":"Model!H47","v":192.70803044895632},{"$f":"Model!I47","v":195.18209306858725},{"$f":"Model!J47","v":197.57070084629578},{"$f":"Model!K47","v":177.73668004386454},{"$f":"Model!L47","v":202.77105992979989},{"$f":"Model!M47","v":204.83752805659088},{"$f":"Model!N47","v":206.7555016458457},{"$f":"Model!O47","v":208.50492089176544},{"$f":"Model!P47","v":172.59041697540002},{"$f":"Model!Q47","v":212.65763251525595},{"$f":"Model!R47","v":217.34777630409758},{"$f":"Model!S47","v":222.14609933182311},{"$f":"Model!T47","v":227.05485154205587},{"$f":"Model!U47","v":232.07632002168884},{"$f":"Model!V47","v":237.2128291922312},{"$f":"Model!W47","v":242.46674097889849},{"$f":"Model!X47","v":247.84045495593233},{"$f":"Model!Y47","v":253.33640846656229},{"$f":"Model!Z47","v":211.28884983022363}]
[null,null,"Pasted CFADS:","$ M",{"$f":"SUM(CFADS_Paste)","v":4240.6491662630706},null,178.59269059350891,192.70272648628415,195.17740208206635,197.56628436028697,177.7329212847734,202.76782267288036,204.83474336810383,206.75348035951046,208.50343824088557,172.58969367600127,212.65763251525595,217.34777630409758,222.14609933182311,227.05485154205587,232.07632002168884,237.2128291922312,242.46674097889849,247.84045495593233,253.33640846656229,211.28884983022363]
[]
[null,null,"Check / Comparison:","$ M",{"$f":"ROUND(E8-E9,0)","v":0}]