› Home › Blog › Training › Debt-sculpting using VBA and Goal Seek

all posts

Debt-sculpting using VBA and Goal Seek

by Rickard Wärnelid at September 10 2009

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-goal-seek

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.

Print this page Email to a friend

Comments

Good illustration to show that the programming techinique to iterate through a certain process is not all that difficult.

But as a debt sculpting (i.e. optimising) approach, I believe that using Excel’s built in Goal Seek functionality is not an optimum approach when working with larger and more complex models.

The issues I have are really with Excel’s Goal Seek functionality in the context of automating the optimisation of a financial model, not with the technique described in this article.

Limitations of using Excel’s in built Goal Seek functionality in larger financial models:

1. Calculation speed - in one case where I have seen the ’sequential goal seek’ approach, it took about 15 minutes to fully recalculate (i.e. optimise) the model after changing a single input. Using different technique reduced the time to about 1 minute.

2. Goal seek solves one cell at a time. In larger profect finance deals it is common that several factors need to be optimised together. And because these factors inter-relate with one another, it’s not a simple case of optimising each in sequential order.

3. The optimisation of one factor (such as debt sculpting) may require other macros to be run that optimise other factors. The Goal Seek function does not allow other macros to be run as part of the goal seeking.

4. The Goal Seek functionality requires a specific target value to be entered. Sometimes the optimisation requirement might be to minimise or maximise a certain input within the constraints of the model. But this minimum or maximum value is not specifically known (or changes as other optimisatin factors change).

I guess this is a new topic of conversation rather than feedback on the original article.

Gavin Townshend | 11/09/2009 12:04am

Hi Gavin,

I fully agree and maybe the title of the post should have been ‘use VBA for sequential goal seek’ rather than to complicate things with a debt sculpting example.

I fully agree that the goal seek method is often now powerful enough for a full debt structuring model but it has a neat simplicity to it which I find very appealing. The problem many people have with larger multi-sequence copy-paste macros is that if (when….) they go chaotic and suddenly only ‘sometimes’ converge it gets hard to understand why and how to solve it. In those cases a back-to-basic approach can be very beneficial – if nothing else to take control of the problem. This is particularly true in forward- and/or backward-looking metrics where copy-paste macros can easily become unstable.

The volatile nature of these solutions sometime remind me of a thesis I was working on during my physics studies in Vienna working out the eigen-frequencies of a titanium hip-replacement. If you have one of these in your body I would recommend you to stay away from your high-rpm-power drill as you may otherwise experience a serious discomfort when it starts wobbling away… Similarly a multi-sequence copy-paste macro can work perfectly well until one day you stress it too much and if simply falls over..

Your point 4 is interesting though and I have a very simple solution to this which it would be good to get your views on. I always define the target value in Excel and optimise the cell ‘Target – Actual’ which avoids coding of numerical values in the VBA.

Rickard Wärnelid | 15/09/2009 3:13pm

An obvious comment regarding speed: turning off screen updating speeds up calculation. Add:

Application.ScreenUpdating = False

after variable declarations. Can make a huge difference.

Stefan Westerbladh | 15/09/2009 6:42pm

Hej Stefan,

Jag kikade litet pa era produkter - later intressant! I assume that you are building software to do the same things that normal financial models do in Excel? That is certainly a way of increasing the integrity of the models (great) but which will certainly impact the flexibility and transparency of a transaction model in Excel (not so great).

Your comment is very true and is definitely an approach I would recommend. I have found while running VBA courses that too early adoption of Application.ScreenUpdating makes debugging harder for novice users as they can’t see what’s going on. For a finished product though it is a must!

In copy-paste macros it can sometime also make a big difference to use manual calculation and only calculate after a series of copy-paste actions. This can in some cases decrease the convergence rate but it is often worth it from a calculation speed point of view.

Rickard Wärnelid | 15/09/2009 8:22pm