求贷款目标余额对应还款期数：SQL/Excel优化需求

你当前的核心问题是依赖双层逐期循环暴力枚举还款期数，导致性能极差（耗时61小时）。本质是没利用贷款摊销的数学公式直接反解，而是用低效的枚举逻辑。下面直接将你现有的UPB计算公式变形，推导出还款期数的求解公式，实现O(1)级别的计算，彻底解决性能问题。

变量定义（对应你原函数的参数） #

• P = @original_unearned_premium_balance：初始未赚保费余额
• r = @interest_rate / 12：月利率
• T = @original_term：贷款总期数
• M = 月还款额，即你原函数中的ROUND((r*P)/(1-POWER(1+r, -T)), 2)
• UPB_target：已知的目标未偿本金余额
• n：待求解的还款期数（对应原函数的@payments_made）

公式推导（从UPB公式反解n） #

原UPB计算公式：

UPB = P*(1+r)^n - M*((1+r)^n - 1)/r

合并整理含(1+r)^n的项：

UPB = (1+r)^n*(P - M/r) + M/r

移项得到(1+r)^n的表达式：

(1+r)^n = (UPB_target - M/r) / (P - M/r)

两边取自然对数，解出n：

n = LN( (UPB_target - M/r) / (P - M/r) ) / LN(1+r)

由于n必须是整数，最后根据业务需求取整（比如向上取整，代表需完成该期数才能达到目标余额；或向下取整后验证实际UPB）

改写后的SQL自定义函数 #

完全去掉循环，直接用公式计算：

CREATE FUNCTION dbo.GetPaymentsFromUPB(
    @original_unearned_premium_balance DECIMAL(11,2),
    @interest_rate FLOAT,
    @original_term INTEGER,
    @target_upb DECIMAL(11,2)
)
RETURNS INTEGER
AS
BEGIN
    DECLARE @r FLOAT = @interest_rate / 12;
    DECLARE @M DECIMAL(11,2) = ROUND( (@r * @original_unearned_premium_balance) / (1 - POWER(1 + @r, -@original_term)), 2 );
    DECLARE @numerator FLOAT = @target_upb - (@M / @r);
    DECLARE @denominator FLOAT = @original_unearned_premium_balance - (@M / @r);
    
    -- 处理边界情况
    IF @target_upb = @original_unearned_premium_balance
        RETURN 0;
    IF @target_upb <= 0
        RETURN @original_term;
    
    -- 计算期数并取整，可根据业务调整为CEILING/FLOOR
    DECLARE @n FLOAT = LN(@numerator / @denominator) / LN(1 + @r);
    RETURN CAST(ROUND(@n, 0) AS INTEGER);
END

关键说明 #

1. 该函数为单次O(1)计算，无需遍历每一期，处理贷款组合时性能会从61小时直接降至秒/分钟级别
2. 边界逻辑（如目标UPB超过初始值、负数等）可根据你的业务场景补充
3. 取整逻辑（ROUND/CEILING/FLOOR）需结合实际业务：比如计算出n=12.3，说明第12期结束后UPB仍高于目标，第13期结束后低于目标，需按需返回对应期数

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