0

# Late fee and accrued interest formula set up

Thu 16 Jul, 2020 10:48 am
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

Thank you thank you thank you!!!!

• Topic Stats
• Top Replies
Type: Question • Score: 0 • Views: 335 • Replies: 3
No top replies

engineer

2
Thu 16 Jul, 2020 10:54 am
@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.
sooz

1
Thu 16 Jul, 2020 11:38 am
@engineer,

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

engineer

2
Thu 16 Jul, 2020 12:35 pm
@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

### 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. » Late fee and accrued interest formula set up