Garreth Handley:
This is Excel Tips, a podcast for accounting and finance professionals, brought to you by CPA Australia.
Neale Blackwood:
Welcome to the Excel Tips podcast. My name is Neale Blackwood. And in this episode, I wanted to discuss custom functions. In the November 2025 article, I take you through building a custom function that creates a six-column loan schedule. The function has four inputs, and the output is flexible depending on the term of the loan. The article goes into a lot of detail, and it goes through seven steps to build what I've called a mega formula, which creates the loan schedule.
Then I take you through the steps to convert this huge formula into a custom function, which anyone can use quite easily. So, check out the article and the companion video. There's also a companion file, and that has all of the various steps in there as well as the custom function, which you can use. There's also a bonus custom function, which I'll talk about at the end of the podcast.
Custom functions use the LAMBDA function. Now, LAMBDA allows you to set up arguments, which are the input areas for your functions, and they also use range names. So you have a range name, and that range name uses the LAMBDA function in the refers to box. And that is the basis for a custom function. There's also a special testing syntax, which I'll discuss a little bit later.
But basically, you create this range name, and that range name then becomes the custom function that you can use throughout the file. Now, I do recommend using a prefix for all your custom functions. I use the lowercase fn. The reason is, if you type that in in lowercase, that will list all of the custom functions that you have. Also, it differentiates it from Excel's built-in functions. Excel has released lots of functions recently, so having this lowercase fn in front of my functions, I know it's not a Excel function, it's a custom function.
Now, the mega formula that was created uses the LET function. Now, LET is a little bit like LAMBDA. So the LET function allows you to set up variables at the top of your formula and then do calculations. And then the last argument of the LET function is what you are outputting. Just to be totally honest here, when you're using the LET function and you're setting up a variable, it's not a variable in the sense of programming.
When you're doing programming and set up a variable, that variable value could change. In the LET function, it's more like what we call a constant in programming. So, constants don't change, as their names suggest. And that's pretty much what happens in the LET function. You set up a name, you give it a value, and that never changes in the formula. If you want to change it, you need to basically use a separate variable or constant to capture that. So even though we use the term variable, it's not variable in the same sense as programming.
When you're using the LET function, you typically use one line in the formula bar per variable that you're creating. And to put a line break in the formula bar, you use the Alt and Enter. So, hold the Alt key and press Enter, and that puts a line break within the formula bar. The formula bar does get quite high. There's also a keyboard shortcut, Ctrl+Shift+U, and that allows you to open or close the formula bar. So, the formula bar does get quite high when you are building complex LET functions.
Now, once you've built the LET function that does all the work, that's the mega formula, you can convert it into a custom function via the LAMBDA function. Now, the thing with LAMBDA is that it has a testing syntax. So, to do the testing, you have an extra set of brackets or parentheses on the end of the LAMBDA. And what's between those parentheses are passed to the arguments in the LAMBDA. And then, typically, what happens then is those arguments are then passed into the LET function, and then the output is generated as per normal.
So, custom functions are really powerful. They can shorten and simplify a long or complex calculation. So in the companion video, there was a very long formula that was converted into a very short function, which then makes the function easy to use. You can also document it. So within the naming dialogue, there is a comment section. So you can put documentation in there that helps yourself and the user understand how to use the custom function.
And once you've created it, it's a centralised calculation. So if you did need to change it, you only need to change the calculation in one spot. So that's worthwhile for maintenance and things like that. It's also reusable. So once it's created, you can use the custom function in other files. It's actually very easy to copy between the files. Now, some people do argue that custom functions are a little bit like a black box in that you have inputs going in and an output coming out.
But in reality, you can actually open up the black box and go and look at the range name and see what the formula is and analyse that formula. So you can actually audit the formula. And it's also testable, so you can go and test it as well.
Now, you can use specialised custom functions. For example, financial models, they might have things like depreciation schedules, so things like that where you might want to create a custom function to handle a specialised calculation.
So, custom functions are great for building schedules and reports. There's two functions that are really useful when you are creating schedules. So, the first function that's really useful when you're building schedules is the HSTACK function. H stands for horizontal. So, what it does, it allows you to combine columns to create a two-dimensional range. Now, the VSTACK function, which is the other useful function, stands for vertical stack.
That allows you to combine rows to create a two-dimensional range. So, in reality, what you tend to do is you use the HSTACK to create all the columns, and then you might use the VSTACK to actually add headings or totals to it. When you're creating headings within a custom function, because when you're creating a custom function, everything has to be self-contained, and so you'll typically create the headings within the custom function, and you'll use something called array syntax, which is pretty much curly brackets. So it's the braces with all of the texts within quotation marks, and then you just separate them with commas, and you can create basically a row of headings.
Now, there is a bonus custom function in the companion file, and it includes the totals. So, the basic custom function that we create in the article just creates the schedule. It doesn't provide any totals. But there is a bonus custom function that does include those totals, and you can check out all of the code in that companion file.
So, custom functions, definitely worth having a look at, especially if you have long or complex formulas that you want to simplify and make easy to use. And that's for you and other people. Hope you found that interesting. Thanks for listening.
Jackie Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show, INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.
