Loans Outstanding Report

Overview

The Loans Outstanding Report provides a comprehensive snapshot of all active loans, showing outstanding principal and interest, expected payments, arrears, and payment history for each loan in the portfolio.

Permission required: reports:readThis report is generated from loan metadata, installment schedules, and ledger account balances. Only loans with outstanding principal > 0 are included.

API Endpoint

Get loans outstanding report:

GET /reports/loans-outstanding?expectedDate={date}
Headers:
  x-organization-id: {organizationId}

Query parameters:

Parameter	Type	Required	Description
`expectedDate`	string (ISO date)	No	Date to calculate balances and arrears. Defaults to current date.

Example request:

curl -X GET "https://api.agatabo.com/reports/loans-outstanding?expectedDate=2026-06-30" \
  -H "x-organization-id: org-abc123" \
  -H "Authorization: Bearer {token}"

Response Structure

{
  "asOfDate": "2026-06-30T00:00:00.000Z",
  "totalOutstanding": 12950000.00,
  "totalExpected": 8500000.00,
  "totalArrears": 500000.00,
  "loans": [
    {
      "loanId": "loan-123",
      "accountNumber": "LOAN-001",
      "organizationUserId": "orguser-456",
      "organizationUserName": "Jane Smith",
      "organizationUserAccountNumber": "SAV-002",
      "loanDate": "2025-12-01T00:00:00.000Z",
      "termMonths": 12,
      "outstandingBalance": 415000.00,
      "monthlyInstallment": 43750.00,
      "expectedByDate": 306250.00,
      "arrears": 50000.00,
      "lastRepaymentDate": "2026-05-15T00:00:00.000Z"
    },
    {
      "loanId": "loan-124",
      "accountNumber": "LOAN-002",
      "organizationUserId": "orguser-789",
      "organizationUserName": "Peter Kalisa",
      "organizationUserAccountNumber": "SAV-003",
      "loanDate": "2026-02-01T00:00:00.000Z",
      "termMonths": 10,
      "outstandingBalance": 258000.00,
      "monthlyInstallment": 30800.00,
      "expectedByDate": 153750.00,
      "arrears": 0.00,
      "lastRepaymentDate": "2026-06-01T00:00:00.000Z"
    }
    // ... more loans
  ]
}

Response fields:

Field	Type	Description
`asOfDate`	string (ISO datetime)	Report calculation date (same as `expectedDate` parameter)
`totalOutstanding`	number	Total outstanding balance across all loans (principal + interest)
`totalExpected`	number	Total amount expected to be paid by `asOfDate` based on schedules
`totalArrears`	number	Total overdue amount (expected - actually paid)
`loans`	array	Individual loan records

Loan row fields:

Field	Type	Description
`loanId`	string	Loan ID
`accountNumber`	string	Loan account number
`organizationUserId`	string	Borrower’s organization user ID
`organizationUserName`	string	Borrower’s name
`organizationUserAccountNumber`	string \| null	Borrower’s savings account number
`loanDate`	string (ISO datetime)	Loan disbursement date
`termMonths`	number	Loan term in months
`outstandingBalance`	number	Current outstanding amount (principal + interest)
`monthlyInstallment`	number	EMI amount (principal + interest per month)
`expectedByDate`	number	Amount that should have been paid by `asOfDate`
`arrears`	number	Overdue amount (expectedByDate - actuallyPaid)
`lastRepaymentDate`	string \| null (ISO datetime)	Date of most recent payment

Key Concepts

Outstanding Balance

Outstanding balance = Unpaid principal + Unpaid interest Calculated from ledger accounts:

Loan Receivable: Outstanding principal
Interest Receivable: Unpaid interest (if interest paid with installments)
Penalty Receivable: Unpaid penalties (if applicable)

Important: Uses actual ledger balances, not theoretical calculations. Reflects all posted payments.

Expected by Date

Expected by date = Amount that should have been paid by expectedDate according to installment schedule Calculation logic:

Get installments due by expectedDate
- Filter installment schedule to dueDate <= expectedDate
Sum expected principal and interest
- Principal: Sum of principal from due installments
- Interest:
  - IN_ADVANCE: All interest if loan started before expectedDate
  - WITH_INSTALLMENTS: Sum of interest from due installments

Example:

