Calculate pmt in t-sql (like excel)

  • RBarryYoung

    SSC Guru

    Points: 143327

    usarian (3/25/2009)


    Point taken on the float bit. In your example one does get the correct result, HOWEVER:

    DECLARE @a AS FLOAT

    SET @a = 1

    SELECT @a

    --VS

    DECLARE @a AS DECIMAL

    SET @a = 1

    SELECT @a

    For me, that's an obvious deal breaker.

    my rule of thumb:

    NEVER use float, unless your smart enought to know when it's going to work right, because most of the time it wont.

    OK. Well MY rule of thumbs are:

    1) Don't believe everything that you hear, especially on the internet.

    2) Don't accept uninformed opinion at face value

    3) Base conclusions on reason and evidence, not claims and emotions

    4) Test everything

    So let's test this, shall we?:

    DECLARE @a AS FLOAT

    SET @a = 1

    SELECT @a

    go

    --VS

    DECLARE @a AS DECIMAL

    SET @a = 1

    SELECT @a

    go

    /* Results:

    ----------------------

    1

    ---------------------------------------

    1

    */

    Hmmm, apparently your deals break for no obvious reason.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Lynn Pettis

    SSC Guru

    Points: 442361

    Scott T (1/16/2009)


    --With simple calculation we don't care about data-type (float or decimal)

    --====================================

    --When we start calculation like the following one, we should use decimal

    -- Let's see example

    --====================================

    DECLARE @APR ??????? (18,3)

    --------------------------------------

    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

    --=====================

    --In Case When we declare with FLOAT

    ---------------------------------------

    DECLARE @APR FLOAT

    ---------------------------------------

    -- RESULT

    ---------------------------------------

    89976.668128

    --=====================

    --=====================

    --=====================

    --In Case When we declare with DECIMAL

    ---------------------------------------

    DECLARE @APR DECIMAL(18,3)

    ---------------------------------------

    -- RESULT

    ---------------------------------------

    89976.668104

    --=====================

    As you can see the results it's not matching. 😉 Well it's up to you know to make a decision.

    -------------------------------------------------------------------

    Only from my personal experience, if there is possibility for mistake it will happens, eventually.

    -------------------------------------------------------------------

    GOOD LUCK 🙂

    Actually, if you apply the rule regarding significant digits you probably should round the results of both calculations to the nearest hundreths (or perhaps thousandths), and if you do that, both values are the same.

  • usarian

    SSC Veteran

    Points: 218

    RBarryYoung (3/25/2009)


    usarian (3/25/2009)


    Point taken on the float bit. In your example one does get the correct result, HOWEVER:

    DECLARE @a AS FLOAT

    SET @a = 1

    SELECT @a

    --VS

    DECLARE @a AS DECIMAL

    SET @a = 1

    SELECT @a

    For me, that's an obvious deal breaker.

    my rule of thumb:

    NEVER use float, unless your smart enought to know when it's going to work right, because most of the time it wont.

    OK. Well MY rule of thumbs are:

    1) Don't believe everything that you hear, especially on the internet.

    2) Don't accept uninformed opinion at face value

    3) Base conclusions on reason and evidence, not claims and emotions

    4) Test everything

    So let's test this, shall we?:

    DECLARE @a AS FLOAT

    SET @a = 1

    SELECT @a

    go

    --VS

    DECLARE @a AS DECIMAL

    SET @a = 1

    SELECT @a

    go

    /* Results:

    ----------------------

    1

    ---------------------------------------

    1

    */

    Hmmm, apparently your deals break for no obvious reason.

    Doggone it, I had deleted that post!

    Anyway, I posted it in the first place becuase my result came back .999999999 for float the first time I ran it, but not since.. I think it might be a difference between SQL Server 2000 and 2005, but I don't know.

    BUT since there's people responding on this topic still, I tried the NPER function somebody posted above, and it's not working for me. I'm trying to duplicate Excel's NPER(rate,payment,presentvalue,futurevalue) using negative numbers for payments and futurevalue = 0. Any ideas or pointers?

  • RBarryYoung

    SSC Guru

    Points: 143327

    usarian (4/3/2009)


    ...

    BUT since there's people responding on this topic still, I tried the NPER function somebody posted above, and it's not working for me. I'm trying to duplicate Excel's NPER(rate,payment,presentvalue,futurevalue) using negative numbers for payments and futurevalue = 0. Any ideas or pointers?

    There were multiple versions posted I think. Please post the version that you are using and let us know what specifically is wrong with the results.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • nsalat

    Newbie

    Points: 2

    Bingo, finally found accurate sql code for a beginning period payment.  Thanks ambgoval.

Viewing 5 posts - 31 through 35 (of 35 total)

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