• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Calculating late charges in Excel

by on
in Admin Pro Forum

Question: “We have several rental properties, and I track payments, late charges, customer information, etc., on an Excel spreadsheet. Included on the spreadsheet is the date rental payments are received. I would like to create a formula that would add a late fee for any payments that are received after the 10th day of the month due.

“The idea is to keep a running ‘balance due’ for each customer that would automatically calculate any late charges based on the date payment is received. Ultimately, the ‘balance due’ will be linked to each customer’s monthly invoice. I would appreciate any ideas for how to do this.”  -- Anonymous


“First, make sure you have a column to calculate the difference between the date due and the date received. That formula should read ‘=D2-C2,’ where D2 is the date received and C2 is the date due. Be sure to format that column as ‘Number’ when formatting cells.

“Second, the late charges can be calculated using an ‘IF’ statement. The syntax is ‘=IF(E2>9, “E2*25”,0)’ where E2 is the difference between the date received and the date due. To calculate a penalty of $25 per day overdue, the difference should be greater than nine; otherwise, no penalty is calculated.

“Finally, add the cell containing the IF statement to the normal balance due to get the total. For example, the Total Balance Due column would be ‘=F2+G2’ where F is the normal amount due and G is the penalty amount.”

Like what you've read? ...Republish it and share great business tips!

Attention: Readers, Publishers, Editors, Bloggers, Media, Webmasters and more...

We believe great content should be read and passed around. After all, knowledge IS power. And good business can become great with the right information at their fingertips. If you'd like to share any of the insightful articles on BusinessManagementDaily.com, you may republish or syndicate it without charge.

The only thing we ask is that you keep the article exactly as it was written and formatted. You also need to include an attribution statement and link to the article.

" This information is proudly provided by Business Management Daily.com: http://www.businessmanagementdaily.com/15418/calculating-late-charges-in-excel "

{ 1 comment… read it below or add one }

Paul December 30, 2011 at 12:37 pm

I used this formula while building an amortization sheet, the information on this post was very helpful!


Leave a Comment