Distribute Payment over the year based on invoice term

[Heba]

Hello Everyone,

That's my first post here and I'm desperate for your help,

I have the below sheet and I want to distribute the amount in K4 over the year based on the invoice term and date of course, so if :

1- The invoice term is 12 months it'll appear in every month and not exceed the end date.
2- The invoice term is 3 months it'll appear only each 3 months and not exceed the end date.
3- If the end date year is bigger than current year it'll also appear in current year " See Row 5"

I tried many formulas but in the end the results is missy

Thanks in Advance

 

[Dave Patton]

The post includes a few ideas that you can try.
You can paste the information into a clean sheet; see the icon below the F(x)

The first post shows the formula just for M Month, Q Quarter , and O Once.
Review the formulas with Excel's Formula Evaluate and then review the second post that uses Choose.

T202007b.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	Description	Start Date	End Date	Amount		# Pymts per yr	Total # Payments	Amount		Jan-21
7
8	Rent	1-Jan-21	1-Mar-22	30,000	M	12	15	2,000		2,000
9	Insurance	1-Mar-21	28-Feb-22	12,000	Q	4	4	3,000		0
10	Once	17-Mar-21	17-Mar-21	25,000	O	1	1	25,000		0
4_

Cell Formulas
Range	Formula
G8	G8	=DATEDIF(B8,EOMONTH(C8,0)+1,"m")
H8	H8	=D8/G8
G9	G9	=DATEDIF(B9,C9+1,"m")/3
H9:H10	H9	=D9/F9
G10,C10	G10	=F10
F8:F9	F8	=LOOKUP(E8,{"M",12;"O",1;"Q",4})
J8	J8	=AND($B8<=J$2,COUNTIF($I8:I8,">0")<$G8)*$H8
J9	J9	=AND($B9-DAY($B9)+1<=J$2,COUNTIF($I9:I9,">0")<$G9)*(MOD(MONTH(J$2)-MONTH($B9)+$F9-1,($F9-1))=0)*$H9
J10	J10	=($F10=1)*($B10-DAY($B10)+1=J$2)*$H10

T202007b.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Description	Start Date	End Date	Amount		# Pymts per yr	Total # Payments	Amount		Jan-21	Feb-21	Mar-21
3	Rent	1-Jan-21	1-Mar-22	30,000	M	12	15	2,000		2,000	2,000	2,000
4	Insurance	1-Mar-21	28-Feb-22	12,000	Q	4	4	3,000		0	0	3,000
5	Once	17-Mar-21	17-Mar-21	25,000	O	1	1	25,000		0	0	25,000
4_

Cell Formulas
Range	Formula
G3	G3	=DATEDIF(B3,EOMONTH(C3,0)+1,"m")
H3	H3	=D3/G3
G4	G4	=DATEDIF(B4,C4+1,"m")/3
H4:H5	H4	=D4/F4
G5,C5	G5	=F5
J3:L5	J3	=CHOOSE(MATCH($E3,{"M","O","Q"},0),(AND($B3<=J$2,COUNTIF($I3:I3,">0")<$G3)*$H3),(($F3=1)*($B3-DAY($B3)+1=J$2)*$H3),(AND($B3-DAY($B3)+1<=J$2,COUNTIF($I3:I3,">0")<$G3)*(MOD(MONTH(J$2)-MONTH($B3)+$F3-1,($F3-1))=0)*$H3))
F3:F4	F3	=LOOKUP(E3,{"M",12;"O",1;"Q",4})