late fee formula

[apgmin]

In cell A1 I put the date when the fees is supposed/committed to be paid by the student
In cell B1 I put the date when the fees is actually paid by the student

In cell C1 I put the amount which is supposed/committed to be paid by the student on date A1
In cell D1 I put the amount that is actually paid by the student
Now if he pays after the committed date or pays less than the combined amount he needs to pay a penalty which should be calculated in cell E1

The rules are as follow for computing the penalty :
If he pays less than commited then automatically 100 USD is to be paid by him
or if he pays after the comitted date then the amount should be USD 100 per month delay

the results in cell E! sould be higher of the 2 above

 

[asakhy]

Hi,
Please provide sample data, so that a suitable formula can be shared with you..

In cell A1 I put the date when the fees is supposed/committed to be paid by the student
In cell B1 I put the date when the fees is actually paid by the student

In cell C1 I put the amount which is supposed/committed to be paid by the student on date A1
In cell D1 I put the amount that is actually paid by the student
Now if he pays after the committed date or pays less than the combined amount he needs to pay a penalty which should be calculated in cell E1

The rules are as follow for computing the penalty :
If he pays less than commited then automatically 100 USD is to be paid by him
or if he pays after the comitted date then the amount should be USD 100 per month delay

the results in cell E! sould be higher of the 2 above

 

[apgmin]

In cell A1 I put the date when the fees is supposed/committed to be paid by the student
In cell B1 I put the date when the fees is actually paid by the student

In cell C1 I put the amount which is supposed/committed to be paid by the student on date A1
In cell D1 I put the amount that is actually paid by the student
Now if he pays after the committed date or pays less than the combined amount he needs to pay a penalty which should be calculated in cell E1

The rules are as follow for computing the penalty :
If he pays less than commited then automatically 100 USD is to be paid by him
or if he pays after the comitted date then the amount should be USD 100 per month delay

the results in cell E! sould be higher of the 2 above

	A	B	C	D	E
1	fees payable date	fees paid date	fees payable amount	fees paid amount	penalty amount
2	02-Jan	12-Mar	500	500	300
3	02-Jan	02-Jan	500	400	150
4	02-Jan	12-Mar	500	300	300

<tbody>
</tbody>

Now the student 1 instead of paying on 2nd jan paid on 12 march so 3 months late ( 2nd jan, 2nd feb and 2nd march ) so he should pay 300USD @ 100 usd per month. as penalty
now student 2 even though he paid on time he paid instead of 500 only 400 usd so to the penalty 150 USD
the student 3 paid late and also paid less so his penalty is Maximum of the 2 scenario above ( because he is supposed to pay penalty for late payment @ 300 and less payment @ 150 so the actual penalty he pays is max of the two amounts ie. 300 USD
give me the formula for the 3rd student in row 4 to be put in cell e4 ( THE RESULTS FOR THE SAME FORMULA SHOULD BE 300 FOR E4, 150 FOR E3 AND 300 FOR E2 )

 

[markmzz]

In cell A1 I put the date when the fees is supposed/committed to be paid by the student
In cell B1 I put the date when the fees is actually paid by the student

In cell C1 I put the amount which is supposed/committed to be paid by the student on date A1
In cell D1 I put the amount that is actually paid by the student
Now if he pays after the committed date or pays less than the combined amount he needs to pay a penalty which should be calculated in cell E1

The rules are as follow for computing the penalty :
If he pays less than commited then automatically 100 USD is to be paid by him
or if he pays after the comitted date then the amount should be USD 100 per month delay

the results in cell E! sould be higher of the 2 above

Maybe this in E1 and copy down:

=D1-C1-(DATEDIF(A1,B1,"m")+(DAY(B1) > DAY(A1))+(D1 < C1))*100<c1))*100< font="">
</c1))*100<><c1))*100< font="">
Markmzz</c1))*100<><c1))*100< font=""></c1))*100<>

 

[apgmin]

sir, it is not showing expected results

 

[asakhy]

Hi apgmin,
Please use the following formula in E column:
=MAX(IF(B2>A2,MONTH(B2-A2)*100,0),IF(D2>=C2,0,150))

 

[asakhy]

Please let me know if the formula works now..

 

[markmzz]

In cell A1 I put the date when the fees is supposed/committed to be paid by the student
In cell B1 I put the date when the fees is actually paid by the student

In cell C1 I put the amount which is supposed/committed to be paid by the student on date A1
In cell D1 I put the amount that is actually paid by the student
Now if he pays after the committed date or pays less than the combined amount he needs to pay a penalty which should be calculated in cell E1

The rules are as follow for computing the penalty :
If he pays less than commited then automatically 100 USD is to be paid by him
or if he pays after the comitted date then the amount should be USD 100 per month delay

the results in cell E! sould be higher of the 2 above

	A	B	C	D	E
1	fees payable date	fees paid date	fees payable amount	fees paid amount	penalty amount
2	02-Jan	12-Mar	500	500	300
3	02-Jan	02-Jan	500	400	150
4	02-Jan	12-Mar	500	300	300

<tbody>
</tbody>

Now the student 1 instead of paying on 2nd jan paid on 12 march so 3 months late ( 2nd jan, 2nd feb and 2nd march ) so he should pay 300USD @ 100 usd per month. as penalty
now student 2 even though he paid on time he paid instead of 500 only 400 usd so to the penalty 150 USD
the student 3 paid late and also paid less so his penalty is Maximum of the 2 scenario above ( because he is supposed to pay penalty for late payment @ 300 and less payment @ 150 so the actual penalty he pays is max of the two amounts ie. 300 USD
give me the formula for the 3rd student in row 4 to be put in cell e4 ( THE RESULTS FOR THE SAME FORMULA SHOULD BE 300 FOR E4, 150 FOR E3 AND 300 FOR E2 )

Hi!

Sorry, but now you have new rules.

Anyway, try this in E4 and copy down:

=MAX(DATEDIF(A4,B4,"m")*100+(DAY(B4) > DAY(A4))*100,(D4 < C4)*150)

<c4)*150)< font="">
</c4)*150)<><c4)*150)< font="">_{^{<strike>
</strike>}}

	A	B	C	D	E	F
1
2
3	Date 01	Date 02	Value 1	Value 2	Result
4	02/01/2018	12/03/2018	500	500	300
5	02/01/2018	02/01/2018	500	400	150
6	02/01/2018	12/03/2018	500	300	300
7
***	**********	**********	********	********	********	**

<tbody>
</tbody>

Markmzz</c4)*150)<><c4)*150)< font=""></c4)*150)<>

 

[apgmin]

thanks works perfectly

 

[asakhy]

=MAX(IF(B4>A4,MONTH(B4-A4)*100,0),IF(D4>=C4,0,150))
In my excel, the value of Student 3 is calculated in E4.