Project Finance RatiosTutorialFebruary 2017

1.0 GeneralPease note the following guidance and instruction is to be used as an accompanimentto the ‘Project Finance Ratios’ Excel file.Please feel free to get in touch at [email protected] if you would likeadditional guidance or to discuss the methodologies represented here and in the Excelfile.2.0 Tutorial2.1Aim and audienceThe aim of this tutorial is to illustrate what the most common project finance debt ratiosfound in loan documentation and term sheets are and how to set them up correctly in afinancial model. The intended audience are those preparing, analysing and reviewingproject finance transaction models.2.2Tutorial conventionsThe three project finance ratios that are covered here include: Debt service coverage ratio (DSCR), Loan life coverage ratio (LLCR), and Project life coverage ratio (PLCR)Essentially, all three ratios are a measure of a project’s ability to produce sufficientcashflow to cover its debt obligations.Given that loans are non-recourse in project finance, lenders can only rely on theprojected cashflows of the project rather than balance sheet of its sponsors to repay itsdebt obligations. Therefore, lenders pay very close attention to project finance ratios inmodels and it is imperative that these ratios are set up correctly.The terms debt and loan are used interchangeably in this tutorial. Cashflow availablefor debt service is represented by the acronym CFADS. Sheet references aredisplayed as ‘Sheet’ while section headings and line references are displayed as‘Item’.2.3Debt service coverage ratio (DSCR)The DSCR is calculated as:DSCR CFADS / debt service CFADS qualifying cashflows available for debt service Debt service interest paid principal repayment

Essentially, the DSCR is a period by period ratio that measures how much headroomthe project’s cashflow has in order to repay the scheduled debt service in any oneperiod. Or more specifically, the DSCR measures how many times the CFADS canrepay the scheduled debt service of that period.DSCR represents the most common used ratio in project finance deals and it would bevery hard to find a debt term sheet which did not include this ratio in some major wayas it is often used to determine anything from debt default levels, to cash lock-ups,releases and lender reserve sizing.Given that the DSCR is a period by period ratio, lenders normally focus on two mainoutputs: The minimum DSCR to identify a period of weak CFADS relative to its debtobligations. The average DSCR to have an understanding of the overall health of theproject cashflows relative to its total debt service.Note that a variation of the DSCR is the interest cover ratio (ICR):ICR CFADS / interest payableThe only difference compared to DSCR is that the ICR only takes into account theinterest paid or payable without considering any principal repayments as part of itsdenominator. the DSCRThe DSCR formulaFor both the numerator and denominator: Link in CFADS from the cashflow waterfall1 – row 20 Multiply CFADS by the loan life binary flag to determine the qualifyingCFADS – row 21 Bring in the interest and principal repayment2 to determine the total debtservice – row 25In calculating the DSCR: Divide the qualifying CFADS by the total debt service – row 27Note: an IF(denominator 0 , 0 , ) formula is recommended when calculatingratios to avoid returning #DIV/0! In the event that the denominator is zero.2.4.212Minimum DSCRSee our Cashflow Waterfall and CFADS tutorial for more guidance on how to calculate CFADS.See our Setting up a Debt Facility tutorial for more guidance on how to set up a debt facility.

Calculating the minimum DSCR is a less than a straight forward task a MIN functionalone would always return a zero and given that there is no in-built MINIF Excelformula (unlike SUMIF or AVERAGEIF), the best option is to create an arrayformula: Start by building a nested MIN and IF formula in E27: MIN(IF(H27:Q27 0,H27:Q27)) Press ‘Ctrl Shift Enter’ to create an array formula.Note: Excel automatically inserts the formula between a pair of opening andclosing brackets { }. This is necessary, otherwise the calculation will return a#VALUE! error given the IF formula is not built to deal with multiple cells, asis the case here.2.4.3Average DSCRA common solution in many financial models for calculating the average DSCR isusing either the AVERAGEIF function or a similar approach as the minimum DSCR:{ AVERAGE( IF(RANGE 0, RANGE) ) }.However, this approach can be misleading as it is giving equal importance to theDSCR of each period. In other words, the periodic DSCR which is a ratio in itself, isbeing given equal weighting in calculating the average. The average DSCR can bedistorted when the repayment profile is not flat and more so when there is a verysmall repayment during the loan life.The better and more straight forward solution is to divide the qualifying CFADS bythe total debt service – F272.5Loan life coverage ratio (LLCR)The LLCR is calculated as:LLCR NPV (of CFADS over loan life) / opening debt balanceUnlike the period by period DSCR, the LLCR is a look forward ratio for lenders tocalculate the number of times the discounted project cashflows can repay theoutstanding debt balance over the scheduled loan life.In many ways where DSCR allows lenders and sponsors too, the opportunity to identifypoints of weakness, LLCR provides a truer example of the whole life quality of theproject.

