Calculate pmt in t-sql (like excel)

Question

Calculate pmt in t-sql (like excel)

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

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]

Sergiy SSC Guru Points: 110208 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

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]

Scott T SSC Veteran Points: 208 More actions · Answer 5

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

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]

Sergiy SSC Guru Points: 110208 More actions · Answer 7

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

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]

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 9

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)

Sergiy SSC Guru Points: 110208 More actions · Answer 10

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

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]

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 12

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)

ambgoval SSC Journeyman Points: 89 More actions · Answer 13

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

usarian SSC Veteran Points: 218 More actions · Answer 14

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.

usarian SSC Veteran Points: 218 More actions · Answer 15

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.