In one of my previous articles: How To Create An Amortisation Schedule For Your Home Loan?, I discussed about using Excel to compute the interest and principle payments for each period.

In this article, we will go a step further by explaining about the set-up of the amortisation schedule for mortgages with different rests.

Rest, or the compounding period, is the frequency in which the outstanding loan amount is calculated.

The more frequently the loan is calculated the lower the interest payments will be, all else being equal. This is because each time repayment is made, the outstanding loan amount is reduced and so will the interest chargeable if the loan is re-calculated. To illustrate, if a loan on an annual-rest basis is repaid monthly the interest for the entire year will still remain the same as the financier uses the outstanding loan at the beginning of the year for interest computation.

Most Singapore home loans follow a monthly rest, while a few is on daily- and annual-rest basis.

Using the below loan details, we will proceed to create the amortisation schedule for daily-, monthly- and annual-reducing loans.

• Loan Quantum = S$1 million

• Loan Tenure = 20 years

• Interest Rate = 10% p.a.

The Excel template workbook with pre-set formulas used throughout this article can be downloaded here.

Daily-rest loan with monthly instalment

There are two main advantages of a mortgage that is calculated daily. Firstly the total interest payable will be relatively lower. Secondly, if partial repayment is made, the interest that will be charged is reduced by the next day since the outstanding principle is calculated everyday.

So how then do we create the amortisation schedule? With the aforementioned loan, we need to find the number of compounding periods for the entire loan duration as well as the daily interest rate.

For simplicity, we assume that every year in the loan has 365 days. Hence
Total Periods = 365 x 20 = 7,300

Next, we calculate the daily interest rate.
Daily Interest Rate = 10% / 365 days = 0.0274% (approximately)

In the Excel sheet, we enter these loan details.

Instantaneously, after entering the loan details, the payment, principal, interest and balance for the first period will appear in the Excel sheet with the pre-entered formulas, as seen below. To understand the formulas, please refer to my previous article.

Select D12:G12 then drag and pull to the 7,300th period.

However, the repayments are monthly so we need to find the total amount for 30 days (We assume here that there are 30 days in each month).

Using the pre-set formula in the Excel sheet and dragging it down, we get the below:

Monthly-rest with monthly instalment

Similar to the daily-reducing loan, we find the total periods and monthly interest, which are

Total Periods = 12 x 20 = 240
Monthly Interest = 10%/ 12 months = 0.083% (approximately)

In the Excel sheet, we get as below:

Repeat the steps for the daily-rest loan to obtain the amorisation schedule and you will obtain something like this:

Annual-rest with monthly instalment

Finally we have the annual-reducing loan. Since the frequency of loan calculation is the same as the loan quantum. We have
Total Periods = 20

The annual interest rate is also already given. Hence we have the following:

The amorisation schedule will be like the below:

But repayment is made monthly. So we must divide all the yearly figures by 12.

For example in the first year:

Total Monthly Payment ($) = 117,459.62 / 12 = 9,788.30

Total Monthly Principal ($) = 17,459.62 / 12 = 1,454.97

Total Monthly Interest ($) = 100,000.00 / 12 = 8,333.33

In the Excel sheet, it will be like this:

Conclusion

For simplicity’s sake, throughout this section, we assume that each month has 30 days and so for the monthly outlay for the daily-reducing loan, we simply sum up the figures for 30 days.

Now, we compare the monthly payment, principal and interest across the three types of rests. As an example, we only use the first month’s amorisation schedule. From Table 1, we see that the total payment and interest is the least for the daily-reducing loan.

In contrast, the monthly interest is the same for the loans on monthly- and annual-rest. This is true only for the first month after which the interest for the former will fall below the latter.

Table 1: Comparison of Amorisation Schedule for the 1st Month

	Monthly Payment ($)	Monthly Principal ($)	Monthly Interest ($)
Daily Rest	9,506.0	1,292.0	8,214.1
Monthly Rest	9,650.2	1,316.9	8,333.3
Annual Rest	9,788.3	1,455.0	8,333.3

 

In Table 2, we clearly see the difference in interest payment for the first year. As expected the interest is always lower for the daily-rest loan, followed by monthly, and finally annual.

Table 2: Comparison of Monthly Interest Payment ($) for the 1st Year

 

If that isn’t enough, we note that the total interest payable for the entire loan duration is lowest for the daily-rest loan.

Table 3: Comparison of Total Interest Payments

	Total Interest ($)
Daily Rest	1,313,134.5
Monthly Rest	1,316,051.9
Annual Rest	1,349,192.5

 

With the daily-rest, you can reap interest-saving of $(1,316,051.9 – 1,313,134.5) = $2,917.40 as compared to the monthly-rest.

When comparing the monthly-rest with the the annual-rest, the interest-saving is even greater at $(1,349,192.5 – 1,316,051.9) = $3,3140.6.

So after reading this article, I hope you understand the importance of selecting a loan with a shorter rest!

For advice on a new home loan.

For refinancing advice.

Download this article here.