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.
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:
For your use case,
dbo.NPV()@rate@precision@npv@new_npvSubstituting all of the values yields the corrected calculation:
@raten+1 = @raten - @npv * @precision / (@new_npv - @npv)Your original equation was missing the δ = @precision term.
More on Newton's Method:
As in informal summary, Newton's method involves the following steps. (To the mathematicians out there, I apologize in advance for my loose usage of dx and derivative notations.)
x.f(x) from that guess.E = f(x) - goal is. If the goal is zero (as in your case), the error amount is the same as as the result.df(x), if a very tiny adjustment dx is made to the guess. This is calculated as df(x) = f(x + dx) - f(x). For your use case, dx = @precision and df(x) = @new_npv - @npv.df(x)/dx of the change in result df(x) to the change in guess dx. This ratio is the derivative of the function at that point x (also denoted as f'(x) or "f-prime of x"). This is also the slope of a curve at that point if your function were graphed.E = f(x), so you need to scale up that tiny amount dx by some factor so that the change in result df(x) also scales up to be (approximately) equal the error amount E = f(x). That scaled up value can be calculated as adjustment = f(x) / f'(x) = f(x) / (df(x) / dx) = f(x) * dx / df(x) = f(x) * dx / (f(x+dx) - f(x)). For your use case, this equals @npv * @precision / (@new_npv - @npv).xn+1 = xn - adjustment = xn - f(x) / f'(x) = xn - f(x) * dx / (f(x+dx) - f(x)). For your use case, this would be @raten+1 = @raten - @npv * @precision / (@new_npv - @npv).