split amount conditionly

[noubaba]

I need help, here I also attached image of excel sheet to explain my question. I have this installment payment sheet. Payed amount to be entered in column E. What I want, I want to enter amount in column E regardless of months for example I had payed 3,76,000 rupees in 15 July and then second installment I had payed in 31 Dec. I need formula to split my payed amount based on installments amount mentioned in column C and fill up automatically column F and G. You can also notice that when I had payed 3,76,000 rupees on 15 July 2019 then payed amount were distributed in column F till F4 and I got 9000 rupees in F4 and when I payed 3,58,000 rupees on 31 Dec 2019 then 1,74,500 rupees add up in F4 to make it 1,83,500 as per C3. Can you please design a formula which can fill up F and G column for me. Further Blank cells in column E can be ignored. Hope you understood my question. Thanks a lot in advance for your help.

 

[CountTepes]

Is it anything like this?

variable installment months

Hi Dears I am not an accountant person that's why i can't calculate this. so please help me i have sheet to calculate the installment amounts of price of the apartment. it's 2500000 $ if cst want to pay 10% (variable percent) in the start which 250000 $ . then install the rest of amount on 1 ...

www.mrexcel.com

 

[Snakehips]

Maybe....

Hope that helps.

Book1
	A	B	C	D	E	F	G
1	Ser	Nature	Amount	Due Dtae	Paid Amount	Instalment Paid	Status
2	1	Inst1	183,500	15/07/2019	376,000	183,500	Paid
3	2	Inst2	183,500	30/09/2019		183,500	Paid
4	3	Inst3	183,500	31/12/2019		9,000
5	4	Inst4	183,500	15/03/2020
6	5	Inst5	183,500	30/06/2020
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=IF(SUM(E$2:E2)>=SUM(C$2:C2),C2,IF(C2+SUM(E$2:E2)-SUM(C$1:C2)>0,C2+SUM(E$2:E2)-SUM(C$1:C2),""))
G2:G6	G2	=IF(F2=C2,"Paid","")

Book1
	A	B	C	D	E	F	G
1	Ser	Nature	Amount	Due Dtae	Paid Amount	Instalment Paid	Status
2	1	Inst1	183,500	15/07/2019	376,000	183,500	Paid
3	2	Inst2	183,500	30/09/2019		183,500	Paid
4	3	Inst3	183,500	31/12/2019	358,000	183,500	Paid
5	4	Inst4	183,500	15/03/2020		183,500	Paid
6	5	Inst5	183,500	30/06/2020
Sheet1

 

[noubaba]

thank you Snakehips for your help, formula almost working fine but one concern if you can fix it further.
Lets say in terms of installment I payed 500,000 amount on 30 Sep-20 then F6 is not being filled. Can you please fix it. F6 or any cell in column F must not left empty, whenever I pay amount it should start right under the installment payed in column F. Hope you understood this issue.

 

[noubaba]

thank you Snakehips for your help, formula almost working fine but one concern if you can fix it further.

Lets say in terms of installment I payed 500,000 amount on 30 Sep-20 then F6 is not being filled. Can you please fix it. F6 or any cell in column F must not left empty, whenever I pay amount it should start right under the installment payed in column F.

 

[Snakehips]

Try this....

Book1
	B	C	D	E	F	G
1	Nature	Amount	Due Dtae	Paid Amount	Instalment Paid	Status
2	Inst1	183,500	15/07/2019	376,000	183,500	Paid
3	Inst2	183,500	30/09/2019		183,500	Paid
4	Inst3	183,500	31/12/2019	358,000	183,500	Paid
5	Inst4	183,500	15/03/2020		183,500	Paid
6	Inst5	183,500	30/06/2020		183,500	Paid
7	Inst6	183,500	30/09/2020	500,000	183,500	Paid
8	Inst7	183,500	31/12/2020		133,000	Part Paid
9	Inst8	183,500	30/03/2021
10	Inst9	183,500	30/06/2021
Sheet1

Cell Formulas
Range	Formula
F2:F10	F2	=IF(SUM($E$2:$E$13)>=SUM(C$2:C2),C2,IF(SUM(E$2:E2)>SUM(C$1:C1),SUM(E$2:E2)-SUM(C$1:C1),""))
G2:G10	G2	=IF(F2=C2,"Paid",IF(F2="","","Part Paid"))

 

[noubaba]

Snakehips, sorry to ask for your help again. Still one issue please see attached picture, if you can extend your help to fix it. Thanks in advance.

 

[Snakehips]

Hopefully this sorts it....

Book1
	A	B	C	D	E	F	G
1	Ser	Nature	Amount	Due Dtae	Paid Amount	Instalment Paid	Status
2	1	Inst1	183,500	15/07/2019	376,000	183,500	Paid
3	2	Inst2	183,500	30/09/2019		183,500	Paid
4	3	Inst3	183,500	31/12/2019	358,000	183,500	Paid
5	4	Inst4	183,500	15/03/2020		183,500	Paid
6	5	Inst5	183,500	30/06/2020		100	Part Paid
7	6	Inst6	183,500	30/09/2020	100
8	7	Inst7	183,500	31/12/2020
9	8	Inst8	183,500	30/03/2021
10	9	Inst9	183,500	30/06/2021
11	10	Inst10	183,500	30/09/2021
12	11	Inst11	183,500	31/12/2021
13	12	Inst12	181,500	31/03/2022
14					734,100	734,100
Sheet1

Cell Formulas
Range	Formula
F2:F13	F2	=IF($E$14>=SUM(C$2:C2),C2,IF(F1<>C1,"",IF(E$14-SUM(F$1:F1)>0,E$14-SUM(F$1:F1),"")))
G2:G13	G2	=IF(F2=C2,"Paid",IF(F2="","","Part Paid"))
E14:F14	E14	=SUM(E2:E13)

 

[noubaba]

Thank you sir, to help me till issue resolved. this formula working fine.