如何基于DataFrame批量生成贷款摊销计划表？

我有一个包含多笔贷款属性的DataFrame，需要为每笔贷款生成还款计划表（展示利息、本金等随时间的变化），并将所有结果合并成一个DataFrame用于对比分析。现有代码仅支持手动输入单条贷款参数生成计划表，但传入DataFrame行数据时，出现RuntimeWarning: overflow encountered in multiply警告且代码无法执行完成，手动输入参数则能正常运行。请问如何遍历DataFrame的每一行，将每行视为一笔贷款并生成合并后的结果？

原代码 #

import pandas as pd
from datetime import date
import numpy as np
from collections import OrderedDict
from dateutil.relativedelta import *


def amortize(principal, interest_rate, years, annual_payments):

    pmt = round(nf.pmt(interest_rate/annual_payments, years*annual_payments, principal), 2)
    # initialize the variables to keep track of the periods and running balances
    p = 1
    beg_balance = principal
    end_balance = principal

    while end_balance > 0:

        # Recalculate the interest based on the current balance
        interest = round(((interest_rate/annual_payments) * beg_balance), 2)

        # Determine payment based on whether or not this period will pay off the loan
        pmt = min(pmt, beg_balance + interest)
        principal = pmt - interest

        # Ensure additional payment gets adjusted if the loan is being paid off
        end_balance = beg_balance - (principal)

        yield OrderedDict([('Period', p),
                           ('Begin Balance', beg_balance),
                           ('Payment', pmt),
                           ('Principal', principal),
                           ('Interest', interest),
                           ('End Balance', end_balance)])

        # Increment the counter, balance and date
        p += 1
        beg_balance = end_balance

尝试的调用代码 #

schedule = pd.DataFrame(amortize(base['principal'][0], base['interest_rate'][0], base['years'][0], base['annual_payments'][0]))
schedule

运行后出现警告：RuntimeWarning: overflow encountered in multiply interest = round(((interest_rate/annual_payments) * beg_balance), 2)，且代码陷入死循环无法结束。

手动输入参数则正常运行：

schedule = pd.DataFrame(amortize(7000, .36, 2.5, 12)) 
schedule

问题原因分析 #

1. 缺失模块导入：原代码使用nf.pmt但未导入numpy_financial模块（通常缩写为nf），手动运行时可能环境已提前加载，但从DataFrame取数时环境未初始化，导致计算异常。
2. 数据类型不匹配：从DataFrame取数时，获取的是pandas.Series对象而非标量值，引发数组运算而非单值计算，导致数值溢出。
3. 死循环逻辑缺陷：当每期还款额pmt小于当期利息时，本金偿还额为负数，end_balance会持续增大，导致while end_balance > 0永远成立，陷入死循环并触发溢出警告。

解决步骤与修正代码 #

1. 修复函数核心问题 #

• 补充缺失的模块导入；
• 强制将输入参数转为标量数值；
• 添加死循环防护逻辑，避免余额无限增长。

2. 遍历DataFrame生成合并计划表 #

• 使用iterrows()遍历每一行，为每笔贷款生成独立还款计划；
• 添加贷款标识列，区分不同贷款的还款记录；
• 合并所有子计划为一个统一的DataFrame。

修正后的完整代码 #

import pandas as pd
import numpy as np
import numpy_financial as nf  # 补充导入缺失模块
from collections import OrderedDict

def amortize(principal, interest_rate, years, annual_payments, loan_id=None):
    # 强制转为标量，避免Series类型引发的数组运算
    principal = float(principal)
    interest_rate = float(interest_rate)
    years = float(years)
    annual_payments = int(annual_payments)
    
    total_periods = int(years * annual_payments)
    # nf.pmt默认返回负数（代表现金流出），取绝对值作为还款额
    pmt = abs(round(nf.pmt(interest_rate/annual_payments, total_periods, principal), 2))
    
    p = 1
    beg_balance = principal
    end_balance = principal

    # 增加周期上限，防止极端情况触发死循环
    while end_balance > 0 and p <= total_periods + 1:
        interest = round(((interest_rate/annual_payments) * beg_balance), 2)
        
        # 若还款额不足以覆盖利息，直接偿还全部本息
        if pmt < interest:
            pmt = beg_balance + interest
        
        principal_paid = pmt - interest
        end_balance = max(beg_balance - principal_paid, 0)  # 确保余额不小于0
        
        record = OrderedDict([
            ('Loan ID', loan_id),
            ('Period', p),
            ('Begin Balance', beg_balance),
            ('Payment', pmt),
            ('Principal', principal_paid),
            ('Interest', interest),
            ('End Balance', end_balance)
        ])
        
        yield record
        
        p += 1
        beg_balance = end_balance

# 示例DataFrame（替换为你的实际数据）
base = pd.DataFrame({
    'principal': [7000, 10000],
    'interest_rate': [0.36, 0.12],
    'years': [2.5, 3],
    'annual_payments': [12, 12]
})

# 遍历生成所有贷款的还款计划并合并
all_schedules = []
for idx, row in base.iterrows():
    loan_schedule = pd.DataFrame(amortize(
        principal=row['principal'],
        interest_rate=row['interest_rate'],
        years=row['years'],
        annual_payments=row['annual_payments'],
        loan_id=idx  # 用行索引作为贷款唯一标识
    ))
    all_schedules.append(loan_schedule)

combined_schedule = pd.concat(all_schedules, ignore_index=True)
print(combined_schedule)

关键说明 #

• 标量转换：通过float()和int()将DataFrame行值转为单值，避免数组运算引发的溢出；
• 死循环防护：增加周期上限判断，同时处理还款额不足以覆盖利息的极端情况；
• 贷款标识：添加Loan ID列，方便后续对比不同贷款的还款差异；
• pmt修正：对nf.pmt结果取绝对值，符合还款额为正数的常规认知。

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