Friday, May 17, 2013

SQL 2008 Excel-like RATE Function Part III

My previous posts on this topic are just terrible.  After gaining 5 more years of experience and becoming wiser, if you want to recreate the Excel RATE function in SQL Server, simply use a SQL CLR function or this nifty bit of code that I ported from PHP after doing a search and finding this post:  http://stackoverflow.com/questions/3198939/recreate-excel-rate-function-using-newtons-method.

The hard coded variables will yield you a rate of ~10.496% and should take 9 passes (@i) to get it done.  Push it up against Excel, it's on.

DECLARE @nper FLOAT = 30
DECLARE @pmt FLOAT = -400
DECLARE @pv FLOAT = 4000
DECLARE @fv FLOAT = 0
DECLARE @type INT = 1
DECLARE @guess DECIMAL(6,3) = .1

DECLARE @rate FLOAT
DECLARE @FINANCIAL_MAX_ITERATIONS INT = 128
DECLARE @FINANCIAL_PRECISION FLOAT = .0000001

DECLARE @y FLOAT
DECLARE @y0 FLOAT
DECLARE @y1 FLOAT
DECLARE @f FLOAT
DECLARE @i INT
DECLARE @x0 FLOAT = 0
DECLARE @x1 FLOAT

SET @rate = @guess

IF(ABS(@rate) < @FINANCIAL_PRECISION)
BEGIN
    SET @y = @pv * (1 + @nper * @rate) + @pmt * (1 + @rate * @type) * @nper + @fv
END
ELSE
BEGIN
    SET @f = EXP(@nper * LOG(1 + @rate))
    SET @y = @pv * @f + @pmt * (1 / @rate + @type) * (@f - 1) + @fv
END

SET @y0 = @pv + @pmt * @nper + @fv
SET @y1 = @pv * @f + @pmt * (1 / @rate + @type) * (@f - 1) + @fv

SET @i  = @x0
SET @x1 = @rate

WHILE ((ABS(@y0 - @y1) > @FINANCIAL_PRECISION) AND (@i < @FINANCIAL_MAX_ITERATIONS))
BEGIN
    SET @rate = (@y1 * @x0 - @y0 * @x1) / (@y1 - @y0)
    SET @x0 = @x1
    SET @x1 = @rate

    IF (ABS(@rate) < @FINANCIAL_PRECISION)
    BEGIN
        SET @y = @pv * (1 + @nper * @rate) + @pmt * (1 + @rate * @type) * @nper + @fv
    END
    ELSE
    BEGIN
        SET @f = EXP(@nper * LOG(1 + @rate))
        SET @y = @pv * @f + @pmt * (1 / @rate + @type) * (@f - 1) + @fv
    END

    SET @y0 = @y1
    SET @y1 = @y
    SET @i = @i + 1
END


SELECT @i   
SELECT @rate