You will build a spreadsheet that calculates your life cover gap and recommended term.
Jun Hao had the pieces of his life cover answer spread across three pages of notes: a gross need, a list of existing cover, two end dates. When Mei asked what would happen to the number if they had a second child, he had to start again from the top. A spreadsheet fixes that. Change one cell and the whole answer updates.
In this exercise you build that sheet for your household. It takes about 35 minutes, most of it spent finding your existing cover figures. Have your notes from the activities in lessons 2.1, 2.2 and 2.3 open.
Set up three sections, one under the other, with a label in column A and a figure in column B.
In the needs block, give each dependant a row with three cells: yearly support, years, and support times years. Below those, add a row for each debt you'd want cleared, at today's outstanding balance, and a row for each large future cost such as education. End the block with a total, the gross need.
Keep the inputs separate from the formulas. Yearly support and years should be typed numbers, and the products and totals should be formulas, so a change to an input flows through on its own.
Write your assumptions in column C beside each input. "Support until Elise is 22." "Education estimate at today's prices." "Loan balance from HDB statement, June." When you come back to this in a year, those notes save you from guessing what you meant.
Give each source from lesson 2.2 its own row: DPS, your share of HPS cover, personal policies paying on death, savings and investments your family could reach, and group cover.
Add a column for the date each one stops. Then make two totals: existing cover without group cover, and with it.
The gap is the gross need minus existing cover. Calculate it twice, without group cover and with it. The first is the figure to plan around.
In the term block, enter the year your youngest dependant becomes independent and the year your loan ends. Use a formula to pick the later of the two, such as =MAX(B20, B21), so the term updates if either date moves.
If your need splits naturally into parts with different end dates, as Jun Hao's did in lesson 2.3, Set the term from your youngest dependant and your loan, show the split: each part's amount and its end year, with a check cell confirming the parts add up to the gap.
Copy the whole sheet to a second tab and change one thing: a second child, a larger loan after an upgrade, your partner stopping work, or losing your group cover. Note how the gap and the term move.
The point of the test is to see which assumption drives your answer. If one change moves the gap by a quarter of a million, that's the assumption to get right and to review first.
Every figure is an example made up for the exercise.
Needs: support of S$42,000 a year for 20 years, S$840,000; the HDB loan balance, S$280,000; Elise's education, S$80,000. Gross need S$1,200,000.
Existing cover without group: HPS on his 50% share, S$140,000; savings and investments, S$60,000; whole life death benefit, S$100,000. Total S$300,000. Gap S$900,000, less his DPS payout. With group cover of S$100,000, the gap is S$800,000, less DPS.
Term: Elise independent in 20 years, at Jun Hao's age 54; loan ends in 24 years, at 58. Later date, 58. Split: S$760,000 to 54 and S$140,000 to 58, and the check cell confirms 760,000 plus 140,000 is 900,000.
Test: a second child born in two years. The youngest is now independent in 24 years, so support runs 24 years instead of 20, and education doubles. Support becomes 42,000 times 24, which is S$1,008,000. Education is S$160,000. With the loan unchanged at S$280,000, gross need is S$1,448,000. Less the same S$300,000 of existing cover, the gap is S$1,148,000, which is S$248,000 more than before.
The term changes shape as well. The second child's independence and the loan both end when Jun Hao is 58, so the split collapses into one policy of S$1,148,000 to 58. And he notes in column C that support would probably rise above S$42,000 with two children, so the true gap is likely higher still.
That single test told Jun Hao and Mei something useful: if a second child is likely in the next few years, buying cover now that is easy to increase later, or leaving room in the budget, may be better than locking in the smaller figure.
One tab with the three blocks, every input typed and every total a formula, assumptions noted beside each input, the gap shown with and without group cover, and the term picked by formula. A second tab with your one test and a short note on how much the gap moved and why. That gap and term go into your household plan in lesson 7.5, so keep the file where you'll find it.
Build the sum assured sheet for your household, write the cover gap and term, and note how the gap changes under one test scenario.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).