SQL Server XIRR Function

Introduction

As you may know, we cannot directly use XIRR calculation in Reporting Services while we can use custom code or custom assembly to perform the XIRR calculation in report.

The code below shows how to calculate XIRR value on SQL Server side. SQL Server XIRR function produces results identical to Microsoft Excel.

Background

Excel XIRR function is described here.

Function has one input parameter - table with values and corresponding dates. Function expects at least one positive cash flow and one negative cash flow.

User-defined data type is used to pass parameter. It could be created by using the script.

CREATE TYPE [dbo].[XIRRTableType] AS TABLE(
       [PayDate] [datetime] NULL,
       [Payment] [money]    NULL
)
GO

XIRR function (idea is taken from here and here) is shown below. Guess value is hardcoded to 10%.

CREATE FUNCTION dbo.XIRR (@table dbo.XIRRTableType readonly)
RETURNS MONEY
AS
BEGIN
    DECLARE @FirstPayDate     Datetime,
            @nPayments        INT,
            @nRate            FLOAT,
            @nLastRate        FLOAT,
            @nRateStep        FLOAT,
            @nResidual        FLOAT,
            @nLastResidual    FLOAT,
            @i                INT

    SELECT  @FirstPayDate = Min(PayDate)
    FROM    @table

    SELECT  @nRate            = 0.1,    -- initial guess
            @nRateStep        = 0.1,    -- arbitrary guess
            @nResidual        = 10,
            @nLastResidual    = 1,
            @nLastRate        = @nRate,
            @i                = 0    -- steps counter
 
    -- do nothing if the table is to short
    IF (SELECT COUNT(*) FROM @table as aXIRR) < 2
        RETURN CAST(null AS MONEY)

    WHILE @i < 100 AND ABS((@nLastResidual-@nResidual)/ISNULL(NULLIF(@nLastResidual,0),1))>POWER(10,-8)
    BEGIN
        SELECT  @nLastResidual    = @nResidual,
                @nResidual        = 0        --  residual of function
 
        SELECT  @nResidual        = @nResidual + _
                aXIRR.Payment/POWER(1+@nRate,DateDiff(day,@FirstPayDate,aXIRR.PayDate)/365.0)
        FROM    @table as aXIRR
    -----------------------------------------------------------
        SELECT    @nLastRate        = @nRate

        IF @nResidual >= 0
            SELECT    @nRate        = @nRate + @nRateStep
        ELSE
            SELECT    @nRateStep    = @nRateStep / 2.0,
                      @nRate        = @nRate - @nRateStep
 
        SELECT        @i            = @i + 1
    END

    RETURN @nLastRate;
END

The snippet below explains how to use the code:

DECLARE @xIRRtable AS dbo.XIRRTableType

INSERT  INTO @xIRRtable
SELECT  Date,
        CashFlow
FROM    #T
WHERE .....

PRINT  dbo.xirr(@xIRRtable)