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

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.

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)

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)

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