Reports for Installment Loans

Galileo currently offers three standard reporting files for installment loans:

Daily Loan Status — A list of the current loans, subproducts, statuses, and installment due dates
Daily Transaction — Details on daily transactions related to loans
Daily Loan Installments Status file — Details on the status of the installments

Reports are securely delivered as a CSV file each day. The reports contain invaluable data to reconcile your loans and transactions data for that day. It can also be used for reporting purposes to your bank partner.

Accessing the report files

The reports are generated daily and contain loan activity from the previous calendar day, 00:00:00 (midnight) to 23:59:59 Galileo system time.

In the event there is a delay in report generation, the report will include any loans that were charged off during that window. For example, if the system started running on January 1st at 12:10:00 MT, and Galileo receives an API request to charge off the loan at 12:01:00 MT on January 1st, the charge off will be recorded in the Daily Loan Status report, and the loan closed date will be January 1st. This is to ensure clients maintain an accurate ledger.

Galileo uses Secure File Transfer Protocol (SFTP) to deliver the reporting files for installment loans. You can arrange to retrieve the files from Galileo's SFTP server or Galileo can push the files to your SFTP server. Coordinate with Galileo to get an account name and password.
Reports filenames have the following format:

daily_loan_status_file__v2_yyymmdd.csv
daily_transaction_file_yyymmdd.csv
daily_loan_installment_status_yyymmdd.csv

If there is no activity in your loan during a calendar day, you will not receive the files.

Daily Loan Status file

This table contains details about the fields included in the Daily Loan Status file. The reports are loan ID specific, meaning there could be multiple active loan_IDs associated with the same Customer_Prn in a daily report.

The fields in the daily file are presented in this order.

Field Name	Type	Length	Description
`Loan_Id`	string	30	Galileo-generated identifier for the loan.
`Customer_Prn`	string	12	The PRN for the customer’s primary account.
`Subproduct_Code`	string	15	Galileo-generated identifier for the loan subproduct assigned to the simulation or loan, as configured for the program. For programs with multiple subproducts, the choice would be determined by underwriting.
`Loan_Status_Description`	string	13	Current status of the loan. See the Loan Payment Status and Loan Status Table for detailed descriptions of each status.
`Loan_Payment_Status`	string	4	Current loan payment status code. See the Loan Payment Status and Loan Status Table for detailed descriptions of each status.
`Loan_Apr`	float	16(2)	Annual percentage rate for interest charged on the loan. Calculated from the interest defined by the subproduct, the length of the loan, and the origination fee (if applicable).
`Loan_Start_Date`	date	8	Date the funds are disbursed and interest, if configured, begins to accrue. Format: `YYYYMMDD`
`Loan_Start_Today_Flag`	string	5	Returns `TRUE` if the loan started today; otherwise, returns `FALSE`.
`Interest_Rate`	float	16(2)	Interest rate applied to the loan, as defined by the subproduct.
`Per_Diem`	float	16(2)	Daily interest charged on the loan. This amount will update as installments are paid.
`Scheduled_Installment_Amount`	float	16(2)	Payment due at each installment.
`Loan_Term`	integer	9	Total count of installments, as defined by the subproduct.
`Original_Loan_Amount`	float	16(2)	Total loan amount borrowed by the customer.
`Principal_Balance`	float	16(2)	Current principal balance remaining on the loan.
`Principal_Paid_To_Date`	float	16(2)	Principal amount of the loan that is paid off.
`Interest_Paid_To_Date`	float	16(2)	Interest amount of the loan that is paid off.
`Fees_Paid_To_Date`	float	16(2)	The fee amount the customer has paid to date.
`Total_Payoff_Amount`	float	16(2)	Total amount to pay off the remaining loan if the customer paid it off today. Includes the remaining principal amount, interest, and fees.
`Maturity_Date`	date	8	The projected date the loan can be paid off by. This is a dynamic value that is based on the customer's actual payment activity. Format: `YYYYMMDD`
`Closed_Date`	date	8	Date that the loan was closed. Value returned if the loan is paid off or charged off. Otherwise, it returns null. Format: `YYYYMMDD`
`Paid_Installments`	integer	2	Number of installment payments that the customer has made on the loan.
`Remaining_Installments`	integer	2	Number of installment payments remaining on the loan.
`Non_Accrual_Flag`	string	5	Returns `TRUE` if interest is not accruing on the loan. Otherwise, returns `FALSE`.
`Delinquency_Date`	date	8	Date when the oldest unpaid installment became past due. Format: `YYYYMMDD`
`Past_Due_Amount`	float	16(2)	The installment amount that is past due for the loan.
`Days_Past_Due`	integer	9	The number of days that the loan is delinquent. When the loan is charged off, this value resets to 0 because the loan is officially closed.
`Loan_Status_Reason_Code`	string	32	The reason provided for charging off the loan, as provided in the API request.
`Total_Charge_Off_Amount`	float	16(2)	The total amount of the loan that was charged off.
`Principal_Charge_Off_Amount`	float	14(2)	The fee amount that was charged off along with the loan.
`Fees_Charge_Off_Amount`	float	14(2)	The fee amount that was charged off along with the loan.
`Fees_Assessed_To_Date`	float	14(2)	Fees assessed against the loan till date.
`Owner_Id`	string	30	Indicates the ownership of the loan assets.

