# PMT Calculation with T-SQL

• ### Soru

• 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

10 Ekim 2009 Cumartesi 17:54

### Yanıtlar

• How Excel doing the equivalent calculation?

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

• Yanıt Olarak İşaretleyen 16 Ekim 2009 Cuma 09:00
• Düzenleyen 3 Ekim 2012 Çarşamba 18:22
10 Ekim 2009 Cumartesi 20:35

### Tüm Yanıtlar

• Based on the number and the formula you provided, the answer is 391.59 and not 410.21
Abdallah, PMP, MCTS
10 Ekim 2009 Cumartesi 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 Ekim 2009 Cumartesi 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 Ekim 2009 Cumartesi 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 Ekim 2009 Cumartesi 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 Ekim 2009 Cumartesi 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 Ekim 2009 Cumartesi 20:09
• What do you mean by "internal formula" the cell numbers that kind of stuff?
10 Ekim 2009 Cumartesi 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)

• Yanıt Olarak İşaretleyen 16 Ekim 2009 Cuma 09:00
• Düzenleyen 3 Ekim 2012 Çarşamba 18:22
10 Ekim 2009 Cumartesi 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 Temmuz 2019 Çarşamba 14:42