Interest

[Stickybeak]

I need to calculate the amount of interest payable on an outstanding bill.

I am only allowed to charge interest at a prescribed rate.

The rate I am allowed to charge varies from time to time.

For a long outstanding invoice there may 2 or 3 or more interest rates applicable for specific periods during the time when the bill has been unpaid.

Any suggestions as to how I work out the interest?

[comment]

This is a recursive calculation, and since you do not have the Advanced version it would probably be best to script it.

I don't think we have enough information here to provide a more detailed answer. What method is to be used for pro-rating the periods? Is there any compounding of interest?

[Stickybeak]

No compounding

Not sure what you mean by pro-rating the periods.

[comment]

Suppose you have a debt of $100 running from Mar 15 until today. And suppose you have these rates:

from Jan 1: 5%

from May 1: 4%

from Sep 1: 3%

These rates are annual so the 4%, for example, needs to be pro-rated from one year to 4 months. Or from 365 days to 123 days. Or from 360 days to 4 * 30 = 120 days. Or...

http://en.wikipedia.org/wiki/Day_count_convention

[Stickybeak]

I didnt know that there was more than one method for pro rating - unless you mean by days, fractions of months or years.

I would need to do it by days

[comment]

I would need to do it by days

I am afraid that's not sufficient. I suggest you consult an accountant or a lawyer regarding the exact method of pro-rating.

At any rate, here's a brief outline of the process:

First, define a global gDate field in the Debts table, and a relationship:

Debts::gDate = Rates::FromDate

Now, start your script by initializing gDate to current date (this thing works backwards).

For each period:

1. Get the applicable rate =

LookupNext ( Rates::Rate ; Lower )

2. Calculate the period's starting date =

Max ( LookupNext ( Rates::FromDate ; Lower ) ; Debts::StartDate )

3. Calculate the number of days in this period:

Debts::gDate - $startingDate + 1

Now set gDate to $startingDate - 1 and repeat the process until gDate ≤ the debt's start date.

[Stickybeak]

Thanks

I am a lawyer

[comment]

LOL, then you should know better...

Seriously, different pro-rating methods produce different results. If there is a maximum allowed rate, then one pro-rating method may be considered as overcharging when compared to another.

[Stickybeak]

Its a statutory rate that may be charged on unpaid fees - this is Australia: near enough is fantastic

[Stickybeak]

I am afraid that's not sufficient. I suggest you consult an accountant or a lawyer regarding the exact method of pro-rating.

At any rate, here's a brief outline of the process:

First, define a global gDate field in the Debts table, and a relationship:

Debts::gDate = Rates::FromDate

Now, start your script by initializing gDate to current date (this thing works backwards).

For each period:

1. Get the applicable rate =

LookupNext ( Rates::Rate ; Lower )

2. Calculate the period's starting date =

Max ( LookupNext ( Rates::FromDate ; Lower ) ; Debts::StartDate )

3. Calculate the number of days in this period:

Debts::gDate - $startingDate + 1

Now set gDate to $startingDate - 1 and repeat the process until gDate ≤ the debt's start date.

If I use a global field wont that calculate an interest component on all debts - I need to charge the interest to specific invoices and there are multiple invoices per client

[comment]

I am not sure I understand the question. Presumably, each invoice has its own start date and amount - so the interest needs to be calculated separately for each invoice.

[Stickybeak]

I am not sure I understand the question. Presumably, each invoice has its own start date and amount - so the interest needs to be calculated separately for each invoice.

I don't follow where the global field comes in

[comment]

The global field is used by the script as a temporary register. The relationship based on the global enables the script to get the correct rate for each period.

[Stickybeak]

The global field is used by the script as a temporary register. The relationship based on the global enables the script to get the correct rate for each period.

I am working on this: wont this technique have a difficulty if an invoice is part paid?

[comment]

It depends. The technique works on a single debt with a start date and end date (here assumed to be today), and a constant amount. If an invoice is partially paid at the start date, then only the remaining balance is a debt. However, if a partial payment is made somewhere in-between, then you have two debts to deal with.

This wouldn't be too difficult if the rate were constant. But in your situation, you must calculate the portion of the original debt that was settled by the partial payment (since the payment includes the interest for this portion). This is the opposite of the above process.

Once you do that, you can subtract that portion from the original debt and continue with only the balance as the debt.