Free Resource – Housing Loan & CPF Calculator

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

Assumptions

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

Getting Started

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.

Inputs

Snapshot of the “Input fields” sheet

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 Balances

CPF-SAPlease fill in your current balance in your CPF SA
CPF-SA Monthly ContributionPlease fill in your monthly contribution towards your CPF SA
CPF-OAPlease fill in your current balance in your CPF OA
CPF-OA Monthly ContributionPlease 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 Detail

Loan Initial AmountPlease fill in the loan amount
Loan InterestPlease 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 AmountNon-input field. The amount will be automatically calculated based on information you had provided in your loan detail
Monthly Loan Payment By CPF OAPlease fill in the amount from your CPF OA which you are making towards your monthly payment.
Monthly Loan Payment By CashNon-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

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.

In practice, your CPF contributions might grow over time and your allocation to SA and MA will increase as you age. Refer to the CPF contribution rates by age and the CPF allocation rates.

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:

DateThis is the date. Do not edit it. I arbitrarily set it to start from June 2020.
Loan beginning balanceYour loan principal at the start of the month
Monthly PaymentYour loan’s monthly payment amount
Payment Towards Loan InterestPart of your monthly payment. It is the portion of your monthly payment that is paid as interest
Payment Towards PrincipalPart of your monthly payment. It is the portion of your monthly payment that is paid towards your principal.
Payment by cashThis is the amount of cash that you use to pay your monthly loan instalments.
Payment by CPFThis 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 OAThis 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 cashThis 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 balanceYour loan principal at the end of the month
CPF OA Beginning BalanceYour CPF OA balance at the beginning of the month
Transfer OA to SAThis 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 PaymentYour CPF OA monthly balance after minusing withdrawals and deduction. This is mainly used for calculating interest.
CPF OA Ending BalanceYour CPF OA balance at the end of the month
CPF OA InterestYour monthly interest gained from your CPF OA. This is not credited to your account.
CPF OA Accrued Interest For the YearThis 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 BalanceYour CPF SA balance at the beginning of the month
CPF SA Ending BalanceYour CPF SA balance at the end of the month
CPF SA InterestYour monthly interest gained from your CPF SA. This is not credited to your account.
CPF SA Accrued Interest For the YearThis 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.

You may edit the date in the first row (highlighted in yellow). The subsequent dates will be automatically updated.

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.

For a 25 year loan which starts on 1 June 2020, scroll down to check whether it will be fully paid on 1 May 2045 (25 years later). Once this checks, the calculation should be correct.

Prepayment

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.

When your monthly payment reduces below your monthly loan payment from your CPF, your monthly payment from CPF reduces accordingly and your monthly cash payment reduces to zero.

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.

Just fill in the amount whenever you transfer from your CPF OA to CPF SA

The Interest gains will update accordingly.

Final Notes

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!

Advertisements

Author: Fatty's Finance

Finance is like health. It becomes an issue if it is not well maintained. I want to help you get back in shape!

Leave a Reply