Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Bob Whatever
Starting Member
7 Posts |
Posted - 2005-09-29 : 08:22:26
|
Afternoon, this is my first post so please bear with me.In Excel, you can use a PMT function which will create the monthy payment for a loan, using the principal balance, interest rate and length of the loan. Now using the example in excel help, a loan of £10,000, for 10 months, at 8%.0.08 Annual interest rate (A1)10 Number of months of payments (A2)10000 Amount of loan (A3)PMT Function = = PMT(A1/12, A2,A3)-£1,037.032089359 Monthly payment for a loan with the above terms (-1,037.03)Now in these forums i found the following code, which has allowed me to recreate the PMT function in SQL.if object_id('dbo.PMT') > 0 drop function dbo.PMTgocreate function dbo.PMT(@rate numeric(15,9), @periods smallint, @principal numeric(20,2) )returns numeric (38,9)asbegin declare @pmt numeric (38,9) select @pmt = @principal / (power(1+@rate,@periods)-1) * (@rate*power(1+@rate,@periods)) return @pmtendgoNow, again, using this function with the same example as beforeselect dbo.pmt(0.08/12, 10, 10000)The result is £1037.039321000As you can see when rounded this will only be a penny out, a bit picky some might say, but unfortunately when it applys to thousands of cases, over long periods of time, those pennies add up...Can anyone help to get the above PMT function as accurate as its Excel counterpart? If you need any further info at all please let me know...thanksIan'The government says they are losing the war on drugs, you know what this means? Theres a war on drugs and the people on drugs are winning it.' - Bill Hicks |
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-09-29 : 11:05:29
|
a) ensure all calculations include decimal points...some of your integer values may be implicitly converting to the wrong data type, thus limiting the result.b) try playing with real/float/decimal datatypes for calc....and trim/round only the end results.numeric may be insufficient for your accuracy....(mind you float/real have issues as well) |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-29 : 11:25:40
|
The problem is probably in using SQL Servers default casting of datetypes. For example, notice how the first query drops the last 3 decimal places: declare @rate numeric(15,9)select @rate = 0.08/12select Rate1 = @rateselect @rate = round(0.08000000/12.0000000,9)select Rate2 = @rateRate1 ----------------- .006666000Rate2 ----------------- .006666667CODO ERGO SUM |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-29 : 12:30:58
|
This seems fairly accurate, and an exact match to what you got from Excel:drop function dbo.PMTgocreate function dbo.PMT ( -- Datetype for @rate changed to float @rate float, @periods smallint, @principal numeric(20,2) )returns numeric (38,9)asbegindeclare @pmt numeric (38,9)declare @WK_periods float, @WK_principal float, @wk_One float, @WK_power float-- Convert data for calculationselect @WK_periods = @periods, @WK_principal = @principal, @WK_One = 1select @pmt =round(-- Do all multiplication before division( @WK_principal * (@rate*power(@WK_One+@rate,@WK_periods)))/ (power(@WK_One+@rate,@WK_periods)-@WK_One),9)return @pmtendgodeclare @rate floatselect @rate = .0800000000000/12.000000000000000000select PMT = dbo.PMT( @rate, 10 , 10000 )PMT -----------------1037.032089359 CODO ERGO SUM |
|
|
Bob Whatever
Starting Member
7 Posts |
Posted - 2005-09-30 : 04:48:18
|
Thanks very much guys, works like a charm 'The government says they are losing the war on drugs, you know what this means? Theres a war on drugs and the people on drugs are winning it.' - Bill Hicks |
|
|
|
|
|
|
|