CPF Calculator

[Perisher]

Maybe should pm TS.

 

[Perisher]

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?file_id=00281808830695621173 (No macros required!)

Any suggestions? Problems? Complaints? Let me know

Paging TS, link no longer available.

 

[soyalove]

Maybe should pm TS.

thanks! I did pm him, even before bumping the thread. let's see. have to depend on the kindness of strangers now

 

[doody_]

Sorry ah, need to find the file again...

 

[soyalove]

Sorry ah, need to find the file again...

hehe TS has come! thank you!

 

[soyalove]

Thanks for the spreadsheet...

LOL, did a calculation with the following assumptions:
-BHS increasing @4.7% yearly till I reach 65yo
-FHS increasing @3% yearly till I reach 55 yo
-salary increasing @4% yearly till hitting $6k, no bonus

-MA is untouched as I will pay cash for ISP and any medical bills
-no transferring of OA to SA as it will be used for housing

MA will hit the projected BHS @ the age of 55
SA will hit the projected FRS @ the age of 59...


jin jialat, must pray to stay employed till 60yo.

how abt other beneficiaries of TS' hard work? care to share TS' masterpiece? would be great if we can share this useful calculator with more people.

can I please trouble you to share this? I will put it up for download if I receive a copy as well. Thank you!

 

[doody_]

Here it is: http://s000.tinyupload.com/index.php?file_id=56966513491273753715

 

[henrylbh]

Here it is: http://s000.tinyupload.com/index.php?file_id=56966513491273753715

Only comment is that it definitely not right or reasonable to hold MA constant at 52k in the projection and let the excess flow into SA/OA. MA will keep increasing till one reaches 65.

So optimistic that every year is 5 months bonus?

 

[soyalove]

Here it is:http://s000.tinyupload.com/index.php...13491273753715

wow, thanks TS!

 

[HWZ1973]

How come got error msg when opening file?

"...is in a different format than specified by the file extension..."

 

[doody_]

Only comment is that it definitely not right or reasonable to hold MA constant at 52k in the projection and let the excess flow into SA/OA. MA will keep increasing till one reaches 65.

So optimistic that every year is 5 months bonus?

Sample data only la...

Are you referring to BHS increasing over the years? Will need to follow the changes over the years to ensure it's correct.

 

[henrylbh]

Sample data only la...

Are you referring to BHS increasing over the years? Will need to follow the changes over the years to ensure it's correct.

I appreciate and laud your effort and time spent on table. But it would be reasonable to assume BHS would certainly increase. A guess of 3% to 5% would prevent too much overflowing to other accounts over the years giving a distorted view.

 

[doody_]

I appreciate and laud your effort and time spent on table. But it would be reasonable to assume BHS would certainly increase. A guess of 3% to 5% would prevent too much overflowing to other accounts over the years giving a distorted view.

Yep that's a good point. You guys can make the formula change yourself hor?

 

[yoyoyaz]

What about the SA?

Yep that's a good point. You guys can make the formula change yourself hor?

You are wonderful! This is incredible, thanks for all the hard work and for the generosity of sharing.

The only question I have is, what about the SA.

I thought the max was 161,000. Currently the SA account, still adds money post that amount, is that what happened?

I noticed for the MA, you add some formula so that anything extra gets puts aside, if that's what happens for SA as well, is there a way to do the same thing for SA?

 

[GeraldineT]

HI TS,

I was trying to use the calculator but it seems that the interest rate differ from the one I try on CPF site?
Ordinary Account-Special Account Savings Transfer Calculator: https://www.cpf.gov.sg/eSvc/Web/Schemes/OAToSATransfer/Calculate

Based on your inputs and these assumptions
By transferring $30,000.00 from the OA to SA, you are projected to earn $32,238.00 more interest after 26 years, as compared to leaving the amount in the OA.

Using ur calculator, I tried to move the same amount in 2019, but I don't seem to be able to derive 32,238k?

I used the assumptions of in Jan 2019
OA $71,046.50
SA $14,032.75

the calculator would give me
If I didn't move 30k from OA to SA till the age of 55?
OA $492,701.34
SA $282,500.46

If I move 30k from OA to SA till the age of 55?
OA $463,470.20
SA $334,776.34

The difference in SA is approx 52k?

 

[tangent314]

The SA difference is ~52k but the OA difference is ~29k. In total the difference is ~23k.

There's a simple calculation you can do yourself in Excel or Google Docs to calculate the difference. Enter the formula: =FV(0.04, 26, 0, -30000) - FV(0.025, 26, 0, -30000). You will get the result $26,165

Not sure why that TS's calculator calculates it different, will need to audit the formulas to check, which I don't really have time to.

I think I know why the CPF's calculator projects more though: It takes into account the extra 1% interest you get for the first $60k in your CPF (capped at $20k for OA). So on Jan 2019 you are only getting the 1% bonus interest on $20k + $14032.75, and if you transfer $30k into your SA you will get the capped bonus interest on $60k.

 

[GeraldineT]

The SA difference is ~52k but the OA difference is ~29k. In total the difference is ~23k.

There's a simple calculation you can do yourself in Excel or Google Docs to calculate the difference. Enter the formula: =FV(0.04, 26, 0, -30000) - FV(0.025, 26, 0, -30000). You will get the result $26,165

Not sure why that TS's calculator calculates it different, will need to audit the formulas to check, which I don't really have time to.

I think I know why the CPF's calculator projects more though: It takes into account the extra 1% interest you get for the first $60k in your CPF (capped at $20k for OA). So on Jan 2019 you are only getting the 1% bonus interest on $20k + $14032.75, and if you transfer $30k into your SA you will get the capped bonus interest on $60k.

I check the excel he got the extra 1%.

 

[timelesstate]

CPF calculator

Hi,

Am not too savvy with excel and chanced upon your post on the CPF spreadsheet.
Any chance that I could get the link to the CPF calculator excel spreadsheet? the previous one that was posted has expired.
Would be so grateful!

Cheers!

 

[AvatarViper]

can anyone share this? the link has expired. thanks

 

[jacky5297]

Yep that's a good point. You guys can make the formula change yourself hor?

Hi Doody, just sent you a pm, could you please share the link again?

Thanks a lot.