NPER Function from Excel

  • 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

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

  • Perfect, thanks for the assist!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply