Excel LAMBDA and Dynamic Arrays: Benefits and Limits for Financial Models with Diarmuid Early
In this episode of Financial Modeler's Corner: The ModSquad, Ian Schnoor, Paul Barnhurst, and Giles Male welcome Diarmuid Early to discuss how modern Excel features are changing financial modeling. They explore the benefits of dynamic arrays, LET and LAMBDA functions, and how these tools can make financial models more flexible, consistent, and efficient.
Diarmuid Early is the Founder of Early Days Consulting and an expert in Excel and financial modeling. He is an accomplished competitive Excel esports participant and an active contributor to the Excel community. He shares practical approaches to using dynamic arrays, LET, and LAMBDA functions to build more flexible, efficient, and reliable financial models.
Expect to Learn
How dynamic arrays improve flexibility and consistency in financial models.
How LET and LAMBDA functions simplify complex calculations.
Why Power Query is useful for connecting Excel workbooks.
How custom functions can improve model accuracy and auditability.
When modern Excel features create compatibility and modeling challenges.
Here are a few quotes from the episode:
“Lambdas can do crazy things, but you don't need them to do crazy things to be useful.” - Diarmuid Early
“For the guts of 40 years, one Excel formula returned one result, and now one Excel formula can return many results.” - Diarmuid Early
Diarmuid explains how dynamic arrays and LAMBDA functions can reduce repetitive work and help financial modelers build more adaptable models. He also highlights their limitations, particularly when dealing with circular dependencies, complex calculations, and older Excel versions.
Follow Ian:
LinkedIn - https://www.linkedin.com/in/ianschnoor/
Follow Giles:
LinkedIn - https://www.linkedin.com/in/giles-male-30643b15/
Follow Diarmuid:
LinkedIn - https://www.linkedin.com/in/diarmuidearly/
Disclosure: Portions of this episode (such as the introduction or promotional segments) use AI-generated voice narration produced under human editorial review.
In Today's Episode:
[00:00] - Introduction
[10:44] - Modern Excel & Financial Modeling
[12:41] - Understanding Dynamic Arrays
[19:15] - Understanding LAMBDA Functions
[24:35] - Practical LAMBDA Applications
[33:16] - Benefits of Reusable Functions
[34:49] - LAMBDA Limitations & Compatibility
[38:48] - Making LAMBDA Models Easier to Audit
[43:10] - When Small Model Changes Become Big Problem
[46:21] - Resources for Learning LAMBDA
Full Show Transcript
Intro (00:05):
The ModSquad. We are the ModSquad. The ModSquad features Ian Schnoor, Executive Director of Financial Modeling Institute, Giles Male, humble MVP and co-founder of Full Stack Modeler, and Paul Barnhurst, the FP&A guy.
Co-Host 1: Ian Schnoor (00:30):
Four of 30, and I'm probably the only one who's crazy enough to attempt all 30, but we are; I bet you're not.
Co-Host 1: Ian Schnoor (00:39):
You bet I'm not. I'm going to agree with Dim on that one with Ramirez. There's probably one other
Co-Host 1: Ian Schnoor (00:44):
Person. Maybe there'll be others joining me. Well, I hope so. I'm going to need some moral support, but we're off to a great, great start. I'm Ian Schnoor. I'm very excited about this session. We've had about a thousand people live in each of the first few sessions. We've had 11,000 people sign up for this conference and we're excited to be here. So here's the plan. I'm going to spend the first three or four minutes just introducing where we're at and what's going on. People are in the chat and as people are coming into the room, you're welcome to indicate where you're here from. We are literally having people all over the world in this session. It's really wonderful to see. I'm Ian Schnoor. I am the executive director of the Financial Modeling Institute, the world's credentialing body in financial modeling. Very, very excited to be here.
(01:32):
My entire career has been wrapped up in financial modeling. If this is the first session you're attending in our conference, the recordings are going to be available for another 24 hours, and I encourage you to check out the recordings. We've had some brilliant sessions so far, an opening panel, a session with Alistair Matchett. We just finished a great session on sensitivities and scenarios with Patrick Tague and France. Well worth a watch. And before we get into today, the guests of this session, just very quickly, a couple of house rules. So we've got hundreds and hundreds of people in the room. We're keeping the chat open, so we'll ask you to be respectful. The chats have been great so far. They've been super in the first few. We're going to ask you not to include any links in the chat just in case they accidentally take people to malware or a site with viruses.
(02:20):
So we'll be watching that. But chatting is great and we're going to keep this session a little bit interactive. And I'm going to allow a couple of my colleagues to introduce our main speaker, Diarmuid Early, who I imagine many of you have known or heard of. He's a really great contributor to the Excel and modeling community, brilliant Excel user, world champion. You were teaching the CEO of Microsoft some things recently, weren't you? And that was wonderful to see. But what I want to do is introduce a couple of my friends and colleagues, Paul Barnhurst and Giles Male, colleagues of mine. We have a podcast called The ModSquad. And for the past year we have been testing AI tools and other tools building models and I've invited them.
(03:08):
We all know Diarmuid; they're going to be here and interact with Diarmuid. I'll be honest, we were going to have a separate session just for the ModSquad in the conference, but then a company called Microsoft came and said that they wanted that time slot. We were going to ask them to deliver it at three in the morning, but thought that they might not appreciate that. So Microsoft has the slot that we were going to take and ModSquad is joining Diarmuid here today. Speaking of which, the head of Microsoft, if you haven't heard that yet, we'll be presenting later today. Check that out on the calendar along with the head of Google Sheets. It's going to be an exciting day. So I'm going to turn it over to you, Paul, known as the FP&A guy, Barnhurst and Giles. Love your contributions to the modeling community.
(03:55):
Why don't you take it from here, introduce Diarmuid, and we'll get into the session on Modern Excel. All
Host: Paul Barnhurst (04:01):
Right. We'll go ahead and be as quick as we can so we can give Diarmuid as much time as we can. But as I look at the screen, I do have to say, I think I'm seeing the four stages of beard growth. We got in with the five o'clock shadow.
Co-Host 1: Ian Schnoor (04:13):
Correct.
Host: Paul Barnhurst (04:14):
We got Diarmuid in that stage where I've let it go a little bit further. Do I now grow a beard or do I shave it all off? You got Giles saying, "I want a beard, but I don't want it long." And then you got the homeless guy. Absolutely. We can figure out who that is. Beard growth here.
Co-Host 1: Ian Schnoor (04:29):
We could spend an hour. Absolutely. And you're the guy, you know beards better than anyone. Thank you for pointing that out by the way. I appreciate
Host: Paul Barnhurst (04:36):
That. I just noticed that, so I had to point it out. But we're thrilled to have you. Like I said, we've been doing the mod squad for the last year as part of Financial Modeler’s Corner. If you haven't listened to it, check it out on Spotify. FMI is our sponsor. So I'll put that plug in. Like I said, Ian and Giles are my co-host. I'm going to turn it to Giles to introduce himself and then he can introduce Diarmuid and we'll get started on Modern Excel in this session in financial modeling. Really excited for that. So Giles, over to you.
Co-Host 2: Giles Male (05:01):
Thanks so much, Paul. Hello everybody. Please be active in the chat. One of the things about Dim is that he's too clever for his own good. So one of the ways we can put him off is to heckle him immensely in the chat. If you're part of the XLE sports community, get in the chat and my one ask would be share your best Dim story, whatever it might be. But really happy to be here. I'm hoping Paul and I will just at times ask the questions you might be thinking of Dim as he gets really deep into crazy dynamic arrays, Lets and Lambdas. That's it. Dim, it's over to you. We will be here in the background.
Guest: Diarmuid Early (05:38):
Alrighty. Thanks for the intro. I will try to live up to that. I guess a general note from me beforehand, it's like a recurring theme for me that I make a presentation, I think to myself, I'll just cover this thing high level. It ends up being quite dense and detailed. And then I tell myself, okay, the next time I've really got to just be a little more 30,000 feet and then I do it again. So in planning this, I thought, okay, nice high level overview. And now I'm kind of looking at it and thinking I may not have achieved that. So we'll see how it goes. I reserve the right to call
Co-Host 1: Ian Schnoor (06:12):
Off those. That is you on the photo, right? Just to confirm, that is you in the photo?
Host: Paul Barnhurst (06:16):
I said the same thing earlier in backstage. Wow.
Guest: Diarmuid Early (06:19):
That is a much better groomed version of me in the photo. So
Co-Host 1: Ian Schnoor (06:22):
Groomed, so clean? It's possible, huh? Okay.
Guest: Diarmuid Early (06:26):
The reason I don't have the FMI background is because I think my wild hair at the moment just made it too hard for the edge detector to handle. I was really struggling with the shape of my head. Anyway, so yeah, we're going to talk about financial modeling in modern Excel. Three kind of broad topics. We're going to talk about dynamic arrays. We're going to talk about custom functions, also known as Lambdas, and we're going to talk about the limitations because for general context, I am as fond of modern Excel and of dynamic arrays and of Lambdas as pretty much anyone. But my position is considerably more nuanced when it comes to using them in financial modeling. I think I'm actually probably slight... There is a wide range of opinion, but I think I'm probably slightly toward the more conservative, less dynamic arrays and Lambdas end of the scale, but close enough to the middle, I think.
(07:23):
So I'll try to give you a balanced view. You'll definitely get, I know Craig is speaking tomorrow and he is probably close to the pro Lambda, pro dynamic array extreme of the scale.
(07:36):
And he will articulate that end of the scale well. So that's the general layout. But before we get into that, I don't know how many of you are old enough or of the same cultural background to remember Baz Luhrmann's ladies and gentlemen of the class of 99, Wear Sunscreen. This was like a valedictorian or a principal speech to his class that went viral and got turned into a spoken word song. But it's this guy giving all his life advice, but he starts and ends with the most important thing is wear sunscreen. Everything else we're going to talk about is it's subjective, there are opinions, blah, blah, blah, but sunscreen is just science. And the equivalent thing for modern Excel in financial modeling is if you still link your workbooks with external links, just do that with Power Query. There's lots of pros and cons about everything else we're going to talk about, but that part is just the long-term benefits of this have been proved by scientists.
(08:36):
The rest of my advice you may take with a pinch of salt. So let's dive on into dynamic arrays. We're going to start with a little bit of Excel. I put that on the quote slide because it's my favorite quote. No more PowerPoint. There will be more PowerPoints. Sometimes I just say no more PowerPoint right at the start. I'm not going to go quite that extreme. Anyway, so the most basic idea of dynamic arrays you've probably heard of, but it's revolutionary in its way, which is for the guts of 40 years, one Excel formula returned one result and now one Excel formula can return many results. So you can split text into many columns dynamically or you can create a sequence. I will say in the land of financial modeling by far the most common type of sequence you will create is counting from one to N, either down a column or across a row.
(09:25):
But the main idea that one formula can return multiple values and that you can interact with those, you basically put a hash on a cell reference at the top left corner of a spilling array to refer to the whole thing, and then you can interact with those and write formulas very much like anything else. So you want to add one to every single one of these, you just slightly change your reference and then plus one and you have a formula that spills out to the same shape and does the same calculation for all of them. And you can probably immediately see the appeal to a financial modeler that you do it once and you get the same thing everywhere. That consistency is something that we're always drilling people on when you talk about financial modeling best practice. And when you think about what are the first things you'll look for that might be alarmingly wrong when you inherit a model or when you go to review a model is someone who's changed that one value in Q3 of 2028 by just hard coding something.
(10:25):
So dynamic arrays, one of the upsides of them is that they're kind of the opposite of that. All right, hang on. I have to keep moving all our faces around to be able to see different bits of my screen. So one of the most common applications of dynamic arrays that you'll see in a financial model is just being able to have a flexible timeline. So this is a version of something that I used for quite a while as just the quick setup for all my Financial Modeling World Cup cases, just have a start date, a number of years, and a number of months per period. So let's say we want a three-year annual model starting from the 1st of October, there it is. And I don't know, wait, we want a quarterly model. Okay, there that is. Okay, we want it to be 20 years. Okay, there that is.
(11:02):
Obviously you can't see the edge of my screen, but it goes all the way over to here. It grows and shrinks as needed. And if you want to get into the weeds, we won't get into the weeds, but if you wanted to get into the weeds, it then has various other things underneath for the year, the year number, the month number, quarter number, and various different day count conventions and all that jazz. One small technical tip, like I said, I'm not mostly getting into the weeds, but small technical tip is if you're going for this setup, have the workings on one sheet somewhere that gets hidden and then just name them and reference them everywhere else. Because then if you make a copy of this timeline sheet, it doesn't duplicate all those names, you're just referring to the one golden source. But anyway, if you're new to dynamic arrays and that doesn't mean anything to you, don't worry about it.
(11:51):
You'll have time to get into those details. So that's the very basics of what is a dynamic array. So a few strengths, this is definitely not an exhaustive list, but number one, just greater flexibility. So we talk all the time, again, when you think about financial modeling best practice, have an assumption in one place and everything drives off that. Don't enter it in multiple different places, but also don't make it structural. So in other words, if you have an assumption in your model that says model timeline is five years, then it's actually pretty cool to just be able to change that cell to six and your model grows. Now, if you want more than just your timeline to grow, there are nuances, but we'll get into that later. But things like that where you can just say, okay, I'm not just going to change what's happening in existing individual cells, but I can also change the number of cells.
(12:38):
I can add another asset to the schedule without having to go and copy a bunch of things. I can add another period without having to go and copy a bunch of things. It's great for flexibility. I already mentioned the upside of enforced consistency, so there's no having just this one thing look weird across a row. I don't think this is exactly financial modeling or at least exactly traditional valuation three statement, that kind of thing. But one area that won't suffer from any of the nuances later is they're great for reporting. So if you have a bunch of data and you want to see something like your top 10 customers for a department, for a region for a year, you want to aggregate some data, sort it, filter it, take the top X, whatever, it's super for that. Very helpful. And I guess the other point to make is in some cases it can be much more robust.
(13:26):
I hope you don't because there have been better ways for a long time, but if you still use some offset to capture the previous seven years or could be five years or could be 10 years for a depreciation schedule, take is a much more robust way to do that. Because if you take the range from the start of the model up to where you are now and then take the last seven of that, if you're in the early stages where there aren't seven periods, it will just truncate that to however many there are rather than extending a reference outside the range of the model, which can cause all kinds of errors if it hits the edge or if it hits a summary stat at the side or anything like that.
Co-Host 1: Ian Schnoor (14:01):
Em, I wonder if you could just clarify for the group for a second. I mean everyone's watching this, it's amazing. Everything you're talking about, you probably want to cap it is only available in certain versions of Excel, right? We've got people here all over the world, so maybe you want to talk to that.
Guest: Diarmuid Early (14:16):
Yeah, so I'll talk more about that when we get to the nuances and downsides. But yes, short answer is all of this stuff, dynamic arrays, LETs, Lambdas are all only in the relatively recent version. So most commonly in 365, the perpetual version, I think they're also in 2021 and 2024. But in any case, it's very easy to check if you have these, just type equal sequence five into a cell and if it vomits on you, then you don't. Slightly more nuanced if you want to check if it's working with somebody else, but there is a compatibility checker tool that will tell you, okay, this is not compatible with versions before 2021 because you're using dynamic arrays or this is not compatible with versions before 2024 because you're using whatever, I guess group by would be 2024, or this is not compatible with anything other than 365 because you're using Regex or whatever it is.
(15:10):
It'll tell you. But yeah, we'll talk a little bit more about that later. Okay, so let's do a quick overview of custom functions. You may have heard them called Lambdas, although honestly, I try to avoid the word when I present the concept because I feel like it was a little bit of a marketing fail on Microsoft's part to name the function that makes custom functions Lambda. If you know your rich mathematical history, it's a very appropriate name, but if they had just called it function, I think people would've been a lot less intimidated by it and a lot more clear when they read something. Oh, it's the function that takes A and B and does this. Okay, why didn't you just tell me that?
Host: Paul Barnhurst (15:50):
I agree with you. I think people get scared by that term Lambdas a little bit.
Guest: Diarmuid Early (15:55):
100%.
Host: Paul Barnhurst (15:56):
I did.
Guest: Diarmuid Early (15:57):
I think at least part of it is because the exposure that a lot of people get to Lambdas through say LinkedIn is the Excel BI and OMID and other regular challenges. People post quite extreme single cell Lambda-based solutions under those. And if you're used to seeing traditional models and then completely insane single cell taking 57 steps, then it feels like that's a real cliff edge, and it's not. Lambdas can be very simple. And the first thing I'm going to do is go back to Excel and just show you. I have a little kind of collection of Lambdas that I'm fond of using to illustrate the concept. The basic idea is anything that you can do through a series of steps in Excel, you can turn into a custom function to do the same thing. And sometimes that's really simple things. So here's an example. Excel has a text before function and a text after function.
(16:53):
So a whole bunch of people, this one is from Alan Murray, but I saw various other people do similar things, just did a text between function, take the text that comes after the first delimiter and before the second delimiter, and it's handy for if you use the cell function to return where you are, it'll give it to you in this format and the file name is in here between square brackets, so it's quite handy for something like that. But it's the easiest function in the world to write and just edit lambda and you can see it. First time I use robot, it takes a second to wake up. So it's literally just saying my arguments are some text, a starting delimiter, and an ending delimiter, and I just want to take text before the second delimiter, and then within that take the text after the first delimiter.
(17:37):
That's the whole function. And then you just say, these are the three arguments, the cell, the cell, and this cell, and away it goes. If you're not familiar with the syntax, we're not going to teach that right now, but it's pretty simple. This is just saying the function that takes this argument, this argument, this argument, and does these things with them written exactly like a normal Excel formula.
(17:57):
So even really simple things, you're literally just chaining two functions together. In esports, we're constantly trying to split text into letters, and there's a standard way to do that, like mid of this text by a sequence from one to the number of characters in the text, so mid of sequence of the length of the thing. But it's mid-sequence len, okay, that kind of thing. The example I always think of is in the days before dynamic arrays, there was this whole recipe that people sometimes use to reverse text, and it was so long and complicated that Bill Jelen hired a singer, Tommy Tutone, to make a jingle that you could sing to yourself to remember all the different steps. So anything you have like that where it's like, okay, okay, I do this and then this and then this and then this. If you bake it into lambda - Are you going to sing it?
Co-Host 1: Ian Schnoor (18:48):
Can you sing the jingle?
Guest: Diarmuid Early (18:49):
I am not going to attempt to sing
Co-Host 1: Ian Schnoor (18:51):
It. Giles is going to sing it.
Host: Paul Barnhurst (18:53):
Maybe
Guest: Diarmuid Early (18:53):
Look at the
Host: Paul Barnhurst (18:53):
Sunscreen
Guest: Diarmuid Early (18:54):
Then
Host: Paul Barnhurst (18:54):
At the end.
Guest: Diarmuid Early (18:55):
Go search on YouTube for Bill Jelen, Tommy Tutone. Y'all find
Co-Host 1: Ian Schnoor (18:57):
It. Jals, will you sing it please? Everyone wants to hear you sing
Co-Host 2: Giles Male (19:00):
It. I think everyone's had enough of singing related to me. Dim, can I call out one thing? So the formula you had on the screen that spread over a few lines then, just to call out for anyone that's not a programmer, you probably all agree, the moment you see that, bearing in mind a lot of us try to keep our formulas nice and small, the moment you start to see, oh my God, this is going over multiple lines, that's bad. And suddenly the world of lets and lambdas totally challenges that belief that you have.
Guest: Diarmuid Early (19:30):
Yep, yep. I mean, it's interesting. I'm still a fan of people talk about the rule of thumb, don't have a formula longer than your thumb. I write 20-line formulas that I feel in spirit are actually still very much in that vein, which is each step is something clear and concise, just like in a traditional model, every cell would be something clear and concise. The functional part of this, even though there's a bunch of other stuff around it, this is the entire calculation and it's small. But yes, the intimidation factor around lambdas is very real and I genuinely believe is overblown. I do crazy things with lambdas, but you don't have to do crazy things with lambdas to get a benefit out of them.
Host: Paul Barnhurst (20:10):
I have to share one comment in the chat that kind of summed up what you said. It's like when you look a recipe up and see that it has eight ingredients, so you just have toast.
Co-Host 2: Giles Male (20:21):
Also note Peter Bartholomews in the chat. So we've got the king arguer of this is the simple way to do it. Hello, Peter.
Host: Paul Barnhurst (20:30):
All right, back to you. Sorry, I just couldn't resist that one.
Guest: Diarmuid Early (20:32):
No worries. No worries. So I'll just quickly show you a few other examples. Again, it's more just to give you a sense of these are some of the things that people do with lambdas. This is 10 price made. Again, this is a pretty standard applicable to financial modeling. You've got a few different business units, a few different periods, or a few different business units, a few different regions. You want to get all the different combinations of them. I remember, okay, there's seven of this and there's 12 of this, so I'm going to copy this 12 times and this seven times, and then I'll sort one of them. But now in the land of dynamic arrays and lambdas, it's much simpler to just write a lambda to do it, and then I'm going to list pairs and just point at these two things, and then it gives me red and every month, orange in every month, yellow and every month, and so on down.
(21:14):
This one is a very fun one, but not particularly financial modeling, and I want to keep moving, so I'll skip it for now. This is just a single formula version that's similar to that timeline I showed you before. Let me do one other verbally, and then I'll show you the last crazy one. The one other verbally is just a common example of a complex calculation in a financial model would be, say, a tax schedule with progressive tax bans where it's like, whatever, 10% rate up to X and then 12% rate and 15% and so on. I mean, you can obviously lay that out as a bunch of different... Okay, here's how much falls within this band and this band and this band, and then here's the rate and here's the multiple. But if you have to do that sort of two-dimensional calculation every time that you're calculating tax and you're calculating tax for many different units in many different periods and so on, it's going to get into a real sprawl if you lay every one of those out.
(22:07):
And so having a Lambda that does that calculation and packs it up into that thing is quite helpful. But then there's also the go completely mad version of things. So I mentioned about making assumptions not be too structural. So here's a model with four different loans with different principal amounts, APRs, terms, and start dates. And here is a single Lambda that produces all of these schedules for the issuance, the balance, the interest, the payment, closing balance, whatever it is, out to 300-odd columns because one of these loans lasts for 30 years. And the cool thing about it is all my assumptions are just this table. So if I say I also want to buy a ring for $15,000, give it a second because it's not happy while there's partially filled in, but it'll get there, and let's say it costs me 10% and I don't know, I'm going to pay it off over five years and it starts in January, then you need to copy the formatting down, although look at Mark Proctor's recent video if you want a neat way to get around that, but it's already added the whole thing.
(23:10):
There's literally no copying and pasting to add in a new schedule and you can point -
Co-Host 1: Ian Schnoor (23:15):
That's one formula. That's one formula.
Guest: Diarmuid Early (23:18):
It's technically four formulas. It's one formula doing all the calculations, one formula for the timeline, one formula for the summaries, which is just summing up the relevant things by row and one formula for the labels. But everything, you add another row here and everything follows from that, and you can have formulas that then summarize, okay, so what's your total payments across all loans, your total interest across all loans? So you really can make a model quite dynamic, and you can check this out. This is one by Craig Hatmaker, who, like I said, he will make you believe in how powerful these things can be. I'm not even going to open this formula up here because it is best viewed in the advanced formula environment and it's best viewed on GitHub where he has it. It's a great example of it is an enormous formula to make all this happen, but it's also very nicely laid out, very well presented.
(24:09):
There is help. So if I call this function without putting in the correct number of arguments, it gives me a bunch of inline help to tell me what's going wrong and what an example would look like and so on, and where I can find more information. You can learn a lot from looking at Craig's Lambdas about what best in class presentation looks like here. The one other point I want to make on Lambdas is, so I talked about the ability to pack away calculations. One of the things I really like about Lambdas is that you can get the best of both worlds. And by that I mean if you lay everything out in detail, then it's easier to manually review and say, okay, here's a calculation, here's a calculation, here's a calculation, and I can step through it, but it takes up way more space.
(24:53):
And if you need to have a 2D blob for every period across a model, then it's like how do you lay those out without just the model exploding? And so here's an example. This is based on an esports case. So the case was you're given various different collections of flight schedules like this, so from this three-character airport code to this three-character airport code, semicolon separator, and then a bunch, I think there were 50 in each one. And you had two lookup tables, one that said, okay, for this code, these are the airports, and then one that said, for any departure and arrival city, this is the distance. And you had to figure out what was the total distance of flights that had any connection in Canada, either departed from or arrived in Canada. And so there's a Canada column here to mark it.
(25:35):
And so I wrote a Lambda for this and it just says Canada just calc of this schedule, and there it is. The answer is 117,000, 107. You feel confident in that? Probably not hugely, but that's why it has this debug mode, and if you turn on debug mode, it spills out all the interim calculations. So it says, okay, here are the... Oh, 70 routes actually. Here are the 70 routes. Here are the departure and arrival cities. So you can say, okay, easy. Let's go look wherever that is. Here is Buenos Aires. Okay, that's working. RIX is Riga. Yep, that's working. Now let's go look up Buenos Aires to RIGA is 12,750. Yep, that's working. Is either of these Canadian? Nope. Okay. And you can go filter like, okay, Canadian, yeah, I see Toronto, I see Montreal, Riga, Toronto, Montreal, Montreal. Okay, that looks good. Let's filter every instance of Montreal or Toronto.
(26:25):
Yes, they're all marked as Canadian. Okay, I feel kind of good about that now. I'll filter for this and sum it up. Okay, 117,000, 107, that matches. So now I've done my diligence and now I can just pack it away again. And so if I have that in every cell in a bunch of different places and I'm reviewing the model and one of them look, hang on, that one looks a little bit weird, let's see what's going on there. So I just copy that formula, put it somewhere there's more space, put it in debug mode, and spill everything out. Here, I just have it linked to a cell just to make it visually easier, but you can make the debug an optional argument and just have that, and then you can say, okay, hang on, I need to review this one. Let's put it in debug mode.
(27:01):
Okay, that's good. Now put it back. And so it really does give you that kind of best of both worlds. So dynamic arrays are great. Lambdas are great.
Host: Paul Barnhurst (27:09):
How did you make the debug? Was that just part of your Lambda that you created?
Guest: Diarmuid Early (27:13):
Yeah, exactly.
Host: Paul Barnhurst (27:14):
Someone was asking, so just help me
Guest: Diarmuid Early (27:15):
Very
Host: Paul Barnhurst (27:15):
Clear on that.
Co-Host 1: Ian Schnoor (27:17):
Yeah, thanks for asking that, Paul. That's great. So that's not a default part of Lambdas then, right Diarmuid? You have to add that.
Guest: Diarmuid Early (27:24):
Exactly, exactly. So let's just...
Host: Paul Barnhurst (27:26):
So this is what we call PhD level. Craig will take us to advanced PhD level.
Guest: Diarmuid Early (27:33):
Oh, postdoc for sure. I don't know why that is misbehaving, but anyway, kind of just talk.
Co-Host 1: Ian Schnoor (27:39):
I mean, just as Diarmuid's doing this, you guys can comment too, but you're seeing what people are seeing is the crazy realm of possibilities, right, Giles? I mean, you use these a lot in your esports competitions as well. It gets pretty well.
Co-Host 2: Giles Male (27:51):
Well, I use some of Dim's -
Host: Paul Barnhurst (27:52):
Craig is in the house. He just asked who we're talking about.
Co-Host 2: Giles Male (27:57):
Dim is kind of the... Well, there's a few. I think in esports, well, Dim, you would probably say Beau Ribdon's maybe one of the only people that outdoes you at times with his approach, but the top tier of esports players like Dim do stuff that I think most people just struggle to believe.
Host: Paul Barnhurst (28:15):
If you're thinking I could never write this, you're not alone. Just know that.
Guest: Diarmuid Early (28:21):
Yeah. I mean, again, Lambdas can do crazy things, but you don't need them to do crazy things to be useful. They can do very useful things without being crazy. That is the thing I would emphasize. But to answer your question
Host: Paul Barnhurst (28:33):
And build up.
Guest: Diarmuid Early (28:34):
There's a let here. So we define each step as we go along. First, we define the list of routes. Then we define the departure city by looking up the start of the route. We define the arrival city by looking up the end of the route. We figure out if it's Canadian. We figure out the distance by doing a sum ifs on this table. And then we have a variable called check where we stack the names of those things with the outputs of each of those interim steps, and then the final step, sorry, figure out the output. And then we say if debug mode, then return check, otherwise return output. So conceptually, it's a pretty simple one to write. Anyway, I want to have time for it. What
Co-Host 1: Ian Schnoor (29:07):
Do you say to people with them though that say, "I get fired if I write a formula like that. I mean, this isn't my job."
Guest: Diarmuid Early (29:14):
Let's get into it just briefly to recap Lambda strengths, and then I do want to get into that because that's important. Some people would get fired for writing a formula like that. Some people would get fired for silly reasons, and some people should consider going to a job that would value their skills more appropriately, but it's a relevant consideration. So yeah, Lambda's easier and faster to use versus writing the six, eight, two, three, whatever number of steps of the process yourself, consistency and error reduction. So if you do some weird tax calculation in a hundred different places in your model, if they all just use the same Lambda, then you know they're all working the same way. You can share logic across files and teams. And this is actually, in a way, I think this is conceptually the biggest one for financial modelers. We go back to the point we talked about earlier, one of the core tenets of Best practice financial modeling is you put your assumptions in one place so that if you change them, they flow through everywhere else.
(30:07):
But if you have a complex tax calculation, say, that occurs in a hundred different places in your model and you want to change that logic, it's not in just one place. You have to go and change a hundred places and you have to remember where each of those is. And if you're saying, "Well, don't worry, I know my model. I'm confident that I will change it in every place," that's what people say about hard coding the tax rate into their formulas as well. We'll talk about the downsides, but there is a real upside to being able to say there is a complex piece of logic and because of this, I know that it is executed exactly the same way every place in the model, or if I share it with my team, every model that my whole team builds. That's a real value to Lambdas.
Host: Paul Barnhurst (30:46):
Absolutely.
Guest: Diarmuid Early (30:46):
But now let's talk about the limitations. So the first category is what I think of as limitations to manage. And mostly what I mean by manage is communicate. Think about your client, think about what the model is being used for, and communicate clearly and well. So what are the issues? First, backward compatibility, Ian already brought it up earlier, so these do not work on old versions of Excel. The more widely you expect your model to be disseminated and the less sure you are of exactly who it's going to end up with, the more of a challenge that is and the more you have to think that is a strike against using these in the model. In other words, if the model is only going to be shared within your team and everyone in your team is on 365, no problem. If the model is also going to be shared with people in other divisions of your company, but you know what version of Excel they're on, okay.
(31:39):
If your model is going to be shared with external investors, that's another step. If your model is going to be shared with external investors and they're going to pass it on to other people to audit and review and so on, the further it's going to go, the more important it is that it works everywhere. And honestly, one that I've come across in my work is if you're going to send it to your client and they're going to open it in Google Sheets, then you have to be careful. Google Sheets has dynamic arrays and Google Sheets has Lambdas, but there are subtleties of the implementation. And that's fine. If your client is going to open in Google Sheets, you open in Google Sheets first and check that things still work. And if they don't, you need to adjust. But then the second part is this is new/different/scary, what I consider the human version of the backward compatibility challenge.
(32:22):
And Ian said there are people whose bosses would fire them for doing that because they're like, what on earth is this? On the other hand, I have met people who got in trouble with their bosses for using the sum formula. "What do you mean? You didn't actually check these calculations? You didn't take your calculator and sum up these numbers? "So there's a bit of a push and pull. You can push someone's envelope by communicating clearly like," Look, I'm using Lambdas in this model. That's because it gives me these control benefits. I'm using dynamic arrays in this model. That's because when we want to extend the model out into another year, when we want to roll the forecast forward, it'll make it much simpler to do that. Here are the benefits. "Communicate and your boss who doesn't get these things might say," Okay, that's kind of weird, but I see the trade-offs, it's worth it.
(33:07):
"And if they say it's not worth it, don't do it. And if your client says that they cannot or will not or don't want to read a model with the Lambdas in it, you can preach all you want, but you need to send them something that they will use and that they will work with. And that's
Host: Paul Barnhurst (33:20):
Important. I think you summed it up well there. Your boss, your client, your job is going to dictate to a certain extent what you can do. And they should, they're paying you. So I think that's important to keep in mind. Just because it can be done doesn't mean it should be done. You have to be astute enough to balance that line.
Guest: Diarmuid Early (33:41):
Exactly, exactly. And look, that doesn't mean that if somebody tells you, I want it done this way, you must do it that way. It's quite common for me in my work that somebody comes to me and says, I need X. The most common thing being, I need a VBA macro to dot, dot, dot. Okay, what do you actually need to happen? This. Okay, that doesn't need a VBA macro, you need this formula or you need Power Query or whatever. There is a role for people who know more about this stuff to educate other people, but you have to balance it. At some point they will say, whoa, whoa, too far. And like I said, that may mean, I know people who work in Excel and will not work with clients that don't have 365 because it's just not worth the backward compatibility hassle. And if you are in a position of strength and can do that, do it.
(34:30):
And it may be that if you want to be at the cutting edge of this stuff and your boss is someone who feels a little uncomfortable that you use the sum formula without actually taking out your calculator and checking it, then maybe you're not working in the right place, but you got to find that balance. The thing that sort of spooks people is this sense of intransparency, a lack of auditability, like what the heck is Canada Discalc? I don't have that function. What is going on here? And again, it's partly an education thing. I would say in many ways a Lambda makes a model easier to audit because you have that confidence that this is happening in the exact same way everywhere. And maybe that means you have a kind of Lambda explainer sheet that says, here's a well-presented thing about how my Lambdas work.
(35:16):
Again, look at Craig Hatmaker's GitHub, look at Eric Ohm's GitHub. They've got good usage examples, things that make it clear without you having to go through all the plumbing, this is what this function does. This is an example of where it would be used. This is an example of the kind of logic it applies. If you can make somebody feel comfortable with that, nobody, somebody presumably does, but nobody I know actually knows the code behind the sum function, but we have a sense of how it works and there's a degree of comfort there. So you can build that as well. So again, that section I called limitations to manage, there is another section which is the more strictly technical challenge. And this is the part, this section I think we should absolutely push the envelope, proselytize, tell people about the benefits of the future, but there is an aspect of the future that is just technically not good with dynamic arrays at the moment, and that is extra circularities.
(36:15):
So this is a typical kind of corkscrew calculation that you would have many times in every supporting schedule in most financial models. The dependency flow looks like this. You have a closing balance that flows into the opening balance, that flows into the next period's closing, that flows into the next period's opening. We call it a corkscrew, turns through the model. If this entire opening row and this entire closing row are each one single dynamic array, then to Excel, these dependency arrows are now circular because in your head you're saying, okay, but yeah, yeah, I'm pointing to that one, but this is pointing to the previous period. It's like, no, no, no, this is one block, this is one block. So this, if you draw the actual dependency arrows in Excel, this is what it looks like with that same thing with just a sort of naive dynamic array calculation.
(37:04):
And that is very hard to get around. So the workaround is you write in a different style. So you write more with scan and reduce functions that are designed to iterate or even with recursion, which is more flexible, but also considerably more terrifying if you think that lambdas are scary. And so you can write one scan that calculates this and one scan that calculates this and work through it that way. The challenge is that even at its best, that approach can make modularity difficult because a corkscrew can just turn through one supporting schedule like this, but it can also turn through a whole series of schedules. So if let's say your interest on your cash balance feeds into your income, which feeds into, which feeds into, which feeds into, which feeds into, even without a kind of circular debt calculation, which is a different thing I've got lots of opinions on, but five minutes left, I'm not going to get into that.
(37:59):
But even with non-circular calculations, like say if your interest is only calculated on your opening balance, that corkscrew turns through your entire model. And so you can get around it by having a dynamic array that calculates a period at a time, but it'll have to be one dynamic array calculating everything that's captured in that corkscrew. And that means that you can very easily end up with a situation where the corkscrew turns through your entire model. And so your entire model needs to run through one giant dynamic array, and that is scary times. So then you do get into a real trade-off around complexity because if you have a simple Lambda that says, "Calculate the tax rate, here's the verbose version of it, here's an explanation of the logic," someone can get behind that. If you have one Lambda that calculates the entire model and the only way to kick the tires on that is to go review the code, I would say to Lambda fans, ask yourself how you would feel if someone said, "Yeah, I just coded the model in Python because it's more efficient, but the code is all there, it's totally auditable, but I don't want to learn a new programming language to review your model." Well, neither do non-Lambda fans, so that's the thing to be aware of.
(39:07):
And just the last point from my slide and then we might have a minute or two for questions is watch out for the small change. So if you get into this, you'll get a sense that sometimes there are situations where you can use a dynamic array, like say for a single loan schedule that doesn't have a lot of outside dependencies, and that's great. And that can really help you build a model more efficiently, but you can get into a situation where a small change in the model builds one of those huge corkscrews that is no big deal for a traditional model, but is a huge deal for a dynamic array model. So for example, if somebody says, "Hey, we've got these upside scenarios where we're beating our revenue targets." Let's say we reinvest 30% of the revenue above target into CapEx, and that helps drive revenue in future years.
(39:56):
Suddenly there was just a simple model that had relatively independent pieces that could be built with dynamic array modules, but now there's this corkscrew that goes through the entire model and you have to either make it totally non-dynamic and rebuild it that way, or you have to make the enormous Lambda that builds the entire model in either one of those for something that feels like it should be a small change is a big challenge.
Co-Host 1: Ian Schnoor (40:22):
Diarmuid, this is brilliant and I think I'm going to let Giles and Paul wrap it up with some couple things of their thoughts. Your slides will be available just because we're trying to keep on schedule, but first of all, no one should feel like they need to be a Lambda expert at the end of this. The harsh reality is, as you said, these tools are there. These tools exist in Excel, and half the reason for becoming familiar with them, I think you'd agree, is because you're going to encounter these things at some point, and you should be familiar when you see something. And by the way, I'm going to spill the beans here. I hope I don't get into too much trouble. I think you all probably know that the head of Excel and some of his colleagues, Brian Jones, are going to be presenting today.
(41:03):
They're going to be introducing, they're going to be making a big announcement and they're going to be introducing some new features in Excel. So just what we need, right Diarmuid, more Excel. It's
Guest: Diarmuid Early (41:13):
Got to be so good.
Co-Host 1: Ian Schnoor (41:14):
It is good. But there's some announcements coming.
Host: Paul Barnhurst (41:17):
It's exciting. Trust these three
Co-Host 1: Ian Schnoor (41:19):
MVPs. Why don't you take the last minute or so, Giles and Paul, and share your thoughts on this and what people should take away, and then we'll move on to the next session for Carolina. Paul?
Host: Paul Barnhurst (41:31):
Yeah, I mean, I will say Lambdas are great. I've played with them a little bit. I really don't use them much. I don't do as much modeling as I used to. They scared me, Let scared me for the longest time, but they have their benefits. If you dig into them, they can help you. Do you need to go extreme? No. Can you? Yes. And so just remember, there's balance in all these things. Do what works for you. If you're doing a good job, the traditional way is working, your boss is happy, that's okay. You don't have to do it this way. So I think just that reminder, it's great to understand them, but use balance for what works for you. Love
Co-Host 1: Ian Schnoor (42:08):
That. Love that, Giles.
Co-Host 2: Giles Male (42:09):
Yeah, very quickly, two video suggestions. My one has just been mentioned in the chat. If you Google what is a Lambda and my name, you'll get an interesting rap video. If you want a more serious guide into Lambdas, find Diarmuid Early's YouTube channel. Dim puts loads of videos out, mainly around esports cases and it goes deep, but it's a great way to learn this stuff.
Co-Host 1: Ian Schnoor (42:32):
Yeah, thank you for that. I mean, that's the thing. There are so many great resources and so many amazing people in Excel, the spreadsheet and the modeling community that have great resources out there. And I encourage you to find things, be in touch with people, and you will elevate your skills because everyone in the previous sessions has said skills still matter. We're in a world of AI, but skills very, very much still matters and so we'll need it. So I want to thank all of you for being here. We've got a great session coming up from Carolina Lago. She's a brilliant presenter and a great financial modeler talking about how to build intelligence with your models, and I'm really looking forward to it. I want to thank all of you for being here. Got a huge number of people here. So we're going to end and leave this session, go back into the lobby, and Carolina's session starts in four minutes for session five here in today's conference.
(43:24):
So thank you all, Diarmuid, brilliant as always. Giles and Paul, thank you for joining. And I look forward to seeing all of you again in future sessions. Okay? Take care everybody. Have a great day. We'll see you shortly.