Best practice approach to copy-paste macros in financial models:

Although VBA is to be avoided where possible in project finance models, due to the inherent circularities in some aspects of project finance calculations VBA is sometimes needed to implement copy-paste functionality in a model in order to ‘break’ these circularities.

This tutorial will demonstrate the best practice approach to implementing such copy-paste macros and discuss potential optimisations which will help you in applying this code robustly and efficiently in different situations.

Why circularities exist

You may have encountered a circular reference in your modelling before or seen the dreaded pop-up and blue arrows, however you may not necessarily know what a circularity is or why they exist in financial models. A circularity just means that the value of a cell is dependent on itself and therefore Excel cannot properly calculate as a result.

The typical project finance example is the case of debt sizing based on a DSCR-sculpted repayment methodology. In this quite common situation the debt limit will be calculated as the total amount of principal repayments able to be paid over the loan tenor given the project cash flows (CFADS / DSCR – Interest), however the interest in this calculation is dependent on the debt limit as this affects the amount of debt drawn initially and therefore will depend on itself (a circularity).

We will cover this type of copy-paste macro as well as a DSRA target balance copy-paste macro in this tutorial.

Setup

Macro sheet

As a matter of best practice all copy-paste macros should be setup on a specified “Macro” sheet as this improves transparency and reduces risk of pasting values in the wrong cells without realising.

The paste areas to be used by the macro should be clearly formatted as such, we recommend setting up a special and distinct cell style with a label such as “macro paste “ (see orange cells in below exhibit), to ensure the user understands these are not input cells. The below screenshot demonstrates the basic setup for our two copy-paste macros:

Named ranges:

VBA code should never reference specific cell references in Excel. Instead, the copy and paste ranges for each macro should be setup as named ranges in the model. This will ensure the VBA code won’t break if rows or columns are inserted or removed from the sheet, causing the cell references of these ranges to change. Named ranges can be created using the Name Manager, accessed with the keyboard shortcut Ctrl + F3.

Delta check

A check should be set up to enable VBA to iteratively implement the copy and paste via a loop (explained below), until the applied (paste) value is within the designated tolerance of the calculated (copy) value. This check will simply be the difference between the copy and paste values, rounded to the designated rounding tolerance.

For a time-series copy-paste macro this check should be performed on a periodic basis and an overall check created which sums the absolute value of each individual check. The overall check can then be used as the condition for the loop. In each case a single delta cell should be setup as a named range so that it can be referenced dynamically within the copy-paste macro.

Copy-paste code

The code to execute a copy and paste of values using VBA is very simple. The below code will copy the values in the named range “Funding_Copy” to the named range “Funding_Paste”:

This code can very easily be adjusted to be used for other named ranges by simply changing the names in quotes, however it is important to note that the named ranges should be exactly the same size, i.e. number of rows and columns.

Loop code

To create the loop using the copy-paste delta we will be using VBA’s Do Until Loop, which allows you to specify the loop condition which needs to be satisfied before the code stops executing. For the above debt limit copy-paste macro this could be set up as follows:

Optimisations

Excel manual calculation mode

During the macro Excel will recalculate after each copy-paste, however in some instances this recalculation is not necessary and will only slow down the macro. To stop Excel from doing this you can change the calculation mode to manual at the start of the macro and then manually recalculate the model after each copy paste using the code “Application.Calculate”. The calculation mode can then be set to the desired mode again at the end of the macro. The three different calculation modes can be set as following:

Manual calculations

Automatic except for data tables:

Automatic calculations:

The final VBA code at the bottom of this tutorial shows how to detect the current calculation mode when the macro runs, set the calculation mode to manual for the duration of the macro and then return the calculation mode to the original setting after the macro has run.

Maximum iterations

In some situations the copy-paste loop may never reach a solution and therefore will continue looping forever. If there is potential for this in your macro we recommend you implement an iteration counter and maximum number of iterations for the loop.

This can be done by declaring an integer variable (initially set to equal 0), increasing the variable value by 1 for each loop and then adding a condition to the loop that the variable must be less than or equal to the maximum number of loop iterations (which should be setup as a named range in the model). Using our above loop code, we have:

Screen updating

In Excel it is possible to disable screen updating while running VBA macros. This is highly useful as typically more than half of Excel’s processing power is used to update the screen while running copy-paste macros, therefore turning this feature off during the macro will reduce time taken to run the macro by more than half. This feature can be turned on and off with the following code:

Excel status bar

The Excel window can look very strange while running a VBA macro. It may either appear as if it is flickering if screen updating is left on or looking like Excel is crashing if screen updating is turned off. To counter this and provide updates on the progress of the macro we recommend utilising the Excel status bar to update the user on what is happening in the macro. A simple example is to update the status bar for each time the macro loops through the copy-paste operation with the number of loops executed:

To achieve this we can add an additional line to our copy-paste loop from above using the Application.StatusBar property.

Full VBA code

Below is the full VBA code incorporating all of the above functionality and optimisations as well as comments in green green explaining each line of code. When coding in VBA it is best practice to always comment your code as it will make it much easier to understand, update and re-use.

Final code: