Calculate Quarterly revenue based on existing EMI

[James Clear]

Hi All,

Sorry for not pasting Xl2BB format , however need an urgent answer

I have such 1L customers but for sake of explaining I am mentioning 2 customers only.

Let us assume we have two customers ABC & XYZ - Collection start date & Maturity date is given , till the maturity they will pay every month 1K as their EMI

Now basis Quarter , i have to calculate their EMIs ( Sum of their installments ) into the summary table as mentioned below

point 2 - As you can see Cust 2 will have early maturity and hence after it's maturity only 1 customer will remain so in the raw data there are 1L customers and maturity date range can be anything for each customer so i need to calculate their future incoming funds to just understand the better revenue for the upcoming quarters.

Please help me to derive this formula

Input
Customer	Collection Start Date	Collection maturity	Monthly Collection
ABC	03-Jan-21	17-May-23	1000
XYZ	03-Jun-19	14-Oct-21	1000
Output
FY 22	Apr-21 to Jun-21	Jul-21 to Sep-21	Oct-21 to Dec-21	Jan-22 to Mar-22
FY	Q1	Q2	Q3	Q4
	2000	2000	2000	1000
Output
FY 23	Apr-22 to Jun-22	Jul-22 to Sep-22	Oct-22 to Dec-22	Jan-22 to Mar-22
	Q1	Q2	Q3	Q4
	1000	1000	1000	1000

 

[James Clear]

Note - Last quarter would be Jan 23 to mar 23

 

[James Clear]

Pls assist?