Calculate pmt in t-sql (like excel)

  • Scott T (12/30/2008)


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

    This is only true for Addition and Subtraction. For Multiplication and Division it is definitely not true, and in many cases, DECIMAL is likely to be a far worse choice that FLOAT.

    [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]

  • Scott T (12/30/2008)


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

    DECLARE @One decimal, @Three decimal

    SET @One = 1

    SET @Three = 3

    SELECT @One/@Three + @One/@Three + @One/@Three

    Is it what you name "Write result" ?

    :w00t:

    Now, make them float and see the difference. 😛

    _____________
    Code for TallyGenerator

  • Sergiy (1/15/2009)


    Scott T (12/30/2008)


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

    DECLARE @One decimal, @Three decimal

    SET @One = 1

    SET @Three = 3

    SELECT @One/@Three + @One/@Three + @One/@Three

    Is it what you name "Write result" ?

    :w00t:

    Now, make them float and see the difference. 😛

    Heh... I was waitin' for ya... knew you'd be along just about any minute. 😛

    --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)

  • Agreed Jeff. Sergiy's example succinctly proves my point.

    Thanks, Sergiy!

    [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]

  • --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 🙂

  • 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

    ...

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

    As you can see the results it's not matching...

    Scott, what makes you think that the DECIMAL answer is more correct than the FLOAT answer?

    [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]

  • Scott T (1/16/2009)


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

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

    In science there is no space for someone's decisions.

    You need to find the proof.

    To see the proof just look where @APR is used.

    First calculation:

    @APR/@Months

    What are the results for DECIMAL and FLOAT options?

    Change the final SELECT to see it:

    SELECT 1+@APR/@Months, @FV

    DECIMAL option returns .00616666666666

    FLOAT option returns 6.1666666666666667E-3

    Anybody who was actually studying math in school will say that FLOAT result is more accurate.

    DECIMAL option introduces bigger error on the first step. And this error is being multiplied by every further calculation.

    Of course, results don't match - DECIMAL calculation makes an error on the first step, and there is nothing to correct that error further down the track.

    Does it mean that FLOAT calculations don't introduce errors at all?

    No.

    There is no way to avoid systematic errors in computer calculations. Just because of limited precision of any number stored in computer memory.

    But!

    FLOAT calculation will be always more precise than any of DECIMAL calculations of the same deepness.

    Deepness - it's the number of bits you reserve for storing the numbers during calculations.

    This is exactly the reason why float point calculations only are allowed in scientific calculations where getting precise result is crucial and amateurism is not welcome.

    Now, back to your case:

    FLOAT carries and uses in calculations 15 digits of a number.

    Because of the systematic errors it loses 1 digit of precision after 2-4 calculations.

    In your formula it's not correct to trust 14th digit, so only 13 digits of the result can be considered correct.

    You take only 11 digit for the final number, so I can state all of them are correct.

    Unlike DECIMAL one which makes an error in 12th digit on the first step, shifts it to 10th digit 2 steps later and keeps to make it worse all the way.

    Is there enough ground for a decision? 😎

    _____________
    Code for TallyGenerator

  • For those who may care, the formal study of numerical approximations and error in calculations is called "Numerical Analysis". It's a course that Math & CompSci majors should take in college. It's been 30 years since I took it and I am admittedly rusty, but Sergiy is obviously still fairly adept at it. 🙂

    [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]

  • Don't, even for a second, think that I'm trying to fan any flames here. 🙂 I'm only interested in knowing because you guys have probably forgotten more about precision than I'll ever know. 😉

    If I were doing mortgage calculations, and let's assume that the max limit on the mortgage were, say, ummm... a penny less than a billion dollars... which would be better to use so far as accuracy goes...

    FLOAT or DECIMAL (38,27)

    --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)

  • Jeff Moden (1/17/2009)


    If I were doing mortgage calculations, and let's assume that the max limit on the mortgage were, say, ummm... a penny less than a billion dollars... which would be better to use so far as accuracy goes...

    FLOAT or DECIMAL (38,27)

    1. If you see a mortgage for a billion dollars then there must be a lawyer involved. Very good one. And very smart one. So you'd better be precise. A smallest error in favor of either side (if he's a really smart one ;-)) could cost you a million or two.

    2. Billion holds 9 digits, penny adds other 2. Totally 11 digits precision required. FLOAT should be enough.

    3. DECIMAL (38, 27) uses 64 bit calculations, SQL version of FLOAT is 32 bit. I guess there is no need to explain further. 🙂

    _____________
    Code for TallyGenerator

  • Heh, I did say "likely", Jeff. 🙂

    [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]

  • Sergiy (1/17/2009)


    Jeff Moden (1/17/2009)


    If I were doing mortgage calculations, and let's assume that the max limit on the mortgage were, say, ummm... a penny less than a billion dollars... which would be better to use so far as accuracy goes...

    FLOAT or DECIMAL (38,27)

    1. If you see a mortgage for a billion dollars then there must be a lawyer involved. Very good one. And very smart one. So you'd better be precise. A smallest error in favor of either side (if he's a really smart one ;-)) could cost you a million or two.

    2. Billion holds 9 digits, penny adds other 2. Totally 11 digits precision required. FLOAT should be enough.

    3. DECIMAL (38, 27) uses 64 bit calculations, SQL version of FLOAT is 32 bit. I guess there is no need to explain further. 🙂

    Thanks ol' friend.

    --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)

  • complement of function return payment of being or end

    select @PV*Power((@apr+1),@nper)*@apr/(Power((@apr+1),@nper)-1); payment end

    select (@pv*Power((@apr+1),@nper)*@apr/(Power((@apr+1),@nper)-1))/(@apr+1); bein of payment

  • 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.

  • Sergiy (1/15/2009)


    Scott T (12/30/2008)


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

    DECLARE @One decimal, @Three decimal

    SET @One = 1

    SET @Three = 3

    SELECT @One/@Three + @One/@Three + @One/@Three

    Is it what you name "Write result" ?

    :w00t:

    Now, make them float and see the difference. 😛

    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.

Viewing 15 posts - 16 through 30 (of 34 total)

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