手把手教学:用Excel和SQL画出你的第一条Vintage风险曲线

当别人还在用模糊的“感觉”判断资产质量时，你已经能用一条精准的曲线预测未来6个月的坏账走势——这就是专业风控的硬核能力。

01 数据准备：你需要这5个核心字段

Vintage分析始于正确的数据结构。以下是每个字段的详细说明：

必选字段（缺一不可）

1. 1. 放款日期：精确到日，格式统一（建议YYYY-MM-DD）
• • 用途：确定客户所属的Vintage批次
• • 注意：同一客户多笔贷款应视为不同记录
2. 2. 客户ID：唯一标识符
• • 用途：追踪单个客户在整个生命周期的表现
• • 注意：确保ID系统稳定，避免重复或变更
3. 3. 逾期状态：时点逾期状态
• • 建议采用标准定义：C-M1-M2-M3-M4+（C为正常）
• • 统计时点：通常为月末最后一天的状态
4. 4. 逾期天数：当前逾期持续天数
• • 用途：计算M1+、M3+等指标
• • 注意：与逾期状态保持逻辑一致
5. 5. 还款记录：每期还款情况
• • 最小字段：应还款日、实还款日、应还金额、实还金额
• • 用途：验证逾期状态，计算回收率

扩展字段（提升分析深度）

• • 产品类型、渠道来源、客户评分
• • 借款金额、期限、利率
• • 地理位置、职业信息（如可用）

实战技巧：建立一个“数据需求清单”，每次提取数据前核对。某机构曾因漏提“提前结清标志”，导致Vintage曲线在后期异常下降——不是风险改善，而是高风险客户提前结清造成的统计假象。

02 数据清洗：避开这三个“天坑”

原始数据从不完美，清洗质量决定分析结果的可信度。

天坑一：缺失值处理

问题场景：放款日期缺失、逾期状态为空专业处理：

1. 1. 放款日期缺失 → 直接剔除，无法确定批次
2. 2. 逾期状态为空 → 根据还款记录反向推导，若推导失败则标记为“数据异常”
3. 3. 关键字段缺失率>5% → 暂停分析，先解决数据源问题

天坑二：异常值识别

常见异常：

• • 放款日期在未来（系统错误）
• • 逾期天数>1000（通常为系统默认值）
• • 还款金额为负数（冲正交易）

处理流程：

-- SQL示例：识别放款日期异常SELECT loan_id, loan_date FROM loan_table WHERE loan_date > GETDATE() -- 未来日期OR loan_date <'2020-01-01'-- 不合理的历史日期

天坑三：特殊客户处理规则

这是Vintage分析最易出错的部分：

提前结清客户：

• • 错误做法：从结清当月开始从分母中剔除
• • 正确做法：在观察期内始终保留在分母中
• • 理由：评估的是放款时的风险判断质量，不是持有期管理能力

核销客户：

• • 核销后不再计入分子（逾期客户数）
• • 但观察期内始终保留在分母中
• • 单独标记，可计算核销率曲线

债务重组客户：

• • 重组后视为新贷款，重新开始账龄计数
• • 原贷款记录保留至观察期结束

清洗检查清单：

• • 各批次客户数在MOB0时是否合理？
• • 相邻MOB客户数下降幅度是否在预期内（通常<5%）？
• • 逾期率有无跳变（如从0%直接到10%）？
• • 核销客户是否被正确处理？

03 手动计算（Excel）：从零到一理解本质

我们以2024年1月批次为例，逐步计算：

步骤1：数据透视表构建批次-账龄矩阵

1. 1. 添加辅助列“批次”：

   =TEXT([@放款日期],"YYYY-MM")

2. 2. 添加辅助列“账龄(MOB)”：

   =DATEDIF([@放款日期],统计月末,"M")

   注：MOB0为放款当月，MOB1为放款后第一个完整月
3. 3. 创建数据透视表：
• • 行：批次 + 账龄
• • 列：逾期状态（分组为C/M1/M2/M3+）
• • 值：客户ID计数（去重）

步骤2：计算单账龄逾期率

在透视表右侧添加计算列：

MOB0总客户数 = MOB0时该批次所有状态客户数之和MOB1 M1+逾期率 = MOB1时M1及以上客户数 / MOB0总客户数MOB2 M1+逾期率 = MOB2时M1及以上客户数 / MOB0总客户数...

