Jump to content

Calcing compound interest on aged invoices


This topic is 7357 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I can't figure out how to apply a compound interest calc to cuming late fees for overdue invoices. If I could apply a calc to a previous balance, it seems like it would be simple.

Problems: While the period is 1 month, not every client is billed every period. Also, payments come in willy nilly and may or may not apply to the most recent invoice, and may not be for the whole amount.

I've searched the forum for compound interest examples and come up with a wonderful post by zincb from a couple of years ago, here.

This thread also offers a good solution, but it doesn't work for my circumstances.

To illustrate, here is some sample data ------------

We charge 1 percent a month on overdue balances now, but will change to 1.5 percent soon.

9/5/00 balance forward 1710

10/17/00 payment -450

11/6/00 invoice 460

1/3/01 invoice 360

3/6/01 invoice 360

3/26/01 payment -450

5/31/01 payment -450

7/2/01 invoice 360

8/3/01 payment -450

8/6/01 invoice 360

10/17/01 payment -350

(This place is great, by the way. Searching for an answer to my problem I have come across some brilliant stuff!)

Link to comment
Share on other sites

My preferred way to do this is have a Client Accounts file that has records for invoices, payments, refunds and interest--essentially anything that would get charged or credited to the client.

Whenever an invoice is issued, a record is created in the Client Accounts file with fields for date, amount, and client ID.

Whenever a payment is received, a record is created with the date, amount and client ID.

At the end of the month, before you enter your new invoices (or else find all items more than month old), you make sure that all received payments have been entered. Do a balance for each client. For clients that have an outstanding balance, create a new interest record and set the amount to the balance times the monthly interest rate. Finally, enter your new invoices (or find all records if the invoices are already entered) and get the grand total for each client. You can then issue monthly statements out of this file. Next month, if an outstanding account is still unpaid, you add another interest record for that client, and it will include interest on the previous month's interest item, so the interest will automatically compound. It's easy to change the interest rate because the old interest items won't change, but new interest records will be entered at the new rate.

This can all be scripted, of course.

Link to comment
Share on other sites

Thanks, BobWeaver for your quick and clear reply. I've tried to do something similar but lost steam. Knowing that it works may give me the juice I need to get through it.

Would you have a separate balance file that is wiped clean each month and rebuilt with a script?

It seems that the simpler example above would be the calc to use. "BaseAmount * ((1+(Interest/100)) ^ Term)"

(Ehem, sorry about breaking the forum display with my long url. Any idea what I did wrong? I thought I followed the form on the help page. )

Link to comment
Share on other sites

Teela Brown said:

(Ehem, sorry about breaking the forum display with my long url. Any idea what I did wrong? I thought I followed the form on the help page. )

Hi Teela,

I don't think it was you, assuming you used the URL button in the Reply window. I was going to send you a link on how to do this and my post, in the preview, look like just like yours. This must be one of those things that broke over the weekend and Steven needs to fix.

HTH

Lee

Link to comment
Share on other sites

If you use a formula like BaseAmount * ((1+(Interest/100)) ^ Term) then you will have problems on outstanding balances if you change the interest rate, because all the existing balances will now recalculate with the new rate. That's why I prefer to add interest charges each month as individual items. Then, if you change the rate, it won't affect previous interest charges.

To elaborate a bit more on the client accounts file approach, you don't clear it each month, you just keep adding in the invoices, payments and interest items from month to month. That way, if there are any outstanding amounts, they will always show up. The file will grow as time goes by. You can handle this in several ways. My preferred way is to find all records more than 6 months old, sort/summarize by client, total them up, and replace them with a single opening balance record for each client.

Link to comment
Share on other sites

"My preferred way is to find all records more than 6 months old, sort/summarize by client, total them up, and replace them with a single opening balance record for each client. "

Brilliant Bob, I'd have never though of that. Another one for my personal book of tips and ideas... thanks!

Link to comment
Share on other sites

This topic is 7357 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.