Dynamic quarterly payments formula

[fkhan123]

Dear Readers,

I needed your help to determine formula to calculate quarterly payments due.

I have a monthly cashflow and in each month there are certain number of sales. For example on 12/31/2021 there are 10 sales. These sales will pay 5% every quarter until a completion date. Then next month for exmaple there are 11 sales, then same rule will apply.

I am unable to determine a formula for this.

Will be grateful if someone can help?

Thank you

 

[BigBeachBananas]

Dear Readers,

I needed your help to determine formula to calculate quarterly payments due.

I have a monthly cashflow and in each month there are certain number of sales. For example on 12/31/2021 there are 10 sales. These sales will pay 5% every quarter until a completion date. Then next month for exmaple there are 11 sales, then same rule will apply.

I am unable to determine a formula for this.

Will be grateful if someone can help?

Thank you

How are the sales recorded?
Pay 5% of what?
Do you know the completion date?
If you could share a sample data set

 

[Toadstool]

Hi Fkhan123,

Here I've assumed your Quarters are calendar. The months must begin with the first day of a quarter so B2 has 1 October 2021.

You say you recognize 5% per quarter so your contracts must run for 60 months. I've pro-rated sales by month as a conservative accounting approach with rows 7 to 10 showing the quarter breakdown.

fkhan123.xlsx
	A	B	C	D	E	F	G														U	V	W																																								BK	BL	BM	BN	BO	BP	BQ
1
2		Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22														May-23	Jun-23	Jul-23																																								Nov-26	Dec-26	Jan-27	Feb-27	Mar-27
3	Sales			10	11																																																															Total	21
4	Will Recognize	0	0	10	21	21	21														21	21	21																																								21	11	0	0	0
5
6
7		Q4-21	Q1-22	Q2-22	Q3-22	Q4-22	Q1-23														Q3-26	Q4-26	Q1-27
8	Qtr Start	01-Oct-21	01-Jan-22	01-Apr-22	01-Jul-22	01-Oct-22	01-Jan-23														01-Jul-26	01-Oct-26	01-Jan-27
9	Qtr End	31-Dec-21	31-Mar-22	30-Jun-22	30-Sep-22	31-Dec-22	31-Mar-23														30-Sep-26	31-Dec-26	31-Mar-27
10	Recognize 5%	0.166667	1.05	1.05	1.05	1.05	1.05														1.05	0.883333	0																																								0	0	0	0	0	Total	21
1st

Cell Formulas
Range	Formula
BK2:BO2,U2:W2,C2:G2	C2	=EOMONTH(B2,0)+1
BQ3,BQ10	BQ3	=SUM(B3:BO3)
BK4:BO4,U4:W4,B4:G4	B4	=SUM(OFFSET($A$3,,MAX(1,COLUMN()-60),,MIN(60,ABS(COLUMN($A$23)-COLUMN()))))
U7:W7,B7:G7	B7	="Q"&(INT((MONTH(B$8)-1)/3)+1)&"-"&TEXT(B$8,"yy")
B8	B8	=B2
U8:W8,C8:G8	C8	=B9+1
U9:W9,B9:G9	B9	=EOMONTH(B8,2)
BK10:BO10,U10:W10,B10:G10	B10	=SUMIFS($B$4:$BO$4,$B$2:$BO$2,">="&B8,$B$2:$BO$2,"<="&B9)*0.05/3

 

[fkhan123]

Thank you so much @Toadstool and @BigBeachBananas , really appreciate your reply.

The issue is the contact doesn't run for 60 months. It is bound by a start date and end date, it is essentially a real estate payment plan, where you make deposit while the unit is under construction plus make quarterly payments until handover.

I have added some data below to illustrate the problem. A contract in Month 1 for example will run for different months compared to contract which started in Month 2.

Once again I'm really grateful for your help.

			Payment Plan
Price per unit	1,132,800.00		Initial	10%​
			Quarterly	5%​
			Remaining on Handover
			Handover Date	8/28/2024​
Year	1​	1​	1​	1​	1​	1​	1​	1​
Month	1​	2​	3​	4​	5​	6​	7​	8​
Date	6/30/2022​	7/31/2022​	8/31/2022​	9/30/2022​	10/31/2022​	11/30/2022​	12/31/2022​	1/31/2023​
Expected Sales	55​	30​	25​	22​	18​	15​	15​	18​
Initial deposit	6,230,400.00	3,398,400.00	2,832,000.00	2,492,160.00	2,039,040.00	1,699,200.00	1,699,200.00	2,039,040.00
Quarterly Payments
Handover

 

[Toadstool]

I'm still struggling to understand.
So they run for different months but in this example all end 8/28/2024?
If each loan has interest paid quarterly then I assume that's against the outstanding loan value so I've added rows for Sales Value and Remainder removed all pennies/cents for clarity.

