0
   

Late fee and accrued interest formula set up

 
 
sooz
 
Reply 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

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

  • Topic Stats
  • Top Replies
  • Link to this Topic
Type: Question • Score: 0 • Views: 133 • Replies: 3
No top replies

 
engineer
 
  2  
Reply 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  
Reply Thu 16 Jul, 2020 11:38 am
@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!




engineer
 
  2  
Reply 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
Copyright © 2020 MadLab, LLC :: Terms of Service :: Privacy Policy :: Page generated in 0.03 seconds on 08/08/2020 at 12:50:00