MrExcel.com - Your One Stop for Excel Tips & Solutions

Using SEQUENCE Inside Another Function

August 24, 2022 - by

Using SEQUENCE Inside Another Function

The figure below calculates the interest payment for each of the first five months of a loan. You have to key in the numbers 1 through 5 in A7:A11 or use =ROW(1:1) and copy down. Having to use the =ROW(1:1) hack to generate a sequence of number will go away soon.

This shows part of an amortization table. You need a column of month numbers, such as 1, 2, 3, 4, 5 as shown here.
Figure 632. Five formulas to calculate interest payments for five months.

For the period argument of the IPMT function, I tried putting in SEQUENCE(5), but, darn it, the formula spilled and gave me five results.

Inside the IPMT function, where you normally enter the month number, enter SEQUENCE(5) and the IPMT function returns an array of 5 monthly payments. =IPMT($B$3/12,SEQUENCE(5),$B$2,$B$1).

Figure 633. I wanted one answer instead of five.

To return a single answer when Excel wants to spill to five cells, you can use a wrapper function, such as SUM: =SUM((IPMT($B$3/12,SEQUENCE(5),$B$2,$B$1)).

Wrap the IPMT in a SUM function and you get one answer that is the sum of the five months. =SUM(IPMT($B$3/12,SEQUENCE(5),$B$2,$B$1)).
Figure 634. Five answers summarized to one cell.

Say that you want to figure out how much interest you will pay in year 3 of a loan. You don't need to create a loan amortization table to do this. The formula shown below does it all at once. You still use the IPMT function. But for the third argument, you can specify SEQUENCE(12,1,37,1) to generate periods 37 to 48.

In the image below, the starting month is in column F. The formula figures out the interest for 12 months, starting from the month number in column F.

This worksheet calculates total interest for each 12 month span. Enter the numbers 1, 13, 25, 37 down the column starting in F10. Then use SEQUENCE(12,1,F10,1) as the Month argument in iPmt.
Figure 635. Easier than the full amortization table.

This article is an excerpt from Power Excel With MrExcel

Title photo by Markus Winkler on Unsplash