PMT function excel formula when the rate is zero

Question

Im tryng to do in SQL Server a Function with the PMT function found in MS Excel.

And when i use in excel

=PMT(0;3;-29590)

give me this result:

$9,863.33

And when i try any formula, give me divide by zero error. and i have some paiments in 3 periods without any rates. So how i can solve that problem?

And here is the actual function:

create function dbo.PMT
(
@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

select  @WK_periods = @periods,
@WK_principal = @principal,
@WK_One = 1

select  @pmt =
round(
( @WK_principal * (@rate*power(@WK_One+@rate,@WK_periods)))
/ (power(@WK_One+@rate,@WK_periods)-@WK_One)
,9)

return @pmt

end

with out any rate the payment is just `principal/periods*-1` So put an `IF` in that checks for `@rate` to be `0` and do the simple division. — Scott Craner, Aug 23 '18 at 20:56

score 0 · Accepted Answer · answered Aug 23 '18 at 22:17

Add a simple if to your function

if (@rate = 0)
    select @pmt = (@principal / @periods) * -1
else
    select  @pmt =
    round(
    ( @WK_principal * (@rate*power(@WK_One+@rate,@WK_periods)))
    / (power(@WK_One+@rate,@WK_periods)-@WK_One)
    ,9)

PMT function excel formula when the rate is zero

1 Answers1