Spilled Array based on different Time Periods and applied periods - Help Needed

To handle this, you can create a spilled array formula that checks each time period and flags when the expense should occur based on the start date, end date, and applied period. Here's a general approach:

For each expense, use SEQUENCE() or similar to generate a series of applied dates (e.g., starting from the start date and repeating every X months).
Use IF() or FILTER() to check if each generated date falls within the range of the time periods at the top.
Populate the flags (1/0) based on the matches.
If you can share more specific details from the attached sheet (like columns used), I can provide a more detailed formula.

James_Buist 

I was going to leave this one to David, but somehow I got embroiled with some interesting results.  Firstly, I do not think I have ever worked with a variable pitch timeline before so generating it was a challenge; I am still not sure what is the most economical way of specifying it.

Turning to the expenses, I wrote a function that returns the payment dates for a single expense

COUNTPMTλ = LAMBDA(start, end, interval,
    LET(
    duration, 1 + DATEDIF(start, end, "m"),
    nPmts, duration / interval,
    counter, SEQUENCE(1, nPmts, 0),
    pmtDate, EOMONTH(start, counter),
    TOROW(DROP(FREQUENCY(pmtDate, Start_of_Period),1))
  )
)

I was planning to assign each payment to a time period and use GROUPBY to accumulate frequent payments over the longer reporting intervals.  Then it occurred to me that using the timeline as FREQUENCY bins would achieve the same goal.  Hence the function as it is.

My next issue was that there are multiple expense lines.  The first thought is to use MAP to run over the various lines.  Except, that returns a nested array error (in common with every other worthwhile problem I have ever seen - OK, so that's probably an exaggeration!)  That is where I turned to a function MAPλ I had written earlier and posted to GitHub as a Gist.

The formula I finished with was

= ext.MAPλ(Start_Date, End_Date, Applied_Periods, COUNTPMTλ)

where ext is a module that I created on this occasion to hold Excel extensions.

cc djclements 

James_Buist My first response used a brute-force method to compare the target periods against all dates between the earliest start date and the oldest end date, which can be quite labor intensive and may affect performance with a larger dataset/date range. It was also originally written as a generalized method to handle any start day, including the 30th and 31st, which is probably overkill for your needs.

Another approach could be to generate an array of thunks (uncalled lambda functions), which contain the required sequence of dates for each line number. Then, the array(s) can be broadcast across (and down) to fill the entire output grid for each period, and MAP can be used to loop through the resulting 3D array, calling each applicable thunk (function) as needed.

Assuming I've understood your request correctly, it could look something like this:

=LET(
    periodStart, G2:R2,
    periodEnd, G3:R3,
    interval, B5:B12,
    startDate, C5:C12,
    endDate, D5:D12+(interval>1),
    duration, ((YEAR(endDate)-YEAR(startDate))*12+MONTH(endDate)-MONTH(startDate)+1)/interval,
    seqλ, MAP(startDate,duration,interval,LAMBDA(sd,d,i,LAMBDA(x,y,
        LET(a,EDATE(sd,SEQUENCE(d,,0,i)),--IFERROR(OR((a>=x)*(a<=y)),0))))),
    MAP(IF({1},periodStart,interval),IF({1},periodEnd,interval),IF({1},seqλ,periodEnd),LAMBDA(ps,pe,λ,λ(ps,pe)))
)

It's not the easiest method to understand, but the amount of data being handled is much less than in my first approach. 😉

EDIT: my first two attempts only checked if the start date landed between the period start and period ending dates, which was based on your manually entered "expected results"; however, after re-reading your instructions in the workbook where you said, "if a start or end date hits the middle of a time period... then it is fine just to apply it to the entire period", I'm thinking this might be more appropriate:

=LET(
    periodStart, G2:R2,
    periodEnd, G3:R3,
    interval, B5:B12,
    startDate, C5:C12,
    endDate, D5:D12,
    duration, ((YEAR(endDate)-YEAR(startDate))*12+MONTH(endDate)-MONTH(startDate)+1)/interval,
    seqλ, MAP(startDate,duration,interval,LAMBDA(sd,d,i,LAMBDA(x,y,
        LET(a,EDATE(sd,SEQUENCE(d,,0,i)),b,EOMONTH(a,i-1),--IFERROR(OR(((a>=x)*(a<=y))+((b>=x)*(b<=y))),0))))),
    MAP(IF({1},periodStart,interval),IF({1},periodEnd,interval),IF({1},seqλ,periodEnd),LAMBDA(ps,pe,λ,λ(ps,pe)))
)

Check them out and see if either one does what you want...

James_Buist 

You might like to check out Craig Hatmaker 's 5G components for financial modelling.

5G functions for Excel for Financial Models: deprecated on 2024-05-29 (github.com)

At first sight TimelinePositionλ would appear to be relevant to the situation you describe but I will take a further look at your problem later.

James_Buist Give this a try and see if it's what you're looking for...

=LET(
    colData, G2:R3,
    rowData, B5:D12,
    rowId, SEQUENCE(ROWS(rowData)),
    start, INDEX(rowData,,2),
    end, INDEX(rowData,,3),
    num, INDEX(rowData,,1),
    low, MIN(start),
    seq, SEQUENCE(,MAX(end)-low+1,low),
    dates1, DATE(YEAR(seq),MONTH(seq),DAY(start)),
    dates2, EOMONTH(seq,0),
    test, NOT(MOD(MONTH(seq)-MONTH(start)+(YEAR(seq)-YEAR(start))*12,num))*(seq=IF(dates1<dates2,dates1,dates2))*(seq>=start)*(seq<=end),
    ids, TOROW(IFS(test,rowId),2),
    dates, TOCOL(IFS(test,seq),2),
    --(MMULT(--(ids=rowId),(dates>=TAKE(colData,1))*(dates<=DROP(colData,1)))>0)
)

It's virtually identical to my solution on another thread, with the additional MMULT formula used to flag the applicable periods.

Reference: Generating a list of Dates using nested array inside SEQUENCE function