A common problem modellers have is that of file size and calculation speed. In fact, it is one of the most commonly posted topics in our popular Financial Modelling in Excel LinkedIn forum. Since writing the article Excel Model File Size Getting out of Hand a few years ago, I have since come to appreciate the role that volatile functions can also play in slowing down the performance of models.
Some of the most commonly-used volatile functions are OFFSET, INDIRECT, RAND, NOW, TODAY, ROW and COLUMN and overuse of these particular functions in your model can really slow down your calculations. Most functions will only recalculate whenever any cell on which they are dependant changes but a volatile function will recalculate whenever anything changes – regardless of dependencies – or when the file is opened or saved. This will slow down the calculations and affect the performance of your model. If size and speed is not a problem, then go ahead and use them, but if memory is an issue then take into account any volatile functions and consider whether there are any alternatives.
Using OFFSET
OFFSET is particularly useful when trying to stagger a payment or value by a variable amount – for example; what if we make a sale, but don’t get paid for a month? How does that impact our cashflow? What if payment terms were three months?
OFFSET functions are also often used for defining a dynamic named range which can be helpful for creating charts. Do you really need to use an OFFSET function, however? Sometimes an INDEX/MATCH combination can achieve a similar result and are much easier for other modellers to audit.
Using INDIRECT
The INDIRECT function is used when you want to retrieve a value from a sheet or file name which is variable. In the example below, the formula is pulling a number from the sheet “June”, based on the value in the input cell B3. If the user changes cell B3 to “July”, the formula in cell C3 will then pull the result from the sheet “July” instead.
The INDIRECT function is useful when you don’t want to insert links to other files in your model. For example, links can be difficult to manage when files that are emailed backwards and forward often. By using the INDIRECT function, you can create a formula which retrieves data from an external file without creating a link to it. Because INDIRECT is volatile however it will continually calculate and if the external file is not open at the same time, it will return an error. You also need to be careful that the spelling and syntax of the name of the sheet or file is exactly the same as the input, or the formula will also return an error.
Besides its calculation issues, INDIRECT can be pretty tricky to build and difficult for other users to understand so consider whether it’s really necessary to include it in your models or if it’s easier to simply insert an ordinary link.
There are a few unusual functions such as NOW(), TODAY(), COLUMN() and ROW() which do not require any arguments. TODAY() returns the current date whenever the file is opened, and NOW() will return the time as well as the date.
COLUMN() and ROW() are useful as helper functions when you want to automatically calculate the row or column in a VLOOKUP or HLOOKUP function – but a MATCH function can often perform the same task.
Bear in mind that the more volatile functions contained in your models, the slower the recalculation will be. If for example you need to include today’s date in many different places in the model, consider putting the =TODAY() function in one cell, and then linking back to that cell whenever you need to refer to today’s date. This will recalculate more quickly than inserting the function TODAY() in multiple cells.
We know that one of the many skills of a good financial modeller is to identify which Excel function is the best choice in each modelling situation. There are advantages and disadvantages of any formula selection, and a good modeller will always choose the most succinct, efficient and easily audited function. The volatility of a function, however, should also be taken into consideration when selecting the function, and the modeller must decide if the additional functionality the volatile function offers will outweigh any size or speed issues.
