Appendix 97

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)