Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server amortization schedule for MBS-type loans


CDRCashflow

Updated: 28 Dec 2016

Use the table-valued function CDRCASHFLOW to return a cash-flow schedule for a loan with a fixed periodic payment with Conditional Prepayment Rates (CPR) and Constant Default Rates (CDR) applied.
Syntax
SELECT * FROM [wct].[CDRCashflow](
  <@PrinAmt, float,>
 ,<@InterestRate, float,>
 ,<@NumPmts, int,>
 ,<@LastPmtNum, int,>
 ,<@PmtPerYr, int,>
 ,<@LSRatesQuery, nvarchar(max),>
 ,<@CPRRatesQuery, nvarchar(max),>
 ,<@CDRRatesQuery, nvarchar(max),>
 ,<@InterestOnly, bit,>
 ,<@PrinPaymentMultiple, int,>
 ,<@FirstPrinPayNo, int,>
 ,<@PmtPayPct, float,>)
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 periodic payment. @InterestRate is an expression of type float or of a type that can be implicitly converted to float.
@NumPmts
the number of periods to be used in the calculation of the periodic payment. @NumPmts is an expression of type int or of a type that can be implicitly converted to int.
@LastPmtNum
the number of the last payment. Use @LastPmtNum for case where the number of payments for the annuity calculation is different than the actual number of payments, For example, an annuity based on 300 monthly payment which will be paid off at the end of 120 months. @LastPmtNum is an expression of type int or of a type that can be implicitly converted to int.
@PmtPerYr
the number of payments per year. @PmtPerYr is an expression of type int or of a type that can be implicitly converted to int.
@LSRatesQuery
a SELECT statement, as a string, which returns the month and loss severity rates to be used in the calculation of the loss severity amounts. @LSRatesQuery should return 2 columns, month and rate, where 1% = .01.
@CPRRatesQuery
a SELECT statement, as a string, which returns the month and prepayment rates to be used in the calculation of the principal prepayments. @CPRratesQuery should return 2 columns, month and rate, where 1% = .01.
@CDRRatesQuery
a SELECT statement, as a string, which returns the month and default rate to be used in the calculation of the default amounts. @CDRRatesQuery should return 2 columns, month and rate, where 1% = .01.
@InterestOnly
a bit value, which when true, identifies that the principal amount is scheduled to be repaid at the end of the loan.
@PrinPaymentMultiple
the ratio of the frequency of the interest payments to the frequency of the interest payments. For example, a loan with monthly payments of interest and quarterly payments of principal would have a @PrinPaymentMultiple of 3. @PrinPaymentMultiple is an expression of type int or of a type that can be implicitly converted to int.
@FirstPrinPayNo
the payment number of the first principal payment. @FirstPrinPayNo is an expression of type int or of a type that can be implicitly converted to int.
@PmtPayPct
a fixed percentage which is applied to the projected principal balance to calculate the projected principal payment. @PmtPayPct is of a type float or of a type that can be implicitly converted to float.
Return Type
RETURNS TABLE (
       [num_pmt] [int] NULL,
       [CPR] [float] NULL,
       [CDR] [float] NULL,
       [LS] [float] NULL,
       [cont_prin_begin] [float] NULL,
       [cont_pmt] [float] NULL,
       [cont_int_pay] [float] NULL,
       [cont_prin_pay] [float] NULL,
       [cont_prin_end] [float] NULL,
       [proj_prin_begin] [float] NULL,
       [proj_pmt] [float] NULL,
       [proj_int_pay] [float] NULL,
       [proj_prin_pay] [float] NULL,
       [prin_prepay] [float] NULL,
       [amt_default] [float] NULL,
       [amt_loss_severity] [float] NULL,
       [proj_prin_end] [float] NULL,
       [proj_amt_cashflow] [float] NULL
)

