SMMAMORT
Updated: 31 December 2015
Use the table-valued function SMMAMORT to return an amortization schedule for a loan with a fixed monthly payment and principal prepayments based on CPR or SMM.
Syntax
SELECT * FROM [wct].[SMMAMORT](
<@PrinAmt, float,>
,<@InterestRate, float,>
,<@NumPmts, int,>
,<@CPR, bit,>
,<@ratesQuery, nvarchar(max),>)
Arguments
@PrinAmt
the principal amount to be amortized. @PrinAmt is an expression of type float or of a type that can be implicitly converted to float.
@InterestRate
the annual rate of interest used to calculate the monthly payment. @InterestRate is an expression of type float or of a type that can be implicitly converted to float.
@NumPmts
the number of months to be used in the calculation of the monthly payment. @NumPmts is an expression of type int or of a type that can be implicitly converted to int.
@CPR
a bit value identify the rates in @ratesQuery as being either CPR rates or SMM rates.
@ratesQuery
a SELECT statement, as a string, which returns the payment number and prepayment rates to be used in the calculation of the principal prepayments. @ratesQuery should return 2 columns, payment number and rate, where 1% = .01.
Return Type
RETURNS TABLE (
[num_pmt] [int] NULL,
[SMM] [float] NULL,
[prin_begin] [float] NULL,
[pmt] [float] NULL,
[int_pay] [float] NULL,
[prin_pay] [float] NULL,
[prin_prepay] [float] NULL,
[prin_end] [float] NULL
)
|
Column
|
Description
|
|
num_pmt
|
payment number
|
|
SMM
|
the SMM (Single Monthly Mortality) rate
|
|
prin_begin
|
When num_pmt = 1 then @PrinAmt else prin_end from the previous row
|
|
pmt
|
periodic monthly payment
|
|
int_pay
|
@InterestRate / 12 * prin_begin
|
|
prin_pay
|
pmt – int_pay
|
|
prin_prepay
|
SMM * (prin_begin – prin_pay)
|
|
prin_end
|
prin_begin – prin_pay – prin_prepay
|
Remarks
· If @NumPmts < 1 then no rows are returned.
Examples
Use the following CPR rates, which have been inserted into the #c table, for all the examples.
SELECT
*
INTO
#c
FROM (VALUES
(1,0.0020)
,(2,0.0040)
,(3,0.0060)
,(4,0.0080)
,(5,0.0100)
,(6,0.0120)
,(7,0.0140)
,(8,0.0160)
,(9,0.0180)
,(10,0.0200)
,(11,0.0220)
,(12,0.0240)
,(13,0.0260)
,(14,0.0280)
,(15,0.0300)
,(16,0.0320)
,(17,0.0340)
,(18,0.0360)
,(19,0.0380)
,(20,0.0400)
,(21,0.0420)
,(22,0.0440)
,(23,0.0460)
,(24,0.0480)
,(25,0.0500)
,(26,0.0520)
,(27,0.0540)
,(28,0.0560)
,(29,0.0580)
,(30,0.0600)
)n(p,r)
In this example we amortize a 10,000,000 loan with a 6% interest rate over 360 month using the data from the #c table without modification.
SELECT *
FROM wct.SMMAMORT(
10000000, --@PrinAmt
.06, --@InterestRate
360, --@NumPmts
'True', --@CPR
'SELECT
p,
r
FROM #c' --@rateQuery
)
This produces the following result. Note that even though we specified 360 payments, because of the impact of the principal prepayments, only 178 rows are returned.
In this example, using the same loan information, we adjust the rates by 1.5
SELECT *
FROM wct.SMMAMORT(10000000,.06,360,'True','SELECT p,r*1.5 FROM #c')
This produces the following result.
In this example, the prepayments aren't applied until the 11th month.
SELECT *
FROM wct.SMMAMORT(10000000,.06,360,'True','SELECT p+10,r*1.5 FROM #c')
This produces the following result.
In this example the prepayment factors are used starting with the 6th factor on the first payment.
SELECT *
FROM wct.SMMAMORT(10000000,.06,360,'True','SELECT ROW_NUMBER() OVER (ORDER BY p),r*1.5 FROM #c WHERE p > 5')
This produces the following result.
In this example, the SEMM are passed into the function rather than having the function calculate them.
SELECT *
FROM wct.SMMAMORT(10000000,.06,360,'False','SELECT p,1-POWER(cast(1-r as float),1/12e+00) FROM #c')
This produces the following result which is the same as the first example.