Using SEQUENCE Inside Another Function
August 24, 2022 - by Bill Jelen

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.

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.
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))
.

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 article is an excerpt from Power Excel With MrExcel
Title photo by Markus Winkler on Unsplash