Variable Interest

The Variable Interest feature enables you to easily and automatically calculate variable interest rates on your accounts based on an underlying interest rate index table. You can have one or several Principal transactions on each account. Collect! uses the Payment Date of each Principal transaction, plus 1 day, for the initial "Calculate interest from date." Subsequent calculations are read from the date ranges and interest rates contained in the interest rate index.

Variable Interest is a licensed extension module.

If you are entering Judgements in Collect!, please examine your financials carefully when enabling Variable Interest.

There are several parts to the Variable Interest feature:

• Variable Interest Rate Index
• Client Settings
• Debtor Interest Detail
• Examining the Breakdown

We will now go into detail for each of these areas.

Variable Interest Rate Index

The interest rate index is a CSV file that you create with two columns, representing the Interest Start Date and Interest Rate. Enter the date ranges and interest rates that you require. Each Interest Start Date is the date you want Collect! to start using the interest rate listed in the corresponding Interest Rate column.

For Example:

These columns do not have labels. The column headings above are for demonstration purposes, only.

Sample CSV

Each entry has to be on its own line.

"01/01/2008","7.50"
"06/01/2008","8.65"
"01/01/2009","9.75"
"03/01/2009","9.80"
"06/01/2009","9.90"
"09/01/2009","10.0"

Even if you have not entered your dates in a strictly ascending order, Collect! will sort earliest to latest when it reads the interest rates file.

Save your settings as a CSV in the cv11\bin\vinterest folder.

After editing the interest rates table and saving your CSV, it is necessary to sign out of Collect! completely to read the new changes.

Date Format

Collect! uses the settings from the Date Format form when reading the variable interest CSV.

Date Format may be accessed through System-> Preferences-> Options, Sounds and Colors. Select the DATE FORMAT button and ensure that the dates in your CSV match the format you have chosen for your dates in Collect!.

Client Settings

Next, you need to specify which clients will use this interest rate index.

In the Client Settings, enter the name of the CSV in the Variable interest rates file field.

Variable Interest Rates File

If you have several clients with different requirements, you can create an interest rate index CSV for each one. Just name each uniquely and place them all in the cv11\bin\vinterest folder.

Sign out of Collect!
After this, it is necessary to sign right out of Collect! and back in, so that your new interest rate table will be ready to use.

Debtor Interest Detail

Please examine any Principal Transactions existing on each account and ensure that you agree with the date in the Payment Date field. Collect! uses the Payment Date of each Principal transaction, plus 1 day, for the initial "Calculate interest from date."

Principal Transaction

In the Interest Detail for each Debtor, select Variable Interest from the Interest Type pick list.

Interest Type Variable Interest

Collect! hides most of the other interest settings. You can select the appropriate Rate Basis.

Select "Ordinary" if you want Collect! to dynamically calculate the interest taking into consideration the number of days in each year. If you select one of the other options for Rate Basis, this will be the value Collect! will use for every interest period in your Variable Interest Rates file, regardless of the actual number of days in the year in question.

As soon as you close the Financial Detail form, Collect! calculates the interest for the account and displays it in the Debtor's Interest field.

Total Interest Calculated and Displayed

The calculation of interest is dynamically reset according to your settings. So you must RECALC the Debtor to see any new changes.

When using Variable Interest, you should recalc all your accounts each day.

Examining Variable Interest Rate Breakdown

To see exactly how Collect! arrives at the total interest you see displayed on your accounts, you can switch on Collect!'s debugging option and a summary will be displayed whenever you view the Debtor.

Interest Calculation Breakdown

When you have more than one Principal transaction, Collect! shows you the breakdown for each one.

Interest Calculation for Two Principal Amounts

To enable the summary window to display, you must switch on Enable internal debug checks in the Options, Sounds and Colors menu. Access this through System, Preferences. Select the OPTIONS, SOUNDS AND COLORS button and place a check mark in "Enable internal debug checks."

This switch is meant for debugging only. It should not be left on for normal usage of Collect!.

Variable Interest Calculations

Variable Interest calculations use the formula:

I = A((1+R/N))^n)-1)

I = Interest due
A = Assessment amount (Ex. 1827.00)
R = Annual interest rate (Ex. 9.00)
N = Number of days in the year (Ex. 365)
n = Number of days in the date window (Ex. 180)

Example:

On $1827 at 9%:

01/01/2009 -> 06/30/2009 (180 days) at 9.00% = $82.90

82.90 = 1827((1+.09/365)^180)-1)

The number of days in the year (N) is determined by the Rate Basis you set in the Interest Detail. Select "Ordinary" if you want Collect! to determine the number of days in each year. Select one of the other options (360, 364 or 365) if you want to use a fixed number of days in the year for every interest period in your table.

When using Variable Interest, you should RECALC your accounts every day.

For debugging purposes, the calculations may be displayed when one opens the Debtor form as demonstrated above. Rates are set as soon as accounts are recalculated either by opening a Debtor, or through Tools, Recalc.

Interest is simple interest, floating point values are truncated Interest detail fields are hidden and calculation is based solely on the CSV interest rate table.

Collect! uses the Principal transaction's Payment Date plus 1 day as the Calculate Interest Start Date.

Multiple Principal transactions are each calculated separately from Payment Date plus 1 day and totals are added into the Interest Field on the Debtor form. Debug Recalc displays the calculations for each Principal for debugging purposes.

Payments

Just like other interest modes in Collect!, when a payment is posted to an account, it will affect the Variable Interest calculations if it reduces the account's outstanding Principal. Any payment that reduces the total Principal will result in interest being calculated on the new Principal amount.

You can control payment breakdown with options set in the Transaction Type form.

Troubleshooting Variable Interest

There are a few situations that may result in an error when using the Variable interest feature. These are explained below.

Failure Reading CSV File
This error may occur if you have failed to create the CSV properly. The format must be standard, as displayed above. If there is a typo in any of the cells, you will get this message.

Invalid Line in Interest CSV

The message shows you the error. There is no comma between the values for the date and the rate. in this particular case.

Failure Opening CSV File
This message usually indicates a file named differently from what you have in your Client Settings. Please ensure that the Client's interest CSV configuration matches the file stored in your cv11\bin\vinterest folder.

Failure Opening CSV File

See Also

- Interest Detail
- Client Settings