PMT AND YEARS - AUTOMATIC

[Pietro Di Micio]

Hi guys!

I've used the formula to calculate the monthly value of an installment; but, I have 06 columns (2023, 2024, 2025, 2026, 2027 and 2028) to automate the periods, for example: If I put the period of 60 months... I would like the monthly installment in 2023 will be multiplied by 7 (Jun-Dec) and in all other years by 12... in the last year, 2028, by 5 (Jan-May) completing 60 months; BUT if I change the period from 60 to 36, for example, I would like to automatically multiply the PMT by 7 in 2023, 12 in 2024 and 2025 and 5 in 2026, zeroing out the years 2027 and 2028.

Thanks in advance for the help.

Best!

 

[Pietro Di Micio]

Any?

 

[Dave Patton]

T202104a.xlsm
	A	B	C	D	E	F	G	H	I	J	K
19						2023	2024	2025	2026	2027	2028
20			5-May-23	60		7	12	12	12	12	5
21			5-Jul-23	60		5	12	12	12	12	7
22
2aa

Cell Formulas
Range	Formula
F20:K21	F20	=MIN(12,DATEDIF(C20,F$19,"m"),$D20-SUM($E20:E20))

 

[Pietro Di Micio]

What is this sum E$20-E20?

 

[Dave Patton]

Does the suggestion work for you?
Try reviewing the formula in say cell G20.

 

[Pietro Di Micio]

I am trying to figure out...

Do you mean =MIN(12;DATEDIF(D21;L$18;"m");$F21-G21) ? because its turning #NUM

 

[Pietro Di Micio]

Should G21 empty? or Should I put something?

 

[Dave Patton]

E20 is empty.
cell f20 has =MIN(12,DATEDIF(C20,F$19,"m"),$D20-SUM($E20:E20))
Your system may be using different separators etc. than mine.
Try putting my example in a clean sheet. Click on the icon below the f(x) in the header and then move to your sheet and paste at cell A19.

Then try reviewing the formulas with formula evaluate.

 

[Pietro Di Micio]

 

[Pietro Di Micio]

#NUM

same error; I am trying to figure out what is wrong but without success, I mean the separator is ; instead of just , (only difference I think)