Calculate pmt in t-sql (like excel)

Question

Calculate pmt in t-sql (like excel)

Stefan Bengtsson

SSC-Addicted

Points: 468
More actions
April 15, 2008 at 9:30 am

#69918

Please anyone!
I like to calculate payment for loan monthly
In excel it should look like this:
=PMT(A1/12,B2,-C3, D4,1)
A1 = 7.40%
B2 = 36 (Month)
C3 = 106344 (Financing)
D4 = 75960 (Rest value)
In excel the result = 1404
How do i do this in t-sql
Please anyone
Regards Stefan

Viewing 15 posts - 1 through 15 (of 34 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

declare @pv money
declare @fv money
declare @nper int
declare @APR float
declare @months int
declare @pmt money
set @pv=106344;
set @fv=75960;
set @nper=36;
set @months=12;
set @APR=.074;
select (@PV-@FV)/@NPER --a.k.a the Principal payment
+ (@PV+@FV-(@PV-@FV)/@NPER)/2*(@APR/@months) --a.k.a the interest payment

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Stefan Bengtsson SSC-Addicted Points: 468 More actions · Answer 2

Thanks Matt!

You the man

Don't be suprise if i add another request:)

I'm doing a lot a calculating just now

Have i nice day

Regard Stefan from Sweden

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

By the way - that was the "interest gets applied at the END of the period" formula. For the "beginning" - it would be:

select (@PV-@FV)/@NPER --a.k.a the Principal payment

+ (@PV+@FV)/2*(@APR/@months) --a.k.a the interest payment

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Stefan Bengtsson SSC-Addicted Points: 468 More actions · Answer 4

Thanks again Matt

I was just ready to ask you again about start of month and end of month

Stefan

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Let's keep the formulae here, so that others can double-check me....

What about Future Value, you ask?

[font="Courier New"]DECLARE @APR FLOAT

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

[/font]

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

johnnie.vaneeden SSC Journeyman Points: 76 More actions · Answer 6

I want to calculate PV as in Excel's formula. pv(rate, nper, pmt, type])

PV = R1,000.00

pv(0.05,6,187.64,0)

Thanks

Shane P Miller Valued Member Points: 69 More actions · Answer 7

I have been trying to do something similar where I am trying to calcualte how many periods (months) it will take to pay a loan off.

pv = 50000

ir = .06

fixed payment = 400

Can anyone help me with the SQL statment?

philip.taufik Valued Member Points: 69 More actions · Answer 8

this result is exactly same with excel result.

but why if the @fv = 0 the result is different.

this script's result is 3272.78583333333, while excel result is $3,282.8337

thanks 🙂

subhash.sharma SSC Journeyman Points: 77 More actions · Answer 9

Hi I am trying to calc the APR in SQL, the result varies from Excel

Excel = RATE(343,-522.71,93252.41,0)*12 = 4.827 %

SQL = It comes to 4.839 %

Is there a way to calculate in SQL which matches with Excel.

Thanks for the help!

Sergiy SSC Guru Points: 110208 More actions · Answer 10

Do you use FLOAT data type for all variables?

_____________
Code for TallyGenerator

Stefan Bengtsson SSC-Addicted Points: 468 More actions · Answer 11

Stefan Bengtsson

SSC-Addicted

Points: 468

November 24, 2008 at 12:44 am

#902300

Yes, i'm using FLOAT to all my varibles

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

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

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

Scott T (12/30/2008)
For any math calculation avoid FLOAT use Decimal you will get Write result.:D

Any bets on that? 😉

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

Kevin Finke SSC Journeyman Points: 91 More actions · Answer 14

Did you ever figure out how to calcuate APR based on PV, PMT, r and N in SQL?