Breaking down walls with Excel's 'Spillover' effect: A New Chapter for cash flow modelling

During the worldwide lockdown sagas, many naturally missed one of the most significant changes being made to the public by Microsoft for Excel users. One of the biggest changes since its initial release half a century ago. The Microsoft Excel Calculation Engine team delivered a change right at the heart of how Excel recalculates everything you do: they introduced functions that spill into other cells… more formally called dynamic arrays.

In the 2020s, one formula in one cell can model its surrounding cells.

Discounted Cash Flow example

Let’s look at an example within Discounted Cash Flow (‘DCFs’): every investment scenario is measured over time (‘Time value of money’ theory and all that…) and the respective Monthly time horizons at the top of the cash flow sheet need to be shown: e.g. “Month 1, Month 2, Month 3” etc.

An Overspill-type function means we can create a time horizon from Month 1 to Month 60 without copying & pasting across 60 cells and columns. As the name indicates, you don’t even need to worry ‘how far right to go’ or even count the columns because the ‘spilling over’ action takes care of this manual drudgery for you.

Introducing one Overspill Function: SEQUENCE

So let’s use a simple example. Imagine you want a 7-year cash flow time horizon:

In one cell you can write =SEQUENCE( # of rows, # of columns, start number, step increment).

Example values for the SEQUENCE inputs can be as follows:

• Number of rows is 1 – because you only want an annual time horizon on the top row.
• Number of columns is 7 - because you want 7 year hold period along the top row
• Start year is 1 - Year 1
• Steps up with 1 - because you want each year header to increase by 1 unit

By changing 1 cell, you change the entire Time Horizon

Here is the Overspill effect when changing 7 years to 20 years: