CPF Calculator

doody_

Supremacy Member
Joined
Nov 27, 2006
Messages
7,508
Reaction score
5
What is this?
It's an Excel spreadsheet to project your CPF account balances into the future.

Can use this do what?
Estimate how much CPF you have at age 55, project impact of additional CPF contributions, etc.

How to use?
You enter your birth year on top in the box highlighted in yellow. The age and category will be auto updated. CPF not very clear so I made some assumptions about when the age category changes. The data table is on the right from Column V onwards.

After that, you enter your CPF contribution for each month. It will show the split into the 3 accounts, and the balance for that month. Interest is calculated every month and added at the end of the year.

sq2LW4h.png


Some other stuff
It's supposed to roll over MA to SA once MA exceeds BHS, and to roll over to OA once SA exceeds FRS. I checked the figures and it looks good, but let me know if something goes wrong with the rolling :s22:.

I didn't include in the extra 1% interest paid on first 60k of balances, still need to think about the best way to fit it in.

Where to get? (updated Feb 2018)
Download link here: http://s000.tinyupload.com/index.php?file_id=56966513491273753715 (No macros required!)

Any suggestions? Problems? Complaints? Let me know :s22:
 
Last edited:

buaytuckchek

Member
Joined
Jan 31, 2017
Messages
226
Reaction score
15
Not at my desktop at the moment, sounds like a good spreadsheet to me.

I thought it will be good to put additional column of the year's BRS, FRS, ERS and BHS with a comparison of how much more to the respective targets. The challenge would be the ever changing values. I suggest to use 3% per annum to extrapolate the BRS after 2020.

I had my own spreadsheet that let me see when can I quit my job such that the interest can self sustain till the extrapolate "ceiling" when I am 55yrs.

To have the additional 1%, you may need quite a number of "if" in your formula.
 

Sinkie

Greater Supremacy Member
Deluxe Member
Joined
Jan 20, 2009
Messages
86,078
Reaction score
21
What is this?
It's an Excel spreadsheet to project your CPF account balances into the future.

Can use this do what?
Estimate how much CPF you have at age 55, project impact of additional CPF contributions, etc.

How to use?
You enter your birth year on top in the box highlighted in yellow. The age and category will be auto updated. CPF not very clear so I made some assumptions about when the age category changes. The data table is on the right from Column V onwards.

After that, you enter your CPF contribution for each month. It will show the split into the 3 accounts, and the balance for that month. Interest is calculated every month and added at the end of the year.

sq2LW4h.png


Some other stuff
It's supposed to roll over MA to SA once MA exceeds BHS, and to roll over to OA once SA exceeds FRS. I checked the figures and it looks good, but let me know if something goes wrong with the rolling :s22:.

I didn't include in the extra 1% interest paid on first 60k of balances, still need to think about the best way to fit it in.

Where to get?
Download link here: http://s000.tinyupload.com/index.php?file_id=00281808830695621173 (No macros required!)

Any suggestions? Problems? Complaints? Let me know :s22:

haha, this excel is so power.. now i know it will take another 5 years before my OA is enough to pay for another condo downpayment

your excel got considered abt accruel interest if i use cpf to pay for housing installment?? :o
 

hyperbole

Master Member
Joined
Oct 10, 2004
Messages
3,672
Reaction score
89
awesome spreadsheet! i think it hasn't included additional 1% on first 60k balance?

yet, i feel so rich already.
 

highsulphur

Great Supremacy Member
Joined
Aug 16, 2011
Messages
65,551
Reaction score
31,153
you did not factor cpf contribution from bonus

edit: i need to add them manually.
 

doody_

Supremacy Member
Joined
Nov 27, 2006
Messages
7,508
Reaction score
5
Not at my desktop at the moment, sounds like a good spreadsheet to me.

I thought it will be good to put additional column of the year's BRS, FRS, ERS and BHS with a comparison of how much more to the respective targets. The challenge would be the ever changing values. I suggest to use 3% per annum to extrapolate the BRS after 2020.

I had my own spreadsheet that let me see when can I quit my job such that the interest can self sustain till the extrapolate "ceiling" when I am 55yrs.

To have the additional 1%, you may need quite a number of "if" in your formula.

awesome spreadsheet! i think it hasn't included additional 1% on first 60k balance?

yet, i feel so rich already.

Yea, not inclusive of the extra 1% interest on first 60k.

haha, this excel is so power.. now i know it will take another 5 years before my OA is enough to pay for another condo downpayment

your excel got considered abt accruel interest if i use cpf to pay for housing installment?? :o

Never, but you log in to CPF can see liao :o

