信贷风控必看！Vintage分析表计算全流程解析

在信贷风控领域，Vintage分析是一种重要的工具，它能够帮助我们更好地理解贷款资产的动态变化情况。本文将详细解析Vintage分析表的计算过程，涵盖从借据与还款计划表的建立，到账龄计算、逾期天数计算、剩余本金计算，再到Vintage透视表的呈现，最后进行总结。希望本文能为你提供清晰的思路和实用的计算方法，助力你在风控领域更进一步。

Part 1. 借据与还款计划表

客户在经过注册、实名认证、授信申请通过后，可在可用额度内发起动支申请订单。当申贷订单被风控审批通过后，形成借据。对于分期产品，同时生成相应的还款计划表，相当于双方约定的履约合同。此后，客户的还款行为信息将会被记录在还款计划表中，每日更新。

例如，假设我们借12,000元本金，选择分12期偿还，按等额本息算法计算一年后需要还的利息。以下是借据表和还款计划表的一些常见字段：

客户借据表：

sql复制

create tableifnotexists dm_risk.fhj_loan_table (
    loan_no        string      comment'借据号（主键）',
    loan_term      int         comment'期限',
    prin_amt       double      comment'本金',
    inter_amt      double      comment'利息',
    remain_prin    double      comment'剩余本金',
    repay_type     string      comment'还款方式',
    loan_date      string      comment'放款日期',
    start_date     string      comment'贷款始期',
    end_date       string      comment'贷款止期',
    settle_date    string      comment'结清日期',
    repay_sts      string      comment'结清状态'
)comment'客户借据表';

还款计划表：

还款计划表记录了每一期的还款日期、应还金额等信息，是客户还款行为的详细记录。

create table if not exists dm_risk.fhj_plan_table ( plan_no        string      comment '计划号(主键)',loan_no        string      comment '借据号',term_no        int         comment '期序',due_date       string      comment '应还日',repay_date     string      comment '实还日',overdue_days   int         comment '逾期天数',amt            double      comment '应还金额',remain_amt     double      comment '剩余本金',prin_amt       double      comment '应还本金',inter_amt      double      comment '应还利息',penalty        double      comment '应还罚息',act_amt        double      comment '实还金额',act_prin_amt   double      comment '实还本金',act_inter_amt  double      comment '实还利息',act_penalty    double      comment '实还罚息',repay_sts      string      comment '结清状态') comment '还款计划表';

在这种情况下，我们设计的借据表和还款计划表可以灵活应对不同分期期限（3期、6期、9期、12期）的需求，因为最长期限为12期。这意味着，无论客户选择哪种分期方式，表格中的字段和结构都能覆盖所有可能的还款周期，从而满足不同期数的还款要求。简而言之，通过合理设计，一张表就可以应对从3期到12期的各种分期情况，无需为每种期限单独设置不同的表格。

create table if not exists dm_risk.fhj_term_define as           select 1  as term_no union all select 2  as term_no union all select 3  as term_no union all select 4  as term_no union all select 5  as term_no union all select 6  as term_no union all select 7  as term_no union all select 8  as term_no union all select 9  as term_no union all select 10 as term_no union all select 11 as term_nounion all select 12 as term_no;

在还款计划表中，我们设定每一期的还款日是每个月固定某一天。比如，如果还款日是每月的15号，那么无论这个月有多少天，还款日都是15号。由于每个月的天数不同（比如30天、31天或28天、29天），所以两个还款日之间的间隔天数并不总是30天。

至于每一期的应还本金、本金和利息等数据，这些是根据具体的还款算法来计算的。不同的还款方式（如等额本息、等额本金等）会得出不同的计算结果，但还款计划表的生成逻辑是固定的，主要是确定每一期的还款日期，然后根据还款算法计算出对应的还款金额。

