late fee formula

apgmin

apgmin

Board Regular
Joined
Mar 1, 2010
Messages
150
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Hi,
Please provide sample data, so that a suitable formula can be shared with you..





apgmin said:
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
Click to expand...
 
Upvote 0
apgmin said:
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
Click to expand...

[TABLE="width: 345"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]fees payable date[/TD]
[TD]fees paid date[/TD]
[TD]fees payable amount[/TD]
[TD]fees paid amount[/TD]
[TD]penalty amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]02-Jan[/TD]
[TD="align: right"]12-Mar[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]02-Jan[/TD]
[TD="align: right"]02-Jan[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]02-Jan[/TD]
[TD="align: right"]12-Mar[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[/TR]
</tbody>[/TABLE]

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 )
 
Last edited:
Upvote 0
apgmin said:
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
Click to expand...

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<>
 
Last edited:
Upvote 0
Hi apgmin,
Please use the following formula in E column:
=MAX(IF(B2>A2,MONTH(B2-A2)*100,0),IF(D2>=C2,0,150))
 
Upvote 0
apgmin said:
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
Click to expand...

apgmin said:
[TABLE="width: 345"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]fees payable date[/TD]
[TD]fees paid date[/TD]
[TD]fees payable amount[/TD]
[TD]fees paid amount[/TD]
[TD]penalty amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]02-Jan[/TD]
[TD="align: right"]12-Mar[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]02-Jan[/TD]
[TD="align: right"]02-Jan[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]02-Jan[/TD]
[TD="align: right"]12-Mar[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[/TR]
</tbody>[/TABLE]

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 )
Click to expand...

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>[TABLE="class: grid, width: 392"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Date 01[/TD]
[TD]Date 02[/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Result[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD="align: right"]12/03/2018[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD="align: right"]12/03/2018[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]********[/TD]
[TD]********[/TD]
[TD]********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz</c4)*150)<><c4)*150)< font=""></c4)*150)<>
 
Last edited:
Upvote 0
=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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

trishcollins
  • Question Question
Looking for formula/VBA to determine where to best put extra funds
Replies
1
Views
229
trishcollins
trishcollins
A
  • Solved
exclude zero, populate last amount for adjacent last enter cell for each range
Replies
6
Views
116
Alaa mg
A
B
  • Solved
Need to add two sets of minutes with custom formatting and round
Replies
1
Views
512
bh24524
B
W
  • Question Question
Late Fees Applied
2
Replies
14
Views
2K
Wils60
W
U
  • Question Question
VLOOKUP that will SUM data based on Date Range
2
Replies
18
Views
1K
UnitedCloud01
U

Forum statistics

Threads
1,223,713
Messages
6,174,043
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top