Loan: 500,000 RWF @ 12% annually, 12 months
Monthly installment: 44,424 RWF (principal + interest)
Expected Date: June 30, 2026
Loan start: January 1, 2026

Installments due by June 30: 6 installments
Expected principal: ~250,000 RWF
Expected interest: ~16,544 RWF
Expected by date: ~266,544 RWF

Arrears

Arrears = Expected amount - Actually paid Formula:

principalArrears = expectedPrincipalByDate - principalPaid
interestArrears = expectedInterestByDate - interestPaid
totalArrears = principalArrears + interestArrears

Arrears = 0: Loan is current (all expected payments made on time) Arrears > 0: Loan is delinquent (payments overdue) Important: Arrears accumulate over time. If a borrower misses multiple installments, arrears grow.

How It Works

The loans outstanding report:

Finds all loans with startDate <= expectedDate

SELECT * FROM loan_meta
WHERE organization_id = {orgId}
  AND start_date <= {expectedDate}

Calculates outstanding balance from ledger
- Uses getOutstandingBreakdownByLoan() ledger service
- Sums all DEBIT and CREDIT journal lines for loan accounts
- Principal: Loan Receivable account balance
- Interest: Interest Receivable account balance (if applicable)
Generates installment schedule
- Uses loan parameters: principal, interest rate, term
- Calculates EMI using standard amortization formula
- Creates installment schedule with due dates
Calculates expected payments by date
- Filters installments with dueDate <= expectedDate
- Sums principal and interest from due installments
- Accounts for interest payment timing (IN_ADVANCE vs WITH_INSTALLMENTS)
Calculates arrears
- Compares expected amount to actually paid (from ledger)
- arrears = max(0, expectedByDate - actuallyPaid)
Gets last repayment date
- Finds most recent LOAN_PAYMENT journal entry
- Excludes reversed entries
- Returns transaction date
Filters paid-off loans
- Skips loans with principalBalance <= 0
- Only active loans with outstanding balances included

Use Cases

Monthly Board Reporting

Track portfolio health for board meetings:

GET /reports/loans-outstanding?expectedDate=2026-06-30

Present to board:

Total outstanding: 12,950,000 RWF (capital deployed)
Total arrears: 500,000 RWF (3.86% of portfolio)
Number of active loans: 45
Delinquency trends (compare to prior month)

Action items:

If arrears > 5%: Increase collection efforts
If many loans in arrears: Review lending criteria
If arrears declining: Collection strategy working

Collections Management

Prioritize collection efforts: Sort loans by arrears (highest to lowest) to identify:

Loans with largest overdue amounts
Members to contact immediately
Candidates for penalties or legal action

Example workflow:

Run report daily or weekly
Filter loans with arrears > 0
Sort by arrears descending
Contact top 10 delinquent borrowers
Track lastRepaymentDate to identify dormant loans (no recent payments)

Liquidity Planning

Understand capital deployment:

GET /reports/loans-outstanding

Analyze:

totalOutstanding: Capital currently lent out (not available for new loans)
Compare to total cash on hand (from Balance Sheet)
Liquidity ratio: Cash ÷ Member Savings

Decision making:

If cash low and outstanding high: Limit new loan approvals
If cash high and outstanding low: Promote loan products
Track loan growth trends month-over-month

Provisioning for Bad Debts

Calculate needed reserves: Industry standard: Provision 50-100% of arrears as bad debt reserve

GET /reports/loans-outstanding?expectedDate=2026-06-30

Example:

Total arrears: 500,000 RWF
Recommended provision: 250,000 - 500,000 RWF
Compare to current reserves (from Balance Sheet)
If reserves insufficient: Allocate more from profits

See: Reserves Management

Portfolio Quality Metrics

Calculate key performance indicators: PAR30 (Portfolio at Risk 30 days):

Percentage of portfolio with payments >30 days overdue
Requires aging analysis (not directly in this report)
Target: < 10%

Delinquency rate:

Delinquency rate = (loans with arrears > 0) ÷ (total loans) × 100

Average arrears per loan:

Average arrears = totalArrears ÷ number of loans

Recovery tracking:

Run report monthly
Compare totalArrears to prior month
Calculate recovery rate: (Prior arrears - Current arrears) ÷ Prior arrears × 100

