EFFECTIVE INTEREST CALCULATION MODELS
(Appendix to Sec. 305 on Method of Computing Interest)
Illustration 1
EFFECTIVE INTEREST CALCULATION MODEL
FIXED EQUAL AMORTIZATION CASE
| A | B | C | D | E | F | G | |
| 1 | Loan Amount | 120,000.00 | |||||
| 2 | Monthly Installment | 11,001.60 | |||||
| 3 | Contractual Rate (Monthly) | 1.50% | |||||
| 4 | Other Charges | 3.00% | |||||
| 5 | No. of Monthly Installment | 12 | |||||
| 6 | |||||||
| 7 | Installment | Gross | Other | O/S | |||
| 8 | Period | Loan | Principal | Interest | Charges | Cash flows | Balance |
| 9 | 120,000.00 | 120,000.00 | |||||
| 10 | 0 | 3,600.00 | 116,400.00 | 120,000.00 | |||
| 11 | 1 | 9,201.60 | 1,800.00 | (11,001.60) | 110,798.40 | ||
| 12 | 2 | 9,339.62 | 1,661.98 | (11,001.60) | 101,458.78 | ||
| 13 | 3 | 9,479.72 | 1,521.88 | (11,001.60) | 91,979.06 | ||
| 14 | 4 | 9,621.91 | 1,379.69 | (11,001.60) | 82,357.15 | ||
| 15 | 5 | 9,766.24 | 1,235.36 | (11,001.60) | 72,590.91 | ||
| 16 | 6 | 9,912.74 | 1,088.86 | (11,001.60) | 62,678.17 | ||
| 17 | 7 | 10,061.43 | 940.17 | (11,001.60) | 52,616.74 | ||
| 18 | 8 | 10,212.35 | 789.25 | (11,001.60) | 42,404.39 | ||
| 19 | 9 | 10,365.53 | 636.07 | (11,001.60) | 32,038.86 | ||
| 20 | 10 | 10,521.02 | 480.58 | (11,001.60) | 21,517.85 | ||
| 21 | 11 | 10,678.83 | 322.77 | (11,001.60) | 10,839.01 | ||
| 22 | 12 | 10,839.01 | 162.59 | (11,001.60) | – | ||
| 23 | TOTAL | 120,000.00 | 12,019.20 | 3,600.00 | |||
| Monthly Installment | = | PMT (C3, C5, -C1)*-1 | 11,001.60 | |
| (using Excel PMT Function) | ||||
| Effective Annual Interest Rate (EIR) | = | (1+IRR (F10:F22))12-1 | 26.71% | |
| (using Excel IRR Function) | ||||
| Effective Monthly Interest Rate (MIR) | = | IRR (F10:F22) | 1.99% | |
| (using Excel IRR Function) |
Illustration 2
EFFECTIVE INTEREST CALCULATION MODEL
FIXED PRINCIPAL AMORTIZATION CASE
| A | B | C | D | E | F | G | |
| 1 | Loan Amount | 120,000.00 | |||||
| 2 | Monthly Installment | 11,001.60 | |||||
| 3 | Contractual Rate (Monthly) | 1.50% | |||||
| 4 | Other Charges | 3.00% | |||||
| 5 | No. of Monthly Installment | 12 | |||||
| 6 | |||||||
| 7 | Installment | Gross | Other | O/S | |||
| 8 | Period | Loan | Principal | Interest | Charges | Cash flows | Balance |
| 9 | 120,000.00 | 120,000.00 | |||||
| 10 | 0 | 3,600.00 | 116,400.00 | 120,000.00 | |||
| 11 | 1 | 10,000 | 1,800.00 | (11,800.00) | 110,000.00 | ||
| 12 | 2 | 10,000 | 1,661.98 | (11,650.00) | 100,000.00 | ||
| 13 | 3 | 10,000 | 1,500.00 | (11,500.00) | 90,000.00 | ||
| 14 | 4 | 10,000 | 1,350.00 | (11,350.00) | 80,000.00 | ||
| 15 | 5 | 10,000 | 1,200.00 | (11,200.00) | 70,000.00 | ||
| 16 | 6 | 10,000 | 1,050.00 | (11,050.00) | 60,000.00 | ||
| 17 | 7 | 10,000 | 900.00 | (10,900.00) | 50,000.00 | ||
| 18 | 8 | 10,000 | 750.00 | (10,750.00) | 40,000.00 | ||
| 19 | 9 | 10,000 | 600.00 | (10,600.00) | 30,000.00 | ||
| 20 | 10 | 10,000 | 450.00 | (10,450.00) | 20,000.00 | ||
| 21 | 11 | 10,000 | 300.00 | (10,300.00) | 10,000.00 | ||
| 22 | 12 | 10,000 | 150.00 | (10,150.00) | – | ||
| 23 | TOTAL | 120,000.00 | 11,700.00 | 3,600.00 | |||
| Effective Annual Interest Rate (EIR) | = | (1+IRR (F10:F22))12-1 | 26.91% | |
| (using Excel IRR Function) | ||||
| Effective Monthly Interest Rate (MIR) | = | IRR (F10:F22) | 2.01% | |
| (using Excel IRR Function) |
Illustration 3
EFFECTIVE INTEREST CALCULATION MODEL
FIXED EQUAL AMORTIZATION CASE WITH GRACE PERIOD
| A | B | C | D | E | F | G | |
| 1 | Loan Amount | 120,000.00 | |||||
| 2 | Monthly Installment | 11,001.60 | |||||
| 3 | Contractual Rate (Monthly) | 1.50% | |||||
| 4 | Other Charges | 3.00% | |||||
| 5 | No. of Monthly Installment | 12 | |||||
| 6 | (2 months grace period on principal and interest payments) | ||||||
| 7 | Installment | Gross | Other | O/S | |||
| 8 | Period | Loan | Principal | Interest | Charges | Cash flows | Balance |
| 9 | 120,000.00 | 120,000.00 | |||||
| 10 | 0 | 3,600.00 | 116,400.00 | 120,000.00 | |||
| 11 | 1 | – | 120,000.00 | ||||
| 12 | 2 | – | 120,000.00 | ||||
| 13 | 3 | 9,201.60 | 1,800.00 | (11,001.60) | 110,798.40 | ||
| 14 | 4 | 9,339.62 | 1,661.98 | (11,001.60) | 101,458.78 | ||
| 15 | 5 | 9,479.72 | 1,521.88 | (11,001.60) | 91,979.06 | ||
| 16 | 6 | 9,621.91 | 1,379.69 | (11,001.60) | 82,357.15 | ||
| 17 | 7 | 9,766.24 | 1,235.36 | (11,001.60) | 72,590.91 | ||
| 18 | 8 | 9,912.74 | 1,088.86 | (11,001.60) | 62,678.17 | ||
| 19 | 9 | 10,061.43 | 940.17 | (11,001.60) | 52,616.74 | ||
| 20 | 10 | 10,212.35 | 789.25 | (11,001.60) | 42,404.39 | ||
| 21 | 11 | 10,365.53 | 636.07 | (11,001.60) | 32,038.86 | ||
| 22 | 12 | 10,521.02 | 480.58 | (11,001.60) | 21,517.85 | ||
| 23 | 13 | 10,678.83 | 322.77 | (11,001.60) | 10,839.01 | ||
| 24 | 14 | 10,839.01 | 162.59 | (11,001.60) | (0.00) | ||
| 25 | TOTAL | 120,000.00 | 12,019.20 | 3,600.00 | |||
| Monthly Installment | = | PMT (C3, C5, -C1)*-1 | 11,001.60 | |
| (using Excel PMT Function) | ||||
| Effective Annual Interest Rate (EIR) | = | (1+IRR (F10:F24))12-1 | 19.68% | |
| (using Excel IRR Function) | ||||
| Effective Monthly Interest Rate (MIR) | = | IRR (F10:F24) | 1.51% | |
| (using Excel IRR Function) |
Illustration 4
EFFECTIVE INTEREST CALCULATION MODEL
CASE: PERIODIC INTEREST PAYMENT, BALLOON PAYMENT AT MATURITY
| A | B | C | D | E | F | G | |
| 1 | Loan Amount | 120,000.00 | |||||
| 2 | Monthly Installment | 1,800.00 (Interest Only) | |||||
| 3 | Contractual Rate (Monthly) | 1.50% | |||||
| 4 | Other Charges | 3.00% | |||||
| 5 | No. of Monthly Installment | 12 | |||||
| 6 | |||||||
| 7 | Installment | Gross | Other | O/S | |||
| 8 | Period | Loan | Principal | Interest | Charges | Cash flows | Balance |
| 9 | 120,000.00 | 120,000.00 | |||||
| 10 | 0 | 3,600.00 | 116,400.00 | 120,000.00 | |||
| 11 | 1 | 1,800.00 | (11,800.00) | 120,000.00 | |||
| 12 | 2 | 1,800.00 | (11,800.00) | 120,000.00 | |||
| 13 | 3 | 1,800.00 | (11,800.00) | 120,000.00 | |||
| 14 | 4 | 1,800.00 | (11,800.00) | 120,000.00 | |||
| 15 | 5 | 1,800.00 | (11,800.00) | 120,000.00 | |||
| 16 | 6 | 1,800.00 | (11,800.00) | 120,000.00 | |||
| 17 | 7 | 1,800.00 | (11,800.00) | 120,000.00 | |||
| 18 | 8 | 1,800.00 | (11,800.00) | 120,000.00 | |||
| 19 | 9 | 1,800.00 | (11,800.00) | 120,000.00 | |||
| 20 | 10 | 1,800.00 | (11,800.00) | 120,000.00 | |||
| 21 | 11 | 1,800.00 | (11,800.00) | 120,000.00 | |||
| 22 | 12 | 120,000.00 | 1,800.00 | (121,800.00) | – | ||
| 23 | TOTAL | 120,000.00 | 21,600.00 | 3,600.00 | |||
| Effective Annual Interest Rate (EIR) | = | (1+IRR (F10:F22))12-1 | = | 23.58% |
| (using Excel IRR Function) | ||||
| Effective Monthly Interest Rate (MIR) | = | IRR (F10:F22) | = | 1.78% |
| (using Excel IRR Function) |
Illustration 5
EFFECTIVE INTEREST CALCULATION MODEL
FIXED EQUAL AMORTIZATION CASE
(WEEKLY INSTALLMENTS QUOTED IN MONTHLY EFFECTIVE RATE)
| A | B | C | D | E | F | G | |
| 1 | Loan Amount | 10,000.00 | |||||
| 2 | Monthly Installment | 788.00 | |||||
| 3 | Contractual Rate (Monthly) | 1.50% | |||||
| 4 | Weekly Compounding Rate | 0.35% | |||||
| 5 | Other Charges | 3.00% | |||||
| 6 | Term (Weeks) | 13 | |||||
| 7 | Period/Year | 52 | |||||
| 8 | Installment | Gross | Other | O/S | |||
| 9 | Period | Loan | Principal | Interest | Charges | Cash flows | Balance |
| 10 | 10,000.00 | ||||||
| 11 | 0 | 300.00 | 9,700.00 | 10,000.00 | |||
| 12 | 1 | 753.38 | 34.62 | – | 9,246.62 | ||
| 13 | 2 | 755.99 | 32.01 | (788.00) | 8,490.63 | ||
| 14 | 3 | 758.61 | 29.39 | (788.00) | 7,732.02 | ||
| 15 | 4 | 761.23 | 26.76 | (788.00) | 6,970.78 | ||
| 16 | 5 | 763.87 | 24.13 | (788.00) | 6,206.91 | ||
| 17 | 6 | 766.51 | 21.49 | (788.00) | 5,440.40 | ||
| 18 | 7 | 769.17 | 18.83 | (788.00) | 4,671.24 | ||
| 19 | 8 | 771.83 | 16.17 | (788.00) | 3,899.41 | ||
| 20 | 9 | 774.50 | 13.50 | (788.00) | 3,124.91 | ||
| 21 | 10 | 777.18 | 10.82 | (788.00) | 2,347.72 | ||
| 22 | 11 | 779.87 | 8.13 | (788.00) | 1,567.85 | ||
| 23 | 12 | 782.57 | 5.43 | (788.00) | 785.28 | ||
| 24 | 13 | 785.28 | 2.72 | (788.00) | (0.00) | ||
| 25 | TOTAL | 10,000.00 | 244.00 | 300.00 | |||
| Weekly Installment | = | PMT (C4, C6, -C1)*-1 | ||
| (using Excel PMT Function) | ||||
| Effective Annual Interest Rate (EIR) | = | (1+IRR (F10:F24))52-1 | 50.46% | |
| (using Excel IRR Function) | ||||
| Effective Monthly Interest Rate (MIR) | = | (1+IRR (F11:F24)13/3-1 | 3.46% | |
| (using Excel IRR Function) |