Property Management Late Fees

fedcco

fedcco

New Member
Joined
Aug 14, 2012
Messages
22


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
Jan.
Feb
Mar

<tbody>
</tbody>

<tbody>
</tbody>
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Mo.Tenant NameRental AmountLease End DatePmt. MethChk. No.Due DateDate Tenant PaidMgmt. FeeRepairs Amt.Repairs Chk. No.Late FeeBal. Due OwnerDate Paid to OwnerPmt. MethChk. No.
Jan.John Smith$40.0012/12/19Check7777701/04/1901/04/19 77770$40.0001/15/19Check77777
Feb $40.00 Visa 01/04/1901/07/00 0$40.00 Visa
Mar MC 01/04/1901/05/19 $0.00 MC
Apr Cash $0.00 Cash
<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> </tbody>


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

Forum statistics

Threads
1,215,148
Messages
6,123,300
Members
449,095
Latest member
Chestertim

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