ECL Engine

The system runs the IFRS 9 / BFRS 9 three-stage Expected Credit Loss model in parallel with the Bangladesh Bank (BRPD-15/2024) provisioning rules and reports the higher of the two. A complete cycle is:

1. Configure global policy (Settings) — provision basis (higher-of / IFRS 9 only / BB only), fallback discount rate, lifetime cap, SICR PD-multiple threshold, and the macro overlay mode (derived vs direct).
2. Calibrate Model Parameters — the seven parameter tabs (rating-grade PDs & term structure, LGD components, CCF for off-balance items, eligible collateral & haircuts, staging thresholds, BB classification bands, BB provision rates). Defaults are illustrative; fit them to your own loss history before production.
3. Macro & scenarios — pull macro actuals from the World Bank API, set the Base/Upside/Downside probability weights (must sum to 1.00), and enter the forward macro paths and PD-elasticities that drive the forward-looking overlay (section 8).
4. Upload the loan book — download the Excel template, populate it, and upload it as a named batch tied to a reporting date.
5. Run the ECL — pick the batch and reporting date; the engine stages each facility, builds the PD curve, blends collateral-adjusted LGD, computes EAD/CCF, derives scenario PD multipliers from the macro paths, discounts at EIR, probability-weights, and computes the parallel BB provision — writing a full audit trail per facility.
6. Review & export — inspect portfolio KPIs, the macro-overlay banner, per-stage and per-segment breakdowns, drill into any facility's eight-step trace, and export the per-facility and full-portfolio workbooks for working papers.

Required columns (the row is rejected without them): loan_account_no, segment_code, outstanding_amount, current_grade_code, maturity_date, days_past_due.

Recommended for accuracy: effective_interest_rate (EIR as a decimal — used for discounting; falls back to the segment default if blank), undrawn_commitment and facility_type (drive the CCF on off-balance exposures), interest_suspense (deducted in the BB base for classified loans), original_grade_code (origination rating, for the SICR PD-multiple test), bb_classification (STD/SMA/SS/DF/BL — if blank it is derived from DPD), and the flags watchlist_flag / forbearance_flag / restructured_flag / poci_flag.

Collateral — provide collateral_type, collateral_market_value and (optionally) the lien/eligible amount; the engine applies the haircut and the BRPD-15 eligibility rule. Amortisation — amortization_profile = bullet (default) or linear (EAD steps down over the remaining tenor).

Manual overrides for auditor judgement: stage_override (1/2/3), pd_override, lgd_override, ead_override. Leave them blank to let the model compute. The Reference sheet in the template lists every valid segment, grade and facility-type code.

Each facility is assigned a stage; the stage sets the loss horizon and whether PD is point-in-time or 100%.

• Stage 1 — performing. 12-month ECL. Applies to facilities with no significant increase in credit risk. The low-credit-risk exemption (§5.5.10) can keep high investment-grade exposures in Stage 1.
• Stage 2 — significant increase in credit risk (SICR). Lifetime ECL. Triggered by any of: DPD ≥ the SICR threshold (default 30); BB classification = SMA; a watchlist, forbearance or restructured flag; or the current-vs-origination PD ratio exceeding the SICR PD-multiple (default 2.0×).
• Stage 3 — credit-impaired. Lifetime ECL with PD = 100%; loss = LGD × EAD discounted over the recovery lag. Triggered by DPD ≥ the default threshold (default 90), BB classification SS/DF/BL, or a stage override of 3.
• POCI — purchased or originated credit-impaired. Lifetime ECL on a credit-adjusted basis from initial recognition; only the cumulative change in lifetime ECL is recognised thereafter.

The staging decision and the exact trigger are recorded in step2_staging of every facility's audit trail, alongside the BB classification it maps to.

ECL is computed per scenario and probability-weighted. For each scenario s:

ECLₛ = Σₜ marginalPDₜ × LGDₛ × EADₜ × DF(t),   DF(t) = (1 + EIR)^−(t−0.5)

• EAD — drawn outstanding + undrawn × CCF (off-balance facilities use the BRPD-06/2023 credit-conversion factor for their facility type). Recorded in step1_ead.
• PD curve — the rating grade's 12-month through-the-cycle PD is expanded into a lifetime conditional-hazard vector via the PD term structure. step4_pd_curve shows every annual hazard.
• LGD — blended secured × coverage + unsecured × (1 − coverage), then floored. Coverage is the haircut-adjusted eligible collateral over EAD. The downturn LGD multiplier is applied per scenario. Recorded in step3_lgd.
• Horizon — Stage 1 uses a 1-year horizon; Stage 2, Stage 3 and POCI use the lifetime (remaining maturity, capped at the configured maximum).
• Discounting — at the facility EIR using the mid-year convention (cash flows assumed mid-period). Stage 3 discounts LGD × EAD over the recovery lag.

The closed-form for the constant-hazard lifetime case ties out to the period-by-period sum to the cent (verified). The scenario-conditional results sit in step5_scenarios and the probability weighting in step6_weighting.

Alongside the ECL, the engine computes the regulatory provision under BRPD Circular No. 15 (27 Nov 2024, effective 1 Apr 2025):

