- Loan Quantum = S$1 million
- Loan Tenure = 20 years
- Interest Rate = 10% p.a.
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 |
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 |
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 |
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. SMS: (65) 9782 8606 Email: loans@PropertyBuyer.com.sg