Excel Macro for Debt Sizing

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:

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.

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.

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.

[Free Video]: Creating a Copy-Paste Macro

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