You will build a sheet that estimates essential and flexible spending each year of retirement.
You now have three things on paper: a list of spending lines marked to fall, stay or rise, a split between essential and flexible, and an inflation rate with a planning age. This exercise puts them into one sheet that the rest of the course builds on. Every later module reads from it: the floor in module 2, the drawdown map in module 3 and the stress tests in module 5.
Set aside about thirty minutes and open a blank spreadsheet. The worked example uses Jasmine's made-up figures from lessons 1.2 and 1.3, so you can check your formulas against hers.
Create one row for each spending line and these columns:
Category, such as food or travel Today's amount, what the line costs in a year at today's prices once you have retired Type, essential or flexible Change in retirement, a short note on why the amount differs from what you spend now Growth rate, the inflation rate this line grows by Then one column for each age you want to see: your first year of retirement, 75 and 85
Put your inflation assumption in a single cell at the top, labelled, and point every formula at that cell. When you test a higher rate later, you change one number instead of fifty.
Jasmine's sheet has ten rows. Her today's amounts add up to S$26,000 essential and S$14,000 flexible, and her growth cell holds 2.5%.
For each line, the amount at a given age is today's amount multiplied by one plus the growth rate, raised to the power of the years between now and that age. In a spreadsheet that is today's amount times (1 + rate) to the power of (age minus your current age).
Jasmine is 55. For her first year of retirement at 60, the power is 5. For 75 it is 20, and for 85 it is 30.
Take her food line, S$8,400 at today's prices. At 2.5% a year it becomes about S$9,500 at 60, S$13,800 at 75 and S$17,600 at 85. None of those figures means she eats more, since they are the same meals bought at later prices.
Fill the formula down for every line and every age column.
Healthcare costs have tended to rise faster than prices in general, and they take a larger share of an older person's spending. So move healthcare and insurance premiums to a line of their own, with a higher growth rate in its own labelled cell.
What rate? No official figure tells you what your own medical costs will do. Pick an assumption above your general inflation rate, write it down as an assumption, and test it. Jasmine used 5% a year for this example.
Her healthcare and insurance line is S$5,000 at today's prices. At 5% a year it becomes about S$6,400 at 60, S$13,300 at 75 and S$21,600 at 85. Compare that with her food line: both start in the same range, but by 85 healthcare costs about S$4,000 more a year.
The other effect shows up in the share. At today's prices, healthcare is about 19% of Jasmine's essentials. At 85 it is about a third. That one line explains much of why plans that look comfortable at 65 get tight at 85. Lesson 6.2, Healthcare and long-term care costs after 65, refines this line with real premium tables. For now, a separate line with a higher rate is enough.
At the bottom of each age column, add two totals: one for essential lines and one for flexible lines.
Jasmine's totals, in future dollars, come out like this. At 60, essential spending is about S$30,100 and flexible about S$15,800, a total near S$46,000. At 75, essential is about S$47,700 and flexible about S$22,900. At 85, essential is about S$65,700 and flexible about S$29,400.
Notice what has happened to the gap between the two. At 60, essential spending is roughly twice flexible spending. At 85 it is more than twice, because healthcare sits in the essential group and grows fastest. The part of the budget you can trim in a bad year shrinks as you age.
Before you move on, run three checks.
Change the inflation cell to your higher test rate and watch the totals at 85. If they jump far more than you expected, check that each formula points to the rate cell and not to a typed number. At 3.5%, Jasmine's essentials at 85 rise from about S$65,700 to about S$80,600, with healthcare unchanged on its own rate, which tells her the sheet is linked correctly.
Look for anything missing. Add a yearly figure for large irregular costs such as replacing appliances or a renovation, as lesson 1.2 suggested.
Decide whether flexible spending should fall with age. Many people travel less in their eighties than in their sixties. Jasmine kept her flexible spending growing with inflation all the way, because it makes the plan cautious, and she can lower it later if she wants a less conservative view.
A finished sheet has every spending line, each marked essential or flexible, with today's amount, a growth rate and an amount at three ages. Healthcare sits on its own line with its own rate. Two totals at the bottom of each age column give you essential and flexible spending, and two labelled cells at the top hold your assumptions.
Keep the file. Lesson 2.4, Size your floor and the gap, adds CPF LIFE beside your essential totals. Now build your own version, starting from the spending lines you marked in lesson 1.2.
Build the estimate and write your first-year essential and flexible spending in future dollars.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).