background
Welcome to Wall Street Prep! Use code at checkout for 15% off.
Wharton & Wall Street Prep Certificates
Now Enrolling for May 2024 for May 2024
:
Private EquityReal Estate Investing
Buy-Side InvestingFP&A
Wharton & Wall Street Prep Certificates:
Enrollment for May 2024 is Open
Wall Street Prep

Excel LAMBDA Function

Last Updated October 24, 2022

Full Excel Online Training

What is so great about the Excel LAMBDA function?

Microsoft announced the launch of the LAMBDA on December 3, 2020 and it is probably safe to say we’ve never seen this much excitement from the Excel MVP community. And that’s saying a lot because just a few months earlier, Microsoft’s Excel team announced the XLOOKUP, which also blew people’s minds.

Excel’s LAMBDA function makes it possible for regular Excel users to create their own functions, give those functions a name, and use them just like any other Excel function.

This can be useful in a variety of ways, but here’s a simple example: We constantly deal with dates, and need to figure out which quarter they occur in. We’ve always wanted a version of EOMONTH that works for QUARTERS. We normally have to create a long complicated formula to figure it out.

With LAMBDA weI can just create our very own EOMONTH function:

As you can imagine, this ratchets up Excel’s power dramatically.

Excel LAMBDA applications for finance

So we got to thinking… What are some immediate, quick and easy and truly useful applications of Excel’s LAMBDA functions for people who use Excel particularly in the corporate finance, investment banking and private equity?

That’s what this mini course is for. Over the course of the 8 short videos below, we will cover all the basics of using LAMBDAs in Excel and teach you how to build a variety of custom functions you can put to use right away (be sure to download the free excel file containing the LAMBDAs below the videos).  Enjoy!

Before we begin: Get the LAMBDA Worksheet

Use the form below to download the Excel worksheet used in this mini-course:

dl

Download the Excel Workbook for This Course (FREE)

Submitting...

Video 1: Create simple custom functions with LAMBDA

Video 2: Create a =CAGR() function to calculate Compound Annual Growth Rate

Video 3: Calculates a company’s Days Sales Outstanding with a =DSO() function

Video 4: Calculate implied growth rate of an annuity with a =IMPLIEDG() function

Video 5: Create a =EOQUARTER() function to solve the problem we mentioned earlier

Video 6: A =TSM() function to calculate dilutive options using the Treasury Stock Method

Video 7: Bonus function! Use =SHEETNAME() to output the name of the active sheet

This one is courtesy of Mr. Excel

Video 8: Use your LAMBDAs across multiple workbooks and share them with others

 

Recursion with LAMBDA

One feature of LAMBDA that we did not cover is something called recursion – which is a superpower Microsoft gave LAMBDA giving it the ability to loop and self reference.

That will be a topic for a subsequent video.  In the meantime, check out Ms. Excel’s excellent starter video on LAMBDA’s recursion with LAMBDA.

This brings us to the end of our lesson – we hope you enjoyed this course!

Have ideas for useful LAMBDAs?

Share them with the world in the comments below!

Comments
2 Comments
most voted
newest oldest
Inline Feedbacks
View all comments
Bruce Franco
March 10, 2021 4:37 pm

Is it possible to use lambda in a cell formula to compare the previous value of a cell with the new value? I use the Stock Connector Addin and would like to store the max value of a cell that updates every five minutes.

Jeff Schmidt
March 10, 2021 4:57 pm
Reply to  Bruce Franco

Bruce:

We’re not sure if that is quite possible or not but our guess is that would probably not work.

Best,
Jeff

Learn Excel Online

Become an Excel “Power User.” Used to train new hires at top financial institutions around the world!

Learn More

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.