Different lenders often internally and procedurally assign different levels of importanceto DSCR and LLCR and as a sponsor it is important to understand where a prospectivelenders priorities lie in this sense as it will give greater direction when debt sculptingand sizing comes into play.Given that the LLCR is essentially a discounted average ratio, lenders normally focuson two main outputs: The minimum LLCR The LLCR at the start of operationsConstructing the LLCRThe LLCR formulaThe numerator is the net present value (NPV) of the project’s CFADS over the loanlife (i.e. qualifying CFADS) discounted by the cost of debt: Bring in the qualifying CFADS and the discount rate, which in this case is theall in rate – row 30 & 32 Calculate the start of period NPV of CFADS from 1st principles. This is doneby summing the current CFADS and the cumulative future discountedCFADS and discounting them to the start of the period by the currentperiod’s discount rate – row 35Given that the NPV of CFADS is a start of period calculation, likewise, the openingdebt balance should be used as the denominator – row 37In calculating the LLCR: Divide the NPV of CFADS by the opening debt balance – row 39 Similar to how the minimum DSCR is calculated, the minimum LLCR iscalculated in E39. Another common LLCR metric (not shown in Excel file) is the result atcommercial operation date – this is usually the point in time at which theproject goes live after the construction phase and represents a quality testfor the whole of operations.

2.6.2Common errorsThe LLCR calculation is prone to mistakes. A few of the most commonly repeatederrors including:2.7 Comparing a start of period NPV as its numerator against the closing debtbalance as its denominator (i.e. start of period vs. end of period). Using the NPV or XNPV function to calculate the numerator. This limits thediscount rate to a single discount rate, which is not normally the case since costof debt often fluctuates across time based on margin schedules or ratchets orindeed if the base rate has not been fully hedged. This reduces the accuracy ofthe LLCR which is far from ideal especially if it’s required for periodic covenanttesting and monitoring. Incorrect use of the NPV or XNPV function in calculating the numerator3. The project CFADS rather than the qualifying CFADS is discounted in thenumerator. Discount rate does not represent a weighted average of cost of debt wheremultiple debt facilities are being considered for the LLCR calculation.Project life coverage ratio (LLCR)The PLCR is calculated as:PLCR NPV (of CFADS over project life) / opening debt balanceThe PLCR is very similar to the LLCR, except that, rather than calculating the CFADSover the loan life, the numerator is the NPV of CFADS over the project life.Note that the definition of project life for the purpose of calculating the PLCR from thelender’s perspective does not necessarily equate to the forecasted project life. Rather,to protect the lenders against relying on future cashflows that may be uncertain, thePLCR project life may be shorter than the forecasted project life by the sponsors oroperators.For example, a sponsor or operator may have a projected mine life of 10 years for acopper mine, however, in the term sheet or loan documentation, the project life is only8 years. This is not strictly what is known as a ‘tail’4, but performs a similar action andrepresents a similar process of risk mitigation by the lender.Ultimately, the PLCR from a lenders perspective is used to highlight the added valuethat is left in the business post planned term end. In other words the ratio represents astatement on the size of the security blanket that remains if the project runs into issuesof paying back debt service and needs to extend into the tail period.3See our NPV tutorial for more detail on the right and wrong way of calculating this essential valuationmetric.4 A ‘tail’ is the period between the loan term end and the project term end, both as defined by the loandocumentation / lender term sheet.

2.8Constructing the PLCRThe steps in constructing the PLCR is very similar as to how the LLCR is constructedwith two exceptions:2.8.1 Cashflows are now based off the project life rather than the loan life – rows 42& 43 Discount rate beyond the loan lifeDiscount rate beyond the loan lifeGiven that the PLCR project life is normally longer than the loan life, a discount ratebeyond the loan life will have to be assumed.A common mistake done in many financial models is that no discounting has beendone to the cashflows post the final maturity of the loan given that cost of debt isnow zero. This would be incorrect as the discount rate should be at least equal tothe cost of debt at the final maturity date given that cashflows beyond the loan lifeare even more uncertain.Therefore, a good solution is to add in an assumption for the discount rate post thefinal maturity of the loan – row 46

Mar 07, 2017 · Mar 07, 2017 · Debt service coverage ratio (DSCR), Loan life coverage ratio (LLCR), and Project life coverage ratio (PLCR) Essentially, all three ratios are a measure of a project’s ability to produce sufficient cashflow to cover its debt obligations. Given that loans are non-recourse in project finance, lenders can only rely on the