• 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