Versions

There are two supported versions of this file: 1.0 and 2.0. All new clients are set up on version 2.0 (documented above). For clients on version 1.0, you can access the version here.

Loan Payment Status and Loan Status table

This table shows fields returned in Loan_Payment_Status and Loan_Status_Description in the Daily Loan Status file.

Loan payment status code	Loan status	Overall loan payment description
`OCUR`	`Valid`	Open and payments are current.
`OPDE`	`Valid`	Open, but at least one payment is past due.
`PYOF`	`Completed`	Paid off
`CGOF`	`Charge Off`	Charged-off
`PNDG`	`Pending`	Loan has been simulated but not created.
`EXPD`	`Expired`	Loan is past the maturity date, and at least one of the installment is unpaid and past due.

Daily Transaction file

This table contains details about the fields included in the Daily Transaction file. The fields in the daily file are presented in this order.

Field Name	Type	Length	Description
`Loan_Id`	string	30	Galileo-generated identifier for the loan.
`Product_Code`	string	5	Indicates the loan product. Currently the only possible value is `90000`.
`Subproduct_Code`	string	15	Galileo-generated identifier for the loan subproduct assigned to the simulation or loan as configured for the program. For programs with multiple subproducts, the choice would be determined by underwriting.
`Branch_Code`	string	3	Indicates the assigned bank branch.
`Institution_Code`	string	4	The fintech identification number.
`Currency_Code`	string	3	Three-letter ISO 4217 code is provided for the currency. Currently it only supports USD.
`Txn_Code`	string	4	Specifies the type of transaction. Possible values: `9001` — Loan advance `9099` — Advance reversal `9002` — Regular payment `9098` — Payment reversal `9020` — Payoff `9024` — Charge off `9006` — Past due payment `9005` — Installment past due
`Reason_Code`	string	4	Specifies whether the transaction source is an external or internal DDA account. `3` — External `9999` — Referential code to indicate internal loan activities
`Transaction_Amount`	float	16(2)	Amount of the transaction.
`Amount_Type`	string	2	Specifies whether the transaction was related to principal, interest, or fees. Possible values: `4` — Principal `5` — Interest `64` — Fees
`Charge_Code`	string		Specifies the fee type that is applied to the transaction. Possible values: `8` — Origination fee `9` — Late fee
`Situation`	string	11	Indicates whether interest is accruing on the loan. Possible values: `Accural` `Non_Accural`
`Business_Date`	date	8	Date the transaction became effective. Format: `YYYYMMDD`
`Value_Date`	date	8	Date the transaction became effective. Format: `YYYYMMDD`
`Payment_Id`	string	30	Galileo-generated unique identifier for the payment on the loan. This is the same field as `loan_payment_number`, which is provided in the Create Loan Payment API response.
`Payment_Source_Code`	string	4	Specifies the source of the payment on the loan. Possible values: `USRP` — User-initiated payment `SYSP` — System-initiated payment `CRDP` — Credit payment `CSRP` — Customer service payment `IVRP` — IVR payment `PCRP` — Provisional credit payment `MCRP` — Merchant credit payment `RCRP` — Refund credit payment `DFRL` — Dispute fraud loss `DWRO` — Dispute write off `ADLP` — Action on delinquent loan payments Note: There is no `Payment_Source_Code` associated with a charged off loan because a charge off is considered a transaction, not a payment. \|

Allocation of payment amount in Daily Transaction file

When a customer makes a payment, they first pay the interest, then the principal, and lastly the fees for the current installment. If there is an overpayment, after making the current installment payment, the remaining amount is allocated first to the interest of the next installment, then the principal and the fees.

For the Daily Transaction file, this appears differently—the transactions will have separate rows. The transaction lines are separated by transaction codes for interest, principal, and fees. If there is an overpayment, the Daily Transaction file will include a line for each interest, principal, and fees depending on the amount due in the installment. If the transaction amount is $0.00, it will not be included. The loan_payment_number for all the transactions will be the same, which indicates that the customer made a single payment for multiple installments.

For example, a customer made a payment of $205.00. The current installment due for the customer was $105.00. This breaks down to:

$25.00 towards the interest
$75.00 towards the principal
$5.00 for the fees on that installment

In the Daily Transaction file, you can expect to see the following:

Transaction line for the interest paid ($25.00) for current installment
Transaction line for the principal paid ($75.00) for current installment
Transaction line for the fees paid ($5.00) for current installment
Transaction line for the interest paid ($25.00) for next installment
Transaction line for the principal paid ($75.00) for next installment

Since the fee amount for the next installment is $0.00, the transaction line for will not be included.

Daily Loan Installments Status file

This table contains details about the fields included in the Daily Loan Installment Status file. The fields in the daily file are presented in this order.

Field Name	Type	Length	Description
`Loan_Id`	string	30	Galileo-generated identifier for the loan.
`Installment_Number`	string	2	The installment number of the loan.
`Installment_Status`	string	Max: 9, Min: 4	The status of the installment.
`Payment_Due_Date`	date	8	The date the installment is due. Format: `YYYYMMDD`.
`Payment_Amount`	float	14(2)	The installment amount derived from amortization schedule`.
`Remaining_Balance`	float	14(2)	The amount pending to be paid in this installment.