What is Lambda?
Lambda is a newer excel function which was available for general release in 2022, which allows users to create a custom formula and store it for future use. Thereby instead of rewriting a long formula every time (and possibly introducing errors), you can simply call the custom function in your financial models. These formulas can be as simple as undertaking multiple lookups or complex multi-step calculations reduced to a single custom function (see below example).
These custom functions can even easily be shared with colleagues, clients or the wider world.
Benefits of Lambda?
By leveraging custom Lambda functions, model development can be improved via:
- Consistency – with a team of multiple model developers, custom lambda functions ensure each developers prepared their calculations in the same consistent way, simplifying model reviews
- Error reduction – once a lambda is built it can be extensively tested to ensure it works and then reused. This reduces the chances of errors being introduced into a model by developers rewriting functions every time (& missing a critical step) You can even build in custom error checks into your function!
- Instructions – in storing a lambda, the developer can add comments throughout their calculations. This improves the readability of the formulas as you can explain in great detail what each step does
If you want a more in-depth discussion, Craig Hatmaker has a great article on what he calls “5G Modelling” and the benefits of leveraging standardised lambda formulas. Beyond Excel – 5G Explained
How Chessboard uses Lambda?
We often reuse custom developed lambdas to ensure consistency in our models.
For example, rather than manually constructing a debt amortisation schedule for each tranche of debt, we have a prepared finance lambda which generates this schedule through a single formula:
The formula to generate this statement looks like this:
=LoanSchedule([Amount],[APR],[TermInMonths],[StartPeriod],[ModelPeriodCount],[MonthsPerPeriod],[LoanType],[MonthlyAmort])
The key inputs for this formula are
- Draw Amount
- Interest rate (annualised)
- Loan term (in months)
- Commencement Date
- Number of columns to show (e.g. width of the financial model)
- Number of months per columns (allowing us to easily scale to schedule between a monthly, quarterly or annual time series)
- Loan type (e.g. a Bullet Style loan, calculated principal & interest, or a fixed monthly amortisation if already known).
By leveraging this and other custom functions, we can efficiently construct forecast financial models with differing terms and periodicity with reduced risk than we otherwise could with manually rebuilding formulas each time.
Curious about leveraging Lambda functions and create efficiencies in your financial modelling? Let us know!