Column
Description
num_pmt
payment number
CPR
the Conditional Prepayment Rate
CDR
the Constant Default Rate
LS
the Loss Severity Rate
cont_prin_begin
When num_pmt = 1 then @PrinAmt else cont_prin_end from the previous row
cont_pmt
the contractual periodic payment.
cont_int_pay
the contractual interest payment; @InterestRate / @PmtPerYr * cont_prin_begin
cont_prin_pay
the contractual principal payment.
proj_prin_begin
When num_pmt = 1 then @PrinAmt else proj_prin_end from the previous row
proj_pmt
the projected periodic payment. The proj_pmt is recalculated every period to reflect the adjustment to the principal balance arising from principal prepayments and defaults: PMT(@InterestRate / @PmtPerYear, @NumPmts - num_pmt + 1, -proj_prin_begin + amt_default, 0, 0)
proj_int_pay
the projected interest payment; @InterestRate / @PmtPerYr * (proj_prin_begin - amt_default)
proj_prin_pay
the projected principal payment.
prin_prepay
(1- POWER(1-CPR,1 / @PmtPerYr) * (proj_prin_beginproj_prin_pay)
amt_default
proj_prin_begin * (1 - POWER(1 - CDR, 1 / @PmtPerYr))
amt_loss_severity
amt_default * @LossSeverity
proj_prin_end
the projected principal ending balance; proj_prin_beginproj_prin_pay - prin_prepay - amt_default
proj_amt_cashflow
the projected cash flow amount; proj_pmt + prin_prepay + amt_default - amt_loss_severity

Remarks
·         If @FirstPrinPayNo is NULL then @FirstPrinPayNo = 1.
·         If @PrinAmt is NULL then @PrinAmt = 0.
·         If @InterestRate is NULL then @InterestRate = 0.
·         If @NumPmts is NULL then @NumPmts = 0.
·         If @LastPmtNum is NULL then @LastPmtNum = @NumPmts.
·         If @InterestOnly is NULL then @InterestOnly = FALSE.
·         If @PrinPaymentMultiple is NULL then @PrinPaymentMultiple = 1.
·         If @FirstPrinPayNo is NULL then @FirstPrinPayNo = @PrinPaymentMultiple.
·         If @NumPmts < 1 then no rows are returned.
·         If @PrinPaymentMultiple < 1 then no rows are returned.
·         If @FirstPrinPayNo < 1 then no rows are returned.
·         @PmtPerYr must be 1, 2, 3, 4, 6, or 12.
·         If @LSRatesQuery returns NULL or no rows then LS is set to zero.
·         If @CDRRatesQuery returns NULL or no rows then CDR is set to zero.
·         If @CPRRatesQuery returns NULL or no rows then CPR is set to zero.
·         Available in XLeratorDB / financial 2008 only
Examples
Use the following CPR rates, which have been inserted into the #cpr table, for all of the examples.
SELECT * INTO #cpr FROM (VALUES
(1,0.002),(25,0.012),(61,0.02),(181,0.06))n(mth,rate)


Use the following CDR rates, which have been inserted into the #cdr table, for all of the examples.
SELECT * INTO #cdr FROM (VALUES
(1,0.1),(61,0.08),(181,0.05))n(mth,rate)


Use the following Loss Severity rates, which have been inserted into the #ls table, for all the examples.
SELECT * INTO #ls FROM (VALUES
(1,0.15),(91,0.16),(181,0.14),(271,0.17),(361,0.13))n(mth,rate)


Example #1
In this example we amortize a 100,000,000 loan with a 5% interest rate over 360 months using the data from the #ls, #cpr, and #cdr tables without modification.
SELECT
   *
FROM wct.CDRCashflow(
  100000000  --@PrinAmt
 ,0.05       --@InterestRate
 ,360        --@NumPmts
 ,NULL       --@LastPmtNum
 ,12         --@PmtPerYr
 ,'SELECT * FROM #ls'     --@LSRatesQuery
 ,'SELECT * FROM #cpr'    --@CPRRatesQuery
 ,'SELECT * FROM #cdr'    --@CDRRatesQuery
 ,NULL       --@InterestOnly
 ,NULL       --@PrinPaymentMultiple
 ,NULL       --@FirstPrinPayNo
 ,NULL       --@PmtPayPct
 )

This produces the following result.


Example #2
In this example, we have a 10-year, 20,000,000 loan, with monthly payments calculated as though it were a 25-year loan.
SELECT
   *
FROM wct.CDRCashflow(
  20000000   --@PrinAmt
 ,0.05       --@InterestRate
 ,300        --@NumPmts
 ,120        --@LastPmtNum
 ,12         --@PmtPerYr
 ,'SELECT * FROM #ls'     --@LSRatesQuery
 ,'SELECT * FROM #cpr'    --@CPRRatesQuery
 ,'SELECT * FROM #cdr'    --@CDRRatesQuery
 ,NULL       --@InterestOnly
 ,NULL       --@PrinPaymentMultiple
 ,NULL       --@FirstPrinPayNo
 ,NULL       --@PmtPayPct
 )

This produces the following result.


Example #3
In this example we have a 10-year, 25,000,000 interest-only loan with quarterly payments
SELECT
   *