create table if not exists dm_risk.fhj_plan_table asselect concat(a.loan_no, b.term_no) as plan_no      ,a.loan_no      ,b.term_no      ,add_months(a.loan_date, b.term_no) as due_date      ,null as repay_date      ,0 as overdue_days      -- 以下字段根据实际计息算法得出      ,prin_amt + inter_amt + penalty as amt  -- 应还金额      ,0 as remain_amt  -- 剩余金额      ,0 as prin_amt    -- 本金      ,0 as inter_amt   -- 利息      ,0 as penalty     -- 罚息      -- 以下字段在生成还款计划表时无数据，根据客户还款结果更新      ,act_prin_amt + act_inter_amt + act_penalty as act_amt  -- 实还金额      ,null as act_prin_amt      -- 实还本金      ,null as act_inter_amt     -- 实还利息      ,null as act_penalty       -- 实还罚息      ,'未结清' as repay_sts   -- 结清状态from dm_risk.fhj_loan_table as a        -- 借据表cross join dm_risk.fhj_term_define as b -- 期序定义where a.term >= b.term_no;

结清状态是根据当前日期来判断的，并且会在还款计划中延迟一天（T+1）更新。它的取值主要有两种情况：一种是“已结清”，包括提前结清、在还款日当天结清或逾期后结清；另一种是“未结清”，包括还没到还款日、还款日当天未还或逾期后仍未还。

如果系统不支持部分还款，那么只有当全部金额都还清了，才会在还款计划表里显示具体的结清日期（repay_date）。所以，我们判断这一期是否还款的依据是：如果repay_date有具体日期，说明这笔款项已经全部还清了；如果没有日期，那就表示还没还清。

when repay_date is null or repay_date in ('NULL','Null','null') then '未结清' else '已结清'

如果系统支持部分还款，那么即使本期金额没有全部还清，repay_date 也会显示一个具体的日期。这个日期表示的是最后一次还款的时间，而不是全部还清的时间。

在这种情况下，我们判断这一期是否还款的依据是：如果repay_date有具体日期，说明客户已经进行了还款操作，无论还款金额是否达到本期应还金额；如果repay_date没有日期，说明客户还没有进行还款操作。

case when repay_date is null or repay_date in ('NULL','Null','null') then '未结清'     when act_amt >= amt then '已结清' -- 实还金额 >= 应还金额else '未结清'

为了方便后续进行Vintage分析，我们需要把与Vintage计算相关的字段从借据表（loan）和还款计划表（plan）中提取出来，然后将这些字段组合到一张新的表中。这样做的目的是简化数据处理流程，让后续的分析工作更加便捷高效，避免在多个表之间来回切换查找数据，直接在这张合成的表里就能获取到所有需要的信息，为Vintage分析提供清晰、完整的基础数据。

create table dm_risk.fhj_vintage_loan_plan asselect distinct        l.loan_no      ,l.loan_date             -- 放款日期      ,l.loan_term             -- 借款期数      ,l.prin_amt as loan_amt  -- 借款金额      ,p.plan_no               -- 还款计划号      ,p.term_no               -- 期序      ,p.due_date              -- 应还日期      ,p.repay_date            -- 结清日期      ,p.prin_amt              -- 应还本金      ,p.act_prin_amt          -- 实还本金      ,p.repay_sts             -- 结清状态from dm_risk.fhj_loan_table lleft join dm_risk.fhj_plan_table p on l.loan_no = p.loan_no;

Part 2. 账龄(MOB)的定义

账龄（Months On Books，MOB）是指贷款自发放之日起至当前时间的月数。它反映了贷款在账上的时间长度，是评估贷款风险的重要指标之一。通常，MOB的计算公式为：

MOB=30天当前日期−放款日期

通过计算MOB，我们可以了解贷款处于哪个阶段，进而分析其风险特征。

create table if not exists dm_risk.fhj_mob_month_end as           select '2019-06-30' as mob_date union all select '2019-07-31' as mob_date union all select '2019-08-31' as mob_date union all select '2019-09-30' as mob_date union all select '2019-10-31' as mob_date union all select '2019-11-30' as mob_date union all select '2019-12-31' as mob_dateunion all select '2020-01-31' as mob_dateunion all select '2020-02-29' as mob_dateunion all select '2020-03-31' as mob_dateunion all select '2020-04-30' as mob_dateunion all select '2020-05-31' as mob_dateunion all select '2020-06-30' as mob_dateunion all select '2020-07-31' as mob_dateunion all select '2020-08-31' as mob_dateunion all select '2020-09-30' as mob_dateunion all select '2020-10-31' as mob_dateunion all select '2020-11-30' as mob_dateunion all select '2020-12-31' as mob_date;

