Excel Tips: Excel Loan schedule custom function

This article demonstrates how to convert a standard multiple formula loan schedule into a single custom function. I previously demonstrated a flexible loan schedule technique that handled interest-free periods by combining Goal Seek with a macro. 

Custom functions were also explained in this article. Once created, the custom function is easily shared between files. This technique can be applied to other schedules.

Here, we’ll cover an advanced custom function. The companion file includes two versions of the custom function. The shorter version will be examined. 

Figure 1 shows the top section of a standard loan schedule. Input cells are shaded yellow.

The formulas in row 11 all use dynamic arrays that spill down as far as required by the loan term entered in cell B4.

The formulas used are listed in Table 1.

The process of creating an advanced custom function is usually broken down into several steps. Initially, a single complex formula will be created to create the schedule. A LAMBDA function will be used to convert the complex formula into an easy-to-use custom function.  

Step 1

The LET function will be used to create the schedule starting with the first three columns. It allows the use of variables to capture values and ranges. In complex formulas, this reduces the length of the formula and makes it easier to understand.

Figure 2 shows a LET function that creates three columns. The sequential month numbers, the months and the monthly repayments. A single formula in cell A11 creates a three-column spill range.

Note the use of line breaks in the Formula Bar in Figure 2. To enter a line break in the Formula Bar, press Alt + Enter. Line breaks are used to separate each variable definition line, which makes following the formula easier. This layout is like computer programming.

The s variable captures the month numbers. 

The mth variable captures the months. 

The rpayts variable captures the list of monthly repayment amounts. 

Each of these variables represents a vertical spill range. The HSTACK function combines the three separate spill ranges into a single three-column-wide spill range. The HSTACK function was described in section nine in this article.

Step 2

Currently the formula relies on the monthly repayment calculation in cell B7. For the formula to be self-contained, the repayment calculation needs to be captured within the LET function.

Figure 3 shows the updated formula with the new entries highlighted in yellow. A new variable, rpayt, calculates the monthly repayment. This rpayt variable is then used in the rpayts variable definition. 

Step 3

The CUMIPMT function calculates the interest component of a loan payment between a start and end period. The PMT and CUMIPMT functions have the same first three arguments. 

To make populating these functions easier, we can capture the number of months and the monthly interest percentage as variables. In Figure 4, the added lines and entries have been highlighted in yellow.

The per variable calculates the number of months.

The i variable calculates the monthly interest.

The per and i variables are then used within the SEQUENCE, PMT and CUMIPMT functions.

The interest variable calculates the monthly interest amounts as a spill range.

The interest variable has been added to the HSTACK function to create a four-column  spill range.

In Figure 5, two more variables are included. The principal variable captures the principal value using the CUMPRINC function. The CUMPRINC function calculates the principal component of a loan payment between a start and end period. It has the same syntax and entries as the CUMIPMT function.

The bal variable captures the loan closing balance using the SCAN function. The SCAN function is typically used to create running balances based on spill ranges. 

Figure 5 has the formula changes highlighted in yellow.

The two new variables have been added to the HSTACK function to complete the six-column loan schedule.

Step 5

Column headings are required to complete the self-contained loan schedule.

The headings are created using something called array syntax. This involves curly brackets (braces) around text entries, separated with commas. 

Figure 6 shows the hdg variable capturing the six column headings. The VSTACK function is used to place the headings above the columns created by the HSTACK function to complete the schedule.

Step 6

This is a single formula creating all six-columns for the loan schedule. It is too complex to use as is. A custom function using the LAMBDA function can simplify it. 

LAMBDA function

The LAMBDA function allows the creation of function arguments and then a calculation based on those arguments. There is a special testing syntax that enables passing entries to the arguments. Once testing has been done, a custom function can be created using the LAMBDA function.

Figure 7 shows the inclusion of the LAMBDA function with its testing syntax. The testing  syntax is an extra set of parentheses after the LAMBDA parentheses to include the inputs to the function. The four cell references are passed to their respective four arguments. The LET function has been modified to incorporate the LAMBDA argument names.

The first entry in the second set of parentheses is B2. That cell’s value is passed to the amount argument in the LAMBDA. The amount argument replaces all the B2 references used throughout the LET function. 

The second entry in the second set of parentheses B3 is passed to the int_rate argument, and this is used to calculate the i variable in the LET function.

The B4 reference is passed to the years argument and used calculate the per variable.

The B5 reference is passed to the start argument and used calculate the list of months for the mth variable.

Step 7

The final step is to create the custom function. Copy the formula in the Formula Bar excluding the extra set of parentheses on the end.

Press Ctrl + Alt + F3 to open the New Name dialog. Enter the function name fnLOAN in the top box. I use a prefix of fn for all my custom functions. I recommend using a naming convention to help identify custom functions. It should differentiate your custom functions from Excel’s built-in functions.

In the Refers to: box delete any formula and paste in the LAMBDA function. Click OK. See Figure 8.

The custom function is ready to use. See it in use in Figure 9.

Final step

I recommend adding detailed comments to document the function. This makes it easier for the user to understand. The final Name dialog with the detailed documentation is shown in Figure 10.

When entering a documented custom function, the comments will display to assist the user. Figure 11 demonstrates this.

Copying a sheet that uses a custom function to another file will install the custom function in the other file. 

Another custom function including column totals has been included in the companion file  — see Figure 12.

This article has covered many advanced techniques. Building a complex custom function like this is typically done in stages, with testing at each stage. This custom function has simplified a complex schedule into a single easy-to-use function, which can be used in multiple files.

The companion video and Excel file will go into more detail to demonstrate these techniques.