Intro to Financial Modelling - Part 13: VBA

Excel (the worksheets, the cells, the functions) is a very transparent and widely understood medium. VBA on the other hand is clunky, hidden away and neglected. VBA goes against the very notion that models should be easy to read. This is a common tenet of financial modelling standards, including ICAEW’s very own. It is only by placing numerical calculations in the Excel worksheets – and not hidden in VBA – that the user can easily understand the financial model. In Excel, the bonnet is always open and the engine available for all to see.

If we dig a little more about why people aspire to learn VBA they usually say that they want to do the fancy optimisation- such as sculpting debt - that often happens in project finance transactions, and which usually involves a bit of VBA.

Very often the optimisation process involves a “copy and paste” macro, which resolves the circularities that often occur in funding calculations. For instance, a debt size affects the commitment fee occurred, which affects the funding requirement, which affects the debt size. These types of macro are by far the most common in project finance models. However, they do not always require a macro.

In my work as a financial modeller I have seen many models where a copy and paste macro was used but where, with a bit of maths, a formulaic alternative could be implemented in Excel. What we are doing in many Excel forecast models are a series of linear equations – a lot of them, yes, but nothing inherently complicated. A bit of school level algebra such as simultaneous equations for instance can often resolve a circularity without VBA. It is sad for me – as a mathematician – to report that unfortunately few think back to that important life skill they were taught when they were 14 (maths!) and instead most reach for more technology – in this case VBA – as the answer.

Having said that, the reality is that circularities often have far too many elements to solve algebraically in a concise manner, so copy and paste macros are an efficient alternative, for example by creating a button to avoid having to manually repeat an action many times.

An attendee at a training course said to me the other day: “If you have to use VBA then Excel is the wrong solution.” I don’t totally agree. Copy and paste macros are useful. But I do concur entirely when it comes to numerical calculations, such as User Defined Functions. If you ever find yourself doing numerical calculations in VBA (perhaps you need the speed ;?) then Excel and VBA are probably not the answer. There are other programming languages that are faster and easier to maintain - use them.

At the risk of doing myself out of business, Excel is sometimes not the right answer!