Calculate pmt in t-sql (like excel)

  • Please anyone!

    I like to calculate payment for loan monthly

    In excel it should look like this:

    =PMT(A1/12,B2,-C3, D4,1)

    A1 = 7.40%

    B2 = 36 (Month)

    C3 = 106344 (Financing)

    D4 = 75960 (Rest value)

    In excel the result = 1404

    How do i do this in t-sql

    Please anyone

    Regards Stefan

  • declare @pv money

    declare @fv money

    declare @nper int

    declare @APR float

    declare @months int

    declare @pmt money

    set @pv=106344;

    set @fv=75960;

    set @nper=36;

    set @months=12;

    set @APR=.074;

    select (@PV-@FV)/@NPER --a.k.a the Principal payment

    + (@PV+@FV-(@PV-@FV)/@NPER)/2*(@APR/@months) --a.k.a the interest payment

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thanks Matt!

    You the man

    Don't be suprise if i add another request:)

    I'm doing a lot a calculating just now

    Have i nice day

    Regard Stefan from Sweden

  • By the way - that was the "interest gets applied at the END of the period" formula. For the "beginning" - it would be:

    select (@PV-@FV)/@NPER --a.k.a the Principal payment

    + (@PV+@FV)/2*(@APR/@months) --a.k.a the interest payment

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thanks again Matt

    I was just ready to ask you again about start of month and end of month

    Stefan

  • Let's keep the formulae here, so that others can double-check me....

    What about Future Value, you ask?

    [font="Courier New"]DECLARE @APR FLOAT

    SET @APR=.074;

    DECLARE @months INT  --how to break up the annual percentage

        SET @months=12;

    DECLARE @Period INT -- (i.e. at what point in the future do you want to know the value)

        SET @Period=36;

    DECLARE @pmt DECIMAL(18,2)  --monthly payment

        SET @pmt=1889;

    DECLARE @PV DECIMAL(18,2)  --present value or starting point

        SET @PV=132930;

    DECLARE @EndPeriod INT --whether compounding occurs at the beginning or the end of the period

        SET @EndPeriod=0;

    DECLARE @FV DECIMAL(36,6)

    SELECT @FV=(@PV*POWER(1+@APR/@Months,(@PERIOD-@EndPeriod))

                    -@PMT*(POWER(1+@APR/@Months,@PERIOD) -1)/(@APR/@Months))*POWER(1+@APR/@Months,@EndPeriod)

    SELECT @FV

                [/font]

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • I want to calculate PV as in Excel's formula. pv(rate, nper, pmt, type])

    PV = R1,000.00

    pv(0.05,6,187.64,0)

    Thanks

  • I have been trying to do something similar where I am trying to calcualte how many periods (months) it will take to pay a loan off.

    pv = 50000

    ir = .06

    fixed payment = 400

    Can anyone help me with the SQL statment?

  • this result is exactly same with excel result.

    but why if the @fv = 0 the result is different.

    this script's result is 3272.78583333333, while excel result is $3,282.8337

    thanks 🙂

  • Hi I am trying to calc the APR in SQL, the result varies from Excel

    Excel = RATE(343,-522.71,93252.41,0)*12 = 4.827 %

    SQL = It comes to 4.839 %

    Is there a way to calculate in SQL which matches with Excel.

    Thanks for the help!

  • Do you use FLOAT data type for all variables?

    _____________
    Code for TallyGenerator

  • Yes, i'm using FLOAT to all my varibles

  • For any math calculation avoid FLOAT use Decimal you will get Write result.:D

  • Scott T (12/30/2008)


    For any math calculation avoid FLOAT use Decimal you will get Write result.:D

    Any bets on that? 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Did you ever figure out how to calcuate APR based on PV, PMT, r and N in SQL?

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply