Feeds:
Posts

## SQL SERVER – Excel Financial functions – PMT

In my earlier article, I implemented a lot of excel financial functions including PMT function using CLR  and I found CLR implementation is the effective way to implement excel functions in SQL Server. However you can develop the same functionality in SQL using User Defined Function as well.

Given below is the script of PMT financial function in SQL Server with examples. Basically PMT function not only calculates the future value of any investment on an interest rate, it also calculates the payments for a loan.

```CREATE FUNCTION UDF_PMT
(@InterestRate  NUMERIC(18,8), --Rate is the interest rate per period.
@Nper          INT,           --Nper is the total number of payment
--periods in an annuity.
@Pv            NUMERIC(18,4), --Pv is the present value, or the
--lump-sum amount that a series of
--future payments is worth right now.
--If pv is omitted, it is assumed to be
--0 (zero). PV must be entered as a
--negative number.
@Fv            NUMERIC(18,4), --Fv is the future value, or the
--lump-sum amount that a series of
--future payments is worth right now.
--If pv is omitted, it is assumed to
--be 0 (zero). PV must be entered as a
--negative number.
@Type	         BIT            --Type is the number 0 or 1 and
--indicates when payments are due.
--If type is omitted, it is assumed
--to be 0 which represents at the end
--of the period.
--If payments are due at the beginning
--of the period, type should be 1.
)
RETURNS NUMERIC(18,2) --float
AS
BEGIN
DECLARE  @Value NUMERIC(18,2)
SELECT @Value = Case
WHEN @Type=0
THEN Convert(float,@InterestRate / 100)
/(Power(Convert(float,(1 + @InterestRate / 100)),@Nper)-1)
* -(@Pv*Power(Convert(float,(1 + @InterestRate / 100)),@Nper)
+@Fv)

WHEN @Type=1
THEN Convert(float,@InterestRate / 100) /
(Power(Convert(float,(1 + @InterestRate / 100)),@Nper)-1)
* -(@Pv*Power(Convert(float,(1 + @InterestRate / 100)),@Nper)
+@Fv)
/(1 + Convert(float,(@InterestRate / 100)))

END
RETURN @Value
END
GO
SELECT dbo.UDF_PMT(0.625,24,5000,0,0)
--OUTPUT in SQL
--(\$225.00)
--Equivalent function in excel
--=PMT(7.5%/12, 2*12, 5000, 0, 0)
--OUTPUT in Excel
--(\$225.00)
GO
SELECT dbo.UDF_PMT(0.11538461,208,8000,0,1)
--OUTPUT in SQL
--(\$43.23)
--Equivalent function in excel
--=PMT(6%/52, 4*52, 8000, 0, 0)
--OUTPUT in Excel
--(\$43.23)
GO
SELECT dbo.UDF_PMT(5.25,10,6500,0,0)
--OUTPUT in SQL
--(\$852.03)
--Equivalent function in excel
--=PMT(5.25%/1, 10*1, 6500, 0, 0)
--OUTPUT in Excel
--(\$852.03)
GO
SELECT dbo.UDF_PMT(0.666666667,36,5000,-1000,1)
--OUTPUT in SQL
--(\$131.14)
--Equivalent function in excel
--=PMT(8%/12, 3*12, 5000, -1000, 0)
--OUTPUT in Excel
--(\$131.14)
GO
```

1. on May 19, 2013 at 8:21 pm | Reply SutoCom