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

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

klin · Accepted Answer · 2017-06-02 01:11:36Z

3

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;

answered Jun 2, 2017 at 1:11

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Function for Postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related