admin管理员组

文章数量:1289828

I found a sample function for calculating the equivalent of Excel IRR using sample test data but I keep getting this error:

Arithmetic overflow error converting numeric to data type numeric.

which occurs on line 65 e.g.

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

This is the query:

-- 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;

This is supposed to create a sample table for cashflows for a loan and calculate the EIR based on the cashflows table. The loan is for 10000 and the expected cashflows per period are listed in the table.

Please assist to identify what needs to be changed or cast to remove the error. The expected output is an IRR rate value based on the computation. First of all, I'm not entirely certain the query is correct but it runs. I got it from Microsoft copilot.

I found a sample function for calculating the equivalent of Excel IRR using sample test data but I keep getting this error:

Arithmetic overflow error converting numeric to data type numeric.

which occurs on line 65 e.g.

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

This is the query:

-- 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;

This is supposed to create a sample table for cashflows for a loan and calculate the EIR based on the cashflows table. The loan is for 10000 and the expected cashflows per period are listed in the table.

Please assist to identify what needs to be changed or cast to remove the error. The expected output is an IRR rate value based on the computation. First of all, I'm not entirely certain the query is correct but it runs. I got it from Microsoft copilot.

  • sql
  • sql-server
  • irr
Share Improve this question edited Feb 19 at 21:11 Dale K 27.5k15 gold badges58 silver badges83 bronze badges asked Feb 19 at 19:32 Emmanuel SalauEmmanuel Salau 236 bronze badges 15
  • 1 GO after func create – IVNSTN Commented Feb 19 at 19:38
  • At what point are you getting the error? – Andrew Commented Feb 19 at 19:43
  • 2 During your second iteration, you try to assign the value -3571162108885.27834640677142857142 to the variable @rate; that won't fit. @rate is defined as a DECIMAL(18, 10) so it can only have 8 digits before the decimal point; the value you attempt to assign has 13. db<<fiddle with simple debugging added. – Thom A Commented Feb 19 at 19:56
  • 1 Line numbers are certainly useful if you have an error message saying "overflow on line 65", which ultimately turned out to be the offending calculation. – T N Commented Feb 19 at 20:46
  • 1 So you want to truncate -3571162108885.27834640677142857142 to -62108885.27834640677, @EmmanuelSalau ? No, that isn't how assignments work. – Thom A Commented Feb 19 at 21:15
 |  Show 10 more comments

1 Answer 1

Reset to default 5

Your new rate calculation appears to be missing a term, causing the algorithm to go wildly unstable.

Try:

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

This will yield a calculated final rate = 0.1282572690.

See this db<>fiddle.

Explanation: Your calculation is a variation on Newtons Method to solve for f(x) = 0, where: