0

# How to calculate aging late fees in excel????

Tue 12 Jan, 2010 09:51 am
Ok, here's the lease agreement: "Any payment of rent which is not received within 10 days of due date, shall be subject to a late penalty fee of 5% of the payment amount due per day. In the event that a payment is not received within 30 days of the due date, such payment shall also be subject of interest in an amount equal to 1,5% per month."
I have to calculate late fees for 2009 for a tenant who was invoiced for \$5,000 per month, but paid \$2,500 each time before 10th of each month.
Could someone help me with the excel formula?
• Topic Stats
• Top Replies
Type: Question • Score: 0 • Views: 21,986 • Replies: 4
No top replies

engineer

2
Tue 12 Jan, 2010 10:03 am
@jelrichard,
A1 = overdue amount
B1 = days overdue

late fee = if(b1>10,(b1-10)*5%*a1,0) + if(b1>30, a1*1.5%*b1/30,0)

If you want to charge 1.5% interest only on the months after the first one, then substract 30 from b1 in the second if statement like I did in the first one. One question: do you want to charge interest on all previously assigned penalties or only on the original payment? This formula only does the original payment.
engineer

1
Tue 12 Jan, 2010 10:24 am
@engineer,
Same setup, but if you want to

a) Charge 5% per day for all days after 10
b) Start charging 1.5% interest per month on the entire amount owed including penalties after 30 days, compounded daily

late fee = IF(B1>10,(B1-10)*5%*A1,0) + IF(B1>30,2*A1*((1+1.5%/30)^(B1-30)-1) + 0.05*A1*(((1+1.5%/30)^(B1-30)-1)*30/1.5%) - (B1-30)*5%*A1,0)
engineer

1
Tue 12 Jan, 2010 10:35 am
@engineer,
Here's a more elegant way using functions built into Excel

total amount owed when you get past 30 days = FV(1.5%/30,B1-30,5%*A1,2*A1)
erthtyme

1
Wed 28 Dec, 2011 04:34 pm
@engineer,
ok, so here is my situation.
My tenants, divorced, I let the wife & baby stay, she paid half the rent, he paid none. my lease calls for late fees of 100 @ 5 days, 100 at 10, 100 at 15 & 200 at 20 if not paid in addition to the rent owed.
so for june 2011, they(both signed the lease) owe;
1125 (half the payment
100
100
100
200
total is 1625
there were late fees in July for not being paid until the 26th, when total rent was paid.
1/2 + the 500 for Aug & Sept.
total of all these is now 5375 plus the interest.
not sure what the b1, etc are for
thanks
0 Replies

### Related Topics

Clone of Micosoft Office - Question by Advocate
Do You Turn Off Your Computer at Night? - Discussion by Phoenix32890
The "Death" of the Computer Mouse - Discussion by Phoenix32890
Windows 10... - Discussion by Region Philbis
Surface Pro 3: What do you think? - Question by neologist
Windows 8 tips thread - Discussion by Wilso
GOOGLE CHROME - Question by Setanta
.Net and Firefox... - Discussion by gungasnake
Hacking a computer and remote access - Discussion by trying2learn

1. Forums
2. » How to calculate aging late fees in excel????