MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel in Microsoft 365: SEQUENCE inside of other Functions such as IPMT


September 30, 2020 - 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


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.