April 15, 2008 at 9:30 am
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
April 15, 2008 at 9:59 am
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?
April 15, 2008 at 11:40 am
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
April 15, 2008 at 12:44 pm
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?
April 15, 2008 at 11:40 pm
Thanks again Matt
I was just ready to ask you again about start of month and end of month
Stefan
April 18, 2008 at 9:24 am
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?
June 9, 2008 at 9:51 am
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
June 19, 2008 at 10:16 am
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?
October 27, 2008 at 9:32 pm
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 🙂
November 21, 2008 at 10:03 am
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!
November 21, 2008 at 10:09 pm
Do you use FLOAT data type for all variables?
_____________
Code for TallyGenerator
November 24, 2008 at 12:44 am
Yes, i'm using FLOAT to all my varibles
December 30, 2008 at 3:28 pm
For any math calculation avoid FLOAT use Decimal you will get Write result.:D
December 30, 2008 at 7:02 pm
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
January 13, 2009 at 11:00 am
Did you ever figure out how to calcuate APR based on PV, PMT, r and N in SQL?
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply