Build a year-by-year spreadsheet that shows when your portfolio reaches your two-part number.
Priya is 34. She wants to know the age at which her invested money first reaches her two-part number from Lesson 3.4, "Build your two-part number", and she works it out in a spreadsheet she builds herself.
An online FI calculator will give you a date. That date is easy to forget, because you can't see how it was produced and you can't change the parts you doubt. In your own model you choose every number and can defend it. When your life changes, you change one cell and watch the date move.
This project is the centre of the course. The model's target is your two-part number from Lesson 3.4, and its inputs are your own figures. Later modules feed healthcare costs into this model and use it for coast FI and barista FI. Expect it to take about an hour.
The model starts from your current invested balance and age, adds your yearly savings, applies an assumed real return, and shows the year your balance first reaches the two-part number. Keep the sheet plain: one tab, a block of inputs at the top, and one row per year below. A model you fully understand is worth more than a clever one you don't.
At the top of the tab, create six labelled input cells: current age, current invested balance, yearly take-home income, savings rate, assumed real return, and target. The target is your two-part number from Lesson 3.4.
A real return is the return after inflation. Every return you enter in this model must be a real return.
Your invested balance for this model includes only money you could actually draw on during the bridge years, as described in Lesson 4.2. That means investments in your own name and long-term savings. Leave out CPF, and leave out your emergency fund.
Below the inputs, add a yearly contribution cell. Write it as a formula, income x savings rate, so it updates when either input changes.
Priya enters age 34, an invested balance of S$200,000 (an example figure), take-home income of S$96,000 and a savings rate of 52.5%. Her contribution cell shows S$96,000 x 52.5% = S$50,400 a year. In the target cell she puts about S$1,437,000. That is the two-part number she built in Lesson 3.4, and it assumed she stops working at 50.
Below the contribution cell, set up five columns: age, starting balance, contribution, return and ending balance.
In the first row, link age to the current age cell, starting balance to the invested balance cell, and contribution to the contribution cell. Return is starting balance x assumed return. Ending balance is starting balance + return + contribution. From the second row on, starting balance is the previous row's ending balance, and age is the previous age + 1. Copy the rows down for about forty years.
This layout treats each year's savings as arriving at the end of the year. It is simple and slightly cautious.
Every formula must refer to the input cells. If you type a number into a formula, the tests later on won't work, because changing an input will no longer change the result.
At a 4% real return, Priya's age-34 row starts at S$200,000, earns S$8,000, adds S$50,400 and ends at S$258,400. Her age-35 row starts at S$258,400, earns about S$10,336, adds S$50,400 and ends at about S$319,136.
Next, add a sixth column showing whether the ending balance has reached the target. A simple yes/no formula is enough. The first row that says yes is your year. Priya's balance passes her target in the row for age 49, so she reaches it at 50. If you want, add conditional formatting so that row changes colour, which makes the answer easy to spot each time you change an input.
Change one input at a time, as Lesson 4.1 suggested, and write down the year each time.
Pick three real returns: a low one you'd be disappointed by, a middle one you think is reasonable, and a high one that would be a good outcome. Then pick two savings rates: your current rate, and a rate you could realistically reach, for example by saving your next pay rise as Lesson 4.3 described. Three returns and two savings rates give you six runs. Record the six results in a small table with savings rates down the side and returns across the top.
Every result must come from changing one input. If you have to edit a formula to get a result, fix the model so the input cell does the work.
Priya tests 2%, 4% and 6%. At her 52.5% savings rate she reaches the target at 53, 50 and 48. She then looks at saving a S$6,000 raise, which lifts her savings rate to about 55.3%. At that rate the ages become 52, 49 and 47. Across the range of returns her date moves by five years. Saving one raise moves it by about a year at every return.
Your own table will probably show a similar pattern. Over a realistic range of returns the date can move by several years, and the savings rate is the input you control.
The model and your two-part number depend on each other. Your target depends on the stopping age you assumed when you built it. If the model gives a later age, the bridge is shorter, so the target is smaller.
At 2%, Priya's model says 53, but her target assumed she would stop at 50. She goes back to Lesson 3.4, "Build your two-part number", and rebuilds it for a stop at 53, which gives a smaller target. With that figure in the target cell, she reaches it at 52.
Do the same with your figures. Rebuild the two-part number for the new stopping age, enter it in the target cell and rerun the model. Repeat once or twice until the model and the two-part number agree on a stopping age.
A finished sheet has a labelled input block, rows running forty years ahead, a clear marker in the target year, and a six-cell results table in which every result came from changing one input only. Beside the results table, write one sentence saying which input moved your date the most.
Save a copy with today's date before you start the activity below. When you change the inputs at your yearly review, you will want to see what last year's version said.
Build the model and run it at three return assumptions and two savings rates. Write the six target years in a small table.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).