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 setup 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 recalculated. To illustrate, if a loan on an annualrest 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 annualrest basis.
Using the below loan details, we will proceed to create the amortisation schedule for daily, monthly and annualreducing loans.
 Loan Quantum = S$1 million
 Loan Tenure = 20 years
 Interest Rate = 10% p.a.
The Excel template workbook with preset formulas used throughout this article can be downloaded here.
Dailyrest 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 preentered 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 preset formula in the Excel sheet and dragging it down, we get the below:
Monthlyrest with monthly instalment
Similar to the dailyreducing 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 dailyrest loan to obtain the amorisation schedule and you will obtain something like this:
Annualrest with monthly instalment
Finally we have the annualreducing 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 dailyreducing 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 dailyreducing loan.
In contrast, the monthly interest is the same for the loans on monthly and annualrest. 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 dailyrest loan, followed by monthly, and finally annual.
Table 2: Comparison of Monthly Interest Payment ($) for the 1st Year
Daily Rest 
Monthly Rest 
Annual Rest 

1st Month 
8,214.1 
8,333.3 
8,333.3 
2nd Month 
8,203.4 
8,322.4 
8,333.3 
3rd Month 
8,192.6 
8,311.3 
8,333.3 
4th Month 
8,181.8 
8,300.1 
8,333.3 
5th Month 
8,170.9 
8,288.9 
8,333.3 
6th Month 
8,159.9 
8,277.5 
8,333.3 
7th Month 
8,148.8 
8,266.1 
8,333.3 
8th Month 
8,137.6 
8,254.6 
8,333.3 
9th Month 
8,126.3 
8,242.9 
8,333.3 
10th Month 
8,114.9 
8,231.2 
8,333.3 
11th Month 
8,103.4 
8,219.4 
8,333.3 
12th Month 
8,091.8 
8,207.5 
8,333.3 
If that isn’t enough, we note that the total interest payable for the entire loan duration is lowest for the dailyrest 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 dailyrest, you can reap interestsaving of $(1,316,051.9 – 1,313,134.5) = $2,917.40 as compared to the monthlyrest.
When comparing the monthlyrest with the the annualrest, the interestsaving 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!
About Property Buyer
http://www.PropertyBuyer.com.sg/mortgage
We are a researchfocused 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.
SMS: (65) 9782 8606
Email: loans@PropertyBuyer.com.sg
Recent Comments