Excel has newly released the LAMBDA feature, currently only available for Excel ‘Insiders’ on Office 365. This new feature allows users to write their own custom Excel functions using normal Excel formula language. These new functions can then be referenced and re-used throughout the entire workbook, the same way as any other built-in Excel function. The LAMBDA feature is relatively simple to use. First, open the workbook’s Name Manager and fill out the “Name” field which will represent the function’s callable name. Then, in the “Refers To” field, input the LAMBDA feature using the syntax “=LAMBDA(parameterA, parameterB, parameterY, parameterX, calculation)”. The parameters will be used as placeholders for the values or cell references that will be passed through the function and used in the calculation component, once called in the worksheet. After setup in the Name Manager, the user can then call this function when required using the newly defined name and pass values through that align with the formula’s parameters.
An instance where the LAMBDA feature can be useful is if you may be working with various data sources with inconsistent terminology, which will require multiple corrections. Using LAMBDA, you can simplify the process by creating a function similar to a FORNEXT Loop in VBA. Among the LAMBDA feature’s many benefits, being able to create your own custom functions without the use of VBA can help with both simplifying the readability of long and complex formulas, as well as being a significant time-saver, as users only need to type the long form of their formula once. Additionally, changing the functions underlying formula in the Name Manager will change how it affects all calculations throughout the workbook, so model auditing is simplified as well. Other beneficial elements of the LAMBDA feature is its ability to work with multiple data types (more than just numbers and strings), the ability to use arrays as parameters, and allow for recursion.
However, as a new function, financial modelers must take care in the communication of its use and have a strong understanding of the functionality. If this is a feature built into models that are generated for external users or clients, then only those who are experienced with Excel, or have seen this function previously, will be comfortable with tracing calculations and understanding the model. Additionally, since this is a feature currently only available for Excel ‘Insiders’ and not fully released yet, the end user’s Excel version may not support it just yet.
