LAMBDA – the new Excel Paradigm for Modeling

March 7, 2024 by Craig Hatmaker and Lance Rubin?

Introduction to the co-author?

Craig is the founder of Beyond Excel (BXL) and 5g. BXL was founded in 2006 to teach readers and viewers how to pull data from enterprise systems (e.g. ERPs like SAP, Netsuite, Dynamics etc) into Excel using VBA. Excel has changed dramatically since 2006 and so has BXL’s mission and solution methods. With the arrival of LAMBDA, BXL is now focused on making the dream of Dynamic Arrays (DA) in financial models possible.??

Why did Craig select the topic and why is he passionate about it?

LAMBDA is a key ingredient in making the dream of DAs in financial models possible. Craig has been pushing automation and scalability of modeling in the industry, having written one of the earlier article on table-based modelling techniques .??

The Dynamic Array revolution and LAMBDA?

For as long as Excel has been used as a modeling tool, there have been numerous challenges with unhygienic spreadsheets and models.??

This is driven largely by Excel users hardcoding formulas, inconsistent logic and formatting and a general lack of structure and design through a history of nasty habits which translate into errors, and complexity and push many to shun Excel altogether.??

The cry for the death of Excel has been fake news even before fake news was a thing. This was driven for the most part by software companies trying to sell their “Excel killer” app.

Recognizing the challenge, but not pandering to the fake news, Microsoft has done an amazing job of providing a new calculation engine (DA) and the ability to build and call upon custom functions (LAMBDA) that leverage DAs without the use of VBA. If you want to learn more about DA take a read of the earlier article in the series .??

In the same year that Microsoft announced the introduction of LAMBDA to beta, they also released Dynamic Arrays to the general public. Dynamic Arrays make possible a revolutionary approach to financial modeling. Their benefits are compelling as they challenge one of the fundamental issues most users have when using other people’s models. Can I trust it???

Dynamic Arrays and LAMBDAs together reduce potential model failure points to a tiny fraction of traditional methods because the dynamic array’s spill region contains results, not formulas. Spill ranges cannot have inconsistent formulas because there are no formulas.??

Dynamic Arrays also make models far more responsive. As an example, a five-year monthly template can, with the change of two input cells, become a ten-year annual template with no formula changes.??

With these advantages, dynamic arrays looked like they would take financial modeling by storm until we discovered that, by themselves, they could not do simple corkscrew calculations.?

With LAMBDA, they can.?

The problem with LAMBDA is it is an advanced Excel skill and not everyone will be comfortable editing or developing these functions, but they are significantly easier than the model logic that goes into producing the outputs of certain components of a model e.g. loan amortization, debt servicing and optimization or depreciation schedules.??

Most financial modelers are finance experts with some understanding of accounting and generally not too technical from a model coding or automation perspective. With the help of financial modeling standards like FAST, BPM and SMART methodologies, modelers did not need to be technical experts as they could follow some of the design principles provided by these methodologies.?

Once again, it seemed DA was doomed to fail as a few of these standards went against the grain of DA (eg enforcing horizontal-only time series and the need for corkscrews) until 5g came along.?

5g is a standard that converts Excel formulas into functions defined by a user ie User Defined Functions (UDFs). Functions, like Excel’s SUMIFS(), can perform complex logic under the covers.?

We did not care before LAMBDAs existed what was under the covers of the SUMIFS() function, ie C# code, so why should we worry or care about it now, provided it's built by a technical expert? Not just Microsoft.??

We should only care that functions like SUMIFS(), do what we want them to do.??

Does it do what it says on the tin without fail????

Who cares what goes into building anything, provided it works as you expect, surely that’s good enough??

In most cases how it's built and what goes into anything we use is protected through intellectual property, so we use many things without actually knowing how they are built, including cars we drive and kids' car seats. Ever thought what the ingredients that went into making Coke so popular??

5g functions use LAMBDA but modelers need not know anything about LAMBDA.??

But unlike Excel’s functions, if we want to, we can see what is under the covers of any 5g function. And under the covers is nothing but Excel formulas.??

Below are some examples of 5g functions:?

• Corkscrewλ() enables dynamic arrays to perform corkscrew calculations.??
• Movementλ() calculates the differences between columns.?
• Depreciateλ() produces an entire depreciation schedule for any number of assets with each having its own acquisition value and date, salvage value and disposal date, and depreciation method.?

5g functions are far simpler to use than the formulas and intertwined logic they replace. They can be crafted by experts and shared with junior staff to enable junior staff to perform expert-level calculations with ease.??

Think of when Neo (Matrix film) suddenly became a Kung Fu master and defeated Morpheus. Now picture that for a junior modeler’s first few months in the role. How long does it take for a junior modeler in a bank or anywhere to become proficient? At least a few months, with many errors and bad habits along the way being repeated by every new modeler after them.?

If you had to teach the topic in a class to school kids, what key tips would you give them to focus on?

Today’s generation of school kids has significant advantages (and disadvantages) when it comes to learning a new skill and developing their problem-solving and thinking capabilities.??

Whilst it’s important to be grounded on solid foundations, spending hours to master mechanical things when this time could be used in designing new approaches and applying these technical skills seems significantly more valuable.??

The rise in AI will force everyone to see how they can apply and use these new tools, rather than doing things the same way they have always done. Learning how to adapt and adopt new ways is the only way we get to flourish or be left behind.?

DA will make models more responsive with less risk.??

LAMBDA makes DA do the things needed by financial modelers.??

5g turns LAMBDAs into simple functions that anyone of any skill level can use in any workbook.?

The paradigm revolution is here now.?

So, what’s it going to be a blue or red pill??

?What practical steps can people take now to learn more??

5gModeling.com (or if you prefer 5gModelling.com ) explains 5g, provides access to 5g functions, and for those who are more adventurous, includes a 5g function creator’s tutorial. All of that is free.?

For those who want to start using 5g now, Eloquens.com offers the 5g Financial Starter Pack.?

It includes three libraries of 5g functions we can use in our models. One library provides date functions like Timelineλ() that create model timelines. Another library contains basic array functions like SumColsλ() that total each column in an array. The last library contains financial functions like Corkscrewλ(), Movementλ(), Depreciateλ(), Amortizeλ() and more. Also included is a workbook that provides examples of each function and links to video tutorials. And last, but not least, it contains example 3 statement models converted to fully dynamic array models.?

?Videos:?

The dynamic array revolution ??

Intro to 5g (Departmental budgeting) part 1 ??

Intro to 5g (Departmental budgeting) part 2 ??

Circularity in Excel Solved with 5g ??

How to Import 5g functions into Excel ??

Depreciateλ() ??

Amortizeλ() ??

5g Testing ??

?