Recurring payments | every 1, 3, 6 or 12 months | sum total income by month in Google Sheets

Question

I have multiple monthly support contracts with always the same payment per client and per month. Some clients pay every month, others pay every 3, 6 or 12 months. I know the starting month for every contract. And I would like to see what is my income for January, February, etc.

How can I calculate the income at the different months?

Here are three samples:

Client	amount per month	starting month	pays every X months
client A	$29	1 (January)	3
client B	$39	3 (March)	6
client C	$49	10 (October)	12

Income for January: $XXX

Income for February: $XXX ...

Answer 1

First, replace starting months that are text strings like 1 (January) with dates like 2023-01-01:

Client	Amount	Start date	Pays every X months
client A	$29	2023-01-01	3
client B	$39	2023-03-01	6
client C	$49	2023-10-01	12

You can then get a list of payments like this:

=lambda( 
  numMonths, amounts, startDates, periods, 
  query( 
    reduce( 
      { "month", "income" }, sequence(numMonths, 1, 0), 
      lambda( 
        result, monthOrdinal, 
        { 
          result; 
          map( 
            amounts, startDates, periods, 
            lambda( 
              amount, startDate, period, 
              { 
                edate(startDate, monthOrdinal), 
                amount * not(mod(monthOrdinal, period)) 
              } 
            ) 
          ) 
        } 
      ) 
    ), 
    "select Col1, sum(Col2) group by Col1 
     limit " & numMonths & " 
     format Col1 'MMM yyyy', sum(Col2) '$0' ", 1 
  ) 
)(
  24, 
  filter(B2:B, isnumber(C2:C)), 
  filter(C2:C, isnumber(C2:C)), 
  filter(D2:D, isnumber(C2:C)) 
)

...with these results:

month	sum income
Jan 2023	$29
Feb 2023	$0
Mar 2023	$39
Apr 2023	$29
May 2023	$0
Jun 2023	$0
Jul 2023	$29
Aug 2023	$0
Sep 2023	$39
Oct 2023	$78
Nov 2023	$0
Dec 2023	$0
...	...

To choose how far in the future the results extend, modify the 24 that signifies the number of months to iterate over.

Answer 2

You can try this formula, according to your proposed cells:

={Sequence(12),BYROW(SEQUENCE(12),LAMBDA(each,SUM(FILTER(B:B,MOD(C:C,D:D)=MOD(each,D:D)))))}

For this to work you should change your values in C to numbers, if you can't let me know!