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.
致力于分析债券。我试图创建一个复制excel的PMT功能的支付功能。对于债券,“Cusip”是他们的标识符,他们的“PASS THRU RATE”是他们的年利率,“ORIGINAL WA MATURITY”是期间的总数,而“ORIGINAL BALANCE”是原始的面值。键。
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!
当我试图执行它。我无法弄清楚造成这种情况的原因。任何帮助都会很棒!
1 个解决方案
#1
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作为FLOAT,并且您要分配@principal的值,即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?
这似乎是最可能的罪魁祸首。我们需要能够看到您的数据以提供帮助。另外,我不清楚为什么你以一种方式创建函数,然后删除它并以不同方式重新创建它。或者你只是展示两种不同的尝试?
#1
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作为FLOAT,并且您要分配@principal的值,即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?
这似乎是最可能的罪魁祸首。我们需要能够看到您的数据以提供帮助。另外,我不清楚为什么你以一种方式创建函数,然后删除它并以不同方式重新创建它。或者你只是展示两种不同的尝试?