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

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:

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.

@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.

@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?

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

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.

>>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!!?

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?

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?

It does't work with the following values

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

Hi, i have the same problem, for some values does not work.

How did you solve this?

Many thks

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.

hi,

your post was very usefull to solve my problem.

thank you very much

Do you have this function for ORACLE ?

Thanks, regards,

Phillip.

/*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;*/

Post a Comment