Loan Tape

For lenders seeking to raise debt capital or acquire term sheets from capital providers, putting together a Loan Tape is usually the first step.

This SQL query is designed to generate a summary of outstanding balances, repayment rates and delinquency on each account, which you would then JOIN with information on customer characteristics. The latter could live inside your CRM or in Canopy (if set up correctly).

This query compiles data from various tables to provide valuable insights into account and payment-related metrics. The report is structured to assist with financial decision-making and monitoring, particularly in a lending or financial management context.

Fields

• CUSTOMER_ID: This is the ID used to identify the account in your system.
• STATUS: This field uses a CASE statement to determine the status of the account based on the outstanding amount (AMOUNT_OUTSTANDING). It assigns labels such as 'Paid in Full,' 'Delinquent,' 'Current,' or the account's actual status.
• ACCOUNT_STATUS: This field represents the account's status.
• ACCOUNT_SUBSTATUS: This field represents the substatus of the account.
• COHORT: Extract the month from the effective_at timestamp.
• PAYMENT_FREQUENCY: This field represents the average payment frequency for the account.
• DAYS_PAST_DUE: This field calculates the number of days past due for the account.
• TOTAL_REVOLVING_FUNDS: This field calculates the total revolving funds.
• TOTAL_LATE_FEES_COLLECTED: This field calculates the total late fees collected.
• MISSED_PAYMENTS: Number of missed payments.
• CHARGE_OFF_DATE: The charge-off date for accounts that have been charged off.
• CHARGE_OFF_AMOUNT: The charge-off amount for accounts that have been charged off.
• ACCOUNT_ID: This field represents the unique identifier for the loan.
• ADVANCE_DATE: Effective date of the loan, converted to a date format.
• ORIGINAL_RECEIVABLE_BALANCE: Calculates the original receivable balance.
• AMOUNT_OUTSTANDING: Calculates the outstanding amount (principal).
• REPAYMENT_START_DATE: Calculates repayment start date by selecting the minimum date between first_due_date from the loan_terms_statistics CTE and earliest_repayment from the loan_payment_statistics CTE.
• LAST_REPAYMENT_DATE: Calculates the last repayment date by selecting the maximum date between last_due_date from the loan_terms_statistics CTE and latest_repayment from the loan_payment_statistics CTE.
• TERM: Represents the total number of terms associated with the loan.
• REMAINING_TERM: Calculates the remaining terms of the loan.
• EXPECTED_PAYOFF_DATE: Expected payoff date of the loan.
• TOTAL_PRINCIPAL_COLLECTED: Calculates the total principal collected.
• TOTAL_PRINCIPAL_OFFSET: Calculates the total principal offset.
• TOTAL_FEE_COLLECTED: Calculates the total fee collected.
• TOTAL_FEE_OFFSET: Calculates the total fee offset.

Main goals

• The primary objective of this query is to create a detailed financial report encompassing various aspects of account and payment data.
• It calculates and presents critical financial metrics, such as delinquency status, charge-off dates, payment frequencies, and more.
• This report aids in assessing the financial health of accounts and tracking important financial events.

SQL Template

