NPER Function from Excel

• Bad_Karma

  Default port

  Points: 1439

  More actions

  February 7, 2014 at 9:52 am

  #288000

  OK, be kind, I'm not a math wiz. But I'm trying to develop a TSQL function that will replicate the functionality of the NPER function in Excel.

  The raw formula is supposedly found here http://customer.optadata.com/en/dokumentation/application/expression/functions/financial.html, 5th one down.

  However when I try to write that in TSQL I can't match excel. Here is my excel formual =NPER(0.249/12,344.63,-11844.67,0,0) which yields 60.8071 Months.

  And when I try to rewrite in SQL I'm coming up with 75.6207 Months. Can someone tell me where I'm going wrong? Thanks!

  DECLARE @Pmt DECIMAL(18, 2),

  @Pv DECIMAL(18, 2),

  @Rate DECIMAL(10, 8),

  @Fv DECIMAL(18, 2),

  @Type int;

  SET @Pmt = 344.63

  SET @Pv = 11844.67

  SET @Rate = .249 / 12

  SET @Fv = 0

  SET @Type = 0

  SELECT (SQRT((@Pmt * (1 + (@Rate * @Type)) + ((-1 / @Rate) * @Fv)) / ((@Pv * @Rate) + (@pmt * (1 + @Rate * @Type))))

  / SQRT(1 + @Rate)) * 100

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  February 8, 2014 at 7:33 am

  #1687318

  That formula appears to be incorrect. Here's Microsoft's:

  SELECT NPER = LOG10((@PMT*(1+@rate*@type)-@FV*@rate)/(@PMT*(1+@rate*@type)+@PV*@rate))/ LOG10(1+@rate)

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• Bad_Karma

  Default port

  Points: 1439

  More actions

  February 11, 2014 at 8:02 am

  #1687914

  Perfect, thanks for the assist!