Function for Postgresql

Question

On this website I saw a function, that I would like to use with postgresql:

https://raresql.com/2013/05/16/sql-server-excel-financial-functions-pmt/

Here is the query:

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

I renamed the variables without @ and changed the body a little bit, but somehow I can't get it to run correctly.

Is it possible to rewrite this query for postgresql? Do you have ideas how to do it? Thanks

• Vijunav Vastivch: The important here is your desired result and a sample data. can you show some?
• John Smith: @reds Hi thanks for your help! For example SELECT UDF_PMT(0.625,24,5000,0,0)would return 225
• John Smith: Some output can be seen in the post as well: raresql.com/2013/05/16/sql-server-excel-financial-functions-pmt @reds Thanks

Answer 1

CREATE OR REPLACE FUNCTION UDF_PMT (
 InterestRate NUMERIC(18,8),
 Nper INTEGER,
 Pv NUMERIC(18,4),
 Fv NUMERIC(18,4),
 Typ INTEGER
)
RETURNS NUMERIC(18,2)
AS $$
 SELECT round(
 CASE
 WHEN Typ = 0 THEN 
 (InterestRate / 100) /
 (Power(1 + InterestRate / 100, Nper) - 1) *
 (Pv * Power(1 + InterestRate / 100, Nper) + Fv)
 WHEN Typ = 1 THEN
 (InterestRate / 100) /
 (Power(1 + InterestRate / 100, Nper) - 1) *
 (Pv * Power(1 + InterestRate / 100, Nper) + Fv) /
 (1 + InterestRate / 100)
 END, 2)
$$ LANGUAGE SQL;