步骤3：计算累计逾期率（Vintage核心指标）

MOB1累计M3+逾期率 = MOB1时M3+客户数 / MOB0总客户数MOB2累计M3+逾期率 = (MOB1时M3+客户数 + MOB2新增M3+客户数) / MOB0总客户数

关键：累计逾期率的分子是“曾经达到过该逾期状态的客户数”，即使后续还款或核销

步骤4：绘制Vintage曲线

1. 1. 选择数据：批次为系列，账龄为X轴，累计逾期率为Y轴
2. 2. 插入折线图
3. 3. 专业美化技巧：
• • 不同批次用不同颜色/线型
• • 添加数据标签（关键MOB点）
• • 添加参考线（行业基准、公司目标）
• • 图表标题明确：如“2024年各批次累计M3+逾期率Vintage曲线”

Excel模板技巧：创建动态名称和下拉菜单，实现“选择批次→自动更新曲线”的交互效果。

04 自动化计算（SQL）：批量处理的工业级方案

对于百万级以上的数据，Excel已力不从心。以下是生产环境SQL脚本的核心逻辑：

基础Vintage统计脚本

WITH-- 步骤1：基础数据准备loan_base AS (SELECT        customer_id,        loan_id,        loan_date,        DATE_FORMAT(loan_date, '%Y-%m') as vintage_month,        loan_amount,        product_typeFROM loan_originationWHERE loan_date >='2024-01-01'AND loan_date <='2024-06-30'AND status ='放款成功'),-- 步骤2：生成账龄日历calendar AS (SELECTDISTINCT        vintage_month,-- 计算每个vintage到各月末的账龄        PERIOD_DIFF(report_month, vintage_month) as mobFROM (SELECTDISTINCT DATE_FORMAT(loan_date, '%Y-%m') as vintage_monthFROM loan_base    ) v,    (SELECTDISTINCT DATE_FORMAT(report_date, '%Y-%m') as report_monthFROM performance_snapshotWHERE report_date >='2024-01-01'    ) rWHERE PERIOD_DIFF(report_month, vintage_month) >=0),-- 步骤3：账龄表现快照performance AS (SELECT        lb.vintage_month,        cal.mob,        lb.customer_id,        lb.loan_id,        ps.dpd, -- 逾期天数CASEWHEN ps.dpd >=90THEN1ELSE0ENDas is_m3_plusFROM loan_base lbJOIN calendar cal ON lb.vintage_month = cal.vintage_monthLEFTJOIN performance_snapshot ps ON lb.loan_id = ps.loan_id AND ps.report_date = DATE_FORMAT(            DATE_ADD(STR_TO_DATE(CONCAT(lb.vintage_month,'-01'), '%Y-%m-%d'), INTERVAL cal.mob MONTH),'%Y-%m-01'        )),-- 步骤4：Vintage指标计算vintage_stats AS (SELECT        vintage_month,        mob,COUNT(DISTINCT customer_id) as vintage_cohort_size,SUM(is_m3_plus) as m3_plus_count,-- 累计M3+逾期率SUM(SUM(is_m3_plus)) OVER (PARTITIONBY vintage_month ORDERBY mob        ) as cum_m3_plus_countFROM performanceGROUPBY vintage_month, mob)-- 步骤5：最终输出SELECT    vintage_month,    mob,    vintage_cohort_size,    cum_m3_plus_count,-- 核心指标：累计M3+逾期率    ROUND(cum_m3_plus_count *100.0/FIRST_VALUE(vintage_cohort_size) OVER (PARTITIONBY vintage_month ORDERBY mob        ), 2) as cum_m3_plus_rateFROM vintage_statsORDERBY vintage_month, mob;

脚本优化技巧

1. 1. 分区键设置：按vintage_month分区，大幅提升查询性能
2. 2. 增量计算：每日只计算新增账龄的数据，避免全量重算
3. 3. 结果缓存：将Vintage统计结果存入专用表，供多场景调用
4. 4. 监控告警：添加数据质量检查（如客户数骤降>10%时告警）

05 实操任务：2024年1-6月批次Vintage分析

现在，让我们完成一个完整任务：

任务描述

基于提供的脱敏数据集（模拟数据），绘制2024年1-6月各放款批次的Vintage曲线，分析：

1. 1. 各批次风险收敛速度差异
2. 2. 识别异常批次
3. 3. 预测各批次终极损失率

数据集说明

