Solved

* HILAIRE * - NPER Excel Function in MSSQL

Posted on 2004-08-04
4
918 Views
Last Modified: 2012-08-14
Hi All

I was searching through EE and viewed the question posted by ktrimmer: Payment function in MSSQL asked by ktrimmer on 11/26/2003 04:36AM PST  - Q_20809063.html

The solution was very informative and was of extreme assistance to me.

I am now looking for a function the equivalent of the excel NPER Function.
The following parameters are known.

Capital Amount - R 5000
Interest Rate - 20.3% .
Payment Amount - R 500 per month.

I want a function that can give me the number of months (taking into account interest) that a person will have to pay R500 for in order to settle a debt.

The excel funtion of NPER needs to be applied in MSSQL.
EG) =NPER(B5/12,B6,-B3)

B5 = 20.3%    B6 = 500, B3 = 5000

Hilaire .. your solution in previously asked question as mentioned above calculates rate as well as payment amount.  Is there a formulae/function in sql that will calculate No of Periods

Your assistance in this regard will be greatly appreciated

Thanking You
Regards
TAZI
0
Comment
Question by:TAZI
  • 3
4 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 11728091
Hi TAZI,

>>Is there a formulae/function in sql that will calculate No of Periods<<
No, you'll have to make your own

I tried to come up with my own version, based on an oarticle at this URL
http://www.duncanwil.co.uk/nper.html

I'm not a finance specialist, and I haven't found a correct algorithm for the moment

That would require more time than I have for the moment.
I'll let you know as soon as I find something better.

Hopefully other experts will join.

Cheers

Hilaire




0
 
LVL 26

Accepted Solution

by:
Hilaire earned 75 total points
ID: 11728356
Finally found something ...

I used formulas from MS : reference http://support.microsoft.com/?kbid=214005

create function NPER(@rate float, @payment float,@capital float)
returns float
as
begin
return log10(@payment/(@payment+@capital*@rate))/log10(1+@rate)
end
go

select dbo.NPER(.203/12, 500, -5000)  ---> returns 11.047592792208778

Hilaire
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11736415
Hi TAZI,

did you try my code above ?
0
 
LVL 1

Author Comment

by:TAZI
ID: 11760237
Hi

Yes, I tried your code and it works wonderfully.

Thanks a million
Sorry, I didn't get a chance to accept your question on Friday.  

Many Thanks
Regards
TAZI
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now