Part 3. 逾期天数的计算

逾期天数是指客户未按时还款的天数。计算逾期天数对于评估贷款违约风险至关重要。逾期天数的计算公式为：

逾期天数=当前日期−应还日期

如果逾期天数大于0，则表示客户逾期；如果逾期天数小于等于0，则表示客户按时还款或未到还款日期。

1.在分析过程中，对于那些还没到还款日期的期序，我们暂时不用考虑它们。因为这些期序还没有产生还款行为，所以它们的逾期天数就是0，或者干脆可以不纳入当前的分析范围。只有当期序过了还款日，我们才会去计算逾期天数，看看是否逾期以及逾期了多久。

when due_date >= mob_date then 0 

2.如果客户在当前日期之前（也就是观察日之前）还没有还清这一期的欠款，那么这一期的逾期天数就可以计算出来了。具体来说，逾期天数就是观察日减去应还日的天数差。因为客户从应还日开始就处于“未结清”状态，所以这段时间都算作逾期时间。

when due_date < mob_date and repay_sts = '未结清' then datediff(mob_date, due_date)

3.这种情况是指客户在观察日之前已经还清了这笔款项，但实际还款日期（实还日）是在观察日之后。也就是说，虽然客户在观察日之前已经完成了还款，但我们是在观察日之前判断逾期情况的。

在这种情况下，计算逾期天数的方法是：以观察日为准，减去应还日的天数差。因为从应还日到观察日这段时间内，款项一直处于未结清状态，所以这段时间都算作逾期时间。

when due_date < mob_date and repay_sts = '已结清' and repay_date >= mob_date then datediff(mob_date, due_date) 

4.这种情况说明客户在观察日之前就已经把钱还清了。对于逾期天数的计算，会根据不同的口径来判断：

在 ever口径 下，逾期天数是从应还日到实还日之间的天数差。因为 ever 口径关注的是历史上最严重的逾期情况，所以会计算从应还日到实际还款日这段时间的逾期天数。

而在 current口径 下，逾期天数是0。因为 current 口径关注的是截至观察日的当前状态，既然客户在观察日之前已经还清了，那么在观察日这一天，这笔款项已经没有逾期情况了。

when due_date < mob_date and repay_sts = '已结清' and repay_date <  mob_date then datediff(repay_date, due_date)  -- ever口径

综上所述，我们生成了两张以 plan_no 为唯一主键的逾期天数统计表。这两张表分别对应不同的口径，用于记录逾期天数。

在 ever口径 下，逾期天数的计算逻辑是这样的：

• 如果客户在观察日之前已经还清了款项，那么逾期天数是从应还日到实际还款日（实还日）之间的天数差。因为 ever 口径关注的是历史上最严重的逾期情况，所以会记录从应还日到实还日这段时间的逾期天数。

• 如果客户在观察日之前还没有还清款项，那么逾期天数就是从应还日到观察日之间的天数差。因为在这段时间内款项一直处于未结清状态，所以这段时间都算作逾期时间。

简单来说，ever口径记录的是从应还日到实际还款日（或观察日）之间的逾期天数，重点是反映历史上的最长逾期情况。

create table dm_risk.fhj_mob_ever_overduedays_stat asselect a.plan_no, a.term_no, a.due_date, a.repay_date, a.prin_amt, a.act_prin_amt     , a.loan_no, a.loan_term, a.loan_date, a.loan_amt     , b.mob_date     , months_between(mob_date, last_day(loan_date)) as mob,  case when due_date >= mob_date then 0when due_date <  mob_date and repay_sts = '未结清' then datediff(mob_date, due_date)when due_date <  mob_date and repay_sts = '已结清' and repay_date >= mob_date then datediff(mob_date, due_date) when due_date <  mob_date and repay_sts = '已结清' and repay_date <  mob_date then datediff(repay_date, due_date) else 0 end as ever_overdue_daysfrom dm_risk.fhj_vintage_loan_plan as a  -- 还款计划表cross join dm_risk.fhj_mob_month_end as b   -- 月末时点where mob_date <= '2020-10-25'

