CDRRate
Updated 2023-10-10 15:15:32.050000
Syntax
SELECT [westclintech].[wct].[CDRRate] (
<@PrinAmt, float,>
,<@InterestRate, float,>
,<@NumPmts, int,>
,<@LastPmtNum, int,>
,<@PmtPerYr, int,>
,<@CPRRatesQuery, nvarchar(max),>
,<@DefAmt, float,>
,<@InterestOnly, bit,>
,<@PrinPaymentMultiple, int,>
,<@FirstPrinPayNo, int,>
,<@PmtPayPct, float,>)
Description
Use the scalar function CDRRate to calculate a CDR Rate given a default amount, to be used in the CDRCashflow table-valued function.
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.
@InterestOnly
a bit value, which when true, identifies that the principal amount is scheduled to be repaid at the end of the loan.
@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.
@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.
@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.
@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.
@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.
@DefAmt
The anticipated default amount for the cash flows. @DefAmt must be of a type float or of a type that can be implicitly converted to float.
@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.
Return Type
float
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 @CPRRatesQuery returns NULL or no rows then CPR is set to zero.
If SIGN(@PrinAmt) <> SIGN(@DefAmt) then NULL is returned,
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);
Example #1 In this example we calculate the CDR rate for a 1,000,000 loan with a 5% interest rate over 120 months using the data from the #cpr table with an a default amount of 5,500.00.
SELECT wct.CDRRate( 1000000, --@PrinAmt
0.05, --@InterestRate
120, --@NumPmts
NULL, --@LastPmtNum
12, --@PmtPerYr
'SELECT * FROM #cpr', --@CPRRatesQuery
5500.00, --@DefAmt
NULL, --@InterestOnly
NULL, --@PrinPaymentMultiple
NULL, --@FirstPrinPayNo
NULL --@PmtPayPct
) as [CDR Rate];
This produces the following result.
{"columns":[{"field":"CDR Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CDR Rate":"0.00104013790645852"}]}
Using this rate in the CDRCashFlow function will calculate periodic default amounts which sum to the default amount, as demonstrated in the following SQL.
SELECT SUM(amt_default) as [Default Amount]
FROM wct.CDRCashFlow( 1000000, --@PrinAmt
0.05, --@InterestRate
120, --@NumPmts
NULL, --@LastPmtNum
12, --@PmtPerYr
NULL, --@LSRatesQuery
'SELECT * FROM #cpr', --@CPRRatesQuery
'SELECT 1, 0.00104013790645852', --@CDRRatesQuery
NULL, --@InterestOnly
NULL, --@PrinPaymentMultiple
NULL, --@FirstPrinPayNo
NULL --@PmtPayPct
);
This produces the following amount.
{"columns":[{"field":"Default Amount","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Default Amount":"5500.0000075183"}]}
Example #2 In this example, we have a 10-year, 2,000,000 loan, with monthly payments calculated as though it were a 25-year loan. The default amount is 16,000.00.
SELECT wct.CDRRate( 2000000, --@PrinAmt
0.05, --@InterestRate
300, --@NumPmts
120, --@LastPmtNum
12, --@PmtPerYr
'SELECT * FROM #cpr', --@CPRRatesQuery
16000, --@DefAmt
NULL, --@InterestOnly
NULL, --@PrinPaymentMultiple
NULL, --@FirstPrinPayNo
NULL --@PmtPayPct
) as [CDR Rate];
This produces the following result.
{"columns":[{"field":"CDR Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CDR Rate":"0.000955127703401892"}]}
Example #3 In this example we have a 10-year, 2,500,000 interest-only loan with quarterly payments. The default amount is 25,000.
SELECT wct.CDRRate( 250000, --@PrinAmt
0.04, --@InterestRate
40, --@NumPmts
NULL, --@LastPmtNum
4, --@PmtPerYr
'SELECT * FROM #cpr', --@CPRRatesQuery
25000, --@DefAmt
'True', --@InterestOnly
NULL, --@PrinPaymentMultiple
NULL, --@FirstPrinPayNo
NULL --@PmtPayPct
) as [CDR Rate];
This produces the following result.
{"columns":[{"field":"CDR Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CDR Rate":"0.0110402404740884"}]}
Example #4 In this example we have a 1,000,000 loan with monthly payments of interest and quarterly payments of principal. The loan matures in 120 months. The default amount is 17,000.
SELECT wct.CDRRate( 1000000, --@PrinAmt
0.06, --@InterestRate
120, --@NumPmts
NULL, --@LastPmtNum
12, --@PmtPerYr
'SELECT * FROM #cpr', --@CPRRatesQuery
17000, --@DefAmt
NULL, --@InterestOnly
3, --@PrinPaymentMultiple
NULL, --@FirstPrinPayNo
NULL --@PmtPayPct
) as [CDR Rate];
This produces the following result.
{"columns":[{"field":"CDR Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CDR Rate":"0.00333466087962908"}]}
Example #5 This example uses the same loan as the previous loan, 1 month later.
SELECT wct.CDRRate( 1000000, --@PrinAmt
0.06, --@InterestRate
119, --@NumPmts
NULL, --@LastPmtNum
12, --@PmtPerYr
'SELECT * FROM #cpr', --@CPRRatesQuery
16721.69, --@DefAmt
NULL, --@InterestOnly
3, --@PrinPaymentMultiple
NULL, --@FirstPrinPayNo
NULL --@PmtPayPct
) as [CDR Rate];
This produces the following result.
{"columns":[{"field":"CDR Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CDR Rate":"0.00328080851014179"}]}
Example #6 This is an interest-only loan.
SELECT wct.CDRRate( 50000, --@PrinAmt
.05, --@InterestRate
36, --@NumPmts
NULL, --@LastPmtNum
12, --@PmtPerYr
'SELECT * FROM #cpr', --@CPRRatesQuery
1100, --@DefAmt
'TRUE', --@InterestOnly
NULL, --@PrinPaymentMultiple
NULL, --@FirstPrinPayNo
NULL --@PmtPayPct
) as [CDR Rate];
This produces the following result.
{"columns":[{"field":"CDR Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CDR Rate":"0.00742080480901541"}]}
Example #7 In this loan the principal payment is a fixed percentage of the outstanding principal.
SELECT wct.CDRRate( 1000000, --@PrinAmt
.18, --@InterestRate
24, --@NumPmts
NULL, --@LastPmtNum
12, --@PmtPerYr
'SELECT * FROM #cpr', --@CPRRatesQuery
13000, --@DefAmt
NULL, --@InterestOnly
NULL, --@PrinPaymentMultiple
NULL, --@FirstPrinPayNo
0.0100 --@PmtPayPct
) as [CDR Rate];
This produces the following result.
{"columns":[{"field":"CDR Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CDR Rate":"0.00616578223371045"}]}
Example #8 In this example we have a 30-year loan with interest-only payments for the first 7 years and fully-amortizing payments thereafter.
SELECT wct.CDRRate( 500000, --@PrinAmt
.05, --@InterestRate
360, --@NumPmts
NULL, --@LastPmtNum
12, --@PmtPerYr
'SELECT * FROM #cpr', --@CPRRatesQuery
16000, --@DefAmt
'False', --@InterestOnly
NULL, --@PrinPaymentMultiple
85, --@FirstPrinPayNo
NULL --@PmtPayPct
) as [CDR Rate];
This produces the following result.
{"columns":[{"field":"CDR Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CDR Rate":"0.00194705884013524"}]}