SQL 2008 Excel-like RATE Function Part III

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   

SQL CLR and Fixed Income Analytics

For the better part of the last 12 months, our team has been spending our time balancing demands of strategic platform build out with the need for rapid deployment of analytical functionality. During that time, we have built a modest library of fixed income analytics. That's not necessarily that interesting. The interesting part is the ability to create robust calculations using C# and exposing those calculations via the SQL CLR in SQL Server 2008. The common theme on this blog thus far has been keeping analytical calculations close to the data so that we can embellish our fairly substantial market data set quickly. But processing the data in batch is only half the story. Real time analytics exposed via application functionality is also required. This necessitates centralizing the calculation library to leverage the same calc in multiple places.

In the weeks to come, we will detail some of these analytics and the mechanism for deploying them to the SQL CLR. Hopefully, this will be useful to those attempting to either create the analytical calculations or get performance gains by running them in the database during batch or both.

SQL 2008 Exponential Regression

As described in the SQL 2008 Excel-like LINEST Function post, the exponential regression function is now a simple matter of transforming the dependent variable (y) with the built-in T-SQL LOG function and the resulting slope, intercept and r-Squared calculations. Now, armed with both linear and exponential regression functions, we can regress large data sets quickly and accurately so that we can perform higher level analytics with the results.

CREATE FUNCTION [dbo].[ExponentialRegression]
(
@RegressionInput RegressionInput READONLY
)
RETURNS @RegressionOutput TABLE
(
Slope DECIMAL(18, 6),
Intercept DECIMAL(18, 6),
RSquare DECIMAL(18, 6)
)
AS
BEGIN
DECLARE @Xaverage AS DECIMAL(18, 6)
DECLARE @Yaverage AS DECIMAL(18, 6)
DECLARE @slope AS DECIMAL(18, 6)
DECLARE @intercept AS DECIMAL(18, 6)
DECLARE @rSquare AS DECIMAL(18, 6)

SELECT
@Xaverage = AVG(X),
@Yaverage = AVG(LOG(Y))
FROM
@RegressionInput

SELECT
@slope =
CASE
WHEN SUM(POWER(X - @Xaverage, 2)) <> 0 THEN
SUM((X - @Xaverage) * (LOG(Y) - @Yaverage))/SUM(POWER(X - @Xaverage, 2))
ELSE
0
END
FROM
@RegressionInput

SELECT
@intercept = EXP(@Yaverage - (@slope * @Xaverage))

SELECT
@rSquare =
CASE
WHEN (SUM(POWER(LOG(Y) - LOG((@intercept * POWER(EXP(1), @slope * X))), 2)) + SUM(POWER(LOG((@intercept * POWER(EXP(1), @slope * X))) - @Yaverage, 2))) <> 0 THEN
1 - (SUM(POWER(LOG(Y) - LOG((@intercept * POWER(EXP(1), @slope * X))), 2))/(SUM(POWER(LOG(Y) - LOG((@intercept * POWER(EXP(1), @slope * X))), 2)) + SUM(POWER(LOG((@intercept * POWER(EXP(1), @slope * X))) - @Yaverage, 2))))
ELSE
0
END
FROM
@RegressionInput

INSERT INTO
@RegressionOutput
SELECT
@slope,
@intercept,
@rSquare

RETURN

END

GO

SQL 2008 Excel-like LINEST Function

Once again, I find myself replicating functionality in SQL that Excel provides out of the box. A necessary step if you find yourself analyzing large data sets with rigorous math. This time, it's the LINEST function, Excel's tried and true linear regression function. Admittedly, what I built does not provide the same level of information, but it does provide the big three: slope, y-intercept, and r-Squared. Once the base linear regression function is built, extending to handle exponential regression becomes straightforward, but I will save that for a different post.

Caveat

This will work out of the box for SQL 2008. You'll need to modify for lower versions of SQL Server.

First create a new Table type (only available in SQL 2008)

CREATE TYPE dbo.RegressionInput AS TABLE
(
x DECIMAL(18, 6),
y DECIMAL(18, 6)
)

GO

Then create the function

CREATE FUNCTION dbo.LinearRegression
(
@RegressionInputs AS dbo.RegressionInput READONLY
)
RETURNS @RegressionOutput TABLE
(
Slope DECIMAL(18, 6),
Intercept DECIMAL(18, 6),
RSquare DECIMAL(18, 6)
)
AS
BEGIN

DECLARE @Xaverage AS DECIMAL(18, 6)
DECLARE @Yaverage AS DECIMAL(18, 6)
DECLARE @slope AS DECIMAL(18, 6)
DECLARE @intercept AS DECIMAL(18, 6)
DECLARE @rSquare AS DECIMAL(18, 6)

