Optimising the maximum debt limit in the structuring phase of a project finance transaction can be made a lot easier if you have a clear dashboard view of what is going on.
This example illustrates the debt sizing concept with a chart and shows how you can then simply optimise one cell to achieve your theoretical maximum debt limit for the base case. It will not solve more complex debt sizing situations but I think it is a good example as if highlights some important concepts
- Goal Seek should always be implemented to solve for zero
- Replacing hard-coded references with range names in VBA
- The importance of an illustrative chart in debt sizing
Minimum DSCR for a term loan project finance facility
I am using the Minimum DSCR as the constraining factor in this example. In other cases other factors, or the combination of several, may be more appropriate
- LLCR (Loan Life Coverage Ratio)
- PLCR (Project Life Coverage Ratio)
- ICR (Interest Coverage Ratio)
- LVR (Loan to Value Ratio)
The DSCR (Debt Service Coverage Ratio) is typically defined as CFADS / Total Debt Service (including principal) for the relevant period (quarterly, semi-annual or annual, forward-looking or backward-looking).
The example above has not been optimized as can be seen by the ‘Delta’ cell =0.31. The Target minimum DSCR is 2.00x and the calculated minimum DSCR is 1.69x. The check indicates ‘Fail’.
This plot shows DSCR per period and the solid red line indicates the target minimum DSCR. A graph of this type can be useful as it very quickly highlights if the covenant is breached by having data points below the solid red line.
Use Goal Seek for debt sizing
To create a debt sizing macro I will first show how to run the goal seek manually. Click Goal Seek and select the arguments as
- Set Cell: Debt Facility Limit
- To Value: 0
- By Changing Cell: Delta
Clicking Ok yields the following results for the debt size.
The chart clearly shows that there are no DSCR data points below the minimum DSCR covenant line Ð proof that the Goal Seek has worked as desired.
Converting the manual debt sizing to a VBA macro
The easiest way of creating a new VBA macro for a Goal Seek is to start off with a recorder macro.
Once the record macro has been activated, simply repeat the manual process as per the section above.
Click Alt-F11 and review the code which will look something like this.
Sub DebtSizingGoalSeek() ' ' DebtSizingGoalSeek Macro ' Recorded by Rickard Warnelid for Fimodo Range("H17").GoalSeek Goal:=0, ChangingCell:=Range("F6") End Sub
Replace hard-coded cell references with range names in VBA
To make this work well in a live model it is critical to replace the hard-coded cell references with defined range names.
The resulting VBA code:
Sub DebtSizingGoalSeek() ' ' DebtSizingGoalSeek Macro ' Recorded by Rickard Warnelid for Fimodo Range("Delta").GoalSeek Goal:=0, ChangingCell:=Range("FacilityLimit") End Sub
There you go, a simple nice debt sizing macro that has been automated with VBA.