# PMT Calculation with T-SQL

• ### คำถาม

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

Thanks very much for your help in advance.

Manny

10 ตุลาคม 2552 17:54

### คำตอบ

• How Excel doing the equivalent calculation?

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

• ทำเครื่องหมายเป็นคำตอบโดย 16 ตุลาคม 2552 9:00
• แก้ไขโดย 3 ตุลาคม 2555 18:22
10 ตุลาคม 2552 20:35

### ตอบทั้งหมด

• Based on the number and the formula you provided, the answer is 391.59 and not 410.21
Abdallah, PMP, MCTS
10 ตุลาคม 2552 18:26
• 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????
10 ตุลาคม 2552 18:31
• 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
10 ตุลาคม 2552 18:34
• 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
10 ตุลาคม 2552 19:45
• 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

10 ตุลาคม 2552 20:03
• 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
10 ตุลาคม 2552 20:09
• What do you mean by "internal formula" the cell numbers that kind of stuff?
10 ตุลาคม 2552 20:14
• How Excel doing the equivalent calculation?

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

• ทำเครื่องหมายเป็นคำตอบโดย 16 ตุลาคม 2552 9:00
• แก้ไขโดย 3 ตุลาคม 2555 18:22
10 ตุลาคม 2552 20:35
• 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).
31 กรกฎาคม 2562 14:42