Comparing Periods

Track portfolio changes over time:

# Prior month
GET /reports/loans-outstanding?expectedDate=2026-05-31

# Current month
GET /reports/loans-outstanding?expectedDate=2026-06-30

Comparison analysis:

Metric	May 2026	Jun 2026	Change
Total Outstanding	12,000,000	12,950,000	+950,000 (+7.92%)
Total Arrears	600,000	500,000	-100,000 (-16.67%) ✓
Number of Loans	43	45	+2 loans
Avg Outstanding/Loan	279,070	287,778	+8,708

Insights:

Portfolio growing (2 new loans disbursed)
Arrears decreasing despite portfolio growth (good - collections working)
Average loan size increasing slightly

Warning signs:

Outstanding increasing faster than repayments (liquidity risk)
Arrears increasing month-over-month (collection problems)
Many loans with zero recent payments (check lastRepaymentDate)

Portfolio Health Indicators

Healthy portfolio:

✅ Arrears < 5% of total outstanding
✅ Most loans with arrears = 0 (current on payments)
✅ lastRepaymentDate within last 30 days for most loans
✅ Total outstanding growing slowly (sustainable lending)

Warning signs:

⚠️ Arrears 5-10% of total outstanding
⚠️ 10-20% of loans delinquent
⚠️ Increasing trend in arrears month-over-month

Action needed:

❌ Arrears > 10% of total outstanding
❌ >20% of loans delinquent
❌ Large individual loans in arrears (single-borrower risk)
❌ Many loans with no recent payments

Corrective actions:

Immediate: Contact delinquent borrowers
Short-term: Apply penalties per organization policy
Medium-term: Tighten lending criteria, require guarantors
Long-term: Increase reserves, write off uncollectible loans

Relationship to Other Reports

Balance Sheet: Loans outstanding totals should reconcile with Balance Sheet:

Balance Sheet (Assets):
  Loans Receivable:        12,500,000 RWF
  Interest Receivable:        450,000 RWF
  Total Loan Assets:       12,950,000 RWF

Loans Outstanding Report:
  Total Outstanding:       12,950,000 RWF ✓ (must match)

Reconciliation check:

If mismatch: Investigate unposted loan payments or data errors
Run both reports with same asOfDate for comparison

Data Sources

Loans outstanding report is generated from: Loan metadata:

SELECT * FROM loan_meta
WHERE organization_id = {orgId}
  AND start_date <= {expectedDate}

Ledger accounts:

Loan Receivable (outstanding principal)
Interest Receivable (unpaid interest)
Penalty Receivable (unpaid penalties)

Ledger service:

getOutstandingBreakdownByLoan(loanId)
// Returns: { outstandingPrincipal, outstandingInterest }

Journal entries (for last payment):

SELECT transaction_date FROM journal_entries
WHERE kind = 'LOAN_PAYMENT'
  AND status = 'POSTED'
  AND reversed_entry_id IS NULL
  AND transaction_date <= {expectedDate}
ORDER BY transaction_date DESC, created_at DESC
LIMIT 1

Installment calculation:

Uses loan terms: principal, interest rate, period
Generates amortization schedule with EMI
Applies interest calculation type and payment timing rules

Best Practices

Managing loan portfolio:Monitoring:

✅ Review weekly for early detection of delinquencies
✅ Sort by arrears to prioritize collection efforts
✅ Track lastRepaymentDate to identify inactive loans
✅ Export monthly for board meetings and financial reports

Analysis:

✅ Compare to prior month to identify trends
✅ Calculate portfolio metrics: delinquency rate, arrears ratio
✅ Reconcile totalOutstanding with Balance Sheet loan assets
✅ Track recovery rate: month-over-month arrears reduction

Collections:

✅ Contact borrowers when arrears > 0 (don’t wait)
✅ Apply penalties consistently per organization policy
✅ Document collection efforts and borrower commitments
✅ Escalate to guarantors or legal action if needed

Risk management:

✅ Provision reserves: 50-100% of total arrears
✅ Limit concentration: Max loan size as % of portfolio
✅ Review lending criteria if arrears consistently > 5%
✅ Write off uncollectible loans (don’t inflate portfolio artificially)

Reporting:

