Financial
Rule of 78s interest allocation: rebate and straight-line comparison
Illustrate sum-of-digits allocation for a precomputed finance charge. Compare with straight-line allocation, not an actuarial payoff calculation.
An arithmetic example of front-loaded allocation
For N equal monthly periods, allocate the precomputed finance charge using descending weights N through 1. After m completed periods, k=N-m remain and the unallocated fraction is k(k+1)/(N(N+1)). This example assumes settlement exactly on a period boundary, without late fees or partial periods.
For a 1560 charge over 12 periods, after 4 periods the formula gives 720 unallocated and 840 allocated. Straight-line allocation would allocate 520 by that point; the 320 difference is not automatically a contractual prepayment fee. This is not the actuarial method and does not calculate the total payoff balance. Actual entitlement and permitted methods depend on the contract and applicable law.
| Loan Audit Input / Output | Cell / Formula | 12-Month Precomputed Loan | Audit Impact |
|---|---|---|---|
| Total Precomputed Interest | B2 | $1,560 | $20 per sum-of-digits unit (1560/78) |
| Original Term N & Paid Months m | B3 = 12, B4 = 4 | Remaining k = 12 - 4 = 8 | k*(k+1)/2 = 36 vs N*(N+1)/2 = 78 |
| Rule of 78s Interest Rebate | B2 * (k*(k+1)) / (N*(N+1)) | $720.00 refunded | 46.15% refunded with 66.7% time left! |
| Lender Earned vs Pro-Rata | ($1,560 - $720) vs $520 | $840 earned vs $520 linear | +320 allocated compared with straight-line |
Rule of 78s unearned interest rebate formula
Require a positive integer term and an integer elapsed-period count between zero and the term. Reject dates past maturity rather than silently clamping one formula while allowing the comparison to become negative.
=IF(COUNT(B2:B4)<>3,"Invalid inputs",IF(OR(B2<0,B3<=0,B3<>INT(B3),B4<0,B4>B3,B4<>INT(B4)),"Invalid inputs",LET(k, MAX(0, B3 - B4), ROUND(B2 * (k * (k + 1)) / (B3 * (B3 + 1)), 2))))Difference in allocated interest versus straight-line allocation
Compares Rule-of-78s earned interest (B2 - Rebate) against straight-line (B2 * B4 / B3).
=IF(COUNT(B2:B4)<>3,"Invalid inputs",IF(OR(B2<0,B3<=0,B3<>INT(B3),B4<0,B4>B3,B4<>INT(B4)),"Invalid inputs",LET(k, MAX(0, B3 - B4), reb, B2 * (k * (k + 1)) / (B3 * (B3 + 1)), ROUND((B2 - reb) - (B2 * B4 / B3), 2))))Try the example
Use the original values or change them to test your understanding.