by Jeff Robson

Financial Modelling using Dynamic Array Functions

Best practice financial modelling has always been to enter your formulas in blocks: enter a formula, then copy this across and possibly down also, making sure you have your absolute and relative references set correctly.

This is very useful because it is faster to build a model, easier to enter lots of formulas, less error-prone and easier to audit … all very desirable things when you’re constructing a financial model!

Excel’s dynamic array functions (more info here) take this concept to a whole new level because they can be setup to automatically copy themselves.

This means they are perfectly suited for financial modelling because they make it impossible to simply change a formula halfway along a row or manually overwrite a formula with a number.

What’s great about Financial Modelling using Dynamic Array Functions?

If we setup our dynamic array functions and formulas correctly, we only need to type each formula once, and it will automatically spill (i.e. copy) itself across and down (if need be).

In addition, by changing one number, our entire financial model can be extended to incorporate additional periods.

And, by inserting additional row(s) into our setup tables, we can automatically add as many new products, staff, projects or whatever else we’re modelling as we want … and these new items are automatically inserted into our model with the correct formulas at every relevant point.

Dynamic array functions can also be used to automatically summarise our detailed calculations and generate all our financial model reporting using the standard SUMIFS formulas and others like these.

In addition, dynamic array functions can even be used to produce simple scenario managers.  These have a number of advantages over data tables since all the results are live all the time plus they’re about 1,000 times faster.

Because all dynamic array functions are processed in arrays (as you might guess!), they are incredibly fast, even over millions of calculations.  I don’t need to tell modellers how useful this will be for their incredibly complex are ever-growing financial models.

In short, dynamic array functions are absolutely revolutionary when it comes to financial modelling!

Take a look at the video & the free example file provided to see how we built an entire financial model without copying and pasting a single formula!

Download our free example financial model using dynamic array formulasNote: as of Oct 2019, Excel Insider Edition is required to use this file

Are there any Disadvantages?

So far, I’ve focussed on the incredible advantages of using dynamic array functions for financial modelling, however, there are a few things to consider:

  • Auditability: it’s not possible to trace a particular formula in a given cell back to see its individual components.  A reviewer can simply look at the original formula entered and check that this is correct, however, it’s much more difficult to answer the question of “Where does that number come from?”.
  • Complexity: the formulas are a little more complex and some functions that are very simple in standard Excel functions are quite complex if done using dynamic array functions (e.g. SUM doesn’t spill so we used the obscure MMULT function to simply add a column of values!).  Hopefully, this will change over time as more dynamic array functions are added.
  • Intuitiveness: until you get used to thinking in arrays, the new dynamic array functions aren’t as intuitive as the standard Excel functions e.g. =MATCH(D7#,D7#,0) is a weird way of saying, “Match the value of D7 in this column with the spilled array of values that start at cell D7”.
  • Unexpected Behaviour: not all functions spill and some don’t behave like you might expect them to e.g. EOMONTH and the logical functions (AND, OR, NOT) don’t spill.  However, DATE spills, as does =(A1#>0)*(A1#<100)
  • Compatibility: like most new functions, the dynamic array functions aren’t compatible with earlier versions of Excel so some will display #NAME errors in earlier versions while others will appear are array formulas that have been entered using Ctrl-Shift-Enter (refer to Excel’s compatibility checker for more information).