I have been trying to find a solution to calculate XIRR in SQL (TSQL). Ideally, the values would match the values that Excel calculates using the same input data. I have found several suggested solutions around the web but all seem to be flawed in some manner. Below is an example that we have been using that works in almost all cases.
CREATE FUNCTION [dbo].[CalcXIRR]
(
@Sample XIRRTable READONLY,
@Rate DECIMAL(19, 9) = 0.1
)
RETURNS DECIMAL(38, 9)
AS
BEGIN
DECLARE @X DECIMAL(19, 9) = 0.0,
@X0 DECIMAL(19, 9) = 0.1,
@f DECIMAL(19, 9) = 0.0,
@fbar DECIMAL(19, 9) = 0.0,
@i TINYINT = 0,
@found TINYINT = 0
IF @Rate IS NULL
SET @Rate = 0.1
SET @X0 = @Rate
WHILE @i < 100
BEGIN
SELECT @f = 0.0,
@fbar = 0.0
SELECT @f = @f + value * POWER(1 + @X0, (-theDelta / 365.0E)),
@fbar = @fbar - theDelta / 365.0E * value * POWER(1 + @X0, (-theDelta / 365.0E - 1))
FROM (
SELECT Value,
DATEDIFF(DAY, MIN(date) OVER (), date) AS theDelta
FROM @Sample
) AS d
SET @X = @X0 - @f / @fbar
If ABS(@X - @X0) < 0.00000001
BEGIN
SET @found = 1
BREAK;
END
SET @X0 = @X
SET @i += 1
END
If @found = 1
RETURN @X
RETURN NULL
END
GO
However, with the data below,
we are getting an error
An invalid floating point operation occurred.
This is happening on the line
SELECT @f = @f + value * POWER(1 + @X0, (-theDelta / 365.0E))
It essentially gets down to a specific calculation
POWER(-0.635634780,-0.0849315)
Could it be that there is some simple syntax adjustment that could fix this error or is the function itself not going to work? Excel seems to handle this calculation without a problem.
This is just one of many examples I have tried using. I can break down another example, if needed. I can edit the post to make it fit Stack Overflow's standard with guidance. I find it very unusual that there is no clear discussion on how to calculate XIRR in SQL. Every solution seems to have problems yet Excel spits out values so effortlessly.
