SQL Floating Point Error with IRR Calculation

Question

I had a coworker that wrote an IRR calculation function. You supply what seems to be set of cash flows, and it returns the IRR. It works 99% of the time but sometimes I get a floating point error. I am trying to figure out where in the code that can happen. The function looks like this:

CREATE FUNCTION [dbo].[CalcXIRR]
(
    @Sample XIRRTable READONLY,
    @Rate FLOAT = 0.1
)
RETURNS DECIMAL(38, 9)
AS
BEGIN

    DECLARE @X FLOAT = 0.0,
    @X0 FLOAT = 0.1,
    @f FLOAT = 0.0,
    @fbar FLOAT = 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

You supply it data that looks like this:

Date                     Value
2017-08-01 00:00:00.000 -3775585
2017-08-01 00:00:00.000 -10763.73
2017-08-01 00:00:00.000 -10763.73
2017-08-01 00:00:00.000 10763.73
2017-08-01 00:00:00.000 10763.73
2017-08-01 00:00:00.000 3775585
2017-08-01 00:00:00.000 3786348.73
2017-08-02 00:00:00.000 -1550780
2017-08-02 00:00:00.000 -4823
2017-08-02 00:00:00.000 -4823
2017-09-11 00:00:00.000 -254800
2017-09-11 00:00:00.000 254800

Provide an example of the data that generates the error. Also, tag with the database you are using. — Gordon Linoff, Jun 26 '20 at 01:20

score 0 · Answer 1 · answered Jun 26 '20 at 18:49

Is there any reason not to use decimal instead of float?

See this link from Bert Wagner.

Here is an excerpt from it:

Floating point math errors can be fixed in a few ways.

One option is to stop caring about them. The error occurring on floats is very small (although when compounded through arithmetic, the error can grow large enough to be noticeable like in my reporting bar chart example). If you are writing queries or reports where such a small amount of error doesn’t matter, then you can continue on your merry way without having to change anything.

A second option is to still store values as floats (for that sweet, sweet storage space savings), but ensure your application code has business logic to correctly round numbers that are in precise.

However, if your data needs to be perfectly accurate every single time with no errors, use a different datatype. The logical choice here would be to use decimal in SQL Server, which uses a different internal method for storing your numbers, resulting in perfect results every time. However, the range of possible values is not as large as float, and you will pay for that precision with additional bytes of storage space.

In the end, floating point is good enough for many applications. The important thing is that you are aware that these kind of errors can happen and that you handle them appropriately.

SQL Floating Point Error with IRR Calculation

1 Answers1