✅ Board reports: Include loans outstanding with Balance Sheet
✅ Member reports: Share portfolio health at general meetings
✅ Regulatory: May be required for financial audits
✅ Archive monthly snapshots for historical analysis

Common Questions

Q: Why doesn’t totalOutstanding match sum of monthlyInstallment × termMonths? A: totalOutstanding is current balance (what’s owed now), not original loan amount. It decreases as payments are made. Original loan amount = principal disbursed + total interest (not in this report). Q: Why is arrears = 0 even though lastRepaymentDate is old? A: Borrower may have made large upfront payments covering future installments. Arrears only tracks expected vs actual as of expectedDate. If expected amount is paid (even early), no arrears. Q: Can arrears be negative? A: No. Backend uses max(0, expectedByDate - actuallyPaid). Overpayments don’t create negative arrears; they reduce future arrears. Q: Why is a fully paid loan still showing? A: Report excludes loans with principalBalance <= 0. If showing, loan still has outstanding principal. Check ledger for payment posting errors. Q: How is monthlyInstallment calculated? A: Uses EMI (Equated Monthly Installment) formula:

EMI = P × r × (1 + r)^n / ((1 + r)^n - 1)
Where:
  P = principal
  r = monthly interest rate
  n = number of months

For interest paid in advance, calculation differs (all interest upfront). Q: Why does expectedByDate seem too high? A: If interest is paid IN_ADVANCE, all interest is expected immediately upon disbursement. This can make expectedByDate very high early in loan term. Q: Can I get arrears aging (30 days, 60 days, 90+ days)? A: This report doesn’t break down arrears by age. Aging analysis requires comparing current arrears to historical arrears from prior periods. Track monthly to calculate aging manually.

Example: Portfolio Analysis

Scenario: Analyze loan portfolio health as of June 30, 2026

curl -X GET "https://api.agatabo.com/reports/loans-outstanding?expectedDate=2026-06-30" \
  -H "x-organization-id: org-abc123"

Response:

{
  "totalOutstanding": 12950000.00,
  "totalExpected": 8500000.00,
  "totalArrears": 500000.00,
  "loans": [45 loans]
}

Calculate metrics: Arrears ratio:

Arrears ratio = 500,000 ÷ 12,950,000 × 100 = 3.86%

Status: ✓ Healthy (< 5%) Delinquency rate:

Loans with arrears > 0: 8 loans
Delinquency rate = 8 ÷ 45 × 100 = 17.78%

Status: ⚠️ Warning (10-20% is moderate risk) Average loan outstanding:

Average = 12,950,000 ÷ 45 = 287,778 RWF

Largest delinquent loan: Filter loans where arrears > 0, sort by arrears descending:

{
  "organizationUserName": "Jane Smith",
  "outstandingBalance": 415000.00,
  "arrears": 50000.00,
  "lastRepaymentDate": "2026-05-15"
}

Action: Contact Jane Smith (45 days since last payment, 50,000 RWF overdue) Recommendation: Overall portfolio health is acceptable (arrears < 5%), but delinquency rate is elevated. Focus collection efforts on 8 delinquent loans to prevent arrears from growing.

Recording Loan Payments

Reduce outstanding balances

Applying Penalties

Handle delinquent loans

Balance Sheet

Reconcile loan assets

Reserves Management

Provision for bad debts

​Overview

​API Endpoint

​Response Structure

​Key Concepts

​Outstanding Balance

​Expected by Date

​Arrears

​How It Works

​Use Cases

​Monthly Board Reporting

​Collections Management

​Liquidity Planning

​Provisioning for Bad Debts

​Portfolio Quality Metrics

​Comparing Periods

​Portfolio Health Indicators

​Relationship to Other Reports

​Data Sources

​Best Practices

​Common Questions

​Example: Portfolio Analysis

​Related Topics

Recording Loan Payments

Applying Penalties

Balance Sheet

Reserves Management

Overview

API Endpoint

Response Structure

Key Concepts

Outstanding Balance

Expected by Date

Arrears

How It Works

Use Cases

Monthly Board Reporting

Collections Management

Liquidity Planning

Provisioning for Bad Debts

Portfolio Quality Metrics

Comparing Periods

Portfolio Health Indicators

Relationship to Other Reports

Data Sources

Best Practices

Common Questions

Example: Portfolio Analysis

Related Topics