## NPER Function from Excel

 Author Message Bad_Karma Mr or Mrs. 500 Group: General Forum Members Points: 535 Visits: 190 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` ChrisM@home SSChampion Group: General Forum Members Points: 12993 Visits: 10881 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 Bad_Karma Mr or Mrs. 500 Group: General Forum Members Points: 535 Visits: 190 Perfect, thanks for the assist!