SELECT
@Xaverage = AVG(x),
@Yaverage = AVG(y)
FROM
@RegressionInputs

SELECT
@slope = SUM((x - @Xaverage) * (y - @Yaverage))/SUM(POWER(x - @Xaverage, 2))
FROM
@RegressionInputs

SELECT
@intercept = @Yaverage - (@slope * @Xaverage)

SELECT @rSquare = 1 - (SUM(POWER(y - (@intercept + @slope * x), 2))/(SUM(POWER(y - (@intercept + @slope * x), 2)) + SUM(POWER(((@intercept + @slope * x) - @Yaverage), 2))))
FROM
@RegressionInputs

INSERT INTO
@RegressionOutput
(
Slope,
Intercept,
RSquare
)
SELECT
@slope,
@intercept,
@rSquare

RETURN

END
GO

SQL 2008 Excel-like RATE Function Redux

After rereading my previous post, I realized that I put a pretty blunt, non-elegant RATE function out there for consumption. I'd like to amend that post with the following offering that should get you started on a thoughtful T-SQL financial library. This post will contain the following functions:

FutureValue
FutureValue_Derivative
FutureValue_SecondDerivative
Rate_IncrementDecrement
Rate_Convergence

The difference here is the application of the Newton Method for the convergence algorithm in the Rate_Convergence function. This approach precipitated the need for the first and second derivative functions for future value. I left the Rate_IncrementDecrement function intact because it is a good back-up for those situations where the Newton Method fails.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Micah Minarik
-- Create date: 11/13/2008
-- Description: Standard future value calculation
-- Example:
-- @pv = 100000 (dollars)
-- @i = 6% (per annum)
-- @p = 5 (years)
-- @pmt = 100 (dollars)
-- SELECT dbo.FV_fn( -96, .4494, 5.375, 3.64)
-- =============================================
CREATE FUNCTION [dbo].[FutureValue]
(
-- Add the parameters for the function here
@pv DECIMAL(18,6), -- current amount in dollars
@i DECIMAL(18,6), -- interest rate
@p DECIMAL(18,6), -- number of compounding periods
@pmt DECIMAL(18,6)
)
RETURNS DECIMAL(18,6)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result DECIMAL(18,6)

IF (@i IS NULL OR @i = 0)
RETURN 0

-- Add the T-SQL statements to compute the return value here
SELECT @Result = @pv * POWER((1 + @i), @p) + (@pmt * ((POWER((1 + @i), @p) - 1)/@i))

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

GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Micah Minarik
-- Create date: 11/13/2008
-- Description: Derivative of the future value calculation used for linear convergence guessing
-- Example:
-- @pv = 100000 (dollars)
-- @i = 6% (per annum)
-- @p = 5 (years)
-- @pmt = 100 (dollars)
-- SELECT dbo.FV_derivative( -96, .4494, 5.375, 3.64)
-- =============================================
CREATE FUNCTION [dbo].[FutureValue_Derivative]
(
-- Add the parameters for the function here
@pv DECIMAL(18,6), -- current amount in dollars
@i DECIMAL(18,6), -- interest rate
@p DECIMAL(18,6), -- number of compounding periods
@pmt DECIMAL(18,6)
)
RETURNS DECIMAL(18,6)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result DECIMAL(18,6)

IF (@i IS NULL OR @i = 0)
RETURN 0

-- Add the T-SQL statements to compute the return value here
SELECT @Result = @pv * (@p * POWER((1 + @i), @p-1)) + (@pmt * (((@p * POWER((1 + @i), @p-1)) - 1)/@i))

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


GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Micah Minarik
-- Create date: 11/13/2008
-- Description: Second derivative of the future value calculation used for linear convergence guessing
-- Example:
-- @pv = 100000 (dollars)
-- @i = 6% (per annum)
-- @p = 5 (years)
-- @pmt = 100 (dollars)
-- SELECT dbo.FV_derivative( -96, .4494, 5.375, 3.64)
-- =============================================
CREATE FUNCTION [dbo].[FutureValue_SecondDerivative]
(
-- Add the parameters for the function here
@pv DECIMAL(18,6), -- current amount in dollars
@i DECIMAL(18,6), -- interest rate
@p DECIMAL(18,6), -- number of compounding periods
@pmt DECIMAL(18,6)
)
RETURNS DECIMAL(18,6)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result DECIMAL(18,6)

IF (@i IS NULL OR @i = 0)
RETURN 0

