Solved

Payment function in MSSQL

Posted on 2003-11-26
18
2,241 Views
Last Modified: 2009-03-25
I have developed a database in Access but am now having a problem moving it over to MSSQL. I'm experienced in Access and new to SQL. The problem is with Functions. In my access database I have a table called Mortgage_Rates. What I have done is create a qurey that will generate the APR based upon the rate.

Here is my SQL Statement from Access.

SELECT
     Product,
     Purch_Ref_Rate,
     -PMT(Purch_Ref_Rate/12,180,100000,0,0) AS Payment,
     Rate(180,Payment,-(100000-2500),0)*12 AS APR,
FROM
     Mortgage_Rates;

This works great in Access however when I create the View in  SQL I get an Error saying that the PMT Function is not a valid function. I did a little digging in the Help files and found it was valid in something called SQL Server Analysis Services. I installed it and took a look at it. I am not sure if this is what I was looking for.

So the question is,
Do anyone know how to make my (Access) SQL Statement work in MSSQL(2000 pro). Or Do I have to use this Analysis Services thing. Or am I completely missing the mark altogether.

0
Comment
Question by:ktrimmer
  • 9
  • 5
  • 3
  • +1
18 Comments
 
LVL 3

Expert Comment

by:Flynnious
ID: 9824445
I don't know anything about what PMT does, but it appears to be an Excel function.  I'm not sure how you call it in Access, unless it's also a standard Access function or it's been implemented in Access (in a module or vba behind a form) by creating a new Excel object and calling the Excel function.  In any case, to use it SQL you'll need to create a new User-defined function to do the calculation (which means knowing what's involved in the PMT calculation).  Once you've set up the function, it's called just like you call PMT in your query above.

As far as I'm aware, analysis services is used for anyalysing your database in real-time, and it doesn't make any difference to what's available through standard queries, so that won't help you.

HTH

Flynnious
0
 

Expert Comment

by:gumball_ch
ID: 9824522
Hi

can you post the result of this select statement, than we can analyze what this function is for.

Jan
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9824732
Here his a function that should do the work

Note you need SQL 2000 or above to have it work
if you have SQL 7, just take the SQL and paste it in your code

create function pmt (@MonthlyRate float, @NumberOfMonths int, @Amount float)
returns float
as
begin
return (@amount*@monthlyrate/(1-power(1+@monthlyrate,-1*@numberofmonths)))
end


Hilaire
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9824741
My function does not support the last optional params of the Excel function (set to 0 in the code you posted)

Tell me if you need them

Back in a few minutes (hopefully) for the rate function

Hilaire
0
 
LVL 1

Author Comment

by:ktrimmer
ID: 9824916
Hilaire,
That worked great.  If you can get the rate funciton, I would be greatful.

gumball_ch
here's the output you asked for.
Product     Rate     Payment     APR
0
 
LVL 1

Author Comment

by:ktrimmer
ID: 9824937
sorry slip of the keyboard

Product     rate         payment                    apr
15 year     4.875%    784.297430306545     5.26%

using the function provided by Hilaire returned the payment correctly.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9825949
If I trust what I have read at these URLs

http://www.gnucash.org/docs/v1.8/C/gnucash-guide/loans_calcs1.html

http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D214105

the rate calculation is more complicated
and involves an iterative process

of course this could be done in T-SQL in a user-defined function, but I'll have to go through the code to pick up the algorithm and I don't have enough time for it just now

I'd tend to say that if the algorithm happens to be too complex, it could be a good idea to use an external component (active X dll) to perform this task

Maybe you could close this question and open a new one,
cause this rate() issue sounds much more complicated, and maybe other experts will come with better ideas

Hilaire
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9828713
Hi,

This kind of function should work,
I haven't tested it yet, not even checked syntax because I don't have a SQL box available at the moment

create function rate (@NumberOfMonths int, @Payment float, @Amount float)
returns float
as
begin
declare @Payment_calc float, @rate_max float, @rate_min float, @rate float, @iter_cnt int
set @rate_max=0.2
set @rate=0.1
set @rate_min=0.0
set @iter_cnt=0
set @Payment_calc = (@amount*@rate/(1-power(1+@rate,-1*@numberofmonths)))
while ((abs(@Payment-@Payment_calc ) > 0.1) and (@iter_cnt < 256))
begin
if (@Payment>@Payment_calc ) set @rate_min=@rate else set @rate_max = @rate
set @rate = (@rate_min + @rate_max)/2.0
set @Payment_calc = (@amount*@rate/(1-power(1+@rate,-1*@numberofmonths)))
set @iter_cnt = @iter_cnt + 1
end
return (@rate)
end

iteration count is to avoid endless loop
I'll check the function tomorrow, you can give it a try if you want

HTH

0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9830411
I just had the chance to test it and it seems to work reasonabily fast.
The algo is maybe not perfect but it's the best I could do at 1:00 AM
gives the same results than Excel

XL :  rate(120,-2000,80000) = 2,3451688%