FROM wct.CDRCashflow(
  25000000   --@PrinAmt
 ,0.04       --@InterestRate
 ,40         --@NumPmts
 ,NULL       --@LastPmtNum
 ,4          --@PmtPerYr
 ,'SELECT * FROM #ls'     --@LSRatesQuery
 ,'SELECT * FROM #cpr'    --@CPRRatesQuery
 ,'SELECT * FROM #cdr'    --@CDRRatesQuery
 ,'True'     --@InterestOnly
 ,NULL       --@PrinPaymentMultiple
 ,NULL       --@FirstPrinPayNo
 ,NULL       --@PmtPayPct
 )

This produces the following result.


Example #4
In this example we have a 10,000,000 loan with monthly payments of interest and quarterly payments of principal. The loan matures in 120 months.
SELECT
   *
FROM wct.CDRCashflow(
  10000000   --@PrinAmt
 ,0.06       --@InterestRate
 ,120        --@NumPmts
 ,NULL       --@LastPmtNum
 ,12         --@PmtPerYr
 ,'SELECT * FROM #ls'     --@LSRatesQuery
 ,'SELECT * FROM #cpr'    --@CPRRatesQuery
 ,'SELECT * FROM #cdr'    --@CDRRatesQuery
 ,NULL       --@InterestOnly
 ,3          --@PrinPaymentMultiple
 ,NULL       --@FirstPrinPayNo
 ,NULL       --@PmtPayPct
 )

This produces the following result.

Example #5
This example uses the same loan as the previous loan, 1 month later.
SELECT
   *
FROM wct.CDRCashflow(
  10000000   --@PrinAmt
 ,0.06       --@InterestRate
 ,119        --@NumPmts
 ,NULL       --@LastPmtNum
 ,12         --@PmtPerYr
 ,'SELECT * FROM #ls'     --@LSRatesQuery
 ,'SELECT * FROM #cpr'    --@CPRRatesQuery
 ,'SELECT * FROM #cdr'    --@CDRRatesQuery
 ,NULL       --@InterestOnly
 ,3          --@PrinPaymentMultiple
 ,2          --@FirstPrinPayNo
 ,NULL       --@PmtPayPct
 )

This produces the following result.


Example #6
This is an interest-only loan.
SELECT
   *
FROM wct.CDRCashflow(
    50000    --@PrinAmt
   ,.05      --@InterestRate
   ,36       --@NumPmts
   ,NULL     --@LastPmtNum
   ,12       --@PmtPerYr
   ,'SELECT * FROM #ls'    --@LSRatesQuery
   ,'SELECT * FROM #cpr'   --@CPRRatesQuery
   ,'SELECT * FROM #cdr'   --@CDRRatesQuery
   ,'TRUE'          --@InterestOnly
   ,NULL     --@PrinPaymentMultiple
   ,NULL     --@FirstPrinPayNo
   ,NULL     --@PmtPayPct
 )

This produces the following result.


Example #7
In this loan the principal payment is a fixed percentage of the outstanding principal
SELECT
   *
FROM wct.CDRCashflow(
    1000000  --@PrinAmt
   ,.18      --@InterestRate
   ,24       --@NumPmts
   ,NULL     --@LastPmtNum
   ,12       --@PmtPerYr
   ,'SELECT * FROM #ls'    --@LSRatesQuery
   ,'SELECT * FROM #cpr'   --@CPRRatesQuery
   ,'SELECT * FROM #cdr'   --@CDRRatesQuery
   ,NULL     --@InterestOnly
   ,NULL     --@PrinPaymentMultiple
   ,NULL     --@FirstPrinPayNo
   ,0.0100   --@PmtPayPct
 )

This produces the following result.


Example #8
In this example, we have a thirty-year loan which is interest-only for the first seven years and then switches to be a fully amortizing loan.
SELECT
   *
FROM
   wct.CDRCashFlow(
        500000   --@PrinAmt
       ,.05      --@InterestRate
       ,360      --@NumPmts
       ,NULL     --@LastPmtNum
       ,12       --@PmtPerYr
       ,'SELECT * FROM #ls'       --@LSRatesQuery
       ,'SELECT * FROM #cpr'      --@CPRRatesQuery
       ,'SELECT * FROM #cdr'      --@CDRRatesQuery
       ,'False'  --@InterestOnly
       ,NULL     --@PrinPaymentMultiple
       ,85       --@FirstPrinPayNo
       ,NULL     --@PmtPayPct
       )

This produces the following result.