title: SQL TVM or Annuity Functions PV,FV,NPER,RATE,PMT
body: <p>I am trying to get a few UDF together and as a good programer prefer the <kbd>CRTL</kbd>+<kbd>C</kbd>, <kbd>CRTL</kbd>+<kbd>V</kbd> method. That and I am not 100% on the math required. Basically, I am looking to emulate the functions from excel: <code>NPER()</code>,<code>RATE()</code>,<code>PV()</code>,<code>FV()</code>,<code>PMT()</code> for some reason these have been surprisingly hard to find, but I cannot believe someone does not have them laying around. I am using SQL SERVER 2008 so maybe if I had enterprise or BI they would be added and that is why no one is has asked for them. Below is one I have found that seems to be working well for <code>PMT()</code> </p>
  
  <pre><code>CREATE FUNCTION DBO.fPMT
      (
      -- DATETYPE FOR @RATE CHANGED TO FLOAT
      @RATE FLOAT,
      @PERIODS SMALLINT,
      @PRINCIPAL NUMERIC(20,2)
      )
  RETURNS NUMERIC (38,9)
  AS
  BEGIN
  DECLARE @PMT NUMERIC (38,9)
  
  DECLARE @WK_PERIODS FLOAT,
      @WK_PRINCIPAL FLOAT,
      @WK_ONE FLOAT,
      @WK_POWER FLOAT
  
  -- CONVERT DATA FOR CALCULATION
  SELECT  @WK_PERIODS = @PERIODS,
      @WK_PRINCIPAL = @PRINCIPAL,
      @WK_ONE = 1
  
  
  IF (POWER(@WK_ONE+@RATE,@WK_PERIODS)-@WK_ONE) = 0
      SET @PMT = 0
  ELSE
  BEGIN
      SELECT  @PMT =
      ROUND(
      -- DO ALL MULTIPLICATION BEFORE DIVISION
      ( @WK_PRINCIPAL * (@RATE*POWER(@WK_ONE+@RATE,@WK_PERIODS)))
      / (POWER(@WK_ONE+@RATE,@WK_PERIODS)-@WK_ONE)
      ,9)
  END
  
  RETURN @PMT
  
  END
  </code></pre>