An XIRR is just a function name in Excel, it is actually an IRR calculation when the timings for the cash flows are given using a date schedule. This then leads to discounting of the cash flows at the actual time period as opposed to periodic time periods in IRR calculation
There are more than one way to define the XIRR equation and there are scores of ways of finding the roots of this equation that results in one or more IRR values
My personal favorite algo to find roots of a given function is the popular Newton-Raphson method that was named after the two mathematicians who independently discovered this method independent of each other about more than two centuries ago. One is the famous inventor of modern day Calculus the English mathematician Sir Isaac Newton (whom it seem was Knighted by the King of England thus the title "Sir" is referenced. The other one is Joseph Raphson, I am not sure of his national origin but it sounds like an American name so who knows I didn't look it up
Code may not always return an XIRR in such a case use a Guess rate other than the last one tried. In other case a Division by zero may be encountered when the differential is zero.
CREATE TYPE dbo.MyXirrTable AS TABLE
(
theValue DECIMAL(19, 9) NOT NULL,
theDate DATETIME NOT NULL
)
GO
CREATE FUNCTION dbo.XIRR
(
@Sample MyXirrTable 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,
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 + theValue * POWER(1 + @X0, (-theDelta / 365.0E)),
@fbar = @fbar - theDelta / 365.0E * theValue * POWER(1 + @X0, (-theDelta / 365.0E - 1))
FROM (
SELECT theValue,
DATEDIFF(DAY, MIN(theDate) OVER (), theDate) AS theDelta
FROM @Sample
) AS d
IF @fbar = 0
RETURN NULL
SET @X = @X0 - @f / @fbar
If ABS(@X - @X0) < 0.00000001
RETURN @X
SET @X0 = @X
SET @i += 1
END
RETURN NULL
END
GO
The above code is based on financial functions found in this JavaScript library tadJS[/url] that itself is based upon tadXL