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
I am using the Minimum DSCR as the constraining factor in this example. In other cases other factors, information pills or the combination of several, order may be more appropriate
The DSCR (Debt Service Coverage Ratio) is typically defined as CFADS / Total Debt Service (including principal) for the relevant period (quarterly, find 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.
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
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.
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.
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:
There you go, a simple nice debt sizing macro that has been automated with VBA.
Great article and great example; kudos to you. Is there any way to get sample file copies of these examples? thanks a bunch.
I have emailed you the file- hope it helps. Please let us know if you found it useful!
You may also be interested in the following article Debt-sculpting using VBA and Goal Seek from the Corality blog:
I agree with you this is indeed a good article especially if you are debt sizing. Keep in mind that if you are not so focussed on debt sizing but just sizing principal repayments then you might find this tutorial useful.
This approach focuses on avoiding VBA whilst aiming for a target DSCR using simple algebra.
Take a look, puts a different perspective on it.
I completely agree that using algebra should be the first solution to any financial modelling problem. The financial modelling tutorial that you are referring to is quite useful and a similar solution can be found on the Sumwise blog which may be of interest.
Maybe just tie up the loose end by adding description for the attachment of the macro to an in-sheet button so the goal seek can be run whenever, without revisiting the VBE?
Liking the website
Good point, thanks. I prefer using Autoshapes rather than Button as illustrated by the following two VBA tutorial documents:
Step 4 Assigning to a Graphic or AutoShape
Or, check the coding of the disclaimer in one of our Corality Tutorials:
I agree with your comment regarding algebra (well first principles anyway) and I should have made it clearer that my post was predominantly to say think before rolling out the Visual Basic. This is a platform that is too readily adopted in the financial modelling arena and although neat should be reserved for a very rainy day! I say to my team, when all you have is a hammer, everything looks like a nail!
The particular solution you quote seems to be popping up a lot lately, Simon over at The Knowledge Base
..has also documented it and it has had a lot of air-time over at Plum Solutions LinkedIn group Financial Modelling in Excel .