title: Replicate PMT function in SQL, use column values as inputs
body: <p>In excel the PMT function gives you a monthly payment based upon the annual rate, total number of periods, and original face value, and ending value of a bond.</p>
  
  <p>The mathematical formula for calculating the monthly payment is:</p>
  
  <pre><code>M=[OF(i(1+i)^n)]/[(1+i)^(n-1)]
  
  M=Monthly payment
  OF=Original Face
  i=annual interest rate/12
  n=number of periods
  </code></pre>
  
  <p>To calculate the monthly payment with (i=.08/12, n=10, OF=10000)  as inputs, I used this:</p>
  
  <pre><code>if object_id('dbo.PMT') &gt; 0
  drop function dbo.PMT
  go
  
  create function dbo.PMT(@rate numeric(15,9), @periods smallint, @principal numeric(20,2) )
  returns numeric (38,9)
  as
  begin
  declare @pmt numeric (38,9)
  select @pmt = @principal 
  / (power(1+@rate,@periods)-1) 
  * (@rate*power(1+@rate,@periods))
  return @pmt
  end
  
  go
  </code></pre>
  
  <p>Then to correct for data types, based on a forum I used:</p>
  
  <pre><code>drop function dbo.PMT
  go
  create function dbo.PMT
  (
  @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
  
  select  @WK_periods = @periods,
  @WK_principal = @principal,
  @WK_One = 1
  
  select  @pmt =
  round(
  ( @WK_principal * (@rate*power(@WK_One+@rate,@WK_periods)))
  / (power(@WK_One+@rate,@WK_periods)-@WK_One)
  ,9)
  
  return @pmt
  
  end
  go
  declare @rate float
  select @rate = .0800000000000/12.000000000000000000
  
  select PMT = dbo.PMT( @rate, 10 , 10000 )
  
  PMT                                                      
  </code></pre>
  
  <p>This returned an accurate and correct answer. However, is there a way to do this so that instead of using these constants  (i=.08/12, n=10 etc) as inputs, I could use a column of values? I have about 10000 bonds all with different periods, rates, and original faces and need to calculate monthly payments based on their column. Ideally, I would have an additional column displaying monthly payment for each bond. Any help at all would be incredible, thanks!</p>