Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 NPER Function from Excel Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, February 07, 2014 9:52 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, April 02, 2014 8:47 AM Points: 47, Visits: 159
 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.63SET @Pv = 11844.67SET @Rate = .249 / 12SET @Fv = 0SET @Type = 0SELECT (SQRT((@Pmt * (1 + (@Rate * @Type)) + ((-1 / @Rate) * @Fv)) / ((@Pv * @Rate) + (@pmt * (1 + @Rate * @Type)))) / SQRT(1 + @Rate)) * 100`
Post #1539266
 Posted Saturday, February 08, 2014 7:33 AM
 Ten Centuries Group: General Forum Members Last Login: Yesterday @ 11:32 PM Points: 1,059, Visits: 5,760
 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 Group: General Forum Members Last Login: Wednesday, April 02, 2014 8:47 AM Points: 47, Visits: 159
 Perfect, thanks for the assist!
Post #1540213

 Permissions