Amortization in Excel: Do You Know Your Loan Well?

100,000 home loan with 7% p.a for 10 years servicing.

1) What is the monthly payment?
2) What total interest paid for 10 years loan?
3) What is the ratio of interest paid and principle over loan?
4) What is the total interest paid, total principle paid, total payment and loan balance by the end of 3rd year servicing?
5) How much I have to pay more if base lending rate (BLR) increase from 7% p.a. to 7.5% p.a.

In excel:
1) Monthly Payment = PMT(rate,nper,pv,fv,type)

= PMT(0.07/12,12*10,100000,0,0) = -1,161.08.

2) Total Interest Paid for 10 years loan = CUMIPMT(rate,nper,pv,start_period,end_period,type)
= CUMIPMT(0.07/12,12*10,100000,1,12*10,0) = -39,330.20.

3) Ratio = (interest paid + principle)/principle
= (39,330.20+100000)/100000 = 1.39.

4) Total Interest paid, end of 3rd year = CUMIPMT(rate,nper,pv,start_period,end_period,type)
= CUMIPMT(0.07/12,12*10,100000,1,12*3,0) = -18,729.38.

Total principle paid, end of 3rd year = CUMPRINC(rate,nper,pv,start_period,end_period,type)
= CUMPRINC(0.07/12,12*10,100000,1,12*3,0) = -23,069.67.

Total payment, end of 3rd year = 3*12*(-1,161.08) or (-18,729.38)+(-23,069.67) = -41,799.05.
Loan balance = FV(rate,nper,pmt,pv,type)
= FV(0.07/12,12*3,-1161.08,100000,0) =($76,930.33).

5) Total Extra Interest = CUMIPMT(0.075/12,12*10,100000,1,12*10,0) - CUMIPMT(0.07/12,12*10,100000,1,12*10,0)
= (-42,442.12) - (-39,330.20)
= -3111.92

Figure 1: Historical Malaysian BLR

Diverted original posting from my facebook note dated 21-Oct-09.