Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

NPER Function from Excel Expand / Collapse
Author
Message
Posted Friday, February 7, 2014 9:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 3:39 PM
Points: 48, Visits: 163
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

Post #1539266
Posted Saturday, February 8, 2014 7:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 1,074, Visits: 6,383
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) 




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1539498
Posted Tuesday, February 11, 2014 8:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 3:39 PM
Points: 48, Visits: 163
Perfect, thanks for the assist!
Post #1540213
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse