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.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Excels PMT function in SQL

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.PMT
go

create function dbo.PMT(@rate numeric(15,9), @periods smallint, @principal numeric(20,2) )
returns numeric (38,9)
as
begin
declare @pmt numeric (38,9)
select @pmt = @principal
/ (power(1+@rate,@periods)-1)
* (@rate*power(1+@rate,@periods))
return @pmt
end

go

Now, again, using this function with the same example as before

select dbo.pmt(0.08/12, 10, 10000)

The result is £1037.039321000

As 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...

thanks

Ian





'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)
Go to Top of Page

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/12
select Rate1 = @rate

select @rate = round(0.08000000/12.0000000,9)
select Rate2 = @rate

Rate1
-----------------
.006666000

Rate2
-----------------
.006666667





CODO ERGO SUM
Go to Top of Page

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.PMT
go
create function dbo.PMT
(
-- Datetype for @rate changed to float
@rate float,
@periods smallint,
@principal numeric(20,2)
)
returns numeric (38,9)
as
begin
declare @pmt numeric (38,9)

declare @WK_periods float,
@WK_principal float,
@wk_One float,
@WK_power float

-- Convert data for calculation
select @WK_periods = @periods,
@WK_principal = @principal,
@WK_One = 1

select @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 @pmt

end
go
declare @rate float
select @rate = .0800000000000/12.000000000000000000

select PMT = dbo.PMT( @rate, 10 , 10000 )

PMT
-----------------
1037.032089359



CODO ERGO SUM
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -