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!

You can also use them for balance sheet calculations, although this is a little more complex at present. More details in our blog on Corkscrew Calculations.

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: Office 365 is required to use this file (ideally monthly or insider channel)

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, unless they’re used to these kinds of formulas, they may find it more difficult to answer the question of “Where does that number come from?”.  On the plus side, some people may find dynamic array spreadsheets easier to audit because there is no way for formulas to be modified halfway across a row.
  • 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 doesn’t spill unless you add a value to the month offset, and none of 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).

Help Improve Dynamic Array Functions

There are some great ideas in Excel UserVoice so please support these and help make dynamic arrays even more useful!

  • Add SUMCOLS and SUMROWS functions so we don’t have to rely on the ugly, difficult and non-intuitive MMULT function just to add up a column of numbers
  • Copy the formatting when a formula spills (at the moment, you need to do this manually or pre-format the range you think your formula might spill to)
  • Array formulas should inherit their number formatting from the source data (at the moment, you need to manually format your dynamic array formulas)