# Step 20 — Loan and Branch Reports — Implementation Report

**Status:** COMPLETED  
**Verified:** 12 August 2026  
**Pull request:** #32

## Reports implemented

Step 20 adds the complete operational loan and branch reporting module:

1. Active Loan Portfolio
2. Outstanding Principal
3. Outstanding Interest
4. Due Today
5. Overdue Loans
6. Completed Loans
7. Written-Off Loans
8. Loan Collection Rate
9. Branch Performance
10. Consolidated Company Performance
11. Loan Officer Performance
12. Collateral Report
13. Top-Up Report
14. Restructuring Report
15. Write-Off Recovery Report

The module is read-only and does not create duplicate reporting balances or reporting-only financial records.

## Reporting semantics

The reports distinguish current-state data from period-event data instead of presenting mutable current balances as historical snapshots.

### Current portfolio reports

The following use current operational state:

- Active Loan Portfolio
- Outstanding Principal
- Outstanding Interest
- Overdue Loans
- Collateral Report

They report the current balances/statuses for the selected branch scope.

### Date-range reports

The selected date range is applied to the relevant permanent event date for:

- Completed Loans
- Written-Off Loans
- Top-Up Report
- Restructuring Report
- Write-Off Recovery Report
- Period components of branch/company/officer performance

`Due Today` uses the selected report end date as its due date, which also allows management to review another day's scheduled obligations without changing system time.

## Active portfolio and outstanding reports

Active portfolio reporting includes:

- Loan/customer/phone
- Branch
- Recorded loan officer/originator
- Loan product
- Original principal
- Total paid
- Principal outstanding
- Interest outstanding
- Fees outstanding
- Penalties outstanding
- Total outstanding
- Days overdue

Outstanding Principal and Outstanding Interest provide focused drill-downs and reconcile to the same current active-loan records.

## Due and overdue reports

Due Today is generated from currently active repayment-schedule installments for the selected end date and includes scheduled amount, penalty, amount already paid and remaining due.

Overdue Loans uses current loan arrears fields and supports the stored arrears buckets:

- 1–7 days
- 8–30 days
- 31–60 days
- 61–90 days
- 90+ days

It reports overdue principal, interest, fees, penalties, total overdue and total loan outstanding.

## Completed and written-off loans

Completed Loans uses permanent loan completion status/timestamps and reports original principal, contract total and total paid.

Written-Off Loans uses the approved write-off snapshot and reports:

- Original principal
- Write-off amount
- Recovered amount
- Current recoverable balance
- Write-off reason

The original written-off loan remains historical and is not reconstructed from recovery transactions.

## Loan collection rate

The period collection-rate formula is:

`Normal loan payments + early settlements collected in period ÷ currently active scheduled installments due in period`

Written-off loan recoveries are intentionally excluded from the collection-rate numerator because they relate to already written-off portfolio. They are surfaced separately in performance and recovery reporting.

The report provides branch rows and a consolidated selected-scope rate.

The rate can exceed 100% when arrears or advance/full-settlement collections exceed the installments scheduled for the selected period.

## Branch and company performance

Branch Performance combines clearly separated current portfolio quality and selected-period activity:

Current:

- Active loans
- Principal outstanding
- Total outstanding
- Overdue loan count
- Overdue amount

Period:

- Disbursed loan count and principal
- Collections
- Scheduled due
- Collection rate
- Completed loans
- Written-off loans
- Write-off amount
- Written-off loan recoveries

Consolidated Company Performance applies the same calculations only to the user's selected/access-allowed company branches.

## Loan officer performance

Loan officer/originator is the employee permanently recorded in `loans.created_by`.

The report includes:

- Loans originated in period
- Originated principal
- Current active portfolio/outstanding
- Current arrears
- Period collections
- Period scheduled due and collection rate
- Completed loans
- Written-off loans and recoveries

The officer filter uses the same originator field consistently across applicable reports.

## Collateral report

The Collateral model now exposes its existing `loan_collateral` many-to-many relationship to Loan for reporting and filtering.

The report includes:

- Collateral code
- Customer
- Branch
- Category/item/serial number
- Estimated and accepted value
- Current custody/status
- Storage location
- Linked loans

It supports current collateral-status filtering.

## Top-up, restructuring and write-off recovery reports

Top-Up Report includes the previous loan/outstanding, internal settlement, replacement loan values and actual net cash given.

Restructuring Report includes old/new schedule versions, outstanding snapshot, reason, status and decision details. It supports pending/approved/rejected status filtering.

Write-Off Recovery Report includes recovery/write-off/loan/customer details, amount, method, account, reference and receiving employee, plus current recoverable balance for represented write-offs.

## Filters

The shared report workspace supports:

- Date range
- One branch
- Several branches
- All accessible branches
- Loan officer/originator
- Loan product
- Loan/customer/phone search
- Arrears bucket where applicable
- Record status where applicable

All requested branch IDs are validated against the authenticated user's assigned branch scope on the backend.

## CSV export

Every one of the 15 reports has CSV export.

The export calls the same `LoanBranchReportService` with the same validated filters as the screen, ensuring exported numbers and displayed numbers use one calculation path.

CSV files include:

- Report title
- Date range
- Branch scope
- Calculation/method note where relevant
- Summary figures
- Report detail rows

Spreadsheet formula-leading values (`=`, `+`, `-`, `@`) are prefixed safely to reduce CSV formula-injection risk.

## Permissions and navigation

Step 20 reuses the existing `reports.view` permission.

All screen and export routes require authentication plus `reports.view` and independently enforce backend branch scope.

Added Finance → **Loan & Branch Reports** navigation beside Financial Reports.

## Database changes

No migration is required.

Step 20 is a read-only reporting module over permanent records created by earlier modules. The only model-layer addition is the missing Collateral → Loans relationship for the already-existing `loan_collateral` pivot table.

## Verification

GitHub Actions Foundation CI on pull request #32:

- Fresh migrations: PASS
- Seeders: PASS
- Full Laravel test suite: **148 passed**
- Assertions: **1,527**
- Existing Step 1–19 regression tests: PASS
- Step 20 feature tests: **6 passed**

Step 20 tests cover:

1. Active portfolio, principal, interest, due-date and overdue reconciliation to current loan/schedule state.
2. Completed, written-off, collateral, top-up, restructuring and write-off recovery reconciliation to permanent records.
3. Collection-rate calculations plus branch and consolidated company performance.
4. Loan-officer/originator attribution and officer/search filters.
5. `reports.view` authorization and backend branch isolation.
6. Successful rendering of all 15 reports and successful permission-scoped CSV export for all 15 reports.

## Remaining Step 20 work

None.

## Next roadmap module

**Step 21 — Management Dashboard**.