• • 文件：loan_performance_2024.csv
• • 记录数：约50万条
• • 时间范围：2024年1-6月放款，追踪至2024年12月
• • 字段：loan_id, customer_id, loan_date, product_type, dpd_202401, dpd_202402, ..., dpd_202412

分步指南

步骤1：数据导入与检查

# Python示例（也可用Excel）import pandas as pddf = pd.read_csv('loan_performance_2024.csv')print(f"总记录数: {len(df)}")print(f"批次分布:")print(df['loan_date'].str[:7].value_counts().sort_index())

步骤2：计算各批次各账龄逾期率

results = []for vintage in df['loan_date'].str[:7].unique():    vintage_df = df[df['loan_date'].str[:7] == vintage]    cohort_size = len(vintage_df)for mob inrange(0, 13):  # 观察13个月        month_col = f'dpd_{2024 + (mob//12)}{str((mob%12)+1).zfill(2)}'if month_col in df.columns:            m3_count = (vintage_df[month_col] >= 90).sum()            cum_rate = m3_count / cohort_size            results.append([vintage, mob, cohort_size, cum_rate])

步骤3：可视化呈现

import matplotlib.pyplot as pltimport seaborn as snsresult_df = pd.DataFrame(results, columns=['vintage', 'mob', 'cohort_size', 'cum_m3_rate'])plt.figure(figsize=(12, 8))for vintage in result_df['vintage'].unique():    vintage_data = result_df[result_df['vintage'] == vintage]    plt.plot(vintage_data['mob'], vintage_data['cum_m3_rate']*100,              marker='o', label=vintage, linewidth=2)plt.xlabel('账龄 (MOB)', fontsize=12)plt.ylabel('累计M3+逾期率 (%)', fontsize=12)plt.title('2024年1-6月批次 Vintage 曲线对比', fontsize=14, fontweight='bold')plt.grid(True, alpha=0.3)plt.legend(title='放款批次')plt.tight_layout()plt.savefig('vintage_curve_2024.png', dpi=300)plt.show()

专业分析要点

1. 1. 曲线形状分析：
• • 陡峭上升：风险早期快速暴露
• • 平缓上升：风险缓慢释放
• • 平台期：风险基本收敛
2. 2. 批次对比：
• • 1月批次：13个月累计逾期率2.1%（基准）
• • 4月批次：9个月已达2.3%（预警）
• • 6月批次：6个月仅0.8%（表现优异）
3. 3. 根本原因挖掘：
• • 检查异常批次的产品结构、渠道构成
• • 分析同期审批策略变化
• • 考虑季节性因素影响

06 从曲线到决策：Vintage分析的闭环应用

画出曲线只是开始，真正的价值在于驱动决策：

决策点1：审批策略调整

• • 若某批次风险显著高于历史：收紧同类客户审批
• • 若某批次风险显著低于历史：可适度放宽，测试边界

决策点2：定价策略优化

• • 高风险批次对应的客群 → 提高定价
• • 低风险批次对应的客群 → 降低定价，提升竞争力

决策点3：拨备计提校准

• • 基于Vintage曲线预测终极损失率
• • 动态调整拨备覆盖率

决策点4：绩效考核关联

• • 客户经理：放款批次的Vintage表现
• • 审批人员：审批决策的长期质量
• • 催收团队：逾期客户的回收表现

某金融机构将Vintage分析嵌入月度经营会议流程：

1. 1. 会前：自动化生成Vintage报告
2. 2. 会中：重点分析异常批次，制定调整方案
3. 3. 会后：跟踪调整效果，下月会议复盘

这一流程实施后，风险识别时间平均提前了4个月，年度坏账损失降低了28%。

在金融风控领域，理论与实践之间，只差一次完整的实操。

看过再多理论文章，都不如亲手从原始数据画出一条完整的Vintage曲线。这条曲线不仅连接了过去与未来，更连接了数据与决策。

当你的同事还在为“感觉风险在上升”而争论不休时，你只需要调出Vintage曲线，指着4月批次在第8个月的异常上升点说：“不是感觉，是数据。问题出现在这个批次，原因可能是这个，我们应该这样做。”

这就是专业的力量——用数据说话，用曲线预测，用事实决策。

现在，打开你的Excel或SQL编辑器，开始绘制属于你的第一条Vintage曲线吧。从今天起，让风险在你的曲线中无所遁形。