HWZ Forums

Login Register FAQ Mark Forums Read

CPF Calculator

Like Tree25Likes
Reply
 
LinkBack Thread Tools
Old 24-03-2017, 02:02 PM   #1
Supremacy Member
 
doody_'s Avatar
 
Join Date: Nov 2006
Posts: 7,513
CPF Calculator

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.



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 .

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...13491273753715 (No macros required!)

Any suggestions? Problems? Complaints? Let me know

Last edited by doody_; 24-02-2018 at 08:49 PM..
doody_ is offline   Reply With Quote
Old 24-03-2017, 03:29 PM   #2
Supremacy Member
 
Join Date: Mar 2001
Posts: 6,301
well done!
Frost45 likes this.
__________________
.: Men Are From Mars,
.: Women Are From Venus
badsector is offline   Reply With Quote
Old 24-03-2017, 03:38 PM   #3
Member
 
Join Date: Jan 2017
Posts: 173
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.
Frost45 likes this.
buaytuckchek is offline   Reply With Quote
Old 26-03-2017, 09:46 AM   #4
Greater Supremacy Member
 
Sinkie's Avatar
 
Join Date: Jan 2009
Posts: 84,074
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.



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 .

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...08830695621173 (No macros required!)

Any suggestions? Problems? Complaints? Let me know
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??
Frost45 likes this.
Sinkie is offline   Reply With Quote
Old 26-03-2017, 11:22 AM   #5
High Honorary Member
 
mrclubbie's Avatar
 
Join Date: Oct 2008
Posts: 181,075
Thanks for the effort
Frost45 likes this.
__________________
Hitori
Hitokoto
Hitorigoto
mrclubbie is online now   Reply With Quote
Old 26-03-2017, 11:51 AM   #6
Arch-Supremacy Member
 
Join Date: Jun 2000
Posts: 20,545
Good job and effort.
Frost45 likes this.
cscs3 is offline   Reply With Quote
Old 26-03-2017, 01:36 PM   #7
Master Member
 
Join Date: Oct 2004
Posts: 3,445
awesome spreadsheet! i think it hasn't included additional 1% on first 60k balance?

yet, i feel so rich already.
Frost45 likes this.
__________________
[SIZE=1][/SIZE]
hyperbole is offline   Reply With Quote
Old 26-03-2017, 03:03 PM   #8
Greater Supremacy Member
 
Sinkie's Avatar
 
Join Date: Jan 2009
Posts: 84,074
awesome spreadsheet! i think it hasn't included additional 1% on first 60k balance?

yet, i feel so rich already.
Yeah I see my OA alone can hit $1m by age 55
Frost45 likes this.
Sinkie is offline   Reply With Quote
Old 26-03-2017, 03:05 PM   #9
High Supremacy Member
 
Join Date: Aug 2011
Posts: 35,176
you did not factor cpf contribution from bonus

edit: i need to add them manually.
Frost45 likes this.
highsulphur is offline   Reply With Quote
Old 26-03-2017, 10:16 PM   #10
Supremacy Member
 
doody_'s Avatar
 
Join Date: Nov 2006
Posts: 7,513
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??
Never, but you log in to CPF can see liao

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.
Frost45 likes this.
doody_ is offline   Reply With Quote
Old 27-03-2017, 06:26 AM   #11
Supremacy Member
 
Join Date: Mar 2001
Posts: 6,301
how about adding the extra 1% in "Excess"?
__________________
.: Men Are From Mars,
.: Women Are From Venus
badsector is offline   Reply With Quote
Old 27-03-2017, 07:24 PM   #12
Supremacy Member
 
doody_'s Avatar
 
Join Date: Nov 2006
Posts: 7,513
how about adding the extra 1% in "Excess"?
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...37893079297958

I think it should be more than sufficient to project pretty accurately at this point, without going into any more detail
Jumpman23 and kennethtbh like this.
doody_ is offline   Reply With Quote
Old 28-03-2017, 05:34 AM   #13
Supremacy Member
 
Join Date: Mar 2001
Posts: 6,301
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...37893079297958

I think it should be more than sufficient to project pretty accurately at this point, without going into any more detail
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?)
__________________
.: Men Are From Mars,
.: Women Are From Venus
badsector is offline   Reply With Quote
Old 28-03-2017, 09:19 AM   #14
Supremacy Member
 
doody_'s Avatar
 
Join Date: Nov 2006
Posts: 7,513
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?
doody_ is offline   Reply With Quote
Old 31-03-2017, 10:19 AM   #15
Supremacy Member
 
Join Date: Mar 2001
Posts: 6,301
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%
__________________
.: Men Are From Mars,
.: Women Are From Venus
badsector is offline   Reply With Quote
Reply
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 Terms of Service for more information.


Thread Tools

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are On