You will build a returns tab that calculates your own account's returns under the course convention.
Marcus kept contract notes and statements for years without ever turning them into a single number he trusted. This exercise does that. You'll build the first tab of the investment workbook you'll keep for the rest of the course, and by the end it will tell you three things: what your investments returned, what your money earned and how that compares with your benchmark.
Set aside about forty minutes, plus time to dig out statements. Use one account, or all your cash investment accounts together if they share a strategy. Leave CPF and SRS out for now unless you invest them the same way.
Open a new sheet and name the tab Returns. You need one row per date on which something happened: each deposit or withdrawal, and each year-end. These are the columns:
Date, cash flow in SGD (deposits negative, withdrawals positive), account value in SGD on that date before the cash flow, account value after the cash flow, benchmark value in SGD, and a notes column
The value before each cash flow is what lets you work out a time-weighted return, so don't skip it. Take account values from your broker's statements. Convert any foreign holdings with the rate source you chose in lesson 1.3, Returns in Singapore dollars when the asset is priced in US dollars, and note the source once at the top of the tab.
Before you use your own numbers, enter these made-up figures for Marcus and check that your formulas give the same answers. A mistake is much easier to find when you know the answer.
Marcus opened with S$70,000 at the start of year one. He added S$70,000 at the start of year two, then S$8,000 at the start of each of years three, four and five. His portfolio's yearly returns were 14%, minus 18%, 16%, 9% and 11%, which left his year-end values at S$79,800, S$122,836, S$151,770, S$174,149 and S$202,185. He put in S$164,000 in total.
Yearly return. For each year, divide the value at year-end by the value at the start of the year after that year's deposit, and subtract one. For year two that is S$122,836 divided by S$149,800, minus one: minus 18%. This is the time-weighted return for the year, because the deposit sits at the boundary.
Arithmetic average. AVERAGE of the five yearly returns gives 6.4%. You calculate it only to show yourself how much it flatters, as lesson 1.1 explained.
Geometric average. Multiply one plus each yearly return, take the fifth root and subtract one. In a spreadsheet, that's the product of the five growth factors raised to the power of 0.2, minus one. Marcus gets about 5.6% a year. This is his time-weighted return, the one a fund would report.
Money-weighted return. In a separate block, list each deposit as a negative number with its date, then the final value of S$202,185 as a positive number dated the last day of year five. XIRR on that block gives about 5.2% a year. It's lower than the geometric average because the S$70,000 that arrived in year two caught the 18% fall, the effect you saw in lesson 1.4, Time-weighted vs money-weighted returns on your own account.
Use the blended benchmark you wrote down in lesson 1.6, Choose a benchmark that matches what you actually hold. For each year, multiply each index's total return in SGD by its weight and add them up. Marcus's blend of 70% world equity, 10% STI and 20% Singapore government bonds returned, in made-up figures, 9.6%, minus 13.4%, 15.3%, 7.6% and 10.6% over the five years. Its geometric average is about 5.4% a year.
Then ask the second question: what would Marcus's actual deposits have earned in the benchmark? Run the same cash flows through the benchmark's yearly returns. His S$164,000 would have grown to about S$203,700, and the XIRR of those flows is about 5.3% a year.
Put the four numbers together. Time-weighted, Marcus returned about 5.6% a year against 5.4% for the benchmark, so his choices of holdings beat the blend by about 0.2 points a year. Money-weighted, he earned about 5.2% against about 5.3% for the same deposits in the benchmark, so after timing he trailed it by about 0.2 points. Same five years, opposite verdicts, and both are true: his picks did slightly better than the index mix, while his timing of the big deposit cost slightly more than that.
The sentence Marcus wrote under his tab was this: "Over five years my holdings beat my benchmark by about 0.2 points a year, but my money earned about 0.2 points a year less than the same deposits would have in the benchmark, mostly because of when I added the S$70,000."
That is the standard to aim for. It names both numbers and the reason for the gap, and it doesn't claim more than five years of data can support.
A finished tab has every cash flow for the period with its date, a value before and after each one, yearly returns, both averages, an XIRR, the benchmark's yearly returns and XIRR on your flows, and one sentence. If your XIRR comes out wildly different from your geometric average, look first for a missing deposit or a withdrawal entered with the wrong sign.
Now replace Marcus's figures with your own, working from your contract notes and statements, and write your sentence.
Build the returns tab from your own contract notes and statements and write one sentence on how your result compares with your benchmark.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).