current口径逾期天数计算逻辑：

create table dm_risk.fhj_mob_curr_overduedays_stat asselect a.plan_no, a.term_no, a.due_date, a.repay_date, a.prin_amt, a.act_prin_amt     , a.loan_no, a.loan_term, a.loan_date, a.loan_amt     , b.mob_date     , months_between(mob_date, last_day(loan_date)) as mob,  case when due_date >= mob_date then 0when due_date <  mob_date and repay_sts = '未结清' then datediff(mob_date, due_date)when due_date <  mob_date and repay_sts = '已结清' and repay_date >= mob_date then datediff(mob_date, due_date) when due_date <  mob_date and repay_sts = '已结清' and repay_date <  mob_date then 0 else 0 end as curr_overdue_daysfrom dm_risk.fhj_vintage_loan_plan as a  -- 还款计划表cross join dm_risk.fhj_mob_month_end as b   -- 月末时点where mob_date <= '2020-10-25'

Part 4. 剩余本金的计算

剩余本金是指贷款在当前时间点尚未偿还的本金金额。计算剩余本金有助于评估贷款的剩余风险。剩余本金的计算公式为：

剩余本金=本金−已还本金

其中，已还本金可以通过还款记录累加计算得出。

create table if not exists dm_risk.fhj_loan_ever_m1_stat asselect a.loan_no         -- 借据号      ,a.loan_term       -- 借款期限      ,a.loan_amt        -- 借款本金      ,a.loan_date       -- 借款日期      ,a.mob             -- 账龄      ,a.mob_date        -- 观察点            ,sum(if(due_date < mob_date, prin_amt, 0)) as due_prin_amt -- 到期应还本金      ,sum(if(due_date < mob_date and ever_overdue_days > 30, prin_amt, 0)) as ovd_prin_amt_ever  -- 逾期本金      ,max(if(due_date < mob_date and ever_overdue_days > 30, 1, 0)) as ovd_flag_ever      ,if(max(if(due_date < mob_date and ever_overdue_days > 30, 1, 0)) = 1 -- 在观察点前，只要任意一期满足m1+标记，则该借据为m1+         ,loan_amt - sum(if(repay_date <= mob_date, act_prin_amt, 0)) -- 借款本金 - 已还本金         ,0       ) as ovd_loan_bal_ever -- 剩余本金     from dm_risk.fhj_mob_ever_overduedays_stat as agroup by a.loan_no        ,a.loan_term      ,a.loan_amt       ,a.loan_date      ,a.mob            ,a.mob_date ;

Part 5. vintage透视表呈现

Vintage透视表是展示贷款资产动态变化的重要工具。它通常以MOB为横轴，以不同风险状态（如正常、逾期、违约等）为纵轴，展示贷款资产在不同时间点的状态分布情况。通过Vintage透视表，我们可以直观地观察贷款资产的风险变化趋势，为风控决策提供有力支持。

create table if not exists dm_risk.fhj_vintage_m1_plus_stat as select substr(loan_date, 1 ,7) as loan_month -- 放款月份，即vintage,mob, loan_term -- MOB、期限（之后作为透视表筛选维度）,count(loan_no) as cnt  -- 放款订单量#,sum(loan_amt) as amt   -- 放款金额量¥,sum(ovd_flag_ever) as m1_plus_cnt      -- 逾期M1+放款订单量#,sum(ovd_loan_bal_ever) as m1_plus_amt  -- 逾期M1+剩余金额量¥from dm_risk.fhj_loan_ever_m1_statwhere mob_date <= '2020-10-25'group by substr(loan_date, 1, 7), mob, loan_term;

Part 6. 总结

本文详细介绍了Vintage分析表的计算过程，从借据与还款计划表的建立，到账龄计算、逾期天数计算、剩余本金计算，再到Vintage透视表的呈现。通过这些步骤，我们可以全面了解贷款资产的动态变化，为信贷风控提供有力支持。希望本文能为你在风控领域的工作带来帮助。