CDRCashflowIRR

Updated 2023-10-10 15:11:25.397000

Syntax

SELECT [westclintech].[wct].[CDRCashflowIRR](
  <@PV, float,>
 ,<@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,>)

Description

Use the scalar function CDRCashFlowIRR to calculate the internal rate of return on cash flows produced using the CDRCASHFLOW inputs. CDRCashFlowIRR just returns the internal rate of return; if you want to explicitly produce the cash flows, use the CDRCASHFLOW table-valued function.

Arguments

@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.

@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.

@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.

@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.

@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.

@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.

@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.

@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.

@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.

@InterestOnly

a bit value, which when true, identifies that the principal amount is scheduled to be repaid at the end of the loan.

@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.

@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.

@PV

the present value of the cash flows. @PV is an expression of type float or of a type that can be implicitly converted to float.

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 @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.

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 calculate the internal rate of return for 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 wct.CDRCashflowIRR(   99000000,             --@PV

                             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

                         ) as IRR;