Provision Coverage Ratio (Provision Coverage)

Что такое Provision Coverage Ratio (Provision Coverage)?

Покрытие неработающих кредитов резервами (provisions).

Формула

$$\text{Provision Coverage} = \frac{\sum \text{provision}}{\sum \text{NPL outstanding}} \times 100\%$$

Как считать

Суммируем начисленные резервы по NPL-кредитам и делим на остаток по NPL.

Зачем нужна

Показывает, насколько банк готов к потерям. Покрытие ниже 100% — часть убытков ударит по капиталу.

SQL-запрос

-- Таблицы: loans(loan_id, user_id, app_id, amount, issued_at, term_months, interest_rate, status)
--          loan_payments(payment_id, loan_id, due_date, paid_at, amount_due, amount_paid, is_overdue, dpd)
--          provisions(loan_id, provision_amount, date)
WITH npl AS (
  SELECT l.loan_id, l.amount
  FROM loans l
  JOIN loan_payments p ON p.loan_id = l.loan_id
  WHERE l.status IN ('active', 'default')
  GROUP BY l.loan_id, l.amount
  HAVING MAX(p.dpd) >= 90
),
latest_prov AS (
  SELECT DISTINCT ON (loan_id) loan_id, provision_amount
  FROM provisions
  ORDER BY loan_id, date DESC
)
SELECT
  ROUND(SUM(n.amount), 2)                     AS npl_volume,
  ROUND(SUM(COALESCE(lp.provision_amount, 0)), 2) AS total_provisions,
  ROUND(
    100.0 * SUM(COALESCE(lp.provision_amount, 0))
    / NULLIF(SUM(n.amount), 0), 2
  )                                            AS provision_coverage_pct
FROM npl n
LEFT JOIN latest_prov lp ON lp.loan_id = n.loan_id;