PMT function - equation needed!

[Davi]

I need to know the math behind the PMT function in Access - or more accurately I need an equation to replace it! Trying to calculate the repayments on capital repayment loans using MS SQL.

Anyone either have an equation or can advise of a built in SQL function to do the same?


Cheers.

[road2ruin]

Davi said:

I need to know the math behind the PMT function in Access - or more accurately I need an equation to replace it! Trying to calculate the repayments on capital repayment loans using MS SQL.

Anyone either have an equation or can advise of a built in SQL function to do the same?


Cheers.

My wife seems to have a lot of PMT I will ask her tonight!

[tigger1]

How's this do you? Make sure you test it though - I'm not accepting any liability!



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

[Davi]

tigger1 said:

How's this do you? Make sure you test it though - I'm not accepting any liability!



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

Cheers Tigger, will give that to the lad doing the programming and we'll do some testing