Debt sizing for minimum DSCR with VBA Goal Seek – Solve for zero delta, covenant



Debt sizing for minimum DSCR with VBA Goal Seek Solve for zero delta!

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, information pills or the combination of several, order may be more appropriate

  • LLCR (Loan Life Coverage Ratio)
  • PLCR (Project Life Coverage Ratio)
  • ICR (Interest Coverage Ratio)
  • Gearing
  • 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, find semi-annual or annual, forward-looking or backward-looking).

Covenant debt solutions

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 .

Covenant debt solutions

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

Covenant debt solutions

Clicking Ok yields the following results for the debt size.

Covenant debt solutions

Covenant debt solutions

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.

Covenant debt solutions

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.

Recent posts by Rickard Wärnelid

Comments for Debt sizing for minimum DSCR with VBA Goal Seek Solve for zero delta!

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 Covenant debt solutions

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 .


Leave a Reply

Your email address will not be published. Required fields are marked *