Tuesday, November 18, 2008

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

6 comments:

Andrew Frazier said...

Hey, what if I want to calculate an interest rate on a loan? The only values I have are:

1. the delta between original balance and current balance
2. the number of periods that have transpired
3. the term of the loan

can it be done?

-Drew

Micah Minarik said...

Drew,

Unfortunately, you would need the payment to give you an accurate calculation of the interest rate. You may be able to infer the payment based on the delta and periods and then plug them into the function.

We actually use this function to calculate yield to maturities on loans. The only drawback is the bullet (full par repayment at maturity) assumption which will lose you principal/discount amortization in the calculation.

Let me know if that works.

Micah

Anonymous said...

I was looking for this function and got your site.

Interestingly the CAGR() formula using wikipedia gives the exact value as the Rate() function in Excel. CAGR was easy to implement in my case.

CAGR = ((futurevalue/previousvalue)^(1/number-of-years)) - 1

Micah Minarik said...

Piyush,

You are correct, the CAGR formula you describe is correct for determining rates of return with no payment stream. For payment streams, it is necessary to use the basic DCF techniques implied by the Rate function.

Micah

flockenator said...

You can now find every EXCEL function implemented in SQL Server at www.westclintech.com. The functions are organized along the same lines as EXCEL, so there are financial functions, engineering fucntions, statistical functions, math functions, etc. They all work exactly the same as EXCEL (and in some instances are less restrictive than EXCEL) and can be used like any user-defined function in SQL Server

Micah Minarik said...

flockenator,

Thanks for the heads up. Looks like good stuff.

Micah