This tutorial demonstrates how the maximum debt limit can be solved based on a target debt service cover ratio (DSCR) when modelling a project finance project. This exercise indicates the maximum debt size that can be supported by a certain cash flow available for debt service (CFADS) profile.
To set up the financial model principal repayments need to be set to a target debt service coverage ratio (DSCR) like in screenshot one, which is also found in the attached model. This layout for calculating principal is also discussed in detail in the “debt sculpting to achieve target DSCR without VBA” tutorial, where we sculpted the principal repayments in every period to achieve a uniform DSCR of 1.50x.
In this tutorial, we will use the cash flow available for debt service (CFADS) to solve for a maximum debt limit.
Screenshot 1: Setting up principal repayment
In this tutorial we cover the following:
• Why and when would we use this method?
• What are the benefits and limitations of Goal Seek?
• Incorporating the sequence as a VBA macro
Why and when would you use Goal Seek for Debt Sizing?
In project finance, it is common practice to solve for an indicative maximum debt limit that can be supported by a project.
There are several methods that can achieve that in a financial model. We can adjust the debt limit manually so that the closing debt balance, at maturity, is zero. Alternatively, we can use the Goal Seek function in Excel to perform the manual process quickly and efficiently.
Goal Seek is an in-built function in Excel and is used when you know the desired result of a single formula but not the input value the formula needs to achieve the result.
What are the benefits and limitations of Goal Seek in sizing debt in project finance?
The Goal Seek function is simple to use because it only requires three inputs:
‘Set cell’ – the cell that contains the outcome that you want
‘To value’ – the outcome
‘By changing cell’ – the input value
However, the limitations are:
Does not work with two or more input values
Only provides one solution, even for formulas which have multiple solutions
Only works if the ‘set cell’ value converges towards the ‘to value’ in each iteration
The ‘set cell’ and ‘by changing cell’ values have to be on the same sheet
Goal Seek to achieve maximum debt limit example
In our downloable workbook attached, we have a project with irregular cash flows, which will be used to solve for the maximum debt limit to achieve the target DSCR of 1.50x in every repayment period.
Step 1: Manual testing
Screenshot 2: Manually inserting debt limit in inputs sheet
Let’s say we start with a limit of $500m - the CFADS is not enough to repay the loan so there is still a ‘balance C/f @ maturity’ (cell 19) of $264m. We will need to manually key in a lower limit until cell C19 is zero.
Step 2: Setting up the condition for Goal Seek
Alternatively, we use the ‘goal seek’ function to replicate the manual process of testing different debt limits (cell F20) that will reduce the debt balance to zero.
In Excel 2003: Tools –> Goal Seek
In Excel 2007-2010: Data –> What – If Analysis –> Goal Seek
Screenshot 3: Goal Seek Window
Set Value = Closing Debt Balance @ Maturity
To Value = 0
By Change Cell = Debt Amount
Screenshot 4: Solution after running a Goal Seek Macro
To solve for a maximum debt limit in C21, we need to adjust downward from a starting number that will be higher than the solution. Any amount smaller than $313.86m will result in a zero debt balance at maturity causing Goal Seek not to run. As a result, we assumed a starting guess (cell C24) of $435m.
The Goal Seek macro using VBA
We can automate the process by setting up a macro that runs the Goal Seek function with a click of the button in the model.
Step 1: Record New VBA Macro
The record macro function allows users to perform commands and store the commands in visual basic for application (VBA). Press ‘stop’ when all commands are complete.
The steps to record a macro are:
In Excel 2003: Tools –> Macro –> Record New Macro
In Excel 2007-2010: View –> Macros –> Record Macro
Set Debt Limit (cell C21) = Starting Guess (cell C24)
Perform Goal Seek function as outlined in step two of the previous section
Stop recording Macro
Step 2: Replace cell references in macros with name ranges
To ensure that the macro works when a new row/column has been inserted, all ranges in VBA should be name ranged.
The following name ranges should be created:
“Actual_CBal” = Closing Debt Balance @ Maturity (C19)
“Debt_Limit” = Debt Amount (Cell C21)
“Starting_Guess” = Starting Guess (Cell C24)
Replace all cell references in the code with the new name ranges.
Screenshot 5: Macro code of performing the goal seek function
Step 3: Create a VBA Goal Seek macro button
For the macro to be used with ease, a button is created to run the macro.
First, create an object in Excel:
To label and assign a macro to the button:
Right click on the object –> “Edit Text”
Right click on the object –> “Assign Macro”
Select the relevant macro
Clicking on the button now will run the Goal Seek macro that we have recorded in step two.
Drawbacks of using VBA
Relies on the user to know when and how to execute the macro
Not all institutions allow the use of macros
Increases audit costs
Cannot be used readily for Data Tables in Scenario Managers (VBA for Scenario Analysis)
In a project finance project, it is useful to know the maximum debt limit that can be supported by a DSCR to estimate the potential size of the debt funding that can be reasonably supported by the cash flows of a project.
In this tutorial we have shown you, by using VBA, how to calculate the size of the maximum debt limit based on a target DSCR that is static. There is another useful article on Fimodo about a similar topic.
Corality Training Academy - SMART CAMPUS
There are numerous other tutorials and free resources related to financial modelling in Corality's SMART Campus.
Some of the more popular courses that relate to this topic include:
Project Finance: Transaction Simulation Masterclass
LBO Transactions: Financial Modelling Techniques
LBO Transactions: Financial Modelling Techniques