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