my function :  rate(120,2000,80000)  = 0.0234527587890625

(note there's no minus sign)

if you need more precision, you'll have to change
((abs(@Payment-@Payment_calc ) > 0.1) in the while loop
into
((abs(@Payment-@Payment_calc ) > 0.05)
lower values => higher precision, more iterations

I let you see what you need

For the sake of clarity, I didn't double-check the input parameters (amount positive, <> 0, ...) but can do it if you want

Hilaire



0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 26

Expert Comment

by:Hilaire
ID: 9830443
Another difference between Access and SQL Server is that sql server won't let you write

select pmt(...) as payment, rate(20,payment,10000) from ...

you can refer to an alias in the same select where it was defined

you'd have to write
select pmt(...) as payment, rate(20,pmt(...),10000) from ...

Tell me if you need clarification on this point, i'm fully aware my english is a little awkward

Hilaire


0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9832542
Hi ktrimmer,

did you try my rate function ?

Hilaire
0
 
LVL 1

Author Comment

by:ktrimmer
ID: 9849751
Sorry for the delay in response.
I was off of work on thurs and fri.
However, I just tried your function for rate, it worked great. I do appreciate all of the work you did on this.

If you don't mind revealing your secrets, may I asked where you found the infromation for how to write these functions,

I am new to sql and I am looking to build my resources a bit. If you know of a few good resource sites, fourms I would be greatful.

Before I close this question, I need to make sure that this rate passes with the higher ups in the Company. I am almost certain we only have to go 5 Decimal places on our equation. Once I know for sure all of my questions will be answerd 100% and the points are yours.

But to help build ego, I must admint I now consider you a genius. thanks for your help.
0
 
LVL 3

Expert Comment

by:Flynnious
ID: 9849829
Strictly speaking, mine was the first correct answer, because I identified that the functions are available in Access and not in SQL, and you'd have to write a User-defined function to implement them yourself.  All I didn't do was give you the code for the functions :-D

I wouldn't dare be so audacious as to say Hilaire doesn't deserve the points.  All the points should go to Hilaire for quite extraordinary amounts of effort.  I just wanted to point out my answer was right, and useful, and I'm going to take a small amount of deflected credit for that.  Hope you don't mind :o)

Flynnious
0
 
LVL 1

Author Comment

by:ktrimmer
ID: 9849834
Okay here is your pat on the back.

Thank YOU TOO.
0
 
LVL 3

Expert Comment

by:Flynnious
ID: 9849860
Haha, you're welcome :)

Thanks.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9849866
Glad to see my functions fit your needs

I've got no secrets, and google is my best friend.
My best site to learn new tips is EE
The good thing in EE is that for someone eager to learn like me, it's always real-world issues, not ugly exercices to counts cats and mice

For the formulas, I didn't find really intersting links apart from the two urls I gave in a previous post.
From the comments on the MS article, I wrote my own algorithm based on a natural method, exactly the same as in the game
"guess a number between 0 and 100"
first you answer 50, to get rid of half of the possible values in the current range, and so on ...
I think it's called a dichotomic decision tree and allows you to find a value whithout beeing really able to calculate it

Cheers
Hilaire (Definitely not a genious)
0
 
LVL 1

Author Comment

by:ktrimmer
ID: 9850834
Well, as luck would have it, I need to go one more decimal. I did increase (@Payment-@Payment_calc ) > 0.1 to .05 but I am still having a problem with accuracy.

My Excel results.                  My SQL Results       What I need
0.05260076170689670000    0.052587890625      .052600
0.05230183997979690000    0.052294921875      .052301
0.05201446376607380000    0.052001953125      .052014

I need my results to be consistent to 6 Decimal places. Any idea what type of change I should do?
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 100 total points
ID: 9851552
Hi, this new version uses decimal data types instead of float
This gives the ability to truncate the results to any number of digits accurately

(there are values that can't be rounded properly using float datatype)

Also changed 0.05 to 0.001 for more precision

Hilaire

create function rate (@NumberOfMonths int, @Payment decimal(15,7), @Amount decimal(15,7))
returns decimal(15,6)
as
begin
declare @Payment_calc decimal(15,7), @rate_max decimal(15,7), @rate_min decimal(15,7), @rate decimal(15,7), @iter_cnt int
set @rate_max=0.2
set @rate=0.1
set @rate_min=0.0
set @iter_cnt=0
set @Payment_calc = (@amount*@rate/(1-power(1+@rate,-1*@numberofmonths)))
while ((abs(@Payment-@Payment_calc ) > 0.001) and (@iter_cnt < 256))
begin
if (@Payment>@Payment_calc ) set @rate_min=@rate else set @rate_max = @rate
set @rate = (@rate_min + @rate_max)/2.0
set @Payment_calc = (@amount*@rate/(1-power(1+@rate,-1*@numberofmonths)))
set @iter_cnt = @iter_cnt + 1
end
return round(@rate, 6)
end
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

759 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

22 Experts available now in Live!

Get 1:1 Help Now