How To Use The Financial Calculation Log
The Financial Calculation Log is a tool that allows you to review the financial calculations for
an account.
When a recalculation is done on an account, the recalc process outputs the data to a text file.
The contents of the text file can be copied and pasted in to Microsoft Excel.
Enabled The Calculation Log
Enabling the Calculation Log is simply adding /calclog to your Collect! shortcut.
- Right-click your Collect! shortcut.
- Select Properties.
- Click in to the Target field and move the cursor to the end.
- Add a space and type in "/calclog" without the quotes.
- Click OK to save.
Run A Recalculation On An Account
- Navigate to the Debtor account that you want to review.
- Click the Recalc button.
- Leave the account open.
Open The Text File
- Navigate to your Collect\bin folder.
- Open the calclog.txt file with a text editor.
Copy The Data To Excel
- Use Ctrl + A to select all of the contents of the calclog.txt file.
- Use Ctrl + C to copy the text.
- Open a Blank Workbook in Excel.
- With the cursor in cell A1, use Ctrl + V to paste the text.
- Highlight the following Columns:
- C - date
- D - newCalcDate
- Q - calcDate
- Use Ctrl + 1 to open the Format Cells dialog box.
- Select the Date Category.
- Select the Date Type that you prefer.
- Click OK.
Review The Results
The Excel file is divided into 3 main sections:
- Transaction Data (Columns B to O)
- Running Totals (Columns S to AG)
- Excel Formulas (Columns AI to AL)
Transaction Data
This data comes directly from the Transactions in Collect!. A START and END entry are added to show
where Collect! starts and ends the interest calculations. Typically, the Start Date should match the
Original Principal date, depending on how you enter your data.
| Column Letter |
Column Name |
Description |
| B |
type |
Financial Type |
| C |
date |
Payment Date |
| D |
newCalcDate |
Date to use for transactions that update interest. |
| E |
newRate |
Records the new rate when a 499 - Accrued Interest transaction is posted. |
| F |
value |
To Us + Direct |
| G |
principal |
Changes to the total principal on the account, such as Original Principal. |
| H |
interest |
Changes to the total interest on the account, such as Original or Accrued Interest. |
| I |
toInterest |
Payment to Interest. |
| J |
toPrincipal |
Payment to Principal. |
| K |
toFees |
Payment to Fees. |
| L |
toLegal |
Payment to Legal. |
| M |
toAdjust |
Payment to Adjustments. |
| N |
toMisc |
Payment to Misc. |
| O |
toOther |
Payment to Other. |
Running Totals
This section has cumulative running totals for each financial type charge and payment. This is useful
when trying to review Payment Breakdown.
Entries for Judgements will reset the totals to 0.00 as Judgements overrule all prior financials.
| Column Letter |
Column Name |
Description |
| Q |
calcDate |
Date used for financial calculations. |
| R |
calcRate |
Current Interest Rate |
| S |
totalPayment |
Cumulative Sum of Payments |
| T |
totalInterest |
Cumulative Sum of Interest |
| U |
totalPrincipal |
Cumulative Sum of Princial |
| V |
totalFees |
Cumulative Sum of Fees |
| W |
totalLegal |
Cumulative Sum of Legal |
| X |
totalAdjust |
Cumulative Sum of Adjustments |
| Y |
totalMisc |
Cumulative Sum of Misc |
| Z |
totalOther |
Cumulative Sum of Other |
| AA |
totalToInterest |
Cumulative Sum of Payments to Interest |
| AB |
totalToPrincipal |
Cumulative Sum of Payments to Principal |
| AC |
totalToFees |
Cumulative Sum of Payments to Fees |
| AD |
totalToLegal |
Cumulative Sum of Payments to Legal |
| AE |
totalToAdjust |
Cumulative Sum of Payments to Adjustments |
| AF |
totalToMisc |
Cumulative Sum of Payments to Misc |
| AG |
totalToOther |
Cumulative Sum of Payments to Other |
Excel Formulas
This section has Excel formulas that reference the Running Totals section to mathematically compare
the data to Collect!'s internal calculations.
| Column Letter |
Column Name |
Description |
| AI |
OWING |
=SUM(T{row}:Z{row})-S{row}:
Sum of cumulative charges less cumulative payments |
| AJ |
PERIODS |
=IF(AND(B{row}<>"JUD PRINCIPAL",OR(Q{row-1}days since the last transaction
or event. |
| AK |
INTEREST |
= IF(AND(AJ{row} > 0,(U{row-1}-AB{row-1}) >= 0),(R{row-1}/365/100) * AJ{row} * (U{row-1} -
AB{row-1}),0):
If the number of periods is greater than 0, and the outstanding principal amount is greater
than or equal to 0, then calculate the accrued interest based on Rate divided by 365 and
100, then multiplied by number of days and outstanding principal, as of the last transaction
or event. |
| AL |
ACCRUED |
=T{row}-AA{row}:
Sum of cumulative interest less cumulative payments to interest. |
| 
Was this page helpful? Do you have any comments on this document? Can we make it better? If so how may we improve this page.
Please click this link to send us your comments: helpinfo@collect.org