# PMT Calculation with T-SQL

• ### Question

• Hi there,

I am having this issue where I have to calculate the PMT of a lon with T-SQL 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 money

set @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.59

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

Manny

Saturday, 10 October 2009 5:54 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)

• Marked as answer by Friday, 16 October 2009 9:00 AM
• Edited by 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, MCTS
Saturday, 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, MCTS
Saturday, 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.com
Saturday, 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 T-SQL.

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 T-SQL.

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.com
Saturday, 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)

• Marked as answer by Friday, 16 October 2009 9:00 AM
• Edited by 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