Friday, November 14, 2008

SQL 2008 Excel-Like RATE Function

Keeping the standard financial calcs close to your data for batch processing is important for large data imports where calculating yields and spreads is necessary for future analytical processing. Present value and future value calculations are straightforward, but what about calculating yields? Rate calculations (and IRR) are tougher because they are based on guessing and iteration. This is abstracted from you in Excel and the VisualBasic .NET library. This leaves few options for replicating the same functionality in T-SQL. SQL 2008 (and 2005) makes it a bit easier with the introduction .NET stored procs, but that adds a layer of complexity that may not be necessary if you understand how the calculation is performed.

I have put together a quick function that will work out of the box in SQL 2008. Some of the new T-SQL language features (e.g. in-line variable initialization) will need to be accounted for in lower versions. Take a look and get back with questions:

-- =============================================
-- Description: T-SQL Equivalent of Excel RATE function
-- Note:
-- Indicate an outlay (purchase) with a negative present value number. The function
-- will handle the conversion internally to return the proper rate.
-- SELECT dbo.Rate_fn( 5.386, 3.64, -82.5, 100, .001, .001, 1000, .1)
-- =============================================
CREATE FUNCTION dbo.Rate_fn
(
@nper MONEY,
@pmt MONEY,
@pv MONEY,
@fv MONEY,
@tolerance DECIMAL(18,6) = .001,
@step DECIMAL(18, 6) = .0001,
@maxIterations INT = 1000,
@guess DECIMAL(18, 6) = .1
)
RETURNS MONEY
AS
BEGIN
-- Solve variables
DECLARE @i
DECIMAL(18, 6)
DECLARE @compareFV MONEY = 0
DECLARE @iteration INT = 0

SET @i = @guess
WHILE (@iteration < @maxIterations)

BEGIN
IF @compareFV > @fv * (1 + @tolerance) OR @compareFV < @fv * (1 - @tolerance)
BEGIN
IF @compareFV > @fv
SET @i -= @step --If the guess is high, decrement for this round
ELSE
SET @i += @step --If the guess is low, increment for this round

SET @iteration += 1

SELECT
@compareFV = -(@pv * POWER((1 + @i), @nper) + (@pmt * ((POWER((1 + @i), @nper) - 1)/@i)))

CONTINUE
END
ELSE
BREAK --If we have hit the target, leave the loop and return
END

-- Return the result of the function
RETURN @i
END

4 comments:

Raphael said...

what about the implementation of the IRR.PAIEMENTS function in T-SQL?
If you have some code working it 'll be welcome
thanks

Mujahid Hassan said...

what if i what to use for first three values like rate function we can use rate(1,-90,50) like this

Mujahid Hassan said...

what if i want to use first three input parameter like i can use rate function in excel like RATE(1,-979093732.8,939680000) what if want to use in same way then next 4 arguments will be what?

Anonymous said...

I'm attempting to use this function, but not sure what to do with the 4th-8th parms as I want to only use the first 3 like the Excel RATE() function. Can someone please tell me if they got this working like Excel and, if so, what to do with the remaining parameters? Thank you!