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, or the combination of several, 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, semi-annual or annual, forward-looking or backward-looking).

pic1

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’.

pic2

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

pic3

Clicking Ok yields the following results for the debt size.

pic4

pic5

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.

pic6

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.

Recent posts by Rickard Wärnelid

Tags: , ,

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

  1. Michael Fozouni says:

    Great article and great example; kudos to you. Is there any way to get sample file copies of these examples? thanks a bunch.

    Michael

  2. Hi Michael,

    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:

    http://blog.corality.com/2009/09/debt-sculpting-using-vba-and-goal-seek/

    Rickard

  3. Nick Crawley says:

    Hi Michael,

    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.

    http://www.navigatorpf.com/training/tutorials/debt-sculpting-dscr-vba

    This approach focuses on avoiding VBA whilst aiming for a target DSCR using simple algebra.

    Take a look, puts a different perspective on it.

    Nick

  4. Hi Nick,

    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.

    http://blog.sumwise.com/2009/08/17/solve-circular-references/

  5. Chris McNeill says:

    Hi Rickard

    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 :)

  6. Hi Chris,

    Good point, thanks. I prefer using Autoshapes rather than ‘Button’ as illustrated by the following two VBA tutorial documents:

    http://www.bettersolutions.com/excel/ECC653/LC216621011.htm
    Step 4 – Assigning to a Graphic or AutoShape

    Or, check the coding of the disclaimer in one of our Corality Tutorials:
    http://www.corality.com/training/tutorials/goal-seek-function-in-excel

    Rickard

  7. Nick Crawley says:

    Hey Rickard,

    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
    http://www.tkb.co.uk/circular.htm
    ..has also documented it and it has had a lot of air-time over at Plum Solutions LinkedIn group “Financial Modelling in Excel”.

    Maybe we should do a full posting on it?

    Nick

  8. Javier says:

    Thank you for this!!! very useful

    Any chance you can send me the file?

    Thanks!

    Javier

  9. Javier,

    No problems, will send you sample file for the debt sizing for minimum DSCR with VBA Goal Seek.

  10. Koning says:

    Very useful. Could you send me the file. Thanks.

  11. Koning,

    Have sent you the debt sizing file with the VBA example.

  12. Koning says:

    Thanks very much for the file. Very useful tool.

  13. Jim says:

    Richard,
    Thank you very much for this article. May I also get a copy of the file?
    Kind regards,
    Jim

  14. Andrew says:

    Hi Michael & Richard,
    Very nice article! You may have wanted to post the file attachment on an auto-response form by this point, but may I ask to get a copy of the file as well?
    Cheers,
    Andrew

  15. Hi Andrew,

    I haven’t quite figured out the auto-reply but just yet… Anyways, happy to send you the file – please let us know if you found it useful!

    Cheers,
    Rickard

  16. John says:

    Thanks for the article, may I have the file also?

  17. John,

    I have emailed you the debt sculpting example file.

    Cheers,
    Rickard

  18. Stephan says:

    Rick,
    thanks for your helpfull artricle!
    May I ask for a copy of the file as well?

    Kind regards,
    Stephan

  19. Stephan,

    I have emailed you the file – did you find it useful?

    Regards,
    Rickard

  20. Jim says:

    I like this approach. May I also get a copy of the file?

    sincerely,

    Jim

  21. Deepak says:

    Rickard, Can you send me the file as well – Many Thanks

  22. Deepak,

    I have sent you the file for debt sizing for minimum DSCR with VBA.

    Regards,
    Rickard

  23. Deepak says:

    Thank you for the files – it is an innovative and simple way getting around a problem that gives many grey hairs.

  24. Jeffri says:

    Very useful article you have here
    Could you please send me the file?

    Thanks.
    Jeffri

  25. I have sent you the debt sizing example – enjoy!

  26. Eleanor Blomdahl says:

    Richard, the article’s very useful for the exercise we’ll do. Can I have the file as well please?

    Regards.

    Eleanor

  27. Eleanor, I have sent the file. Please let me know if you have any questions!

  28. TAMARA BRENES says:

    Richard,

    Very useful article. Can I get a copy of the file as well?

    Thank you!

    Tamara

  29. Richard says:

    Rickard,
    Fantastic solution! I have attended a few Project finance modelling classes but they never teach you what you run into in the real world. Any chance I can get the excel file for the solution above?

    Many thanks!

  30. rachmat says:

    Hi Richard,

    It is very useful and helpful article.
    Any chance I can get the excel file for the solution above?

    Many thanks

  31. Radhika Batra says:

    Rickard,

    I’d love to gt a copy of the file.

    Thanks for your help!

    Radhika

  32. Rasmus Friis says:

    Dear Richard,
    Quite interesting article and helpful – at present struggling with getting the right structure mapped out for DSCR calculation for our solar projects in Europe. Could I please have a copy.
    Regards
    Rasmus

Comment on this Article