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   

14 comments:

Anonymous said...

Hi, any thoughts using the following values.
=RATE(475,-1022.93,272779.21,,0)
gives(0.2716102532370%)
and
DECLARE @nper FLOAT = 475
DECLARE @pmt FLOAT = -1022.93
DECLARE @pv FLOAT = 272779.21
DECLARE @fv FLOAT = 0
DECLARE @type INT = 0
gives(4.24700647473906E-20)
all other pieces of the SQL Script being untouched.

David Rueter said...

@Anonymous,

I tried the values you provided, and Micah's code produced the same value as Excel:

0.0027161025 (Micah)
0.0027161025 (Excel 2013)

I turned Micah's code into a function and ran like this:

PRINT dbo.udfRate(475, -1022.93, 272779.21, 0, 0, default)

This returned the value above. FWIW, I did switch the floats to decimal(18,10).

I think Micah has a winner this time ;-)

Micah, thanks for your persistence. This helped me out a lot.

JefDwi said...

@David,

I've been wondering which floats did you switch to decimal(18,0), because i reproduce Micah's code and this:
PRINT dbo.udfRate(475, -1022.93, 272779.21, 0, 0, default)
returned this:
3.53917206228255E-21

what is it between float and decimal(18,10), why data precision differs greatly?

Anonymous said...

Thanks for this. For larger loan values, I had to put the GUESS value to 0.01 instead of 0.1.

Anonymous said...

Thanks very much for this code. One thing I noticed is I had to delete the BEGIN and END keywords in the IF statements. With them in, I return wild negative rates; when removed, flawless results.

Unknown said...

>>Thanks very much for this code. One thing I noticed is I had to delete the BEGIN and END keywords in the IF statements. With them in, I return wild negative rates; when removed, flawless results.

Oh God! You are CORRECT and I have a big question WHY!!?

Anonymous said...

What's the ideal way to include this in a query to pull nper, pmt, pv for a large number of records from a table and have this figure out the rate for each one instead of having to plug in those values?

Project weekend said...

Thanks for this. I'm getting floating point error on values,

DECLARE @nper FLOAT = 240
DECLARE @pmt FLOAT = -25844
DECLARE @pv FLOAT = 1000000

on this line:
SET @f = EXP(@nper * LOG(1 + @rate))

'An invalid floating point operation occurred.'

The sql log function returns the error when checked value >> select LOG(1-1.407)

how to solve this?

Anonymous said...

It does't work with the following values

select dbo.uf_Financial_RATE(104, 212.55, -10512.58, 1, 0, default);

Raquel said...

Hi, i have the same problem, for some values does not work.
How did you solve this?

Many thks

Jelil Adetoro said...

I have been able to write an SQL Server CLR that handles this function properly and able to get select dbo.RATE(104, 212.55, -10512.58, 1, 0, default) without floating point error.

Anonymous said...

hi,
your post was very usefull to solve my problem.
thank you very much

Phillip M. said...

Do you have this function for ORACLE ?
Thanks, regards,

Phillip.

Phillip M. said...


/*COMPONEMOS LA FUNCION RATE */
CREATE OR REPLACE FUNCTION RATE (pv IN number, fv IN number, pmt IN number, nper IN number, type IN number )
RETURN number IS rate number;
BEGIN
DECLARE
guess NUMBER(6,3) := .1;
rate BINARY_DOUBLE;
FINANCIAL_MAX_ITERATIONS NUMBER(10) := 100;
FINANCIAL_PRECISION BINARY_DOUBLE := .000001;
y BINARY_DOUBLE;
y0 BINARY_DOUBLE;
y1 BINARY_DOUBLE;
f BINARY_DOUBLE;
i NUMBER(10);
x0 BINARY_DOUBLE := 0;
x1 BINARY_DOUBLE;
BEGIN
rate := guess;
IF(ABS(rate) < FINANCIAL_PRECISION) THEN y := pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
ELSE
f := EXP(nper * LN(1 + rate));
y := pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
END IF;

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

i := x0 ;
x1 := rate;

WHILE ((ABS(y0 - y1) > FINANCIAL_PRECISION) AND (i < FINANCIAL_MAX_ITERATIONS))
LOOP
rate := (y1 * x0 - y0 * x1) / (y1 - y0);
x0 := x1;
x1 := rate;

IF (ABS(rate) < FINANCIAL_PRECISION) THEN y := pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
ELSE
f := EXP(nper * LN(1 + rate));
y := pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
END IF;

y0 := y1;
y1 := y;
i := i + 1;
END LOOP;
RETURN RATE;
END;
END;

/*select rate(10053.96, -9457.25, -124.38, 6, 1)*12*100 FROM DUAL;*/