Quarterly prepaid and postponed payments formula

[s_1592]

Hi there!
I'm trying to build a formula to correctly calculate quarterly prepaid and postponed payments, and I'm a bit stuck at the moment.

As seen in the example below, let's assume a contract start & end dates on Feb 23 & Jan 24.
The quarter period must start from the first month of the contract (so in this case Feb 23) and end on the last month of the contract (Jan 24), as shown in the table.

	1/31/2023	2/28/2023	3/31/2023	4/30/2023	5/31/2023	6/30/2023	7/31/2023	8/31/2023	9/30/2023	10/31/2023	11/30/2023	12/31/2023	1/31/2024
Costs		100	300	400	500	700	100	200	300	800	1000	1000	2000
Quarterly Prepaid		800			1300			1300			4000
Quarterly Postponed				800			1300			1300			4000

Would very much appreciate your help in solving this.

Thank you!

 

[fjns]

Hi, see the linked file for a possible solution...

The formulas used in the table...
C3: =IF(MOD(COLUMN(),3)=0,C2+D2+E2,"") (Range: C3:N3)
C4: =IF(AND(COLUMN()>2,MOD(COLUMN(),3)=2),A2+B2+C2,"") (Range: C4:N4)

QPayments.xlsx

 

[s_1592]

Hi, see the linked file for a possible solution...

The formulas used in the table...
C3: =IF(MOD(COLUMN(),3)=0,C2+D2+E2,"") (Range: C3:N3)
C4: =IF(AND(COLUMN()>2,MOD(COLUMN(),3)=2),A2+B2+C2,"") (Range: C4:N4)

QPayments.xlsx

View attachment 93131

Thanks for your reply. This formula however will only work for this specific example and when applied to colomns C:N.

I'm trying to build a formula that can correctly calculate the quarterly payments for any start-end date of the contract (not only specifically Feb 23-Jan 24 as shown in the example above).

 

[fjns]

Hi, I created two new tables for the general case.

Two almost completely empty columns (B and C) are required in the table for two reasons.
The values of cells B1 and C1 are important for the formulas, and the entire two columns are also necessary for the formulas to work correctly.
The Fill Color of the B1 and C1 cells is black, so the values in them are not confusing.

The new formulas used in the tables:
B1: =MOD(MATCH(1,IF(A2:AA2>0,2,0),1)+1,3)
C1: =MOD(MATCH(1,IF(A2:AA2>0,2,0),1),3)
D3: =IF(AND(D2<>"",MOD(COLUMN(),3)=$B1),D2+E2+F2,"") (Range: D3:AA3)
D4: =IF(AND(D2<>"",MOD(COLUMN(),3)=$C1),B2+C2+D2,"") (Range: D4:AA4)

QPayments2.xlsx
QPayments3.xlsx