LAMBDA() is one of the most exciting functions to be developed in Excel since the introduction of dynamic arrays. This special function allows a function to be created by a user and then re-used throughout the workbook.
These functions are similar to user-defined functions in VBA but do not require elevated security permissions.
In this example, we will be creating a loan amortization schedule, which is a commonly used financial schedule written in Excel. However; in the example, we will be using in this post, we will be generating the entire schedule dynamically with 1 LAMBDA() function.
This file can be downloaded at the link below.
What is an amortization schedule?
A loan amortization schedule is used to calculate the installments on a loan. The types of loans typically calculated using an amortization schedule include:
- Mortgages
- Car loans
- Student loans
The schedule uses the loan principal, interest rate, duration of the loan and payment frequency as inputs. The outputs for a schedule include (for every period):
- Outstanding balance on the loan at the beginning of the period.
- Loan payment.
- Interest paid in the period.
- Principal paid in the period.
- Outstanding balance on the loan at the end of the period.
The Supporting Functions
In order to build the LAMBDA() function, other functions are required. These functions include:
- Financial Functions
- PMT()
- PPMT()
- IPMT()
- CUMPRINC()
- Conditional Functions
- SWITCH()
- CHOOSE()
- LET() [this is optional, but it makes the LAMBDA() much easier!]
- The MAKEARRAY() LAMBDA() support function
Financial Functions on the Schedule
These formulas will already be set up on the spreadsheet. As a result, there will be more focus on the non-financial formulas.
PMT() – This function calculates the payment on a loan for all periods in the loan. This is the most complex financial function, and is described in further detail on another post.
=PMT(rate, number of total periods, initial loan value)IPMT() – This function calculates the portion of the payment applied to interest in a given period. Unlike the PMT() formula, it needs to know what period in the loan the formula is being applied for.
=IPMT(rate, current period, number of total periods, initial loan value)PPMT() – This function calculates the portion of the payment applied to the principal in a given period. This formula uses the same inputs as the IPMT() formula.
=PPMT(rate, current period, number of total periods, initial loan value)CUMPRINC() – This function calculations the amount of principal that has been paid between 2 periods on a loan. This function takes most of the inputs of the PPMT() formula, but also requires 2 additional arguments:
- 2 periods are required – both a start period and an end period; so the first year would be 1 and 12.
- The payment at start or end of period is required (unlike others where it is implied). Use a zero value for the final argument for payment at the end of period.
=CUMPRINC(rate, number of total periods, initial loan value, start period, end period, start or end of period)
Conditional Functions on the Schedule
The conditional functions are used to apply logic on a column by column basis or to provide numeric values based on a text value.
SWITCH() – This function changes a specified value to another specified value; it is effectively creating a small lookup table within the formula.
This function will be used to switch values like “Quarterly” to value 3, meaning 3 payments per year.
=SWITCH(value to check, condition1, result1, condition2, result2 <up to 256 conditions>, default)In the example below, the formula would return 52 if the value in A1 is “Weeks”, 4 if the value is “Quarters” or 1 if the value is “Years”. Should the value in the cell be none of those, it will return 12; the default would be Months.
=SWITCH(A1,"Weeks",52,"Quarters",4,"Years",1,12)
CHOOSE() – This function picks a value from a list of values based on an index value; this can either be a single number (like 4), or an array of numbers (like SEQUENCE(4)).
Unlike SWITCH(), there is no option for a default. An error will show if the value is greater than all possible options in the SWITCH() function.
In the LAMBDA() function, the CHOOSE() function will be used to insert a different value for each column in the formula.
=CHOOSE(index, value if index is 1, value if index is 2...)In the example below, the value “e” is returned, as it is the 5th value in the index.
=CHOOSE(5,"a","b","c","d","e")
However; the CHOOSE() function can also use a dynamic array function; if the function SEQUENCE(5) is used, which returns the numbers 1 through 5, all values from a through e are returned.
=CHOOSE(SEQUENCE(5),"a","b","c","d","e")
LET() function – Making our Formula Simpler!
The LET() function can be used within a LAMBDA() function to repeat calculations that need to be performed frequently.
This is accomplished by assigning a name to the frequently used calculation or value.
=LET(variable name, variable calculation, variable name2, variable calculation2 ... formula)In the case of our loan amortization schedule, we pass in the number of years that the loan is amortized for, but when we actually perform our calculation, we use payment frequency multiplied by the number of years in all calculations.
As a result, without using LET(), we would see a lot of years X frequency or years/frequency calculations!
In this example, we use LET() to create the number of payments and periodic rate on our loan, so that within the PMT() function, we can refer to the variable name as opposed to having to multiply and divide by the annual periods!
Making a LAMBDA() Array – The MAKEARRAY() Function
Since we need to use a different function for every column in our schedule, we need to use the MAKEARRAY() function to create a container for all of our schedule’s values.
In the function, the number of rows and columns need to be specified, then a LAMBDA() needs to be created using only 2 variables; the reference for rows and the reference for columns.
Within the MAKEARRAY() LAMBDA(), the row and column name can be anything, but it should be best practice to provide simple names such
=MAKEARRAY(# of rows, # of columns, LAMBDA(row variable name, column variable name, lambda function))In the example below, a 10X10 array is created, with rows incrementing from 1 to 10 and columns incrementing from A through J.
=MAKEARRAY(10,10,LAMBDA(row,col,TEXTJOIN("",,CHAR(col+64),row)))
It’s time to make the LAMBDA() function!
There are 5 major steps required in order to successfully create the amortization schedule LAMBDA() formula. While 2 of these steps are optional, they are highly recommended.
- Create the formula in the Advanced Formula Environment (optional)
- Name the required variables
- Create the array for the schedule
- Renaming values using LET() based on payment frequency (optional)
- Adding financial functions to the array
Create the formula in the Advanced Formula Environment
As a result of the amount of code that is required on this formula, using the Advanced Formula Environment is strongly recommended, only if to avoid the risk of missing a bracket or comma somewhere in the formula!
Adding the Advanced Formula Environment (if you do not already have it)
To add the Advanced Formula Enviornment, select Add-Ins from the Developer tab.
Then, in the Office Add-ins store, search for “Advanced”. It should be near the top of the listings of add-ins.
Once you have provided the required permissions to install the environment, it should be on your home tab; furthest to the right.
Create a New Lambda Function
Upon opening the Advanced Formula Environment, you should not see any windows (except to take a tour of the environment).
In the Manager, click the “+” button, then select Function.
This will bring up a new formula, that will show as =LAMBDA(x, x). At this point, all you need to do is name the formula and click the add button.
The formula has now been created, and is available for editing within the advanced editor!
Naming the Required Variables
As we saw earlier, a loan takes on 4 required arguments:
- The length of the loan, typically meaured in years.
- The interest rate paid on the loan, stated annually.
- The value, or present value, of the loan.
- The frequency with which the loan is paid down.
The length of the loan will be named “years”; to reduce future confusion with the number of periods on the loan that will be determined later.
The interest rate will be added in annual terms.
The value is entered as a positive number (even though the PV on the financial functions uses a negative number).
Finally, the frequency is enclosed in square brackets. This makes the frequency an optional variable. In many cases, the period of repayment is monthly. If no value is supplied, we can add logic to ensure that monthly is used as a default.
Creating the Array
Now that we have created our initial variables, it’s time to create the array where we will be stored. A loan schedule will always have 6 columns (in this example – other schedules may include date and cumulative payments):
- The period that the payment applies to, starting from 1 and finishing with the final payment.
- The balance remaining on the loan at the beginning of the period.
- The payment in the period – this should always be the same!
- The principal paid in the period.
- The interest paid in the period.
- The balance remaining at the end of the period.
While the number of columns will always be 6, the number of rows corresponds to the number of periods – this will only be the number of years if “Annual” is selected.
We also do not have a number for the frequency of payment on the sheet; the value is currently a text value like “Quarterly” or “Annual”.
To convert these values back to numbers, use the SWITCH() function.
With the SWITCH(), the “Annual” text value is converted to a 1, “Quarterly” to 4 and “Bi-weekly” to 26. \
The final number, 12, represents the default. If the value “Monthly” is used, this will show 12; but it will also show 12 if the value is blank.
Once we know the frequency of payment within a year, the array can be made, consisting of:
- Rows: Years * Payment Frequency
- Columns: 6
Finally, we add a LAMBDA() formula with the row and column arguments; these will be used in the final formula.
Renaming values using LET() based on payment frequency
While this step is not required, it will make the final formula much more readable and will save a lot of retyping in the final formula.
The first variable in our LET() is the payment frequency – we need to redo this SWITCH() formula since now we are using it for our loan formula. The variable in this case will be named freq.
We use the frequency variable to define 2 more variables:
periods: This is the number of periods on the loan, defined by frequency * years.
periodrate: This variable is the interest rate which applies to the period; since the interest rate was defined as an annual rate, it needs to be divided by payment frequency to determine the appropriate interest rate for an individual period.
principal: This variable just renames the PV variable to make the formula easier to read.
Adding Financial Variables to the LET() function
For the next 2 variables, a financial function is being calculated in order to improve readability. However; it is important to call out the row variable within this function.
Since the row variable is used, it means that instead of specifying the period in the PPMT() function, it will instead use the current row within the array (not the row on the spreadsheet!).
This row variable corresponds with the active period – so the value returned by this formula will change with every period!
There are 2 formulas that will be named using the LET() formula, as the calculation is used multiple times within the final schedule:
prinpay: The principal payment applied in the period. It uses the periodic interest rate and number of periods as defined earlier in the LET function.
cumpay: The cumulative payments made to the loan at the selected period.
Now that all of our variables are defined, we can move on to the final formula!
Adding Financial Functions to the Array
We are now at the final function – and since the MAKEARRAY() and LET() functions have done most of the hard work, it’s just a CHOOSE() function that is used to actually make the schedule.
The CHOOSE() function will pick a different formula for the schedule based on each column in the formula. As a result, each column will have different results.
For the first column, which corresponds to the period, it can simply take the row within the array using the row variable.
Opening Balance – The opening balance can be defined as the original principal plus the cumulative amount paid at the end of the period (which is a negative number), then add back the principal that will be paid during the period.
Payment – This uses the payment function, based on the periodic rate, number of periods and principal.
Principal Payment – We already defined that in the LET() function, so all we need to do is subtract the principal payment in the period.
Interest Payment – The IPMT formula uses the same syntax as the PPMT() formula defined in the LET() function, but since it is only used once, it can be defined in the body of the final array formula.
Closing Balance – The opening balance can be defined as the original principal plus the cumulative amount paid at the end of the period (which is a negative number).
And we’re done! The formula is complete. All there is left to do is to sync the formula to the spreadsheet.
Syncing Names and Conclusion
The final step, after your LAMBDA() formula is complete, is to synchronize the LAMBDA() function to the spreadsheet. This is done using the sync button in the Advanced Formula Environment.
If you managed to complete the formula – congratulations! This is a very complex example of the functionality of the LAMBDA() function, and uses multiple concepts including:
- Optional parameters in a LAMBDA() function
- LET() functions to define new variables
- The MAKEARRAY() helper function
- Financial functions
- CHOOSE() and SWITCH() to reshape our variables and arrays
I look forward to seeing what incredible new functions will be built in the months and years to come with LAMBDA()!