R78PAYOFF

Updated 2023-10-11 14:02:32.380000

Syntax

SELECT [westclintech].[wct].[R78PAYOFF] (
  <@IntAmt, float,>
 ,<@NumPmts, int,>
 ,<@PeriodNo, int,>
 ,<@Pmt, float,>)

Description

Use the scalar function R78PAYOFF to calculate the payoff amount for a loan or lease using the Rule-of-78.

Arguments

@IntAmt

the total interest over the life of the loan. You can use the TOTALINT function to obtain this value. @PV is an expression of type float or of a type that can be implicitly converted to float.

@Pmt

the payment amount. You can use the LPMT function to calculate @Pmt. @Pmt is an expression of type float or of a type that can be implicitly converted to float.

@NumPmts

the total number of payments to be recorded over the life of the loan. @NumPmts is an expression of type int or of a type that can be implicitly converted to int.

@PeriodNo

the period number for which you want calculate the payoff amount. Rule-of-78 payoffs can only be calculated on regular payment dates. You can use the NPNO function to calculate the next payment date. @PeriodNo is an expression of type int or of a type that can be implicitly converted to int.

Return Type

float

Remarks

@IntAmt must be greater than zero.

@NumPmts must be greater than 1.

@PeriodNo must be greater than 1 and less than @NumPmts.

@Pmt must be greater than zero.

The Rule-of-78 calculation is based on the number of payments, not the number of interest period. Thus odd long first periods do not affect the number of payments, though the first payment may include interest for multiple periods.

Examples

Calculate the payoff amount, using the Rule of 78, for a 24,000 loan that originated on 8/15/2009 at a rate of 5.9%, with payments on the first of every month for 36 months, commencing on 9/1/2009, paid off on 12/1/2010. The monthly payment is 727.37.

SELECT wct.R78PAYOFF(wct.TOTALINT(36, 727.37, 24000, 0), --Total Interest
                     36, --Number of Payments
                     16, --Payoff Period Number
                     727.37 --Monthly Payment
       ) as R78PAYOFF;

This produces the following result.

{"columns":[{"field":"R78PAYOFF","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"R78PAYOFF":"14585.7051351351"}]}

If you do not know the payment amount, you can use the LPMT function to calculate the amount.

SELECT wct.R78PAYOFF(m.TOTALINT, --total interest on the loan
                     36, --number of loan payment
                     16, --payoff payment number
                     m.PMT --payment amount
       )
  FROM (   SELECT wct.TOTALINT(36, --Number of periods
                               n.pmt, --Payment Amount
                               24000, --Loan Amount
                               0 --Future Value
                  ) as TOTALINT,
                  PMT
             FROM (select ROUND(wct.LPMT(24000, --Loan Amount
                                         '8/15/2009', --Loan Start Date
                                         .059, --Annual Interest Rate
                                         '9/1/2009', --First Payment Date
                                         36, --Number of payments (5*12)
                                         12, --Number of payments per year
                                         NULL, --Days in year (defaults to 360)
                                         NULL, --FV (defaults to 0)
                                         NULL --IntRule (defaults to 'A' meaning actuarial)
                                ),
                                2) as pmt) n ) m

This produces the following result.

{"columns":[{"field":"R78PAYOFF","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"R78PAYOFF":"14585.7051351351"}]}