you did not factor cpf contribution from bonus

edit: i need to add them manually.

I simplified it by making it "CPF Contribution". You will have to calculate your own contribution based on OW and AW. It shouldn't be too complicated unless you get multiple bonuses in a year and earn <6k, then you have to watch out for the AW cap which will vary.
 

doody_

Supremacy Member
Joined
Nov 27, 2006
Messages
7,508
Reaction score
5

badsector

Supremacy Member
Joined
Mar 10, 2001
Messages
6,492
Reaction score
39
I have added in the extra 1% interest using its own column. The excess column was used to overflow the MA to SA and OA, so left it as it is.

Download link: http://s000.tinyupload.com/index.php?file_id=36038237893079297958

I think it should be more than sufficient to project pretty accurately at this point, without going into any more detail :o

the extra 1% should be calc backwards too

1st 60k goes to MA and SA, if below 60k then OA earns the balance(up to 20k)
And all OA 1% earned all flow to SA or MA(any experts can clarify this?)
 

doody_

Supremacy Member
Joined
Nov 27, 2006
Messages
7,508
Reaction score
5
the extra 1% should be calc backwards too

1st 60k goes to MA and SA, if below 60k then OA earns the balance(up to 20k)
And all OA 1% earned all flow to SA or MA(any experts can clarify this?)

The priority is RA, OA (20k), SA, MA. Interest goes into their respective accounts except OA interest which goes to SA.

I'm not aware if there are any conditions that affect where this extra interest goes. Like if FRS is reached, will the interest flow elsewhere?
 

badsector

Supremacy Member
Joined
Mar 10, 2001
Messages
6,492
Reaction score
39
The priority is RA, OA (20k), SA, MA. Interest goes into their respective accounts except OA interest which goes to SA.

I'm not aware if there are any conditions that affect where this extra interest goes. Like if FRS is reached, will the interest flow elsewhere?

but ur calc method still wrong. you deduct the first 20k from OA first.
if combined SA and MA has 60k, OA does not earn xtra 1%
 

doody_

Supremacy Member
Joined
Nov 27, 2006
Messages
7,508
Reaction score
5
but ur calc method still wrong. you deduct the first 20k from OA first.
if combined SA and MA has 60k, OA does not earn xtra 1%

Where did you get that from? CPF website states it's RA, OA, SA, MA in that order. So I'm right to deduct up to 20k from OA first. Only if you have $0 in OA, then you will have 60k for SA and MA.
 

badsector

Supremacy Member
Joined
Mar 10, 2001
Messages
6,492
Reaction score
39
Where did you get that from? CPF website states it's RA, OA, SA, MA in that order. So I'm right to deduct up to 20k from OA first. Only if you have $0 in OA, then you will have 60k for SA and MA.

sorry. my mistake. u are right.
 

duhduhduh

Arch-Supremacy Member
Joined
Sep 5, 2009
Messages
14,319
Reaction score
907
Hello TS!

Thanks for the calculator, but the figure differs from the one given from the CPF board app? How come ah?

For CPF App,
- I used 3500 as my wages
- It shows that the monthly contribution will be 1295. (my contribution is $700)

For your excel sheet,
- i just used 1295 for the monthly figures
 

anfielder

Master Member
Joined
Sep 16, 2005
Messages
4,554
Reaction score
1
Hello TS!

Thanks for the calculator, but the figure differs from the one given from the CPF board app? How come ah?

For CPF App,
- I used 3500 as my wages
- It shows that the monthly contribution will be 1295. (my contribution is $700)

For your excel sheet,
- i just used 1295 for the monthly figures

1295 is inclusive of your employer's contribution
 

pcmdan

Supremacy Member
Joined
Jun 21, 2010
Messages
6,961
Reaction score
179
TS, thumbs up on the calculator

Correct me if i am wrong (besides those already pointed out by others) - no obligations, just afraid my understanding is not correct

1. The CPF contribution should always be a month lag? I.e. Your Jan pay will only be contributed in Feb

2. Next, CPF calculated the interests base on the lowest balance in the month. I.e. if your contribution comes in 2 Jan X1, your interest given by CPF will only be based on the balance in 1 Jan X1 x 2.5%/12 instead of 2 Jan X1 x 2.5%/12

I do agree it provide a good sensing though not 100% accurate.
 
Last edited:
Important Forum Advisory Note
This forum is moderated by volunteer moderators who will react only to members' feedback on posts. Moderators are not employees or representatives of HWZ. Forum members and moderators are responsible for their own posts.

Please refer to our Community Guidelines and Standards, Terms of Service and Member T&Cs for more information.
Top