本票折价摊销逻辑解析及SQL复现方法技术咨询

一、摊销逻辑解析 #

核心规则：实际利率法（按实际天数计息） #

1. 初始记录：购入日（2023-02-09）记录全额初始折价额（511,824,313.83），代表未来需逐步摊销的总折价。
2. 每期摊销计算：采用实际利率法，以当期期初摊余成本为基数，按年度实际利率（13.5028%）和当期实际天数计算摊销额，公式如下：

   当期折价摊销额 = 当期期初摊余成本 × 年度实际利率 × 当期实际天数 / 365
   

   • 摊余成本初始值为购入成本：1,315,147,186.17
   • 每期摊余成本更新规则：当期期末摊余成本 = 上期期末摊余成本 + 当期摊销额（折价摊销会逐步增加摊余成本，到期日摊余成本等于票面价值1,826,971,500.00）
   • 系统数据中负数表示当期摊销的折价金额（冲减初始待摊销折价）

验证示例 #

• 2023-02-09至2023-03-07（共26天）：

  摊销额 = 1315147186.17 × 13.5028% × 26/365 ≈ 9,001,548.39
  

  与系统数据完全匹配。
• 2023-03-07至2023-03-31（共24天）：
  期初摊余成本 = 1315147186.17 + 9001548.39 = 1324148734.56

  摊销额 = 1324148734.56 × 13.5028% × 24/365 ≈ 14,080,392.04
  

  同样与系统数据一致。

二、SQL复现方案 #

使用递归CTE（Common Table Expression）迭代计算每期摊销额，步骤如下：

1. 准备基础数据与日期序列 #

WITH base_data AS (
    SELECT
        DATE '2023-02-09' AS purchase_date,
        DATE '2025-12-28' AS maturity_date,
        1826971500.00 AS face_value,
        1315147186.17 AS initial_carrying_value,
        511824313.83 AS initial_discount,
        0.135028 AS effective_annual_rate
),
amortization_dates AS (
    SELECT DATE '2023-02-09' AS amort_date UNION ALL
    SELECT DATE '2023-03-07' UNION ALL
    SELECT DATE '2023-03-31' UNION ALL
    SELECT DATE '2023-04-28' UNION ALL
    SELECT DATE '2023-05-31' UNION ALL
    SELECT DATE '2023-06-30' UNION ALL
    SELECT DATE '2023-07-31' UNION ALL
    SELECT DATE '2023-08-31' UNION ALL
    SELECT DATE '2023-09-29' UNION ALL
    SELECT DATE '2023-10-31' UNION ALL
    SELECT DATE '2023-11-30' UNION ALL
    SELECT DATE '2023-12-31' UNION ALL
    SELECT DATE '2024-01-31' UNION ALL
    SELECT DATE '2024-02-29' UNION ALL
    SELECT DATE '2024-03-29' UNION ALL
    SELECT DATE '2024-04-30' UNION ALL
    SELECT DATE '2024-05-31' UNION ALL
    SELECT DATE '2024-06-28' UNION ALL
    SELECT DATE '2024-07-31' UNION ALL
    SELECT DATE '2024-08-30' UNION ALL
    SELECT DATE '2024-09-30' UNION ALL
    SELECT DATE '2024-10-31' UNION ALL
    SELECT DATE '2024-11-29' UNION ALL
    SELECT DATE '2024-12-31' UNION ALL
    SELECT DATE '2025-01-31' UNION ALL
    SELECT DATE '2025-02-28'
),
ranked_dates AS (
    SELECT
        amort_date,
        ROW_NUMBER() OVER (ORDER BY amort_date) AS date_rank
    FROM amortization_dates
)

2. 递归计算摊销额 #

, recursive_amortization AS (
    -- 初始记录：购入日的初始折价
    SELECT
        rd.amort_date,
        bd.initial_discount AS discount_amortization,
        bd.initial_carrying_value AS carrying_value,
        rd.date_rank
    FROM ranked_dates rd
    CROSS JOIN base_data bd
    WHERE rd.date_rank = 1

    UNION ALL

    -- 递归计算后续每期摊销
    SELECT
        curr.amort_date,
        ROUND(prev.carrying_value * bd.effective_annual_rate * DATEDIFF(curr.amort_date, prev.amort_date)/365, 2) * -1 AS discount_amortization,
        ROUND(prev.carrying_value + (prev.carrying_value * bd.effective_annual_rate * DATEDIFF(curr.amort_date, prev.amort_date)/365), 2) AS carrying_value,
        curr.date_rank
    FROM ranked_dates curr
    JOIN recursive_amortization prev ON curr.date_rank = prev.date_rank + 1
    CROSS JOIN base_data bd
)

3. 查询最终结果 #

SELECT
    TO_CHAR(amort_date, 'MM/DD/YYYY') AS "AMORTIZATION DATE",
    discount_amortization AS "DISCOUNT AMORTIZATION",
    TO_CHAR((SELECT maturity_date FROM base_data), 'MM/DD/YYYY') AS "MATURITY DATE"
FROM recursive_amortization
ORDER BY date_rank;

注意事项 #

• 不同SQL方言的日期函数存在差异：比如PostgreSQL中计算天数差用curr.amort_date - prev.amort_date，MySQL用DATEDIFF(curr.amort_date, prev.amort_date)，需根据实际数据库调整。
• 保留两位小数与系统数据精度一致，若需更高精度可调整ROUND函数参数。

内容的提问来源于stack exchange，提问作者Emmanuel Salau