In this article, you’ll learn how to build a debt sizing macro commonly used in project finance models. The debt sizing macro is simply a copy / paste macro, which calculates debt sizing through either a gearing ratio, or DSCR target. While macros are also used elsewhere in project finance models (e.g. a goal seek macro to calculate sales price, or development fee based off a target IRR), the copy-paste is definitely the workhorse.
What are Excel Macros?
Excel Macros refer to a sequence of steps written in Excel VBA that can be used to automate tasks within Excel.
As a general best practice in financial modeling, macros should be used sparingly. That’s because while they can be extremely powerful, they undercut one of Excel’s key advantages, which is transparency and flexibility.
The debt sizing macro is one of the exceptions because it can take calculations that take 40 minutes, and reduce them to 30 seconds through just a tiny bit of simple streamlining.
5 steps to building a debt sizing macro
The following is our five-step foray into how to speak to macros nicely. You should end up with a robust and quick macro, via a ten minute macro tour of duty. Feel free to try this as we go, it doesn’t necessarily need to be on a project finance model – but any copy paste will cement these skills.
Step 1: Record a copy-paste macro
Recording a macro is the first port of call to understand how actions translate from Excel into VBA.
To access the record macro there are a few different ways. A fairly reliable one is: View > Macros > Record Macro:
After naming the macro and clicking “OK,” you are given the opportunity to record a macro. This is reasonably simple. Follow these four steps as you would ordinarily in Excel:
- Select area to copy
- Copy
- Select area to paste
- Paste special > Values
When you’re done, select ‘Stop Recording’ from the Developer ribbon.
Stop Macro Recording
Congratulations! You’ve created a macro. The macro records all your actions (not including your actual mouse movements). You can actually see the underlying code for the sequence of steps that you took by selecting Alt+F11 to bring up the Visual Basic Editordouble clicking “Module1” in the left pane. If you didn’t click extraneous cells, the result should look something like this:
Sub Basic_Macro()
‘ This macro copies the interest calculated row into the interest applied row
Range(“J10:M10”).Select
Selection.Copy
Range(“J11:M11”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=x1None, SkipBlanks _:=False, Transpose:=False
End Sub
The Ultimate Project Finance Modeling Package
Everything you need to build and interpret project finance models for a transaction. Learn project finance modeling, debt sizing mechanics, running upside/downside cases and more.
Enroll TodayStep 2. Refine the macro
This macro will work perfectly well, however we have the opportunity to refine this for speed and robustness.
For starters, we can replace the “.select” in both cases directly with the instructions to copy or to paste special. There is no need to select the range, and then copy it – this modification will just copy it in one step.
We can also remove the extraneous detail around the paste special to just leave the “PasteValues”. This looks like this:
Sub Basic_Macro()
‘ This macro copies the interest calculated row into the interest applied row
Range(“J10:M10”).Copy
Range(“J11:M11”).PasteSpecial Paste:=x1PasteValues
End Sub
This makes the macro run faster – and is clearer to the user.
Step 3: Automate with a For Loop
The next step is to create a “For Loop”. A For Loop will run the code for a specified number of times – in the example below, ten times. The “For i = 1 to 10” gives the instructions to run this (i is an undefined variable which VB keeps track of), and the “Next” gives the instructions to finish that cycle and go back to the “For.” You can think of the “For” and “Next” as bookends!
We can also name the ranges of the J10:M10 to make the macro more robust. Use a named range on the ranges to achieve this. This is one not to miss – because if for whatever reason the macro operates when you’re on a different sheet in Excel, the macro will copy and paste whatever is in the range on the active sheet. And the same applies if you enter a row, and the desired range is shifted down – this is not updated in Visual Basic.
Sub Basic_Macro2()
‘ This macro copies the interest calculated row into the interest applied row
For i = 1 To 10
Range(“Macro_IntCopy”).Copy
Range(“Macro_IntPaste”).PasteSpecial Paste:=x1PasteValues
Next
End Sub
Step 4: Change to a Do Until Loop
A “Do Until” loop is not so ham-handed as a For Loop which will copy-paste until the cows come home if you let it.
A “Do Until” loop is laser focused, and will copy and paste until some condition is met… therefore we setup a “Delta” with a defined tolerance, so we know when to stop the code executing.
In order to do this, change the For with a Do Until, and a range (see screenshot below). And setup a Delta in Excel between the sum of the copied values and the sum of the pasted values. Name this Delta (see here “Macro_IntDelta”)
Pro Tip: A “Do Until” loop is interchangeable with the “Do While” loop – however the “Do While” syntax will be while the value is <> 0
Step 5: Speed up and refine with the Direct Copy
Instead of a “copy” and “paste” setup, we can more directly assign the properties of one range to another. The properties we’re assigning are values.
To do this, we need to assign the Paste Range to be equal to whatever the Copy Range is.
The below screenshot shows both Step 4 and Step 5.
Sub Interest_Macro3()
‘ This macro copies the interest calculated row into the interest applied row
Do Until Range(“Macro_IntDelta”).Value = 0
Range(“Macro_IntPaste”).Value = Range (“Macro_IntCopy”).Value
Loop
End Sub
So that’s pretty much it, in getting to an “intermediate” level of knowledge with macros. Is it truly intermediate? Well maybe not quite, because you may need some experience in trouble shooting unruly macros.
But these steps (especially the DO LOOP, and refining with DIRECT COPY) are steps that a lot of project finance modelers miss, and provide a solid base.
Where to next? One macro to rule them all
Try recording a macro in your project finance model.
And as you add more moving pieces (DSRA, with a four quarter look forward, anyone?), one macro affects the other, and before you know it, you’re herding cats.
Also known as the “Master cat-herding macro,” a master macro fires off the macros in sequence, and hopefully, optimizes the whole process. This means that all the analysis around debt sizing on a debt case, and then switching to an equity case, or running a debt process with different debt terms, can be done with one click.
