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

## Friday, November 14, 2008

Subscribe to:
Post Comments (Atom)

## 4 comments:

what about the implementation of the IRR.PAIEMENTS function in T-SQL?

If you have some code working it 'll be welcome

thanks

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

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?

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!

Post a Comment