Log in  ::  Register  ::  Not logged in

 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 7, 2014 9:52 AM
 SSC Rookie Group: General Forum Members Last Login: Friday, September 16, 2016 9:39 AM Points: 49, Visits: 172
 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 8, 2014 7:33 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 12:24 AM Points: 1,149, Visits: 9,656
 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: Friday, September 16, 2016 9:39 AM Points: 49, Visits: 172
 Perfect, thanks for the assist!
Post #1540213

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.