In my previous article, I did some calculations on the interest gained from various scenarios when it comes to paying down your housing loan.
In this post, I would like to share the sheet which I have created, explain how it works and explain how you may use it.
I have since updated and improved my sheet from the previous post.
Google Spreadsheet: Loan & CPF Interest Calculator Final Template
Before using this spreadsheet, here are some assumptions:
- Your CPF contribution remains constant over the loan duration
- The loan interest remains fixed over the loan duration
- I had excluded all other fees and charges, i.e. late payment charges, prepayment penalties etc.
Table of Content
Explanation of the spreadsheet
How to use the spreadsheet
Start by creating a copy of the spreadsheet as this will allow you to edit the file. I had locked the master copy to prevent any further edits.
You may read the “README” sheet for more instructions.
Now you are ready to configure the inputs for your loan interest and CPF interest calculator.
I have organized the inputs into three parts: CPF balances, loan detail and loan payment arrangements.
I had named the inputs for these fields which I will use in my interest calculation. If you wanted to find details of my defined named, check it by clicking: Data -> Name Ranges
|CPF-SA||Please fill in your current balance in your CPF SA|
|CPF-SA Monthly Contribution||Please fill in your monthly contribution towards your CPF SA|
|CPF-OA||Please fill in your current balance in your CPF OA|
|CPF-OA Monthly Contribution||Please fill in your monthly contribution towards your CPF OA|
Unfortunately, this part is manual as it takes effort to automatically breakdown your CPF contribution and allocation from your salary as it depends on your age and salary amount.
|Loan Initial Amount||Please fill in the loan amount|
|Loan Interest||Please fill in the interest rate of your loan. If you loan is variable, please input the expected effective/average interest rate over the duration of the loan.|
|Loan Duration (Years)||Please fill in the duration of your loan in years|
The assumption here is that the interest rates are fixed throughout the duration of the loan. It is the case for HDB loans.
For bank loans, which usually are variable interest rates loans, please estimate and input the effective/average interest rate. Banks commonly offer fixed interest rate for first one – three years and after which it will switch to variable interest rates. For those cases, please use a higher interest rate than the fixed rate for the effective loan interest rates.
Loan Payment Arrangements
|Monthly Loan Payment Amount||Non-input field. The amount will be automatically calculated based on information you had provided in your loan detail|
|Monthly Loan Payment By CPF OA||Please fill in the amount from your CPF OA which you are making towards your monthly payment.|
|Monthly Loan Payment By Cash||Non-input field. The amount will be automatically calculated – the difference of payment by CPF OA amount and the monthly payment amount.|
The loan payment amount is calculated with the PMT formula based on:
- Loan interest
- Loan duration
- Loan amount
Monthly Loan Payment = -PMT(Loan_Interest/12,Loan_Duration*12,Loan_Amount)
The Monthly Loan Payment By Cash is automatically calculated.
However, I did not put in any checks in place, so if you fill in Monthly Loan Payment By CPF OA > Monthly Loan Payment Amount, it will return a negative value for Monthly Loan Payment By Cash. The results will be wrong.
Outputs / Results
The ‘Main Calculator (duplicate this)’ sheet contains the main results. This sheet is busy with information so I will try my best to explain.
On the top, I had shown three main outputs: Total Payments, Total Interest Gain, and Net Interest Gain.
Total payments shows you the total amount you paid to your loan over the entire duration of the loan. I had broken down the payment details into two parts:
The first part shows you the portion of payment that went towards Interest payments and the portion of payment that went towards the loan. In my example above, you can see that $88,244.59 went towards interest and $200,000 went towards the loan.
The second part shows you the payment you paid in cash or via CPF OA. In my example, I had financed the loan totally with CPF OA with $288,244.59.
The sum of the first part should equal the second part – in my example the sum $288,244.59 are equal.
Total Interest Gain
This part shows you the interest you had gained from your CPF OA and CPF SA over the duration of 30 years.
I had assumed that your CPF contributions remained the same throughout the thirty years.
Net Interest Gain
The net interest gain is just the total interest gain from CPF OA and CPF SA minus the interest paid to the loan.
The Main Calculator
This is the main function of this spreadsheet. The loan and cpf calculator will display your month on month values:
|Date||This is the date. Do not edit it. I arbitrarily set it to start from June 2020.|
|Loan beginning balance||Your loan principal at the start of the month|
|Monthly Payment||Your loan’s monthly payment amount|
|Payment Towards Loan Interest||Part of your monthly payment. It is the portion of your monthly payment that is paid as interest|
|Payment Towards Principal||Part of your monthly payment. It is the portion of your monthly payment that is paid towards your principal.|
|Payment by cash||This is the amount of cash that you use to pay your monthly loan instalments.|
|Payment by CPF||This value is assigned by you. This is the amount of CPF OA that you are using to pay your monthly loan instalments.|
|Extra payment by CPF OA||This is the additional payment that is on top of your monthly instalments (prepayment) towards your loan from your CPF OA. Whenever you prepay, fill in the amount for that month.|
|Extra payment by cash||This is the additional payment that is on top of your monthly instalments (prepayment) that you pay your loan with cash. Whenever you prepay, fill in the amount for that month.|
|Loan ending balance||Your loan principal at the end of the month|
|CPF OA Beginning Balance||Your CPF OA balance at the beginning of the month|
|Transfer OA to SA||This is the amount you had transferred from your CPF OA to your CPF SA. Whenever you make a transfer, fill in the amount for that month|
|CPF OA Beginning Balance Minus Mortgage Payment||Your CPF OA monthly balance after minusing withdrawals and deduction. This is mainly used for calculating interest.|
|CPF OA Ending Balance||Your CPF OA balance at the end of the month|
|CPF OA Interest||Your monthly interest gained from your CPF OA. This is not credited to your account.|
|CPF OA Accrued Interest For the Year||This is the accumulated value of your CPF OA interest for the year. This amount is only credited to your CPF OA account on 1 Jan the next year.|
|CPF SA Beginning Balance||Your CPF SA balance at the beginning of the month|
|CPF SA Ending Balance||Your CPF SA balance at the end of the month|
|CPF SA Interest||Your monthly interest gained from your CPF SA. This is not credited to your account.|
|CPF SA Accrued Interest For the Year||This is the accumulated value of your CPF SA interest for the year. This amount is only credited to your CPF SA account on 1 Jan the next year.|
You may edit the start date of your loan at the first row, highlighted in yellow.
The loan interest and payment towards loan principal is calculated monthly with the following formulas:
- monthly interest = loan beginning balance*Loan_interest/12
- monthly payment towards principal = monthly payment – monthly interest
How to Use The Calculator
Adjusting the Loan Detail
If you are taking up $500,000 from a bank with a fixed rate of 1.8% over 25 years, please adjust the inputs accordingly.
If you monthly CPF OA contribution is not enough to pay your loan, you will have to top up the remaining payment with cash.
Go to the calculator and scroll down to the end of your loan tenure to make sure that your monthly payment exactly adds up to the loan amount on the last month of your loan tenure.
Whenever you choose to prepay, indicate whether it is coming from your CPF OA or cash and input the amount on the month which you start prepaying.
You will notice that after you have filled in the prepayment, your monthly payment reduces. This is because once you prepaid, you loan amount had reduce but your loan tenure remains fixed. To check, you need to scroll to the end of your loan tenure and make sure that it remains the same.
You may add as many prepayments as you wish. The sheet will adjust the monthly payments accordingly.
Once the monthly payment amount reduces below your CPF OA payment amount, your cash payment amount will go to $0 and your CPF OA payment will match the monthly payment amount.
Do scroll down to the end of your loan tenure to make sure that your monthly payment exactly adds up to the loan amount on the last month of your loan tenure.
For those of you who are topping up cash on top of your CPF for monthly loan payments, you may play around with the calculator to find out how much you need to prepay for you to fully pay your monthly loan payments from your CPF only.
Transfer From CPF OA To CPF SA
Whenever you choose to transfer from CPF OA to CPF SA, just input the value in the month that you made the transfer in the column “Transfer OA to SA”.
Do make sure that the transfer amount does not exceed the CPF OA ending balance, otherwise it will be a negative value and the calculations will be wrong. I was lazy to put conditions to prevent negative values.
The Interest gains will update accordingly.
Please feel free to use the template.
Do drop me comments or feedback if you need any features or improvements. I have enabled commenting in the master template.
I hope that it is useful.
Like this article? Follow Fatty’s Finance social media for more quality content!