A few days ago I was asked to create simple tool for calculating Interest rate. I received also excel file with all necessary calculations and formulas. Most of formulas used excel financial functions: PPMT , IPMT . I started with googling for SQL port of PPMT but I didnt find anything except this:
SQL Server – Excel Financial Functions – using CLR
Its quite ok, but I noticed that CLR functions are rounded to two decimal plaseces witch was not acceptable for me so I wrote my own functions based on this article:
A Guide to the PMT, FV, IPMT and PPMT Functions
Below you will find T-SQL version of PPMT / IPMT / PMT / FV excel financial functions
IF OBJECT_ID('Pmt', 'FN') IS NOT NULL DROP FUNCTION dbo.Pmt
GO
CREATE FUNCTION dbo.Pmt (@r NUMERIC(18,4), @nper INT, @pv NUMERIC(18,4), @fv NUMERIC(18,4), @type INT)
RETURNS NUMERIC(18,4)
AS
BEGIN
DECLARE @pmt NUMERIC(18,4)
SET @pmt = @r / (Power(1.0 + @r, @nper) - 1.0) * -(@pv * Power(1.0 + @r, @nper) + @fv)
if @type = 1
SET @pmt = @pmt / (1 + @r)
RETURN @pmt
END
GO
IF OBJECT_ID('Fv', 'FN') IS NOT NULL DROP FUNCTION dbo.Fv
GO
CREATE FUNCTION dbo.Fv (@r NUMERIC(18,4), @nper INT, @c NUMERIC(18,4), @pv NUMERIC(18,4), @type INT)
RETURNS NUMERIC(18,4)
AS
BEGIN
DECLARE @fv NUMERIC(18,4)
IF @type = 1
SET @c = @c * (1 + @r);
SET @fv = -(@c * (Power(1 + @r, @nper) - 1) / @r + @pv
* Power(1 + @r, @nper))
RETURN @fv
END
GO
IF OBJECT_ID('Ipmt', 'FN') IS NOT NULL DROP FUNCTION dbo.Ipmt
GO
CREATE FUNCTION dbo.Ipmt (@r NUMERIC(18,4), @per INT, @nper INT, @pv NUMERIC(18,4), @fv NUMERIC(18,4), @type INT)
RETURNS NUMERIC(18,4)
AS
BEGIN
DECLARE @ipmt NUMERIC(18,4)
SET @ipmt = dbo.Fv(@r, @per - 1, dbo.Pmt(@r, @nper, @pv, @fv, @type), @pv, @type) * @r
if @type = 1
SET @ipmt = @ipmt / (1 + @r)
RETURN @ipmt
END
GO
IF OBJECT_ID('Ppmt', 'FN') IS NOT NULL DROP FUNCTION dbo.Ppmt
GO
CREATE FUNCTION dbo.Ppmt (@r NUMERIC(18,4), @per INT, @nper INT, @pv NUMERIC(18,4), @fv NUMERIC(18,4), @type INT)
RETURNS NUMERIC(18,4)
AS
BEGIN
RETURN dbo.Pmt(@r, @nper, @pv, @fv, @type) - dbo.Ipmt(@r, @per, @nper, @pv, @fv, @type);
END
GO
