Elements

Table of Contents

Overspill Functions

• SEQUENCE

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)
alter-text

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:

alter-text

• VSTACK

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:

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

Excel screenshot of this real estate Stacking Plan exercise:

alter-text

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:

alter-text

• UNIQUE

Input variables

(TBC)

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.