Loan-Level Reconciliation
Loan-level reconciliation compares a lender's outstanding balance report against Fineract's loan data to identify discrepancies in principal, interest, and fees.
How It Works
- Upload a lender's outstanding balance CSV
- The system fetches each loan's outstanding amounts from Fineract
- Compares the two and flags mismatches
Using Loan-Level Recon
1. Select Cutoff Date
Choose the date the lender report was generated. This is used for reference only - the comparison uses current Fineract balances.
2. Upload Lender CSV
Click Upload Lender CSV and select the file. Expected columns:
| Column | Description | Example |
|---|---|---|
loan_id | Fineract loan external ID | VCPL006546 |
outstanding_principal | Lender's principal balance | 308101.61 |
outstanding_interest | Lender's interest balance | 102871.99 |
outstanding_fees | Lender's fee balance | 0 |
A template CSV can be downloaded using the Template button.
3. Review Results
Summary cards show:
- Total Loans - number of loans in the CSV
- Matched - amounts match between lender and Fineract
- Mismatched - amounts differ (highlighted in red)
- Not Found - loan doesn't exist in Fineract
The comparison table shows side-by-side:
- Lender principal vs Fineract principal
- Lender interest vs Fineract interest
- Lender fees vs Fineract fees
Mismatched values are highlighted in red. "Not Found" rows are highlighted in amber.
4. Filter & Export
- Filter by: All, Matched, Mismatched, Not Found
- Export downloads the full comparison as CSV
Status Values
| Status | Description |
|---|---|
| Matched | All three components (principal, interest, fees) match |
| Mismatched | One or more components differ between lender and Fineract |
| Not Found | Loan external ID not found in Fineract |
API Endpoints
| Method | Path | Description |
|---|---|---|
POST | /api/reconciliation/loan-level/compare | Upload CSV and compare |
GET | /api/reconciliation/loan-level/latest | Get most recent comparison |
GET | /api/reconciliation/loan-level/{id}/export | Download comparison as CSV |
Fineract Report
The loan-level recon uses a registered Fineract SQL report (Loan Outstanding By Date) to fetch outstanding balances. This report is registered using:
bash
./venv/bin/python scripts/register_loan_outstanding_report.py