Excel 2024: SEQUENCE inside of other Functions such as IPMT

August 12, 2024 - by Bill Jelen

After SORT, SORTBY, FILTER, and UNIQUE, the SEQUENCE and RANDARRAY functions seem pretty tame. SEQUENCE will generate a sequence of numbers.

The syntax for SEQUENCE is Rows, Columns, Start, Step.

It does not seem like this is very interesting. Who needs to generate a list of numbers?

=SEQUENCE(5) returns the numbers 1 through 5 in a column. =SEQUENCE(5,2,3,9) returns 5 rows and 2 columns, starting with 3, incrementing by 9. The results of this second formula are 3 and 12 in the first row, 21 and 30 in the second row, and so on.

Try putting SEQUENCE inside other functions. Here, IPMT calculates the interest in the 7th month of a loan:

To calculate the interest during the 7th month of a loan, use =IPMT(H5/12,7,H4,H3). The next example will replace that 7 with a SEQUENCE function.

Thanks to SEQUENCE, this formula calculates the interest paid during 12 months starting in month 7:

To calculate the interest for months 7 through 18, use =SUM(IPMT(H5/12,SEQUENCE(12,1,7),H4,H3).

Two formulas create a forward-looking calendar:

=SEQUENCE(52,1,TODAY(),-7) would produce a list of the past 52 weeks.

This article is an excerpt from MrExcel 2024 Igniting Excel

Title photo by Jeffrey Brandjes on Unsplash