Table of Contents
- What are Excel Macros?
- 5 steps to building a debt sizing macro
- Step 1: Record a copy-paste macro
- Step 2. Refine the macro
- Step 3: Automate with a For Loop
- Step 4: Change to a Do Until Loop
- Step 5: Speed up and refine with the Direct Copy
- Where to next? One macro to rule them all
- [Free Video]: Creating a Copy-Paste Macro
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
- Select area to paste
- Paste special > Values
When you’re done, select ‘Stop Recording’ from the Developer ribbon.
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:
‘ This macro copies the interest calculated row into the interest applied row
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=x1None, SkipBlanks _:=False, Transpose:=False