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

## 12 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

Post a Comment