WITH payment_split_json_records AS (
    select
        jsonb_array_elements(line_item_relationships) field,
        valid_at as effective_at,
        external_line_item_id as payment_external_line_item_id,
        account_id,
        line_item_type
    from
        data_direct.line_items
    where
        line_item_type IN ('PAYMENT', 'DEBIT_OFFSET')
        and line_item_status = 'VALID'
),
splits_as_transactions AS (
    select
        'PAYMENT_SPLIT' as line_item_type,
        payment_external_line_item_id,
        (psjr.field ->> 'split_amount_cents') :: int as original_amount_cents,
        (psjr.field ->> 'line_item_client_id') as transaction_id,
        (psjr.field ->> 'paid_down_line_item_client_id') as paid_down_client_id,
        (psjr.field ->> 'paid_down_line_item_type') as paid_down_type,
        (
            psjr.field ->> 'paid_down_line_item_parent_client_id'
        ) as paid_down_parent_external_line_item_id,
        CASE
            (psjr.field ->> 'paid_down_line_item_type')
            WHEN 'AM_INTEREST' THEN (
                psjr.field ->> 'paid_down_line_item_parent_client_id'
            )
            WHEN 'LOAN' THEN (psjr.field ->> 'paid_down_line_item_client_id')
        END AS loan_id,
        effective_at,
        account_id,
        line_item_type AS original_payment_type
    from
        payment_split_json_records psjr
),
collected_splits AS (
    select
        *
    from
        splits_as_transactions
    where
        original_payment_type = 'PAYMENT'
),
offset_splits_statistics AS (
    select
        sum(original_amount_cents) offset_cents,
        loan_id,
        paid_down_type
    from
        splits_as_transactions
    where
        original_payment_type = 'DEBIT_OFFSET'
    group by
        account_id,
        loan_id,
        paid_down_type
),
loan_payment_statistics AS (
    select
        loan_id,
        max(effective_at) latest_repayment,
        min(effective_at) earliest_repayment
    from
        collected_splits sat
    where
        loan_id is not null
    group by
        loan_id
),
late_fee_payment_statistics AS (
    select
        account_id,
        SUM(original_amount_cents) collected
    from
        collected_splits
    where
        paid_down_type = 'MANUAL_FEE'
    group by
        account_id
),
loan_terms_statistics AS (
    select
        external_line_item_id as loan_id,
        min(cycle_exclusive_end) as first_exclusive_end_date,
        max(cycle_exclusive_end) as last_exclusive_end_date,
        max(min_pay_due_at) as last_due_date,
        min(min_pay_due_at) as first_due_date,
        sum(
            case
                when paid_on_time = false then 1
                else 0
            end
        ) missed_payments,
        sum(
            case
                when clock_timestamp() > min_pay_due_at then 1
                else 0
            end
        ) terms_due,
        count(1) total_terms_count
    from
        data_direct.am_forecast_public
    group by
        external_line_item_id
),
account_payment_statistics AS (
    select
        account_id,
        count(1) AS payments_in_month,
        EXTRACT(
            MONTH
            FROM
                payments.effective_at
        )
    from
        data_direct.line_items payments
    where
        payments.line_item_type = 'PAYMENT'
        AND payments.line_item_status = 'VALID'
    group by
        payments.account_id,
        EXTRACT(
            MONTH
            FROM
                payments.effective_at
        )
),
account_monthly_payment_freq AS (
    select
        account_id,
        avg(payments_in_month) avg_payment_in_month
    from
        account_payment_statistics
    group by
        account_id
),
all_loans AS (
    SELECT
        *
    FROM
        data_direct.line_items
    where
        line_item_type = 'LOAN'
        and line_item_status = 'VALID'
),
all_accounts AS (
    SELECT
        *
    FROM
        data_direct.accounts
),
loan_tapes AS (
    select
        external_line_item_id AS LOAN_ID,
        account_id,
        effective_at :: date AS ADVANCE_DATE,
        original_amount_cents / 100.0 AS ORIGINAL_RECEIVABLE_BALANCE,
        principal_cents / 100.0 AS AMOUNT_OUTSTANDING,
        LEAST(lts.first_due_date, lps.earliest_repayment) :: date AS REPAYMENT_START_DATE,
        GREATEST(lts.last_due_date, lps.latest_repayment) :: date AS LAST_REPAYMENT_DATE,
        lts.total_terms_count AS TERM,
        lts.total_terms_count - lts.terms_due AS REMAINING_TERM,
        lts.last_due_date :: date AS EXPECTED_PAYOFF_DATE,
        (
            loans.original_amount_cents - loans.principal_cents
        ) / 100.0 AS TOTAL_PRINCIPAL_COLLECTED,
        COALESCE(ABS(osls.offset_cents) / 100.0, 0) AS TOTAL_PRINCIPAL_OFFSET,
        loans.total_interest_paid_to_date_cents / 100.0 AS TOTAL_FEE_COLLECTED,
        COALESCE(ABS(osfs.offset_cents) / 100.0, 0) AS TOTAL_FEE_OFFSET
    from
        all_loans loans
        left join loan_payment_statistics lps ON lps.loan_id = loans.external_line_item_id
        left join loan_terms_statistics lts ON lts.loan_id = loans.external_line_item_id
        left join offset_splits_statistics osls ON osls.loan_id = loans.external_line_item_id
        and osls.paid_down_type = 'LOAN'
        left join offset_splits_statistics osfs ON osfs.loan_id = loans.external_line_item_id
        and osfs.paid_down_type = 'AM_INTEREST'
),
delinquency_metrics AS (
    SELECT
        account_id,
        delinquent_as_of_date,
        dr.days_past_due,
        count(1) over (partition by account_id) missed_payments,
        rank() over (
            partition by account_id
            order by
                effective_at desc
        ) ordering,
        CASE
            WHEN delinquent_as_of_date IS NOT NULL
            AND (delinquent_as_of_date + INTERVAL '180 days') <= clock_timestamp() THEN delinquent_as_of_date + INTERVAL '180 days'
        END AS charge_off_date
    FROM
        data_direct.delinquency_report dr
        JOIN data_direct.statements s using(account_id)
    WHERE
        dr.delinquent_as_of_date is not null
        and s.min_pay_due_at >= dr.delinquent_as_of_date
        AND s.min_pay_due_at <= clock_timestamp()
)
SELECT
    aa.external_account_id AS CUSTOMER_ID,
    CASE
        WHEN lt.AMOUNT_OUTSTANDING = 0 THEN 'Paid in Full'
        WHEN aa.account_status_subtype = 'DELINQUENT' THEN 'Delinquent'
        WHEN aa.account_status = 'ACTIVE' THEN 'Current'
        else aa.account_status
    END AS STATUS,
    aa.account_status AS ACCOUNT_STATUS,
    aa.account_status_subtype AS ACCOUNT_SUBSTATUS,
    TO_CHAR(aa.effective_at, 'month') AS COHORT,
    ampf.avg_payment_in_month AS PAYMENT_FREQUENCY,
    COALESCE(dr.days_past_due, 0) AS DAYS_PAST_DUE,
    aa.credit_limit_cents / 100.0 AS TOTAL_REVOLVING_FUNDS,
    COALESCE(ABS(lfps.collected / 100.0), 0) AS TOTAL_LATE_FEES_COLLECTED,
    (
        CASE
            WHEN dr.delinquent_as_of_date IS NULL THEN 0
            ELSE dr.missed_payments
        END
    ) AS MISSED_PAYMENTS,
    (
        CASE
            WHEN dr.charge_off_date IS NOT NULL
            AND aa.account_status_subtype = 'CHARGE_OFF' THEN dr.charge_off_date
        END
    ) AS CHARGE_OFF_DATE,
    (
        CASE
            WHEN dr.charge_off_date IS NOT NULL THEN lt.AMOUNT_OUTSTANDING
        END
    ) AS CHARGE_OFF_AMOUNT,
    lt.*
FROM
    loan_tapes lt
    join all_accounts aa on aa.account_id = lt.account_id
    join account_monthly_payment_freq ampf on ampf.account_id = aa.account_id
    left join delinquency_metrics dr on dr.account_id = aa.account_id
    and dr.ordering = 1
    left join late_fee_payment_statistics lfps on lfps.account_id = aa.account_id
order by
    aa.external_account_id;

Sample Data