Trying to Create a Dymanic Prepaid Amortization Schedule

[stlpony]

Trying to amortize the $80k in cell D4 .... Obviously only $219 in cell F4 is complete wrong. Any idea??

A Vendor​ Start Date End Date Amount 1/31/2022​ 2/28/2022​ 3/31/2022​ 4/30/2022​ Company 1​ 1/15/2022 1/14/2023 $ 80,000.00 $ 219.18​ 219.1781​ 219.1781​ 219.1781​ Company 2​ 2/1/2022 11/30/2022 $ 15,000.00 0​ 49.50495​ 49.50495​ 49.50495​
E4:N5 formula =MAX(0, MIN(EOMONTH(E$3, 0), $C4) + 1 - MAX(E$3, $B4))*$D4/($C4-$B4+1)​

 

[Dave Patton]

T202211a.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2								info
3	Vendor	Start Date	End Date	Amount	1-Jan-22	1-Feb-22	1-Mar-22	1-Apr-22	1-May-22	1-Jun-22
4	Company 1	15-Jan-22	14-Jan-23	$80,000.00	3,726.03	6,136.99	6,794.52	6,575.34	6,794.52	6,575.34
5	Company 2	1-Feb-22	30-Nov-22	$15,000.00	0.00	1,386.14	1,534.65	1,485.15	1,534.65	1,485.15
6
5b

Cell Formulas
Range	Formula
E4:J5	E4	=MAX(0, MIN(EOMONTH(E$3, 0), $C4) + 1 - MAX(E$3, $B4))*$D4/($C4-$B4+1)

 

[stlpony]

T202211a.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2								info
3	Vendor	Start Date	End Date	Amount	1-Jan-22	1-Feb-22	1-Mar-22	1-Apr-22	1-May-22	1-Jun-22
4	Company 1	15-Jan-22	14-Jan-23	$80,000.00	3,726.03	6,136.99	6,794.52	6,575.34	6,794.52	6,575.34
5	Company 2	1-Feb-22	30-Nov-22	$15,000.00	0.00	1,386.14	1,534.65	1,485.15	1,534.65	1,485.15
6
5b

Cell Formulas
Range	Formula
E4:J5	E4	=MAX(0, MIN(EOMONTH(E$3, 0), $C4) + 1 - MAX(E$3, $B4))*$D4/($C4-$B4+1)

Thank you. So it looks like my problem is in the formatting of the dates?

 

[Dave Patton]

Thanks for the feedback.

N.B. I used the beginning of each month not the end of each month.

Your post was not in useable form; it was partially corrupted and had merged cells. You could try the forum's tool named XL2BB.

 

[stlpony]

Thanks for the feedback.

N.B. I used the beginning of each month not the end of each month.

Your post was not in useable form; it was partially corrupted and had merged cells. You could try the forum's tool named XL2BB.

Looks like switching from beginning of month to end of month was my problem.