[You can download the Cashflow Projection Excel from the Resources page]
[Edit: Version 03 is up! CPF LIFE -related calculations are now more faithful to the actual mechanisms]
Consider the following scenario: The year is 2022, the stock markets are down, you're a young working adult with some savings, some stocks, and a BTO on the way (along with the dreaded monthly mortgage repayments). You've got 30k on hand and a huge temptation to average down on your stocks. Markets are down, so if not now, when? Okay, so the plan is to put 4k in Medisave and 4k in CPF SA (yay for high interest rates, AND TAX SAVINGS!), and throw the remaining 22k into stocks. Emergency funds aside, that'll leave you with 20k cash-in-bank, but that'll only grow as you continue to get your monthly paychecks.
But wait! You've got a wedding coming up next year, and the BTO renovation costs (almost forgot about those). Total expected damage: 50k. Will the 20k grow fast enough? Also, once married, you may be expecting a child within another year. How fast will the expenses grow? Can your salary increments keep up? You plan forward for 3 years, and estimate that maaaybe you'll only be able to invest 10k instead of 22k. Crisis averted, phew!
Consider this other scenario: You've been squirrelling away all your excess cash into your CPF SA, because 4% interest rate. You think "hey at this rate, imma be a multimillionaire by 45 and I can retire and travel the world. Awesome!" Fast-forward to 45 and you realize you've got millions in CPF, 100k in the bank, and yearly expenses of 40k. You can't withdraw the "CPF SA excess above FRS" until 55. Guess who's not retiring after all?
I could go on and on, but the above are just 2 examples of how retirement planning goes way beyond saving religiously and compounding interests. There are many ways of looking at it, but personally I believe in looking at cash flow. It doesn't matter if you have millions in net worth; if at any point in time you have trouble paying your bills, that's trouble right there. To this end, I made an Excel sheet for some simple projections, while taking into account inflation, expenses at different stages of life, increasing CPF Retirement Sums, etc. There are instructions in the Excel itself, and a sheet with an example of how a hypothetical person may plan his retirement. Still, here's a demonstration of how you may want to use it.
Let's say I'm a hypothetical male who was born in 1990, and it's now 2022. I hope to have my 1st child at age 32, and the 2nd child at 34. I'll kick them out of the house once they turn 25. I'm making a salary of 5.5k with an average 1.5 months bonus, and I expect an average wage growth of 6% a year, including promotions and job switches. I'll set aside 15% of my take-home pay every year to give my parents and the government (it's hard to reduce income tax to zero, nor do I think it's a good idea), and maybe use whatever remains of that 15% to treat myself to something nice. I'll also set aside 10% of the take-home pay to invest, and I think I can get about 5% return per year, which is great, considering that the liquid cash that I have (cash-in-bank, Singapore Savings Bonds, fixed deposits, etc) only generate an average of 1% per year. I have 160k already invested in stocks, and adding 10% of my take-home pay to it every month is a lot; I think I can afford to start taking out 2k per month from those sweet dividend payouts and NOT re-invest them, starting from age 48. I intend to spend an average of 2k per year per pax on vacations (I pay 2k, my wife pays 2k. Once we have 1 child (3x$2k=$6k), I will pay 3k, and she will pay 3k. Once we have 2 children (4x$2k=$8k) it will be 4k by me and 4k by my wife). Inflation is an average of 2.5% per year, and I plan to retire at age 51. I will aim to hit the Enhanced Retirement Sum at age 55 when my RA is created, and I will choose the CPF LIFE Escalating plan, and delay the payouts until age 67. I enter all of that in to the sheet named 'Input - Basic Info'. I then move on to the next sheet titled 'Input - Housing Loan'
We have a 350k housing loan between the 2 of us from HDB (2.6% interest), to be repaid over 15 years. That's 21.4% of my current income so getting the loan approved shouldn't be a problem. That's all the input that this page needs, but you can also play with the numbers to see how much "extra" you're paying in interest by choosing 15-year vs a 10-year loan. Or to compare HDB vs Bank loan. Or to see how much of the principal is left if you choose to, say, do early repayment halfway through the loan, or if you choose to make extra partial repayments at any time. However, do note that bank early repayment penalties and/or "administrative fees" are not considered here.
Next, I move on to the sheet 'Optional Edit - Expenses'. The values here were taken from https://whatsenough.sg/whats-enough-for-you/, which were in turn obtained from a 2019 study by NUS and NTU. If you think you will, say, spend less on utilities compared to the values given, feel free to edit them. For me, I'll just leave them as they are for simplicity's sake. We are now ready to look at the base results in the sheet 'Results (Case 0)' (Note: some columns have been hidden for compactness).
I'll have to spend a one-off 50k on BTO renovations and other things in 2022. Immediately I'll see a cash shortfall. I started with 20k in liquid assets, and have a take-home of 59k. After deducting expected daily and holiday expenses, I'll be $600 short for the 50k one-off expenses. But that's okay, I guess I'll just skip the vacation this year; the $2000 budget that I free up should cover it. Moving on, the next issue is this: once we have our 2nd child, my liquid assets start dropping really fast. In fact, on 31 December 2027 (i.e. EOY liquid asset balance for Year 2027), I'll be in the red again, and this time it's not just a few hundred dollars. As we go on we see more and bigger problems (so much red...), but we also see that those shortfalls seem to "heal" given some time, and I'll die a multi-millionaire. Perhaps there's a way to take some of those money from the future and use them to cover these shortfalls? I'm sure the future me who's 6-feet-under won't mind! I create a copy of the sheet and rename it Case 1, and implement a plan.
Here, on top of the 50k expenses in 2022, I've added more formulas. For example, while I'm still working (i.e. i'll get lots of CPFOA contributions), I will transfer 20k from CPF OA to CPF SA every year, since I don't think I need so much money in my OA and I'd prefer to grow them at 4%p.a. in SA instead of 2.5%p.a. in OA. This means that in Cell AG4 (Other CPF OA inflow/(outflow), for year 2023), I enter the formula IF(AND(C4<'Input - Basic Info'!$B$20,AM3<2*E3),-20000,0). [C4<'Input - Basic Info'!$B$20] means Age (in column C) is less than Retirement Age (as defined in the Basic Info sheet). [AM3<2*E3] means CPF SA balance as of end of last year (EOY SA/RA balance for year 2022) is less than twice the Basic Retirement Sum (i.e. the Full Retirement Sum) for the year 2022. I have a similar formula in Cell AH4 (Other CPF SA/RA inflow/(outflow)), but with +20000 instead of -20000.
I will also perform a cash top-up of 4k to Medisave every year while I am working, and once Medisave is maxed out, I'll top up my SA. I intend to do that for tax deductions. However, as you can see from the automatically highlighted cells, my Medisave will hit the prevailing BHS at age 35, while my SA will hit the prevailing BRS/FRS at age 35 and 38 respectively, so I'll no longer be able to enjoy those tax deductions from age 39 onwards. This means I may want to think of other avenues of tax deductions (maybe open a Supplementary Retirement Scheme Account? If so, best open it today to lock-in the withdrawal age before the government increases it further). At age 55, I still have quite some money left in the OA, so I will leave 20k in it and withdraw the rest. I also have other ad-hoc actions like liquidating some stocks to cover the renovation expenses, and again at age 37 (to make sure I don't end up with too little cash). With a combination of formulas and simple plus/minus numbers, you can simulate quite a diverse set of actions.
Now this looks great! My wallet still looks really tight for the first few years, but at least the numbers are positive now. I went from being unable to retire at 51 to being able to with some simple money management. The cost is that I'll have much less money left over at age 120, but that's a price I'm more than happy to pay!
This post has gone long enough. I'll end here and let you try out the Excel yourself. Do note that there are still a million and one things the Excel doesn't consider. Off the top of my head, one major thing is Tax. If you're in the 2% tax bracket right now but expect to hit the 11.5% bracket in a few years' time, perhaps it doesn't make sense to do the voluntary Cash-to-SA or Cash-to-Medisave top-ups now, since it means you'll gain only marginal tax savings, and will end up maxing out those accounts and lose this tax deduction avenue just as you need it most (i.e. when you hit the 11.5% tax bracket). Also, I haven't done much beta-testing; I think some of the interest calculations could be 1 step behind (i.e. they use the previous year's value to avoid circular references). Mostly, however, the Excel should give a rough estimate of your projected cash flows for retirement planning purposes. Have fun!
Comments