Click here to Skip to main content
14,641,496 members
Articles » Database » Database » SQL Server
Tip/Trick
Posted 8 Jul 2016

Tagged as

Stats

13.8K views
3 bookmarked

SQL Server XIRR Function

Rate this:
3.77 (3 votes)
Please Sign up or sign in to vote.
3.77 (3 votes)
8 Jul 2016CPOL
This tip describes Microsoft SQL Server implementation of 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)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Michael Ecklin
United States United States
No Biography provided

Comments and Discussions

 
Generalnot matching with excel at all Pin
Nitin Vilas Karke10-Sep-19 1:43
MemberNitin Vilas Karke10-Sep-19 1:43 
QuestionIssue with function Pin
Member 1398480414-Sep-18 8:38
MemberMember 1398480414-Sep-18 8:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.