Sunday, November 4, 2012

HDB Loan Calculator

This file does a pretty accurate calculation of your HDB Loan, provided you key in the correct information in the blue cells. If you have not obtained your HLE, assume the full amount for the moment.

HDB LOAN CALCULATOR - DOWNLOAD

Take note that the maximum loan period is 25 years or until you are reach 65 of age, whichever is lesser.

The interest rate is based on the current concessionary rate of 2.6%. This rate is adjusted every quarterly, in January, April, July and October each year. Current HDB market interest rate is actually 3.38%

The file gives you 4 options

1. Calculate by loan period with a maximum of $40,000 (combined) parked in CPF-OA
2. Calculate by loan period without any amount parked in CPF-OA
3. Calculate by desired monthly payments with a maximum of $40,000 (combined) parked in CPF-OA
4. Calculate by desired monthly payments without any amount parked in CPF-OA.

You can choose to see either with maximum $40,000 (combined) parked in CPF-OA or without for the loan breakdown.


MAXIMUM OF $40,000 (COMBINED) IN CPF-OA

The option of having a maximum of $40,000 (combined) parked in CPF-OA is included because the first $20,000 in each individual CPF-OA earns extra interest of 1% making it a total of 3.5%, which is higher than HDB 2.6% interest rate. It means you are better off keeping that amount to earn that extra interest, if you can afford to*. Look at the calculated actual interest paid to make the comparison of having any amount parked or not.

Although the extra 1% interest goes into your Special Account and you will not be able to use it for other purpose except for retirement, it goes into meeting your Minimum Sum later on when you retire. That means, there is more of your Ordinary Account to draw out from when you reach the age of 55.

Having a maximum of $20,000 in your own CPF-OA on standby will also come in handy if you are unable to service your mortgage when there are no contributions to your CPF-OA for a period of time due to lost of income etc. The number of months that amount can help you out will be

Amount parked / monthly installment = number of months you are covered


MAXING OUT MONTHLY INSTALLMENT USING CPF-OA CONTRIBUTION

To know if you need to top up in cash on top of your monthly CPF-OA contribution to pay for your monthly installment, take note of the table below. All you need to concentrate on is the amount credited into your Ordinary Account (as highlighted in blue). If the amount is lesser than your calculated monthly installment, it means you have to top up the difference in cash.

Take note the percentage decreases as you grow older so if your income do not increase as you age, it means you have to top up more after every stage in the table. So take note if you plan to max out your monthly installment using your monthly CPF-OA contribution.

The file provides an estimate of the monthly cash top up that you may need to make.

Employee
Age (Years)
Contribution Rate
(for monthly wages ≥ $1,500)
Credited into
Contribution by Employer
(% of
wage)
Contribution by Employee
(% of
wage)
Total Contribution
(% of wage)
Ordinary Account
(% of wage)
Special Account
(% of wage)
Medisave Account
(% of wage)
35 & below1620362367
Above 35-451620362178
Above 45-501620361989
Above 50-551418.532.513.59.59.5
Above 55-6010.51323.51229.5
Above 60-6577.514.53.51.59.5
Above 656.5511.5119.5

 If you are a first timer, you may not have the luxury of  maintaining any amount in your CPF-OA as it will be emptied when you apply for HDB loan.

13 comments:

Anonymous said...

Hey, thanks for the excel! It saves me lots of time trying to compute the loan calculation!

I'm trying to find the balance of how much to retain in my CPFIA given the projection of 5%-10% return from investments vs the initial downpayment for the HDB loan.

Terence Lee said...

Great spreadsheet and very useful. Thanks for sharing it! Is it right to say that each partner (husband and wife) should actually part 20k each in their accounts to maximize the 1% interest rate? That would be 40k in total right? I don't see that reflected in the spreadsheet.

azacamis said...

Hi Terence,

Thanks for your comments. Yes, the file did not include spouse CPF contribution at this moment therefor the amount to park is capped at 20k.

I will make the necessary adjustment and post an updated file.

Ken said...

Hi Azacamis

How to unlock yr excel file as i cant to keyin the figures i want

Azhar Khamis said...

Hi Ken,

Which field do you need to key figures in? The file should have allowed the necessary field to be modified and block the rest to prevent the formulas from being removed.

Ken said...

hi Azacamis..

Sorry for the late reply.. with regards to the calculator it seem to be wrong.. actually there is more figure to input.. i really need your help as i not good in excel..

Is there any email add i can send to u or are u able to email me
my email:agpnucleus@gmail.com
or u can skype me marcross23@hotmail.com

zachary said...

Hi,

Regarding CPFIA. I've used my CPF (basically the money came from AHG) to pay for the 5% downpayment of my BTO. At the time of downpayment, this constituted the 1st 20k of my OA and wiped out my OA. Now, i now have 20k sitting in my OA. Will CPF consider the amount spent for downpayment as the 1st 20k of OA, and thus I do not get to earn the additional 1%? Thanks in advance!

Azhar Khamis said...

Hi zachary,

The additional 1% interest is paid on the first 20k of the OA each month. CPF will just takes the lowest balance of each month and anything up to 20k will earn 3.5% interest.

This means your 20k now is earning the additional 1% interest.

Anonymous said...

can i suggest you not to protect your workbook?
because i'm unable to see some values as they exceed the cells, they show #####

Anonymous said...

and the alternative calculation, the year of desired repayment, you have set it as 'currency'. please amend.

Emy Oreo said...

Hi, can u increase the selection of montly installment from 25 to 30 years as my selection was mase before the readjustment made by hdb and still able to opt for 30 months

Unknown said...

Hi,
the download link has security error.
Could you email the spreadsheet ?
Thanks

Anonymous said...

hi how to unprotect your worksheet? or is there an email i can contact you to get an unprotected copy? thanks!