Debt-sculpting using VBA and Goal Seek

In project finance transactions it is common practice to use debt sculpting when sizing the debt - i.e. finding an appropriate level of debt from the lenders point of view. In our VBA training courses I have found that one of the most appreciated methods is what we refer to as the ‘sequential goal seek’ so I would like to share this method today.

Debt-sculpting in Excel/VBA - alternative methods.

Debt sculpting can be done in a number of ways - often even using simple algebra. In other cases where there are circular logic due to look-back covenants or sweep mechanisms one is required to use a VBA macro. The example below outlines to core principles in how to use Goal Seek in combination with VBA to solve a range of cells to an optimal value - cell by cell.

Automate manual tasks in Excel with VBA

The example is an illustrative example of how simple it can be to automate a manual task without having to introduce any particularly complex VBA code. The focus in our example is on the VBA and Goal Seek rather than the actual Debt Sculpting, but hopefully both messages are clear.

VBA and named ranges

Before looking at the code we need to set up the named ranges in Excel. It is critical to ALWAYS work with range names in Excel even though it may cause confusion to novice users, as there is a great risk that your vba code will stop working over time otherwise.

DebtService: The highlighted yellow range in the image
DSCRDebtDelta: The DSCR Delta row
CountDeltas: The cell with the value ‘6′. This could easily be coded in VBA but for simplicity I have done it in Excel.

VBA Goal Seek example

Sub SeqGoalseek()

Dim i
Dim NumberOfGoalseeks
Dim rngDebtService As Range
Dim rngDSCRDebtDelta As Range

'Pick up the number of Deltas to goal seek
NumberOfGoalseeks = [CountDeltas]

'Pick up DebtService range
Set rngDebtService = Range("DebtService")

'Pick up DSCRDebtDelta
Set rngDSCRDebtDelta = Range("DSCRDebtDelta")

For i = 1 To NumberOfGoalseeks

'Goalseek DSCRDebtDelta(i) by changing DebtService(i)
rngDSCRDebtDelta(i).GoalSeek Goal:=0, ChangingCell:=rngDebtService(i)

'End Loop
Next i

End Sub

How does the VBA goal seek example code work?

The VBA code will run a loop for 6 iteration (=the number of cells to optimise) and for each iteration optimise Debt Service (rngDebtService(i)) as a function of the DSRC Delta (rngDSCRDebtDelta(i)).

If you want to use this in a ‘live’ project finance model I would recommend you to refine the errorhandling and make sure that it is really stable. There is nothing worse than a VBA macro that only works sometimes….

Would you like to get a copy of the VBA goal seek workbook?

You could simply copy the code above and set up the range names and it will work. Alternatively, drop me an email and I can send you my version which may save you a bit of time.

COMMENTS