Wall Street Prep

Debt sizing Excel Macro for Project Finance: Example with VBA Code

How to Create the "Copy-Paste" Excel Macro for Debt Sizing in Project Finance Models: Example with VBA Example Code

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:

  1. Select area to copy
  2. Copy
  3. Select area to paste
  4. 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:=x1PasteValues, Operation:=x1None, SkipBlanks _:=False, Transpose:=False

End Sub

Step-by-Step Online Course

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 Today

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

Kyle Chaning Pearce
Kyle is a financial modeling instructor/practitioner with years of experience of project finance modeling training at Investment Banks, Funds, Project Developers, Big 4 Accountancy firms and more. Kyle was previously head of training EMEA at Mazars Global Infrastructure Finance, and trained professionals and students in project finance financial modeling around the world with project finance consulting firm Corality.
Comments
avatar
X

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.