# 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

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

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

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

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:

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.

### 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.

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.

## 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!