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