CODEWITHSUNDEEP
sqlsql-servert-sqlexcel-formulaxirr
cbrawl
cbrawl

Reputation: 975

Calculating XIRR in SQL

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,

enter image description here

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.

Upvotes: 1

Views: 2736

Answers (1)

pnuts
pnuts

Reputation: 59485

Assuming your image is in A1 at the top left. Does not solve your SQL but I hope may be of some help:

Firstly, my Excel also returns -0.6719218.

Secondly, Excel's calculation is detailed at XIRR function from which part is:

XIRR formula

However, that is for a series of cash flows, for which Excel uses iteration, and your specific example is merely a pair of paired entries, for which the soluble equation may be expressed as:

=10^(LOG(-A2/A1)*365/(B2-B1))

This part: 365/(B2-B1) is the reciprocal of your 0.0849315, so it may be worth investigating your 0.635634780 more closely (I'm not clear how you arrived at that).

Upvotes: 1

Related Questions