Calculate pmt in t-sql (like excel)

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

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

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

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

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

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