> ## Documentation Index
> Fetch the complete documentation index at: https://help.agatabo.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Loans Outstanding Report

> Track active loans, outstanding balances, arrears, and portfolio health

## 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.

<Note>
  **Permission required**: `reports:read`

  This report is generated from loan metadata, installment schedules, and ledger account balances. Only loans with outstanding principal > 0 are included.
</Note>

***

## API Endpoint

**Get loans outstanding report:**

```http theme={null}
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:**

```bash theme={null}
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

```json theme={null}
{
  "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:**

1. **Get installments due by expectedDate**
   * Filter installment schedule to `dueDate <= expectedDate`

2. **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:**

```typescript theme={null}
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:

1. **Finds all loans with startDate \<= expectedDate**
   ```sql theme={null}
   SELECT * FROM loan_meta
   WHERE organization_id = {orgId}
     AND start_date <= {expectedDate}
   ```

2. **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)

3. **Generates installment schedule**
   * Uses loan parameters: principal, interest rate, term
   * Calculates EMI using standard amortization formula
   * Creates installment schedule with due dates

4. **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)

5. **Calculates arrears**
   * Compares expected amount to actually paid (from ledger)
   * `arrears = max(0, expectedByDate - actuallyPaid)`

6. **Gets last repayment date**
   * Finds most recent `LOAN_PAYMENT` journal entry
   * Excludes reversed entries
   * Returns transaction date

7. **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:**

```http theme={null}
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:**

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

***

### Liquidity Planning

**Understand capital deployment:**

```http theme={null}
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

```http theme={null}
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](/tontines/reserves/overview)

***

### 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:**

```http theme={null}
# 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:**

1. **Immediate**: Contact delinquent borrowers
2. **Short-term**: Apply penalties per organization policy
3. **Medium-term**: Tighten lending criteria, require guarantors
4. **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:**

```sql theme={null}
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:**

```typescript theme={null}
getOutstandingBreakdownByLoan(loanId)
// Returns: { outstandingPrincipal, outstandingInterest }
```

**Journal entries (for last payment):**

```sql theme={null}
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

<Note>
  **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
</Note>

***

## 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

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

**Response:**

```json theme={null}
{
  "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:

```json theme={null}
{
  "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.

***

## Related Topics

<CardGroup cols={2}>
  <Card title="Recording Loan Payments" icon="money-bill-wave" href="/tontines/loans/recording-loan-payments">
    Reduce outstanding balances
  </Card>

  <Card title="Applying Penalties" icon="gavel" href="/tontines/loans/applying-penalties">
    Handle delinquent loans
  </Card>

  <Card title="Balance Sheet" icon="scale-balanced" href="/tontines/reports/balance-sheet">
    Reconcile loan assets
  </Card>

  <Card title="Reserves Management" icon="shield" href="/tontines/reserves/overview">
    Provision for bad debts
  </Card>
</CardGroup>