Google Sheets - sequence function using array

Question

I have a table that contains a column with Initial Year and another with the number of amortisation years. I'm trying to generate a new table that duplicates the rows where amortisation years is >1 and recalcs the Year for each duplication.

I've drafted an example and the incomplete solution I got to so far:

sample sheet

I'd ideally like to solve it using formulas (instead of a script). Breaking my head here, so really appreciate your help!

Answer 1

use:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(A4:A9&"×"&B4:B9&"×"&IF(
 SEQUENCE(1, MAX(D4:D9), 0)<D4:D9, C4:C9+
 SEQUENCE(1, MAX(D4:D9), 0), )&"×"&E4:E9), "×"), 
 "where Col4 is not null", ))