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

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

September 30, 2020 - by

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.