Property Management Late Fees

fedcco

fedcco

New Member
Joined
Aug 14, 2012
Messages
22
[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
 
Sort by date Sort by votes
Ensure that you have actual dates in columns G and H. =ISNUMBER(G2) should yield True.
Another similar alternative is =IF((H2-G2)>=3,(H2-G2-1)*5,0)
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
[TABLE="width: 692"]
<colgroup><col width="33" style="width: 25pt; mso-width-source: userset; mso-width-alt: 1206;"> <col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="39" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1426;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;" span="2"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"> <col width="39" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1426;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <tbody>[TR]
[TD="width: 33, bgcolor: transparent"]Mo.[/TD]
[TD="width: 114, bgcolor: transparent"]Tenant Name[/TD]
[TD="width: 62, bgcolor: transparent"]Rental Amount[/TD]
[TD="width: 61, bgcolor: transparent"]Lease End Date[/TD]
[TD="width: 39, bgcolor: transparent"]Pmt. Meth[/TD]
[TD="width: 42, bgcolor: transparent"]Chk. No.[/TD]
[TD="width: 59, bgcolor: transparent"]Due Date[/TD]
[TD="width: 71, bgcolor: transparent"]Date Tenant Paid[/TD]
[TD="width: 55, bgcolor: transparent"]Mgmt. Fee[/TD]
[TD="width: 55, bgcolor: transparent"]Repairs Amt.[/TD]
[TD="width: 55, bgcolor: transparent"]Repairs Chk. No.[/TD]
[TD="width: 72, bgcolor: transparent"]Late Fee[/TD]
[TD="width: 62, bgcolor: transparent"]Bal. Due Owner[/TD]
[TD="width: 60, bgcolor: transparent"]Date Paid to Owner[/TD]
[TD="width: 39, bgcolor: transparent"]Pmt. Meth[/TD]
[TD="width: 42, bgcolor: transparent"]Chk. No.[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jan.[/TD]
[TD="width: 114, bgcolor: transparent"]John Smith[/TD]
[TD="width: 62, bgcolor: transparent, align: right"]$40.00[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]12/12/19[/TD]
[TD="width: 39, bgcolor: transparent"]Check[/TD]
[TD="width: 42, bgcolor: transparent, align: right"]77777[/TD]
[TD="width: 59, bgcolor: transparent, align: right"]01/04/19[/TD]
[TD="width: 71, bgcolor: transparent, align: right"]01/04/19[/TD]
[TD="width: 55, bgcolor: transparent"] [/TD]
[TD="width: 55, bgcolor: transparent"] [/TD]
[TD="width: 55, bgcolor: transparent, align: right"]7777[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="width: 62, bgcolor: transparent, align: right"]$40.00[/TD]
[TD="bgcolor: transparent, align: right"]01/15/19[/TD]
[TD="width: 39, bgcolor: transparent"]Check[/TD]
[TD="width: 42, bgcolor: transparent, align: right"]77777[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Feb[/TD]
[TD="width: 114, bgcolor: transparent"] [/TD]
[TD="width: 62, bgcolor: transparent, align: right"]$40.00[/TD]
[TD="width: 61, bgcolor: transparent"] [/TD]
[TD="width: 39, bgcolor: transparent"]Visa[/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 59, bgcolor: transparent, align: right"]01/04/19[/TD]
[TD="width: 71, bgcolor: transparent, align: right"]01/07/00[/TD]
[TD="width: 55, bgcolor: transparent"] [/TD]
[TD="width: 55, bgcolor: transparent"] [/TD]
[TD="width: 55, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="width: 62, bgcolor: transparent, align: right"]$40.00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 39, bgcolor: transparent"]Visa[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Mar[/TD]
[TD="width: 114, bgcolor: transparent"] [/TD]
[TD="width: 62, bgcolor: transparent"] [/TD]
[TD="width: 61, bgcolor: transparent"] [/TD]
[TD="width: 39, bgcolor: transparent"]MC[/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 59, bgcolor: transparent, align: right"]01/04/19[/TD]
[TD="width: 71, bgcolor: transparent, align: right"]01/05/19[/TD]
[TD="width: 55, bgcolor: transparent"] [/TD]
[TD="width: 55, bgcolor: transparent"] [/TD]
[TD="width: 55, bgcolor: transparent"] [/TD]
[TD="width: 72, bgcolor: transparent"] [/TD]
[TD="width: 62, bgcolor: transparent, align: right"]$0.00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 39, bgcolor: transparent"]MC[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Apr[/TD]
[TD="width: 114, bgcolor: transparent"] [/TD]
[TD="width: 62, bgcolor: transparent"] [/TD]
[TD="width: 61, bgcolor: transparent"] [/TD]
[TD="width: 39, bgcolor: transparent"]Cash[/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 59, bgcolor: transparent"] [/TD]
[TD="width: 71, bgcolor: transparent"] [/TD]
[TD="width: 55, bgcolor: transparent"] [/TD]
[TD="width: 55, bgcolor: transparent"] [/TD]
[TD="width: 55, bgcolor: transparent"] [/TD]
[TD="width: 72, bgcolor: transparent"] [/TD]
[TD="width: 62, bgcolor: transparent, align: right"]$0.00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 39, bgcolor: transparent"]Cash[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


=((H5-G5)>3)*10+((H5-G5)>4)*(H5-G5-4)*5
=((H5-G5)>=3)*(H5-G5-1)*5

I've been trying both of these formulas all day with no success. I've attached the revised sheet and have made the column and row changes accordingly but no luck. Parts of the formulas work but can't seem to make the whole thing work. Any help will be greatly appreciated. Thanks.
 
Upvote 0
And I don't see where it adds the $10 after the due date.
$10 the day after the due date then $5 each day thereafter.
$10 when 1 day late
$5 each additional day after the 1 day late fee. $10,+$5+$5+$5...
 
Upvote 0
01/07/00 Doesn't look like a relevant date

For clarity try formatting the date mmm dd, yyyy Which should show as Jan 07, 2019.
Evaluate the formula using Excel's Evaluate Formula; it should yield the result that you specified

Excel 2010
GHIJK
1Due DateDate PaidMgmt. FeeRepairs Amt.Late Fee
2Jan 04, 2019Jan 07, 201910
3Jan 04, 2019Jan 07, 201910
3a
Cell Formulas
RangeFormula
K2=IF((H2-G2)>=3,(H2-G2-1)*5,0)
K3=((H3-G3)>=3)*(H3-G3-1)*5
 
Last edited:
Upvote 0

Excel 2010
GHIJK
1Due DateDate PaidMgmt. FeeRepairs Amt.Late Fee
2Jan 04, 2019Jan 07, 201910
3Jan 01, 2019Jan 07, 201925
4Feb 01, 2019Feb 03, 20190
5Feb 01, 2019Feb 28, 2019130
3a
Cell Formulas
RangeFormula
K2=IF((H2-G2)>=3,(H2-G2-1)*5,0)
K3=((H3-G3)>=3)*(H3-G3-1)*5
K4=((H4-G4)>=3)*(H4-G4-1)*5
K5=((H5-G5)>=3)*(H5-G5-1)*5
 
Upvote 0
N.B. If you are not putting Dates in the 2 columns but you are putting numbers like 4 for the Due Date and numbers for Date Paid,
either version of the formula still works.


Excel 2010
ABCDEFGHIJKL
1MonthTenant NameRental AmountLease End DateMethod of Pmt.Chk. No.Due DateDate PaidMgmt. FeeRepairs Amt.Late FeeBal. Due Owner
2Feb40471050
3Feb4013040
4Feb40172565
5Feb100128130230
3a
Cell Formulas
RangeFormula
K2=((H2-G2)>=3)*(H2-G2-1)*5
K3=((H3-G3)>=3)*(H3-G3-1)*5
K4=((H4-G4)>=3)*(H4-G4-1)*5
K5=((H5-G5)>=3)*(H5-G5-1)*5
L2=C2+K2
L3=C3+K3
L4=C4+K4
L5=C5+K5
 
Upvote 0
The formula works just great but what would I need to do if I wanted to change the $10 to $50 and the additional $5 to $10? I tried to work it on my own but no success.
 
Upvote 0

Excel 2010
ABCDEFGHIJKL
1MonthTenant NameRental AmountLease End DateMethod of Pmt.Chk. No.Due DateDate PaidMgmt. FeeRepairs Amt.Late FeeBal. Due Owner
2Feb40145090
3Feb401560100
4Feb401780120
5Feb100Feb 1, 2019Feb 7, 201980180
3a
Cell Formulas
RangeFormula
K2=((H2-G2)>=3)*50+((H2-G2)>=4)*(H2-G2-3)*10
K3=((H3-G3)>=3)*50+((H3-G3)>=4)*(H3-G3-3)*10
K4=((H4-G4)>=3)*50+((H4-G4)>=4)*(H4-G4-3)*10
K5=IF((H5-G5)>=3,50+IF((H5-G5)>=4,(H5-G5-3)*10,0))
L2=C2+K2
L3=C3+K3
L4=C4+K4
L5=C5+K5
 
Last edited:
Upvote 0
Dave, I can't thank you enough for putting up with me. Everything is right with the world and the formula works just as intended. So if you're ever in my neck of the woods (Central Florida) and need any real estate help or advice, even if it's not in Florida, please feel free to contact me. Thanks a million.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Late Fees Applied
2
Replies
14
Views
2K
Wils60
W
N
  • Question Question
Managing Payment table separate from billing, calculating lps on every received payment based on criteria.
Replies
1
Views
590
NaushadAnwar
N
K
  • Solved
Calculate Total Owed with Multiple Non-consistent Payments
Replies
5
Views
1K
Dave Patton
D
D
  • Question Question
VBA Code - the code is not pasting to the column I need it to.
Replies
2
Views
804
dragontbone
D
M
  • Question Question
Auto adjust row heights based on specific column
Replies
1
Views
405
XLfriend777
XLfriend777

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

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