Question

Loan Amortization Schedule, $80,000,000 at 8% for 15 years
Year	Beginning Amount	Payment	Interest	Payment of Principal	Ending Balance
1	$     80,000,000.00	$         1,933.28	$            533,333.33	$                (531,400.05)	$             80,531,400.05
2	$     80,531,400.05	$         1,933.28	$            536,876.00	$                (534,942.72)	$             81,066,342.77
3	$     81,066,342.77	$         1,933.28	$            540,442.29	$                (538,509.01)	$             81,604,851.78
4	$     81,604,851.78	$         1,933.28	$            544,032.35	$                (542,099.07)	$             82,146,950.84

179	$ 260,404,969.06	$         1,933.28	$        1,736,033.13	$           (1,734,099.85)	$          262,139,068.91
180	$ 262,139,068.91	$         1,933.28	$        1,747,593.79	$           (1,745,660.51)	$          263,884,729.42
		$   347,990.40	$ 184,232,719.82	$     (183,884,729.42)	$ 27,818,792,702.38

As per the above amortization schedule.

a. Discuss or list what can be done to save on interest charges for this loan.

B. What is the effective interest rate? Show your calculation.

Answer 1

The amortization table is essentially a data table that shows how the loan is getting paid off, what part of a installment goes to principal and what part goes towards interest payment.

We have used the excel sheet to come up with amortization schedule. The inputs used for the calculation is summarized as follows:

Interest Yearly (A)	8%
Interest Per Period (A/B)	0.67%
Tenure in Years ©	15
Installments Per Year (B)	12
Total No of Periods (B*C)	180
Amount (in $)	8,00,00,000.00
Future Value of Loan	0
Payment Type	End of Period

The payment made during each installment is computed using the PMT function of Excel.

PMT(Interest Per Period,Total Number of Periods,Amount,Future Value,End of Period)

PPMT is used to compute Principal Component and IPMT is used to compute Interest Component in a payment.

PPMT(Interest Per Period,Payment Number,Total Number of Periods,Amount,Future Value,End of Period)

IPMT(Interest Per Period,Payment Number,Total Number of Periods,Amount,Future Value,End of Period)

Ending Balance = Beginning Amount - Payment of Principal

Cumulative Interest = Sum of all interest paid.

The calculation of loan amortization schedule has been summarized in the below table:

Payment Number	Beginning Amount	Payment Made	Payment of Principal	Interest Component	Ending Balance	Cumulative Interest	Total Payment
									% Principal	% Interest
1	8,00,00,000.00	7,64,521.67	2,31,188.33	5,33,333.33	7,97,68,811.67	5,33,333.33	30.24%	69.76%
2	7,97,68,811.67	7,64,521.67	2,32,729.59	5,31,792.08	7,95,36,082.08	10,65,125.41	30.44%	69.56%
3	7,95,36,082.08	7,64,521.67	2,34,281.12	5,30,240.55	7,93,01,800.96	15,95,365.96	30.64%	69.36%
4	7,93,01,800.96	7,64,521.67	2,35,842.99	5,28,678.67	7,90,65,957.96	21,24,044.63	30.85%	69.15%
5	7,90,65,957.96	7,64,521.67	2,37,415.28	5,27,106.39	7,88,28,542.68	26,51,151.02	31.05%	68.95%
6	7,88,28,542.68	7,64,521.67	2,38,998.05	5,25,523.62	7,85,89,544.63	31,76,674.64	31.26%	68.74%
7	7,85,89,544.63	7,64,521.67	2,40,591.37	5,23,930.30	7,83,48,953.26	37,00,604.93	31.47%	68.53%
8	7,83,48,953.26	7,64,521.67	2,42,195.31	5,22,326.36	7,81,06,757.95	42,22,931.29	31.68%	68.32%
9	7,81,06,757.95	7,64,521.67	2,43,809.95	5,20,711.72	7,78,62,948.00	47,43,643.01	31.89%	68.11%
10	7,78,62,948.00	7,64,521.67	2,45,435.35	5,19,086.32	7,76,17,512.65	52,62,729.33	32.10%	67.90%
40	6,97,41,773.64	7,64,521.67	2,99,576.51	4,64,945.16	6,94,42,197.13	2,00,23,063.83	39.18%	60.82%
41	6,94,42,197.13	7,64,521.67	3,01,573.69	4,62,947.98	6,91,40,623.44	2,04,86,011.81	39.45%	60.55%
42	6,91,40,623.44	7,64,521.67	3,03,584.18	4,60,937.49	6,88,37,039.26	2,09,46,949.30	39.71%	60.29%
100	4,77,29,833.49	7,64,521.67	4,46,322.78	3,18,198.89	4,72,83,510.71	4,37,35,677.45	58.38%	41.62%
101	4,72,83,510.71	7,64,521.67	4,49,298.26	3,15,223.40	4,68,34,212.45	4,40,50,900.86	58.77%	41.23%
102	4,68,34,212.45	7,64,521.67	4,52,293.58	3,12,228.08	4,63,81,918.86	4,43,63,128.94	59.16%	40.84%
150	2,13,47,507.37	7,64,521.67	6,22,204.95	1,42,316.72	2,07,25,302.42	5,54,03,552.54	81.38%	18.62%
151	2,07,25,302.42	7,64,521.67	6,26,352.98	1,38,168.68	2,00,98,949.44	5,55,41,721.23	81.93%	18.07%
152	2,00,98,949.44	7,64,521.67	6,30,528.67	1,33,993.00	1,94,68,420.77	5,56,75,714.22	82.47%	17.53%
176	37,47,329.76	7,64,521.67	7,39,539.47	24,982.20	30,07,790.29	5,75,63,603.76	96.73%	3.27%
177	30,07,790.29	7,64,521.67	7,44,469.73	20,051.94	22,63,320.56	5,75,83,655.70	97.38%	2.62%
178	22,63,320.56	7,64,521.67	7,49,432.86	15,088.80	15,13,887.69	5,75,98,744.50	98.03%	1.97%
179	15,13,887.69	7,64,521.67	7,54,429.08	10,092.58	7,59,458.61	5,76,08,837.09	98.68%	1.32%
180	7,59,458.61	7,64,521.67	7,59,458.61	5,063.06	0.00	5,76,13,900.14	99.34%	0.66%

A. Steps to Reduce Interest Cost:

The following can be used to reduce the interest cost:

• From the table its clear that large portion of installment made during the initial days is predominantly towards interest. To reduce interest cost one can make balloon payments or additional payments to reduce the principal component during the start of the loan.  
• One effective way to reduce the overall interest paid is to reduce the tenure of the loan. However this option will lead to bigger installments. An example for 3 different tenure is shown in the following table:

Tenure of Loan                     (in Years)	Payment per Period	Cumulative interest Paid
15	7,64,521.67	5,76,13,900.14
10	9,70,620.75	3,64,74,490.58
5	16,22,111.54	1,73,26,692.58

• One can divert savings / investment which is yielding less than 8% (cost of loan) towards payment of loans over and above stated installment in initial days.
• Also one can look at loans with variable interest rate or those that will charge based on the principal outstanding which will have a varied installments to be made.

B. Effective Interest Rate:

Effective interest rate is the interest rate actually paid on a loan. The effective interest rate takes into account the effect of compounding.

Effective interest Rate =

where,

i - Interest Rate per Year

n - Number of payments per year

Thus Effective Interest Rate = [(1 + (8%/12) ^12 ] - 1 = 8.30%