Arithmetic overflow error converting expression to data type float

Question

Working on an analysis of bonds. I have attempted to make a payment function that replicates the PMT function of excel. For the bonds, the "Cusip" is their identifier, their "PASS THRU RATE" is their annual interest rate, the "ORIGINAL WA MATURITY" is the total number of periods, and the "ORIGINAL BALANCE" is the original face value of the bond.

The equation for calculating a monthly payment by paper is:

M=[OF(i(1+i)^n)]/[(1+i)^(n-1)]
M=Monthly payment
OF=Original Face
i=annual interest rate/12
n=number of periods

I have a table with all the columns needed for this function, as well as different tables for different months that I will try and use this for. This is what I have so far, creating the function and trying to fix for data types:

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

drop function dbo.PMT
go
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
go

select ALL [CUSIP NUMBER]
,[PASS THRU RATE]
,[ORIGINAL WA MATURITY]
,[ORIGINAL BALANCE],
dbo.pmt((mbs012013.[PASS THRU RATE]),mbs012013.[ORIGINAL WA MATURITY],mbs012013.[ORIGINAL BALANCE])
FROM 
    [MBS_STATS].[dbo].[mbs012013]

However, I receive

(502882 row(s) affected)
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type float.

when I attempt to execute it. I cannot figure out what is causing this. Any help would be great!

you should state the programming language you use to get better support. not only SQL... so, please change the tags by adding additional one or few... — Kazimierz Jawor
– Kazimierz Jawor, Commented Jun 20, 2013 at 15:30

Jim · Accepted Answer · 2013-06-21 03:28:50Z

1

In the line below, you have @WK_principal as a FLOAT, and you're assigning the value of @principal which is a NUMERIC(20,2).

@WK_principal = @principal,

That seems to be the most likely culprit. We'd need to be able to see your data to help otherwise. Also, I'm not clear on why you're creating the function one way, then dropping it and recreating it differently. Or are you just showing two different attempts?

answered Jun 21, 2013 at 3:28

Jim

6,88113 gold badges48 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2501627 Over a year ago

It was my first attempt. I've changed data types around but still get returned the same error. If it helps, "principal" is the initial amount of the bond that ranges from 0-70,000,000,000, "rate" is an interest rate that ranges from 1-10, "periods" is the total months in the bond and ranges from 0-360. I would share the data, but the file is incredibly large (which is why I can't do this in excel!). Thanks again for any help!

user2501627 Over a year ago

EDIT: Alright, without changing any of the above code and correcting for another error I noticed, "arithmetic overflow" is now gone. Realizing that I wasn't making (PASS THRU RATE) a monthly interest rate I corrected the line in my last query to: dbo.pmt((mbs012013.[PASS THRU RATE]*(0.01/12)),mbs012013. [ORIGINAL WA MATURITY],mbs012013.[ORIGINAL BALANCE]) Now I receive the error: Msg 8134, Level 16, State 1, Line 2 Divide by zero error encountered.

Collectives™ on Stack Overflow

Arithmetic overflow error converting expression to data type float

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related