locked
PMT Calculation with T-SQL RRS feed

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

    Thanks very much for your help in advance.

    Manny

    Saturday, 10 October 2009 5:54 PM

Answers

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)


    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