Late Fees Applied

[Wils60]

I have been researching this for several months with absolutely no success.

I created a spreadsheet to receive rental payments with a $50 late fee after 5 days with a balance owed column. I got it all worked out except for one very frustrating problem:

When I enter their actual payment without late fee after the 5th, excel sees it as paid and removes the late fee. If I enter the payment after the 5th with late fee included, it again sees it as paid and credits them $50 in the balance owed column.
How can I keep the late in intact even after a payment is posted after the 5th?

Example:

A B C D E F G H I J K

Unit #	1st resident	2nd resident	Current Monthly Rate	Parking Space Fee	Monthly Total Owed	Days Late	Late Fee ($50)	Paid Amount	K=Checks R=Cash M=Money Order C= Cashiers	Balanced Owed This Month
12	Joe Blow	Joes Wife	519.00	-	569.00	7​	-	519		-
14	John Doe	Mrs. Doe	470.00	15.00	535.00	7​	-	535		+50

15

Janet Jackson

406.00

-

456.00

7​

50

(456.00)

Column G: I used =SUM(TODAY()-$M$1) M1 not shown here, it contains due date, to determine how many days late
Column H: I used =IF(AND(G2>5,I2<D2+E2),50,0) To me this says if they are more than 5 days late (G2) and have not paid (I2) then add $50, if they have paid amount owed, then return 0
Column K: I used =IF(AND(G2>5,I2<(D2+E2+50)),I2-(D2+E2+50),I2-(D2+E2)) I had to use part of the Column H formula to prevent a circular reference. I've also tried various incarnations of this formula with no success

AS you can see from my examples, they are all 7 days late generating a $50 late fee. Unit #12 I entered the amount owed without late fee and it removed the late fee and gave them a $0 balance owed
Unit #14 I entered the amount owed with the late fee, it removed the late fee and credited them with $50 on Balance owed
Unit #15 correctly shows total amount owed when late, adding regular monthly payments plus late fee.

Please help - how can I tell excel that if they are late, leave the late fee in even after they pay past the due date?

 

[Candyman8019]

Column F would be =D2+H2
for Column H, you can use =IF(G2>5,50,0)
and column K would be =F2-I2

 

[Wils60]

Candyman8019
Wow, Is this a prime example of me way waaaaaaay over thinking things? I tried your suggestion and it worked!
Thank you very much

 

[Candyman8019]

Happy to help

 

[Wils60]

Wait, we still got a problem with this correction. If the resident pays on time, after the 5th day it still adds the late fee. I need it to not add it if they are not late.
Over thinking things again but possibly another if(and or sum(if ?

 

[Candyman8019]

Seems to me, the late days is the culprit here. You may need another column (in addition to your late days count) to indicate if they are late or not late...and take that into consideration for your sum. the reason for this is that the value in G will increase daily because it doesn't check to see if the payment is made on time. Alternatively, you can add a payment date field...but that could also get messy if partial payments are made.

 

[Candyman8019]

After thinking about this more, a payment date is probably the way to go then your pseudocode would be:
If payment date is > first of the month + 4 days (ie 5th of the month) then charge $50 else $0

 

[Wils60]

Ugh, this can't be this hard. I've played with it some more and still can't get it to behave.

 

[Candyman8019]

If you can use the XL2BB (found on this site) add-in and paste an actual copy of what you have, I can do some work on it and offer suggestions.

 

[Wils60]

I'm a new member so I don't know wat that is