In my last article, I explained the difference between effective and nominal interest rate. Basically the latter usually does not reflect the true cost of borrowing. Banks or financial institutions use different reference rates to communicate the cost of borrowing to customers. In Singapore banks can use the effective interest rate or the nominal rate. However, for licensed moneylenders, they are mandated by law - since 1 June 2012 - to show the effective interest rate. For more details, refer to the Ministry of Law web-page:“Amendments to the Moneylenders Rules to take effect from 1 June 2012”, or the Insolvency & Public Trustee’s Office: “What Borrowers Should Know About Effective Interest Rates”. Elsewhere in the world, banks may use the nominal rate per annum (p.a.) but calls it by a different term. For example, in Canada, Hong Kong, UK and US it is known as the Annual Percentage Rate (APR). In Australia, borrowing costs are reflected to borrowers in the Comparison Rate which takes into account the "hidden" costs like the upfront fees and charges on top of the interest rate and frequency of repayments. Returning to Singapore, given that it is not legislated that banks provide the effective rate on their loans; this article attempts to set forth the steps to calculate the effective rate for mortgages on different rests but with monthly instalments.

Equivalent Nominal Rate (ENR)

To make a fair comparison between housing loans on different rests basis but with the same number of instalment periods, we first have to find the Equivalent Nominal Rate (ENR) per annum. But before we do that, we need to know what is the present value factor (PVF) of an annuity. In mathematical notation it is defined as   Furthermore, we have Finding ENR through manual calculation is an extremely tedious process, so we need the aid of a spreadsheet or financial calculator. In the next section, I am going to make use of Excel.

Excel Function - RATE

In Excel and most other spreadsheet software, ENR can be obtained through the RATE function. RATE will return ENR is then m*RATE. RATE (NPer, PMT, PV, FV, Type, Guess): The nominal interest rate per period for an annuity Each of the argument in the parenthesis is defined as

• NPer (Required): Total number of compounding periods or payment periods

• PMT (Required): Total payment (principle and interest) payable for that compounding period

• PV (Required): Present value of the loan

• FV: Loan amount outstanding after all payments have been made. If this variable is omitted, Excel will assume the default value of 0.

• Type: The timing of the payment. It can be either 0 or 1. If this variable is omitted, Excel will assume the default value of 0.

   

• Guess: Your guess of what RATE is. If omitted, it is assumed to be 10%.

Using RATE

Of note is that RATE is independent of the loan amount and instalment in each period. In other words, RATE will be the same for loans with the same number of payment periods and nominal interest rate per annum. To illustrate Loan A

• Loan Quantum = S$1 million

• Loan Tenure = 20 years

• Interest Rate = 10% p.a.

• Annual Rest with Monthly Repayments

• Number of Payment Periods = 12 * 20 = 240

• Monthly Instalment for the First Year = $ 9,788.30

Loan B

• Loan Quantum = S$5 million

• Loan Tenure = 20 years

• Interest Rate = 10% p.a.

• Annual Rest with Monthly Repayments

• Number of Payment Periods = 12 * 20 = 240

• Monthly Instalment for the First Year = $ 48,941.51

In Excel, enter the syntax as shown in Table 1:

Table 1

  For both loans, the RATE will return 0.83%. The ENR per annum is then 12*0.83% = 10.21%. Next we will learn how to compute the ENR for loans with different rests but all with monthly repayments.

ENR for XX-rest Loans with Monthly Instalment

We will rely on the same loan example used in my previous article, which is Loan Quantum = S$1 million Loan Tenure = 20 years Interest Rate = 10% p.a. Table 2 displays the monthly payment amount for the three rests.

Table 2: Monthly Instalment for the First Year

  So to find the ENR, we enter the below syntax.

Table 3

  And Excel will yield the following ENR based on monthly rest. Put another way, we just obtained the nominal rate based on monthly rest for the daily-, monthly- and annual-reducing loans. We need to convert the interest to a common basis in order for fair comparison.

Table 4

  Going a step further, we will also like to find out the effective interest rate per annum. Table 5 shows the syntax to enter in Excel.

Table 5

  Finally, we will be able to compare fairly the true cost of borrowing. As expected the cost increases as the rest (frequency of loan calculation) becomes shorter; hence the daily-reducing loan has the lowest effective interest rate.

Table 6

---

About Property Buyer http://www.PropertyBuyer.com.sg/mortgage We are a research-focused Singapore mortgage consultancy which helps you compare Singapore home loans either for new loans or refinancing. We use loan reports from Singapore's best loan analysis system (exclusive to us) at http://www.icompareloan.com/consultant/ to serve our customers. Our services are completely FREE to you as the banks pay us a referral fee upon loan disbursement.