# Calculate pmt in t-sql (like excel)

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

Regards Stefan

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

• 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

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

• Thanks again Matt

Stefan

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

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

• 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

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

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

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

• Do you use FLOAT data type for all variables?

_____________
Code for TallyGenerator

• Yes, i'm using FLOAT to all my varibles

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

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