• General provision on the outstanding for unclassified and SMA exposures.
• Specific provision on the base for provision for classified exposures, where base = outstanding − interest-in-suspense − eligible collateral (with a 15% floor for non-cash-equivalent security under Para 9).
• Eligible collateral (Para 10) — deposit/government-securities lien and government/BB/MDB guarantees deductible at 100%; gold at 100%; land & building and shares up to 50%; commodities 50%.
• Rates are configured in the BB provision-rates tab. Confirm them against the circular and your own classification policy before production — the engine uses exactly what is stored there.

The reported provision is the higher of the IFRS 9 ECL and the BB provision by default. Switch the basis to ifrs9_only or bb_only in Settings if your reporting requires it. step7_bb_provision and step8_final show the comparison and the figure carried.

This is the mechanism that makes the ECL respond to the economic outlook. Instead of fixed scenario multipliers, each scenario's PD multiplier is derived from its macro forecast path:

PD multiplierₛ = exp( Σᵥ elasticityᵥ × (forecastᵥ,ₛ − baselineᵥ) )

• Macro variables & elasticities — seven drivers (GDP growth, CPI inflation, unemployment, private-sector credit growth, USD/BDT, policy rate, remittance growth). Each has an elasticity β = change in ln(PD) per one-unit move of the variable versus the Base path (stored in pd_macro_sensitivity).
• Scenario forecasts — the Base/Upside/Downside forward values for each variable (stored in macro_scenario_forecasts). Deviations are measured against the Base path, so the Base scenario resolves to exactly 1.0000 and Upside/Downside fall out of the economics.
• LGD — the configured downturn-LGD multiplier is applied per scenario (the sensitivity table is PD-only).
• Mode — macro_overlay_mode = derived (default) computes the multipliers from the paths above; direct reverts to the fixed multipliers stored on each scenario.

How to verify it is working: after a run, the Results screen shows a Forward-looking PD overlay banner with a macro-derived tag and the resolved multiplier per scenario. Open any exposure → Step 5 for the full variable-by-variable bridge (baseline, forecast, deviation, β, β×deviation, Σ → exp). Both Excel exports include the same bridge.

To change the impact, edit the forecast paths and/or elasticities (more adverse downside ⇒ higher downside PD multiplier ⇒ higher ECL on PD-driven Stage 1/2 facilities). Stage 3 is unaffected because its PD is already 100%.

• admin — full configuration, all calculations, and user management (approve/decline accounts, assign roles).
• analyst — upload data, edit model parameters, run calculations, view and export results.
• viewer — read-only access to results and reports.

First account auto-bootstraps. The very first user to register is automatically promoted to an approved admin — no manual SQL is required on a fresh install. Every later sign-up lands in pending status after email verification and cannot access any data until an admin approves it on the User Setup screen.

Access is enforced in the database by row-level security: an unapproved or anonymous session sees nothing, and only admin/analyst roles may write. Users cannot change their own role or approval status (the policy freezes both on self-update). All writes are captured in the immutable audit log. See the User Setup screen for the full field-by-field reference and the auth ⇄ profile link-up.

Every run stores a parameters_snapshot — scenarios, staging rules, settings and the macro-overlay summary frozen at run time — so a result is always reproducible against the exact policy that produced it. For each facility a calc_steps JSON document reproduces the full eight-step workings: inputs, EAD build-up, staging decision, LGD blend, PD curve, per-scenario discounting with the macro bridge, probability weighting, BB provision, and the higher-of conclusion.

Exports: open any result row and use Download (.xlsx) for that facility's complete workings (inputs, the eight-step formula/worked-value/reference table, the scenario detail and the macro-overlay bridge). The Results toolbar offers Full calculation (.xlsx) (every facility with all intermediate columns, a Formulas & References sheet, and a run-level Macro overlay sheet) and Summary report (.xlsx) (by stage and segment). It also offers Accounting note (.doc) — a Word document for management approval that states the measurement basis and methodology, the portfolio summary by stage and segment, the financial impact, and the proposed journal entries (the IFRS 9 ECL charged to profit or loss and the excess of the Bangladesh Bank minimum appropriated to a regulatory reserve in equity), with a sign-off block. Approve it before posting the impairment entry to the accounts.

• Database setup — run supabase_ifrs9_setup.sql in the project's SQL editor. It is idempotent and safe to re-run; the function is replaced in place, scenarios are de-duplicated, and the macro elasticities/forecasts are upserted to the script's values.
• Auth URLs — in Supabase → Authentication → URL Configuration set the Site URL and Redirect URLs to where the app is hosted. The default http://localhost:3000 breaks email verification and password-reset links.
• Host over http(s), not file:// — email redirect links will not work from the file system (the app shows an amber warning if opened that way).
• "I changed the macro inputs but the ECL did not move." Re-run supabase_ifrs9_setup.sql (older builds left the elasticity and forecast tables empty, so the overlay silently used the fixed multipliers), then re-run the calculation. Confirm the Results banner shows macro-derived and check the resolved multipliers. Remember Stage-3 exposures (PD = 100%) are immune to PD multipliers by design.
• "Email not confirmed" on sign-in — verify the Site URL above; for internal testing you can disable email confirmation in Supabase (the approval gate still protects the data).