Python代码输出0而非目标值：SAS转Python贷款计算问题

问题背景 #

我在将SAS代码转换为Python的项目中，大部分脚本已完成，但以下SAS逻辑的Python复现存在问题：循环内的prinbal和intbal始终返回0，无法得到期望的计算结果。

SAS原始代码 #

if balloondt_i <= intnx('month',datadt,1,'end') then prinamt = currbal;
else do;
    do i = 1 to dpd_mult;
        if currbal > 0 then do;
            intbal = currbal * rate / 1200 * pmtfreq;
            if      int_only = 'IO'  then prinbal = 0;
            else if intbal >= pmtamt then prinbal = mort(currbal,.,rate*pmtfreq/1200,max(1,(intck('month',datadt,balloondt)-((i-1)*pmtfreq)))/pmtfreq) - intbal;
            else                          prinbal = pmtamt - intbal;

            currbal = currbal - prinbal;
            prinamt = prinamt + prinbal;
            intamt  = intamt + intbal;
        end;
    end;
end;

Python示例数据集 #

import pandas as pd
import datetime as dt
import numpy_financial as npf
from dateutil.relativedelta import relativedelta

df = pd.DataFrame({
    'loannum': [111, 222],
    'datadt': [dt.datetime(2023, 12, 31), dt.datetime(2023, 12, 31)],
    'balloondt_i': [dt.datetime(2026, 6, 1), dt.datetime(2031, 3, 1)],
    'balloondt': [dt.datetime(2026, 6, 1), dt.datetime(2031, 3, 1)],
    'currbal': [2171044.89, 2020983.87],
    'rate': [5.50, 4.17],
    'pmtfreq': [1, 1],
    'int_only': [None, None],
    'dpd_mult': [1, 3],
    'prinbal': [0, 0],
    'prinamt': [0, 0],
    'pmtamt': [77622.93, 26188.26],
    'intamt': [0, 0],
    'intbal': [0, 0]
})

错误的Python复现代码 #

for idx in df.index:
    datadt = df.loc[idx, 'datadt']
    balloondt_i = df.loc[idx, 'balloondt']  # 错误：应该取balloondt_i
    currbal = df.loc[idx, 'currbal']
    rate = df.loc[idx, 'rate']
    pmtfreq = df.loc[idx, 'pmtfreq']
    pmtamt = df.loc[idx, 'pmtamt'] if not pd.isna(df.loc[idx, 'pmtamt']) else None
    int_only = df.loc[idx, 'int_only']

    if balloondt_i <= datadt + relativedelta(months=+1):  # Approximates 'intnx'
        df.loc[idx, 'prinamt'] = df.loc[idx, 'currbal']
    else:
        for i in range(df.loc[idx, 'dpd_mult']):
            if currbal <= 0:  # Break if current balance is zero or negative
                break
            intbal = df.loc[idx, 'currbal'] * rate / 1200 * pmtfreq  # 错误：应该用当前循环的currbal变量
            if int_only == 'IO':
                prinbal = 0
            elif intbal >= pmtamt:
                nperiods = max(1, (balloondt_i.year - datadt.year) * 12 + (balloondt_i.month - datadt.month) - (i - 1) * pmtfreq)
                # 错误：pmt参数符号和利率计算错误
                prinbal = npf.pmt(rate * pmtfreq / 1200 / 12, nperiods, df.loc[idx, 'currbal']) - intbal
            else:
                prinbal = pmtamt - intbal
                # 错误：仅在else分支更新，SAS逻辑是所有分支都要更新
                df.loc[idx, 'currbal'] -= prinbal
                df.loc[idx, 'prinamt'] += prinbal
                df.loc[idx, 'intamt'] += intbal

关键错误分析 #

1. 变量赋值错误：将balloondt赋值给balloondt_i，导致日期判断逻辑错误
2. 循环内数据引用错误：intbal计算始终取DataFrame初始的currbal，而非循环中实时更新的变量值
3. 更新逻辑范围错误：仅在else分支更新currbal/prinamt/intamt，SAS逻辑中所有分支计算完prinbal后都要执行更新
4. 金融函数参数错误：npf.pmt的利率参数计算错误，且SASmort与Pythonpmt的符号逻辑相反（pmt返回负值，需取绝对值）
5. 未写入计算结果：未将循环中计算的prinbal/intbal写入DataFrame
6. 循环索引不匹配：Pythonrange生成的索引与SAS的i起始值不同，需调整nperiods计算中的索引逻辑

修复后的Python代码 #

for idx in df.index:
    datadt = df.loc[idx, 'datadt']
    # 修正：取正确的balloondt_i字段
    balloondt_i = df.loc[idx, 'balloondt_i']
    # 复制当前currbal到变量，循环中实时更新
    currbal = df.loc[idx, 'currbal']
    rate = df.loc[idx, 'rate']
    pmtfreq = df.loc[idx, 'pmtfreq']
    pmtamt = df.loc[idx, 'pmtamt'] if not pd.isna(df.loc[idx, 'pmtamt']) else None
    int_only = df.loc[idx, 'int_only']
    dpd_mult = df.loc[idx, 'dpd_mult']
    # 初始化当前行的累计值
    total_prin = 0
    total_int = 0
    last_prinbal = 0
    last_intbal = 0

    # 修正intnx的计算：取datadt下一个月的月末
    next_month_end = datadt + relativedelta(months=1, day=31)
    if balloondt_i <= next_month_end:
        df.loc[idx, 'prinamt'] = currbal
        df.loc[idx, 'currbal'] = 0
        df.loc[idx, 'intamt'] = 0
    else:
        for i in range(1, dpd_mult + 1):  # 匹配SAS的i从1到dpd_mult
            if currbal <= 0:
                break
            # 用实时更新的currbal计算intbal
            intbal = currbal * rate / 1200 * pmtfreq
            if int_only == 'IO':
                prinbal = 0
            elif intbal >= pmtamt:
                # 计算剩余期数
                months_diff = (balloondt_i.year - datadt.year) * 12 + (balloondt_i.month - datadt.month)
                nperiods = max(1, months_diff - (i - 1) * pmtfreq)
                # 修正pmt参数：利率无需再除以12，取绝对值匹配SAS mort逻辑
                monthly_pmt = abs(npf.pmt(rate * pmtfreq / 1200, nperiods, currbal))
                prinbal = monthly_pmt - intbal
            else:
                prinbal = pmtamt - intbal
            
            # 更新变量
            currbal -= prinbal
            total_prin += prinbal
            total_int += intbal
            last_prinbal = prinbal
            last_intbal = intbal
        
        # 将计算结果写入DataFrame
        df.loc[idx, 'currbal'] = currbal
        df.loc[idx, 'prinamt'] = total_prin
        df.loc[idx, 'intamt'] = total_int
        df.loc[idx, 'prinbal'] = last_prinbal
        df.loc[idx, 'intbal'] = last_intbal

期望输出 #

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