The table below shows an illustration of how your monthly instalment will be for the various loan amount and tenure:

Loan Amount = $5000 | Tenure | |||

24 months | 18 months | 12 months | 6 months | |

Payment per month | $227 | $297 | $438 | $858 |

Total payment at end of tenure | $5,448 | $5,346 | $5,256 | $5,148 |

Loan Amount = $10000 | Tenure | |||

24 months | 18 months | 12 months | 6 months | |

Payment per month | $454 | $594 | $875 | $1,717 |

Total payment at end of tenure | $10,896 | $10,692 | $10,500 | $10,302 |

Now the question is what is the actual interest rate that you are incurring? In the first part of this article I am going to guide you on how to make use of excel RATE function. We shall take an example for a loan amount of $10000 and 24 months tenure of payment.**Step 1:** Open a new excel worksheet and click on any cell. Then click fx -> select Financial in category box -> select RATE -> click OK

If you can't find XIRR function, do the following steps:

- On the Tools menu, click Add-Ins
- In the Add-Ins available list, select the Analysis ToolPak box, and then click OK
- If necessary, follow the instructions in the setup program

**Step 2:** When a function arguments window pops up, type the following values:

Nper = 24 -> since it is a 24-month instalment and you are paying monthly, this is the number of payment periods

Pmt = -454 -> monthly payment in negative because it is cash outflow

Pv = 10000 -> this is the loan amount taken

Fv = 0 -> you need to pay until the balance is 0

Type = 0 -> assuming payment is payable at the end of every month

Guess = 0.1 -> assuming rate is at 10%

**Step 3:** Computed result is shown as 0.00698 (you need to change format of cell) or 0.698% per period. In this example a period is one month. Therefore the actual or effective interest rate turns out to be 8.376% per annum.

As you can see from this example, a one time processing fee of 9% turns out to be at a rate of 8.376% per annum. No wonder the banks choose the former method to illustrate loan to consumers in order to make it more attractive.

I have computed a summary of interest rates for the rest of the loan amount and tenure in the table below. It is interesting to see that a shorter tenure actually resulted in a higher effective interest rate.

Loan Amount = $5000 | Tenure | |||

24 months | 18 months | 12 months | 6 months | |

Rate per month | 0.698% | 0.714% | 0.777% | 0.840% |

Rate per annum | 8.378% | 8.568% | 9.320% | 10.078% |

Loan Amount = $10000 | Tenure | |||

24 months | 18 months | 12 months | 6 months | |

Rate per month | 0.698% | 0.714% | 0.759% | 0.857% |

Rate per annum | 8.378% | 8.568% | 9.105% | 10.281% |

So I hope after reading this article, you will be better informed when dealing with loans in future. Most importantly you need to also calculate the actual or effective interest rate per annum and compare among the various options before deciding. So it is not fair to decide based on the total amount of payment at end of tenure alone.

## 2 comments:

A loan of 10k for a tenure of 24 months and payment of $10896 at end of tenure translates to 8.378% per annum? Hmm, if 8.378% p.a. then the total interest paid should be 10k*8.378%*2=$1,675.60 rite? But we are paying $10896 instead of $11675.60. Is there anything wrong with your calculation?

no ku you cant compute it that way. remember the interest is on the balance of the reducing principal amount. for example 1st month is 8.378%/12 x 10k = A, then 2nd month is 8.378%/12 x (10k-A).

Post a Comment