Late fee and accrued interest formula set up

Forums: Computers, Excel, Excel Formulas
Email this Topic • Print this Page

I need help on setting this up in excel.
1. All invoices terms = net 30
2.Late payment fee on invoice is 7% of fees due due on each invoice
3.All past due items will accrue interest at 1.5% per month, calculated daily and compounded monthly
4. To date 7/15/20, no payment received
5. Arrive at all late payment fees and accrued interest through 6/30/20 and at 7/15/20.

ex: Inv date = 6/30/19, Inv amt = $10,200

Can someone help me please!?
Thank you thank you thank you!!!!

Topic Stats
Top Replies
Link to this Topic

Type: Question • Score: 0 • Views: 1,288 • Replies: 3
Topic Closed

No top replies

@sooz,

The late fee is 7%x$10200 = $714
There are 31 days in July so the interest per day is $10,200 x 1.5% / 31 = $4.94/day assuming you do not pay interest on the late fee.
Since you compound monthly, you don't need to worry about that until you get into August.

1 Reply

@engineer,

Thank you for the reply!

the invoice was from 2019

so inv date=6/30/19, first due month is 7/30/19. From 7/30/19 to now 7/15/2020, How to set up the formula for the past months and accrued interest on that?

july 2019: 10,200 * 1.5% = 153
aug 2019: (10,200 + 153)* 1.5% = 155.30
sept 2019:
to june 2020 is twelve months

Again thank you in advance!

1 Reply

@sooz,

Are you looking for a formula or a one time calculation?

The amount owed after 12 months is AMT x (1 + 1.5%)^12. AMT is either the the amount due or the amount due plus the late fee depending on if you pay interest on the fee. You'd have to calculate the 15 days of interest after that.

0 Replies

Late fee and accrued interest formula set up

Related Topics

Quick Links

My Account

able2know