Loans Payments Consolidated Cash Flow

[ricafonyat]

Let me know if you can help me to solve this excel problem:

The goal is to generate a matrix of monthly loans´s payments so I will be able to consolidate a final cash flow.

In range G2:X17 which formula I have to insert in order to generate a matrix like that?

Teste Matriz.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2		Num. Of Payments	6
3		Monthly Rate	1%
4
5		Loans Date	Loan Amount	Payment Amount		jan.23	fev.23	mar.23	abr.23	mai.23	jun.23	jul.23	ago.23	set.23	out.23	nov.23	dez.23	jan.24	fev.24	mar.24	abr.24	mai.24	jun.24
6		jan.2023	5.000,00	862,74			862,74	862,74	862,74	862,74	862,74	862,74
7		fev.2023	3.000,00	517,65				517,65	517,65	517,65	517,65	517,65	517,65
8		mar.2023	4.500,00	776,47					776,47	776,47	776,47	776,47	776,47	776,47
9		abr.2023	7.500,00	1.294,11						1.294,11	1.294,11	1.294,11	1.294,11	1.294,11	1.294,11
10		mai.2023	5.000,00	862,74							862,74	862,74	862,74	862,74	862,74	862,74
11		jun.2023	10.000,00	1.725,48								1.725,48	1.725,48	1.725,48	1.725,48	1.725,48	1.725,48
12		jul.2023	10.000,00	1.725,48									1.725,48	1.725,48	1.725,48	1.725,48	1.725,48	1.725,48
13		ago.2023	10.000,00	1.725,48										1.725,48	1.725,48	1.725,48	1.725,48	1.725,48	1.725,48
14		set.2023	10.000,00	1.725,48											1.725,48	1.725,48	1.725,48	1.725,48	1.725,48	1.725,48
15		out.2023	10.000,00	1.725,48												1.725,48	1.725,48	1.725,48	1.725,48	1.725,48	1.725,48
16		nov.2023	10.000,00	1.725,48													1.725,48	1.725,48	1.725,48	1.725,48	1.725,48	1.725,48
17		dez.2023	10.000,00	1.725,48														1.725,48	1.725,48	1.725,48	1.725,48	1.725,48	1.725,48
18
19					Total	0,00	862,74	1.380,39	2.156,85	3.450,97	4.313,71	6.039,19	6.901,93	8.109,77	9.058,79	9.490,16	10.352,90	10.352,90	8.627,42	6.901,93	5.176,45	3.450,97	1.725,48
Planilha1

Cell Formulas
Range	Formula
G5	G5	=C6
H5:X5	H5	=EDATE(G5,1)
E6:E17	E6	=PMT($D$3,$D$2,-$D6)
G19:X19	G19	=SUM(G6:G17)

Any help will be very appreciated

 

[DRSteele]

I think this might help. Copy across and down.

G6:X17 formula: =IF((G$5>$C6)*(G$5<=EOMONTH($C6,6)),$E6,"")

Note that the dates in Column C and in Row 5 are the last day of each month.