Property Management Late Fees

[fedcco]

[TABLE="width: 500"]
<tbody>[TR]
[TD]

[TABLE="width: 625"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Month

[/TD]
[TD="width: 92, bgcolor: transparent"]Tenant Name
[/TD]
[TD="width: 75, bgcolor: transparent"]Rental Amount
[/TD]
[TD="width: 62, bgcolor: transparent"]Lease End Date
[/TD]
[TD="width: 67, bgcolor: transparent"]Method of Pmt.
[/TD]
[TD="width: 65, bgcolor: transparent"]Chk. No.
[/TD]
[TD="width: 65, bgcolor: transparent"]Due Date
[/TD]
[TD="width: 73, bgcolor: transparent"]Date Paid
[/TD]
[TD="width: 72, bgcolor: transparent"]Mgmt. Fee
[/TD]
[TD="width: 63, bgcolor: transparent"]Repairs Amt.
[/TD]
[TD="width: 59, bgcolor: transparent"]Late Fee
[/TD]
[TD="width: 76, bgcolor: transparent"]Bal. Due Owner
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jan.
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 62, bgcolor: transparent"][/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 73, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="width: 63, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 76, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Feb
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 62, bgcolor: transparent"][/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 73, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="width: 63, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 76, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Mar
[/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 62, bgcolor: transparent"][/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 73, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="width: 63, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 76, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I need a formula that will calculate late fees if the payment is made past the 3rd. day of the month.

Ex. If the payor pays on the 4th. of the month, then he must pay a $$$ amount and if he pays on the 5th., then he must pay an additional amount. (Paid on the 7th. of the month, $10+5+5+5)

Thanks

 

[Gerald Higgins]

OK, so is the calculation something like -
If he pays on the 4th, he must pay $10
Then every day after the 4th, another $5 ?
Right up to the 31st ?

 

[fedcco]

Correct and thanks for your quick reply.

 

[Gerald Higgins]

Is it POSSIBLE that they could pay early, and if YES, how do you want to handle that ?

So for example, payment due on 3rd February but they actually pay 31st January ?
Is that possible ?

 

[fedcco]

If they pay on the due date or earlier, they just pay the amount due or the $10.

Thanks.

 

[Gerald Higgins]

Whoa . . . the amount due OR the $10 ?

Do we need to work out which ?
If so, how ?

 

[fedcco]

Sorry for the confusion, the amount due is lets say $40. This is just an example of what I need to do. If the tenant pays the amount due ($40) prior to or up to the 3rd., they just pay the amount due ($40). If they pay on the next day or on the 4th. they pay $40+$10 for a total of $50. If they pay on the 7th., they pay ($40+$10 for the 4th.)+($5 for the 5th.)+($5 for the 6th.)+($5 for the 7th.) Totaling $65

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Month	Tenant Name	Rental Amount	Lease End Date	Method of Pmt.	Chk. No.	Due Date	Date Paid	Mgmt. Fee	Repairs Amt.	Late Fee	Bal. Due Owner
2	Jan.		40				1-Jan-19	1-Jan-19			0
3	Feb		40				1-Feb-19	10-Feb-19			35	75
3a

Cell Formulas
Range	Formula
K2	=((H2-G2)>3)*10+((H2-G2)>4)*(H2-G2-4)*5
K3	=((H3-G3)>3)*10+((H3-G3)>4)*(H3-G3-4)*5
L3	=C3+K3

 

[fedcco]

Unfortunately, the formula is returning a "0" for the calculation. I substituted a + sign for the * before the 10 which gave a return of 10 but still didn't add the additional $5 per day late.

 

[Dave Patton]

The formula works but the following is more concise.
Ensure that the columns shown are the same as your spreadsheet.

Excel 2010
	C	D	E	F	G	H	I	J	K	L
1	Rental Amount	Lease End Date	Method of Pmt.	Chk. No.	Due Date	Date Paid	Mgmt. Fee	Repairs Amt.	Late Fee	Bal. Due Owner
2	40				1-Jan-19	4-Jan-19			10	50
3	40				1-Feb-19	10-Feb-19			40	80
4	40				1-Feb-19	7-Feb-19			25	65
3a

Cell Formulas
Range	Formula
K2	=((H2-G2)>=3)*(H2-G2-1)*5
K3	=((H3-G3)>=3)*(H3-G3-1)*5
K4	=((H4-G4)>=3)*(H4-G4-1)*5
L2	=C2+K2
L3	=C3+K3
L4	=C4+K4