Using Excel’s New LAMBDA Feature to Create Custom Functions for Real Estate Financial Modeling

In December 2020, Excel rolled out a new featured called LAMBDA as part of the Office Insiders (i.e. beta) program. This feature allows the user to create custom functions, which can greatly reduce duplicative formulas and reduce errors.

Well over the holiday break, I took sometime to learn how to use this feature. And I’m quite excited about how this will make creating template real estate financial models much easier! In the following video tutorial, I first show you how to use the LAMBDA feature and than provide a few examples of how it could be used in real estate financial modeling.

• Click here to download the sample Excel file used in the video tutorial (Important Note: You MUST have the beta version of Excel 365 to use the LAMBDA feature in this file)

What is the LAMBDA Feature?

LAMBDA is a feature included in the December 2020 beta version of Microsoft Excel 365. The feature allows the user to create custom functions without VBA code.

Previous to this release, if a user wanted to create a custom function. They would have to code that function in VBA. And then in order to share it with others, they would either have to include it in a macro-enabled workbook, share the VBA code, and build a custom add-in that others would install.

But the LAMBDA feature makes it possible to create custom functions without code and for easier sharing.

How Does the LAMBDA Feature Work?

Using the LAMBDA to create custom functions is quite easy. It involves four steps, and then the custom function is ready to deploy and use. In the video below, I demonstrate these steps. The steps are:

1. Create the LAMBDA.

To create a LAMBDA, you first write first a LAMBDA statement in any cell of your workbook. The statement includes parameters and the calculation. The parameters are the values that will go into your function. The calculation is how the parameters/values go together.

So for instance, if you were to create a custom function that would add two values together. You would write =LAMBDA(x, y, x+y), where x and y are the parameters, and x+y is the calculation.

2. Test the LAMBDA

Once you’ve written the LAMBDA statement, it’s important to test it. The x and y from our example above haven’t been assigned values yet, and so you can’t know whether the calculation was written correctly.

To test the LAMBDA, you simply append the LAMBDA statement with values wrapped in parens. So for instance, using the example above, to test the =LAMBDA(x, y, x+y) statement you wrote above, you would write: =LAMBDA(x, y, x+y)(1,2). If the result is 3 (i.e. 1+2), you know you’ve written the statement correctly.

3. Create Custom Name for the LAMBDA

Once you have a LAMBDA statement and have tested it, you then need to name the custom function. In the current version of this feature, you use Excel’s Name Manager to name your LAMBDA.

Copy the LAMBDA statement (don’t include the test portion), and go to Formula>Name Manager.

Once in Name Manager, click ‘New’. Then enter the name for your custom function in the ‘Name’ box and paste the LAMBDA statement in the ‘Refers to:’ box. In our example above, let’s imagine you call the custom function ‘ADD’.

Then added a description of the parameters that go into the custom function within the ‘Comment’ box. Then click OK.

4. Deploy the LAMBDA

Now that you’ve created a custom function using the LAMBDA feature, it’s time to use the function. In any cell within your workbook, type the equals sign followed by the name of your function.

As you begin to type the custom function name, Excel will show the comment you added when you created the new name. This guides the user as to what parameters must be entered for the custom function.

In the example above, you would type =ADD(1,2) and the cell would return the value of 3. You can also use cell references. So you might type =ADD(A1,B1), which would add A1 and B1 together and return that result.

How LAMBDA Could Be Used in Real Estate Financial Modeling

Now I get to the exciting part for real estate financial modeling professionals. LAMBDA has the potential to make building complex real estate financial model templates much, much easier. The feature allows the modeler to write a complex function once as part of LAMBDA creation, and then use that custom function over and over again. This will speed model creation, allow for more complex functions, greatly reduce errors, and even make auditing workbooks easier.

To understand the power of this feature, one only needs to scan the thousands of formulas found in any one of the real estate financial models shared to A.CRE. What you’ll find is that we use the same general logic statements over and over again. But because we must write those formulas over again each time, it is time consuming and subject to error.

I can imagine building a custom set of real estate financial modeling functions that I will then use in all of my models. So for instance, I might have a custom function for handling s-curve development forecasting, a custom function for creating an annual row in my header, a custom function for modeling lease timing, and so forth.

The options are endless!

In 2021, my plan is to begin building a replacement for my All-in-One model using LAMBDA functions. This will be the next generation of real estate financial models in Excel. Very exciting!

Video Tutorial – Using LAMBDA Feature in Real Estate Financial Modeling

As mentioned, I also created a video tutorial showing you how to use the LAMBDA feature yourself. Again, as of December 2020 you will need the Excel 365 beta version in order to have access to this feature. Learn how to get access to the beta version of Excel.