I Want to Create Financial Functions in SQL 1

[Ross1]

I need some financial functions that will work in SQL. In Excel (and Access too I believe) these functions are
RATE; which tells you the interest rate when you know loan amount, number of periods, and payment amount.
PMT; which tells you the payment amount when you know loan amount, number of periods, and interest rate.
NPER; which tells you the number of periods when you know interest rate payment amount and loan amount.

Has anyone created user-defined functions for these types of financial functions, or has anyone a solution for solving these equations using the functions available in SQL Server 2000?

Thanks

 

[donutman]

They all revolve around the basic formula:
Loan (NPV)= PMT(1-v^NPER)/RATE
where v=1/(1+RATE) and the first payment isn't due until the end of the 1st NPER.
-Karl

 

[Ross1]

Ahh...donutman...from you're handle we are obviously kindred spirits

You are correct about the basic formula. I'm having difficulty solving for NPER. I know I need to use some kind of a LOG function here, but I'm not sure exactly how to do that with the functions in SQL. I believe the only log functions available are the natural log and log base 10. Can that equation be solved for NPER using the natural log function? Or am I barking up the wrong tree?

Thanks

 

[donutman]

Your bark is right on. You can use any base to the log:
NPER=log(1-(LOAN)*RATE/PMT) / log(v).
-Karl

 

[Ross1]

Thanks donutman!