Answered by:
PMT Calculation with TSQL
Question

Hi there,
I am having this issue where I have to calculate the PMT of a lon with TSQL script. the Excel formula to calculate such a thing is; PMT(rate,nper,pv,fv,type)
I'm trying to get the same results with a SQL script but I'm not having any luck. this is what I'm doing here;
declare @pv money
declare @fv money
declare @nper int
declare @APR float
declare @months int
declare @pmt moneyset @pv=49581.89;
set @fv=0.0;
set @nper=182;
set @months=12;
set @APR=.058;select (@PV@FV)/@NPER a.k.a the Principal payment
+ (@PV+@FV(@PV@FV)/@NPER)/2*(@APR/@months) interest_payment
the results with this script is; $391.59when I run the same numbers with the PMT formula in Excel I get; $410.21 which the correct one.
Can somebody tell me what Am I missing in the SQL Script?
Thanks very much for your help in advance.
Manny
Saturday, 10 October 2009 5:54 PM
Answers

How Excel doing the equivalent calculation?
select (@PV@FV)/@NPER a.k.a the Principal payment
+ (@PV+@FV(@PV@FV)/@NPER)/2*(@APR/@months)
Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012 Marked as answer by Zongqing Li Friday, 16 October 2009 9:00 AM
 Edited by Kalman Toth Wednesday, 3 October 2012 6:22 PM
Saturday, 10 October 2009 8:35 PM
All replies

Based on the number and the formula you provided, the answer is 391.59 and not 410.21
Abdallah, PMP, MCTSSaturday, 10 October 2009 6:26 PM 
yeah, but is the difference if you run the exact same parameters into the PMT function in Excel, you get 410.21 result. I really don't understand why????Saturday, 10 October 2009 6:31 PM

Then it must be an Excel thing. I ran your code with the same values in SQL and on a regular calculator and I got the same results, try it out.
Abdallah, PMP, MCTSSaturday, 10 October 2009 6:34 PM 
when I run the same numbers with the PMT formula in Excel I get; $410.21 which the correct one.
Can you post the Excel formula so we can compare?
Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.comSaturday, 10 October 2009 7:45 PM 
so this is the Excel formula I get the 410.21 with the parameters above;
PMT(rate,nper,pv,fv,type)
so PMT(058/12, 182, 49581.89, 0)
the result in Excel with this formula is $410.21. I would like to get the same result with the TSQL.Manny
Saturday, 10 October 2009 8:03 PM 
so this is the Excel formula I get the 410.21 with the parameters above;
PMT(rate,nper,pv,fv,type)
so PMT(058/12, 182, 49581.89, 0)
the result in Excel with this formula is $410.21. I would like to get the same result with the TSQL.
That is not sufficient for comparison. Do you have the Excel PMT calculation formula (internal formula)?
Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.comSaturday, 10 October 2009 8:09 PM 
What do you mean by "internal formula" the cell numbers that kind of stuff?Saturday, 10 October 2009 8:14 PM

How Excel doing the equivalent calculation?
select (@PV@FV)/@NPER a.k.a the Principal payment
+ (@PV+@FV(@PV@FV)/@NPER)/2*(@APR/@months)
Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012 Marked as answer by Zongqing Li Friday, 16 October 2009 9:00 AM
 Edited by Kalman Toth Wednesday, 3 October 2012 6:22 PM
Saturday, 10 October 2009 8:35 PM 
It is obvious that they do not give the same result. In SQL you are using a linear formula, while the PMT function in Excel is a series of sums (financial mathematics).Wednesday, 31 July 2019 2:42 PM