Amortization in Excel 2: Smart Loan Malaysia Comparison

Recap: please read Amortization in Excel: Do You Know Your Loan Well?

Mortgage Refinancing Parameter: completed, Selangor, RM100,000 loan, conventional and Islamic loan, floating interest, 10-year-tenure, no flexi loan. BLR=6.05%p.a.

Loan Comparison Platform: http://www.smartloans.my

Let's compare the cheapest and most expansive package.

Example 1: Hong Leong Flexi Package (BLR-1.9%) - Cheapest
1) Interest Rate = RATE(nper,pmt,pv,fv,type)

= 12*Rate(12*10,-1020,100000,0,0) = 4.16% p.a.

2) Total Interest Paid for 10 years loan = CUMIPMT(rate,nper,pv,start_period,end_period,type)
= CUMIPMT(0.0416/12,12*10,100000,1,12*10,0) = -22,408.80

3) Ratio = (interest paid + principle)/principle
= (22408.80+100000)/100000 = 1.22.

Example 2: Standard Chartered - Standard Term Loan - Most Expansive
How to calculate the Standard Chartered - Standard Term Loan?
Uneven cashflow is determined in the study via XIRR.
First 1 year: RM1,022, Next 4 years: RM1,025, thereafter RM1,205. Bank always confuses his customer, and it leads to difficult way to compare with others.





1) IRR = 5.99%p.a.

2) Total Interest Paid for 10 years loan = CUMIPMT(rate,nper,pv,star

t_period,end_period,type)
= CUMIPMT(0.0599/12,12*10,100000,1,12*10,0) = -33164.43.

3) Ratio = (interest paid + principle)/principle
= (33164.43+100000)/100000 = 1.33.

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.