Installment Collection Schedule

[ArslanButt]

Hello Excel Gurus,

I have been benefiting a lot from this forum for which I am thankful. This time i am trying build an installment collection schedule and trying to automate the same so that it changes as i vary inputs/assumptions but i am not being able to construct the spreadsheet and/or design a suitable formulae for the same. Query is as follows:

Apartment sales are to be booked on monthly basis starting January
Downpayment is 30% of the sales in the month sale is booked
20% payment is on possession of the apartment- date of possession is same for all apartment sales is December no matter when the sale is made January or Feb or June
Customer has a choice of installments type- Monthly/Quarterly etc. during the remaining time period

So in this instance sales made in January will have downpayment collected in January, 11 monthly installments and possession payment in Dec (assumed) and sales made in Feb will have downpayment in Feb, 10 monthly installments and possession payment in Dec.

Accordingly, i need to model collections received every month. Moreover, customers can choose monthly installments, quarterly installments as well.

Installment Schedule.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Downpaymnet	30%												Total
2	Last payment	20%
3
4	Sales Month	31-Jan-23	28-Feb-23	31-Mar-23	30-Apr-23	31-May-23	30-Jun-23	31-Jul-23	31-Aug-23	30-Sep-23	31-Oct-23	30-Nov-23	31-Dec-23
5	Sales Amount	100	150	200	150	100	50	80	60	50	100	50	200	1290
6	Remaining Installment Tenor	11	10	9	8	7	6	5	4	3	2	1	1
7
8
9	Downpayments	30	45	60	45	30	15	24	18	15	30	15	60	387
10	Installments	50	75	100	75	50	25	40	30	25	50	25	0	545
11	Payments on possession												358	358
12		80	120	160	120	80	40	64	48	40	80	40	418	1290
13	Installments
14	31-Jan-23	0	0	0	0	0	0	0	0	0	0	0	0
15	28-Feb-23	5	0	0	0	0	0	0	0	0	0	0	0
16	31-Mar-23	5	8	0	0	0	0	0	0	0	0	0	0
17	30-Apr-23	5	8	11	0	0	0	0	0	0	0	0	0
18	31-May-23	5	8	11	9	0	0	0	0	0	0	0	0
19	30-Jun-23	5	8	11	9	7	0	0	0	0	0	0	0
20	31-Jul-23	5	8	11	9	7	4	0	0	0	0	0	0
21	31-Aug-23	5	8	11	9	7	4	8	0	0	0	0	0
22	30-Sep-23	5	8	11	9	7	4	8	8	0	0	0	0
23	31-Oct-23	5	8	11	9	7	4	8	8	8	0	0	0
24	30-Nov-23	5	8	11	9	7	4	8	8	8	25	0	0
25	31-Dec-23	5	8	11	9	7	4	8	8	8	25	25	0
26	Total Collection	50	75	100	75	50	25	40	30	25	50	25	0
Sheet2

Cell Formulas
Range	Formula
B4	B4	=EOMONTH(TODAY(),0)
C4:M4	C4	=EOMONTH(B4,1)
N5,N9:N12	N5	=SUM(B5:M5)
B6:L6	B6	=COUNTA(C4:$M$4)
M6	M6	=COUNTA($M4:N$4)
B9:M9	B9	=B5*$B$1
B10:M10	B10	=B26
M11	M11	=N5-N9-N10
B12:M12	B12	=SUM(B9:B11)
B14:M25	B14	=IF($A14>B$4,B$5*(1-$B$1-$B$2)/B$6,0)
B26:M26	B26	=SUM(B14:B25)

Best Regards,

 

[Skybluekid]

What version of Excel are you using?

 

[ArslanButt]

I am using ms excel 365

 

[Fluff]

I am using ms excel 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ArslanButt]

Account details updated