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)