# SQL Server Rule-of-78 rebate calculation

R78REBATE

Updated: 31 October 2010

Use R78REBATE to calculate the rebate amount for a loan or lease using the Rule of 78.
Syntax
SELECT [westclintech].[wct].[R78REBATE] (
<@IntAmt, float,>
,<@NumPmts, int,>
,<@PeriodNo, int,>)
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.
@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 reabte amount. Rule of 78 rebates 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
Â·         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.
Example
Calculate the rebate 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.R78REBATE(
wct.TOTALINT(
36               --Number of Periods
,727.37           --Payment Amount
,24000            --Loan Amount
,0                --Future Value
)           --Total Interest
,36               --Number of Payments
,16               --Payoff Period Number
) as R78REBATE
This produces the following result.
R78REBATE
----------------------
757.971351351351

(1 row(s) affected)