-- Add the T-SQL statements to compute the return value here
SELECT @Result = @pv * (((@p-1)*@p) * POWER((1 + @i), @p-2)) + (@pmt * (((((@p-1)*@p) * POWER((1 + @i), @p-2)) - 1)/@i))

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

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Micah Minarik
-- Create date: 11/13/2008
-- Description: Standard rate calculation
-- Example:
-- 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_IncrementDecrement( 5.386, 3.64, -82.5, 100, .001, 1000, .1)
-- =============================================
CREATE FUNCTION [dbo].[Rate_IncrementDecrement]
(
@nper MONEY,
@pmt MONEY,
@pv MONEY,
@fv MONEY,
@Tolerance DECIMAL(18,6) = .001,
@MaxIterations INT = 1000,
@Guess DECIMAL(7, 5) = .1
)
RETURNS MONEY
AS
BEGIN
-- Solve variables
DECLARE @i DECIMAL(7,5) = @Guess
DECLARE @compareFV MONEY = 0
DECLARE @iteration INT = 0

WHILE (@iteration < @MaxIterations) BEGIN IF @compareFV > @fv * (1 + @Tolerance) OR @compareFV < @fv * (1 - @Tolerance) --Allow for 1 basis point up or down on the guess BEGIN IF @compareFV > @fv
SET @i -= .0001 --If the guess is high, decrement for this round
ELSE
SET @i += .0001 --If the guess is low, increment for this round

SET @iteration += 1

SELECT
@compareFV = -dbo.FutureValue(@pv, @i, @nper, @pmt) --Cashflow is indicated with a negative for an outlay and reversed for comparison

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
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Micah Minarik
-- Create date: 11/18/2008
-- Description: Rate calculation using Newton's Convergence Method
-- Example:
-- 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_Convergence( 8.16, 3.75, -91, 100, .0001, 1000, .085)
-- =============================================
CREATE FUNCTION [dbo].[Rate_Convergence]
(
@NumberOfPeriods DECIMAL(18, 6),
@Payments DECIMAL(18, 6),
@PresentValue DECIMAL(18, 6),
@FutureValue DECIMAL(18, 6),
@Tolerance DECIMAL(18,6) = .001,
@MaxIterations INT = 1000,
@Guess DECIMAL(18, 6) = .1
)
RETURNS DECIMAL(18, 6)
AS
BEGIN
-- Solve variables
DECLARE @Interest DECIMAL(18, 6)
DECLARE @InterestPrevious DECIMAL(18, 6)
DECLARE @CompareFutureValue DECIMAL(18, 6) = 0
DECLARE @Iteration INT = 0

--Test the inputs to determine if convergence will work. If it will not, use the straight increment, decrement method.
/*
ABS(f(x)f''(x)/f'(x) SQUARED) < 1
*/
IF ABS(((-dbo.FutureValue(@PresentValue, @Guess, @NumberOfPeriods, @Payments)) * (-dbo.FutureValue_SecondDerivative(@PresentValue, @Guess, @NumberOfPeriods, @Payments)))/POWER(-dbo.FutureValue_Derivative(@PresentValue, @Guess, @NumberOfPeriods, @Payments), 2)) < 1
BEGIN
WHILE (@Iteration < @MaxIterations)
BEGIN
--This a T-SQL representation of the Newton Method for convergence
--In an attempt to solve for zero, guesses should converge on the correct answer using the iterative formula:
/*
Xn+1 = Xn - f(x)/f'(x)
where f(x) = the future value function minus the known future value to get to zero
*/
IF @Iteration = 0
BEGIN
IF dbo.FutureValue_Derivative(@PresentValue, @Guess, @NumberOfPeriods, @Payments) = 0
BEGIN
RETURN NULL
END
SET @Interest = @Guess - ((-dbo.FutureValue(@PresentValue, @Guess, @NumberOfPeriods, @Payments) - @FutureValue)/-dbo.FutureValue_Derivative(@PresentValue, @Guess, @NumberOfPeriods, @Payments))
END
ELSE
BEGIN
IF dbo.FutureValue_Derivative(@PresentValue, @InterestPrevious, @NumberOfPeriods, @Payments) = 0
BEGIN
RETURN NULL
END
SET @Interest = @InterestPrevious - ((-dbo.FutureValue(@PresentValue, @InterestPrevious, @NumberOfPeriods, @Payments) - @FutureValue)/-dbo.FutureValue_Derivative(@PresentValue, @InterestPrevious, @NumberOfPeriods, @Payments))
END

--The final test is consecutive guesses within a specified tolerance range from each other
IF ABS(@Interest - @InterestPrevious) <= @tolerance
BREAK
ELSE
SET @InterestPrevious = @Interest

SET @Iteration += 1
END
END
ELSE
BEGIN
SELECT
@Interest = dbo.Rate_IncrementDecrement(@NumberOfPeriods, @Payments, @PresentValue, @FutureValue, @Tolerance, @MaxIterations, @Guess)
END

-- Return the result of the function
RETURN @Interest
END
GO

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