Table of Contents

Overspill Functions


Input variables

= SEQUENCE( number of rows, number of columns, start number, step-up value)

DCF Time Horizon Example

So let’s use a simple example. Imagine you want a 7-year cash flow time horizon. These are the types of inputs to have in mind:

=SEQUENCE( 1 timeline, 7-year time horizon, starting from Year 1, step-up increase is 1 (i.e. annual increases))

i.e. SEQUENCE( 1, 7, 1, 1)

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 an entire Time Horizon

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



Input variables

= VSTACK( item 1, item 2, item 3, etc)

Stacking Plan Example

So let’s use a simple example. Imagine you have two items: Upper residential floors and some retail units on the ground floor.

Let’s express them with 2 SEQUENCES, and then stack them vertically with VSTACK:

=VSTACK( "Upper floors", "Ground floor" )

For this particular example, let's assume the floors are represented by the following:

"Upper floors" = SEQUENCE( C5, C6, C7, C8)
"Lower floors" = SEQUENCE( I5, I6, I7, I8)

To build the Stacking Plan, we then assemble the whole building by calling:

    SEQUENCE( C5, C6, C7, C8),
    SEQUENCE( I5, I6, I7, I8)

Excel screenshot of this real estate Stacking Plan exercise:


The final Stacking Plan displays the 20 residential units, starting from Apartment 1 on the 2nd level and numbered progressively per floor and up to the top 6th level. On the ground floor we now have the separately numbered retail units at the Street Level.

Note the ‘-1’ value used in these SEQUENCE formulae. You can ‘decrease’ as well and ‘increase’ the desired numbering. But in this scenario, you have to be confident about the final number of floors and units. If you change the SEQUENCE to display 6 floors of residential units, you will encounter unrealistic Apartment numbering like this:



Input variables


Other reading / viewing

Youtube: Excel Meets Lambda

A video from The Archimideans in conjunction with the University of Cambridge Competitive Programming Society.

Among other topics, we hear from which perspectives Simon Peyton-Jones and Andy Gordon approached their work on introducing Lambdas to Excel at Microsoft and their fascination with functional programming.