There are no payments in months 1 or 2 as a quarter has not elapsed.
In month 3 the payment is 5% of the remaining loan from month 1.
In month 4 the payment is 5% of the remaining loan from month 2.
In month 5 the payment is 5% of the remaining loan from month 3.
In month 6 the payment is 5% of the remaining loan from month 4 plus a repeat of the payment for the loan in month 1.
In month 7 the payment is 5% of the remaining loan from month 5 plus a repeat of the payment for the loan in month 2.
In month 8 the payment is 5% of the remaining loan from month 6 plus a repeat of the payment for the loan in month 3.
In month 9 we get payments for the new month 7 loan plus repeat payments for loans from months 4 and 1.

I that what you're looking for?

			Payment Plan
Price per unit	1,132,800​		Initial	10%
			Quarterly	5%
			Remaining on Handover	201,864,960​
			Handover Date	8/28/2024
Year	1	1	1	1	1	1	1	1	1
Month	1	2	3	4	5	6	7	8	9
Date	6/30/2022	7/31/2022	8/31/2022	9/30/2022	10/31/2022	11/30/2022	12/31/2022	1/31/2023	2/28/2023
Expected Sales	55	30	25	22	18	15	15	18	0
Sales Value	62,304,000​	33,984,000​	28,320,000​	24,921,600​	20,390,400​	16,992,000​	16,992,000​	20,390,400​	0​
Initial deposit	6,230,400​	3,398,400​	2,832,000​	2,492,160​	2,039,040​	1,699,200​	1,699,200​	2,039,040​	0​
Remainder	56,073,600​	30,585,600​	25,488,000​	22,429,440​	18,351,360​	15,292,800​	15,292,800​	18,351,360​	0​
		Months	1	2	3	4,1	5,2	6,3	7,4,1
			2,803,680​	1,529,280​	1,274,400​	1,121,472​	917,568​	764,640​	764,640​
						2,803,680​	1,529,280​	1,274,400​	1,121,472​
									2,803,680​
		Total Qtrly	2,803,680​	1,529,280​	1,274,400​	3,925,152​	2,446,848​	2,039,040​	4,689,792​

 

[fkhan123]

@Toadstool

Thank you. Yes that is correct. After 1 year every quarter will have 5% from one of the quarters from Year 1, as you have correctly highlighted above. Issue is if the ending date is couple of years in advance or quarterly payments are low then this matrix becomes very long.

 

[Toadstool]

Here's my calculations. Please test.

fkhan123-v2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1				Payment Plan
2	Price per unit	1,132,800		Initial	10%
3				Quarterly	5%
4				Remaining on Handover	201,864,960
5				Handover Date	8/28/2024
6
7
8	Year	1	1	1	1	1	1	1	1	1	1	1	1	2	2	2	2	2	2	2	2	2	2	2	2	3	3	3
9	Month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27
10	Date	6/30/2022	7/31/2022	8/31/2022	9/30/2022	10/31/2022	11/30/2022	12/31/2022	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	2/29/2024	3/31/2024	4/30/2024	5/31/2024	6/30/2024	7/31/2024	8/31/2024		Totals
11	Expected Sales	55	30	25	22	18	15	15	18	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		198
12	Sales Value	62,304,000	33,984,000	28,320,000	24,921,600	20,390,400	16,992,000	16,992,000	20,390,400	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		224,294,400
13	Initial deposit	6,230,400	3,398,400	2,832,000	2,492,160	2,039,040	1,699,200	1,699,200	2,039,040	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		22,429,440
14	Remainder	56,073,600	30,585,600	25,488,000	22,429,440	18,351,360	15,292,800	15,292,800	18,351,360	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		201,864,960
15
16		1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3
17	Quarterly Payments			2,803,680	1,529,280	1,274,400	3,925,152	2,446,848	2,039,040	4,689,792	3,364,416	2,039,040	4,689,792	3,364,416	2,039,040	4,689,792	3,364,416	2,039,040	4,689,792	3,364,416	2,039,040	4,689,792	3,364,416	2,039,040	4,689,792	3,364,416	2,039,040	0		74,577,888
fkhan123

Cell Formulas
Range	Formula
E4	E4	=SUM($B$14:$AC$14)
C8:AB8	C8	=IF(MOD(C9,12)=1,B8+1,B8)
C9:AB9	C9	=B9+1
C10:AB10	C10	=EOMONTH(B10,1)
B12:AB12	B12	=(B11*$B$2)
B13:AB13	B13	=(B11*$B$2)*$E$2
B14:AB14	B14	=B12-B13
AD17,AD11:AD14	AD11	=SUM(B11:AB11)
B16	B16	=B9
C16:AB16	C16	=IF(MOD(B16,3)=0,1,B16+1)
D17:AB17	D17	=IF(D10>$E$5,0,SUMIFS($B$14:B$14,$B$16:B$16,B16)*0.05)

 

[fkhan123]

@Toadstool, thank you so much! yes it works perfectly.

Appreciate your help

 

[Toadstool]

@Toadstool, thank you so much! yes it works perfectly.

Appreciate your help

You're welcome.
Thanks for the feedback.