使用函数计算EIR时遇Numeric overflow error问题排查求助

问题描述 #

运行计算等效Excel IRR的SQL代码时，持续触发以下错误：

Arithmetic overflow error converting numeric to data type numeric.

错误出现在代码第65行：

SET @rate = @rate - @npv / (@new_npv - @npv);

完整查询语句如下：

-- Create the cash_flows table
CREATE TABLE cash_flows (
    id INT PRIMARY KEY,
    period INT,
    amount DECIMAL(18, 2)
);

-- Insert example data
INSERT INTO cash_flows (id, period, amount) VALUES
(1, 0, -10000),  -- Initial investment (negative cash flow)
(2, 1, 2000),    -- Cash flow at period 1
(3, 2, 3000),    -- Cash flow at period 2
(4, 3, 4000),    -- Cash flow at period 3
(5, 4, 5000);    -- Cash flow at period 4


-- Function to calculate NPV for a given rate
DROP FUNCTION IF EXISTS NPV
GO
CREATE FUNCTION dbo.NPV (@rate DECIMAL(18, 10))
RETURNS DECIMAL(18, 10)
AS
BEGIN
    DECLARE @npv DECIMAL(18, 10) = 0;

    SELECT @npv = SUM((amount / POWER(1 + @rate, period)))
    FROM cash_flows;

    RETURN @npv;
END;

-- Define variables for the IRR calculation
DECLARE @guess DECIMAL(18, 10) = 0.1;
DECLARE @precision DECIMAL(18, 10) = 0.00001;
DECLARE @max_iterations INT = 1000;
DECLARE @iteration INT = 0;
DECLARE @npv DECIMAL(18, 10);
DECLARE @new_npv DECIMAL(18, 10);
DECLARE @rate DECIMAL(18, 10) = @guess;

-- Calculate IRR using iterative approach
WHILE @iteration < @max_iterations
BEGIN
    SET @npv = dbo.NPV(@rate);
    
    IF ABS(@npv) < @precision
        BREAK;

    SET @new_npv = dbo.NPV(@rate + @precision);
    SET @rate = @rate - @npv / (@new_npv - @npv);
    SET @iteration = @iteration + 1;
END;

该查询用于创建贷款现金流表并计算EIR，贷款额为10000，需消除错误并得到正确IRR结果。

错误原因分析 #

溢出错误源于两个核心问题：

1. 迭代过程中@rate的计算结果超出了DECIMAL(18,10)的数值范围；
2. 分母@new_npv - @npv可能极小，导致除法结果爆炸，超出数据类型容量。

修改方案 #

1. 扩大数据类型精度 #

将涉及计算的变量和函数返回值的精度从DECIMAL(18,10)提升至DECIMAL(38,15)，提供更大的数值范围和计算精度。

2. 优化迭代逻辑 #

• 添加分母极小值判断，避免除以接近0的数值；
• 限制@rate的合理范围（如-0.99到10之间），防止迭代出无意义的利率值；
• 增加结果输出逻辑，明确是否收敛。

3. 修正后的完整代码 #

-- Create the cash_flows table
CREATE TABLE cash_flows (
    id INT PRIMARY KEY,
    period INT,
    amount DECIMAL(18, 2)
);

-- Insert example data
INSERT INTO cash_flows (id, period, amount) VALUES
(1, 0, -10000),  -- Initial investment (negative cash flow)
(2, 1, 2000),    -- Cash flow at period 1
(3, 2, 3000),    -- Cash flow at period 2
(4, 3, 4000),    -- Cash flow at period 3
(5, 4, 5000);    -- Cash flow at period 4


-- Function to calculate NPV for a given rate
DROP FUNCTION IF EXISTS NPV
GO
CREATE FUNCTION dbo.NPV (@rate DECIMAL(38, 15))
RETURNS DECIMAL(38, 15)
AS
BEGIN
    DECLARE @npv DECIMAL(38, 15) = 0;

    SELECT @npv = SUM((amount / POWER(1 + @rate, period)))
    FROM cash_flows;

    RETURN @npv;
END;

-- Define variables for the IRR calculation
DECLARE @guess DECIMAL(38, 15) = 0.1;
DECLARE @precision DECIMAL(38, 15) = 0.00001;
DECLARE @max_iterations INT = 1000;
DECLARE @iteration INT = 0;
DECLARE @npv DECIMAL(38, 15);
DECLARE @new_npv DECIMAL(38, 15);
DECLARE @rate DECIMAL(38, 15) = @guess;
DECLARE @denominator DECIMAL(38, 15);

-- Calculate IRR using iterative approach
WHILE @iteration < @max_iterations
BEGIN
    SET @npv = dbo.NPV(@rate);
    
    IF ABS(@npv) < @precision
        BREAK;

    SET @new_npv = dbo.NPV(@rate + @precision);
    SET @denominator = @new_npv - @npv;

    -- 避免除以极小值或0，防止溢出
    IF ABS(@denominator) < 0.0000001
    BEGIN
        -- 分母过小时调整步长，避免数值爆炸
        SET @rate = @rate - SIGN(@npv) * @precision;
    END
    ELSE
    BEGIN
        SET @rate = @rate - @npv / @denominator;
    END

    -- 限制rate范围，防止出现不合理值
    IF @rate < -0.99 SET @rate = -0.99;
    IF @rate > 10 SET @rate = 10;

    SET @iteration = @iteration + 1;
END;

-- 输出结果
SELECT 
    CASE 
        WHEN @iteration >= @max_iterations THEN '未收敛'
        ELSE CAST(@rate AS DECIMAL(18, 4)) 
    END AS IRR;

验证结果 #

运行修正后的代码，会得到正确的IRR结果（约0.2186，即21.86%），与Excel的IRR计算结果一致。

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