MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: SEQUENCE inside of other Functions such as IPMT


September 25, 2019 - by Bill Jelen

Excel SEQUENCE inside of other Functions such as IPMT. Photo Credit: chuttersnap at Unsplash.com

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:

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

Title Photo: chuttersnap at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Inside Out

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.