Build the returns tab of your investment workbook

Build a returns tab that works out your own account's returns using the course convention.

Open a new sheet in your investment workbook, name the tab Returns, and put your broker's statements and contract notes beside you. This tab is the first one in the workbook you will keep for the rest of the course. The exercise takes about forty minutes, plus whatever time it takes to find the statements.

When it's done, the tab shows what your investments returned, what your money earned, and how each of those compares with your benchmark, with one sentence underneath that sums up the result.

Decide first which money goes in. Use one account, or use all your cash investment accounts together if they follow the same strategy. Leave CPF and SRS out for now, unless you invest them the same way as the cash accounts.

Setting up the columns

At the top of the tab, write down the rate source you chose in lesson 1.3, "Returns in Singapore dollars when the asset is priced in US dollars". You only need to note it once, and you use it to convert every foreign holding to SGD. Then set up these columns:

Date Cash flow in SGD (deposits negative, withdrawals positive) Account value in SGD before the cash flow Account value after the cash flow Benchmark value in SGD Notes

Add one row for each date on which something happened: each deposit, each withdrawal and each year-end. Take the account values from your broker's statements. The value before each cash flow is easy to leave blank, but you need it to work out a time-weighted return, so fill it in on every row.

Testing the formulas on Marcus's figures

Before you enter your own numbers, enter Marcus's and check that your formulas give his answers, because a mistake is much easier to find when you already know what the answer should be. Marcus is an invented investor, and his figures and the benchmark figures below are all made-up examples.

For years, Marcus kept his contract notes and statements but never turned them into a single number he trusted. Over five years he deposited S$164,000 in total. He opened his account with S$70,000 at the start of year one. That year returned 14%, and the account ended the year at S$79,800. At the start of year two he added another S$70,000, and year two returned minus 18%, ending at S$122,836. He then added S$8,000 at the start of each of years three, four and five, and those years returned 16%, 9% and 11%, ending at S$151,770, S$174,149 and S$202,185.

Each yearly return is the value at year-end divided by the value at the start of the year after that year's deposit, minus 1. For year two, the starting value is S$79,800 + S$70,000 = S$149,800, so the return is S$122,836 / S$149,800 - 1 = minus 18%. Because the deposit sits at the boundary between years, this figure is the time-weighted return for the year.

Working out the averages and the XIRR

Below the yearly returns, add rows for the calculated figures. The first is the arithmetic average, using AVERAGE on the five yearly returns. You calculate it only to see how much it flatters the result, as lesson 1.1 explained, and for Marcus it comes to 6.4%.

The second is the geometric average. Turn each yearly return into a growth factor (1 + the return), multiply the factors together, raise the product to the power 1/n, and subtract 1. For five years the power is 0.2. This gives the time-weighted return, the figure a fund would report, which measures how the holdings performed. Marcus's geometric average is about 5.6% a year.

The third is the money-weighted return. In a separate block, list each deposit as a negative number with its date. Add the final value as a positive number dated the last day of the period, then run XIRR on the block. This gives the money-weighted return, which measures what your money earned, including the effect of when you put it in. For Marcus, the final value is S$202,185 dated the last day of year five, and XIRR gives about 5.2% a year.

Marcus's money-weighted return is lower than his geometric average because his large year-two deposit arrived just in time for that year's fall, the pattern you saw in lesson 1.4, "Time-weighted vs money-weighted returns on your own account". If your XIRR comes out wildly different from your geometric average, first look for a missing deposit or a withdrawal entered with the wrong sign.

Comparing with your blended benchmark

Take the blended benchmark you wrote down in lesson 1.6, "Choose a benchmark that matches what you actually hold". Its return for each year is the sum of each index's total return in SGD multiplied by its weight. Marcus's blend is 70% world equity, 10% STI and 20% Singapore government bonds. Its yearly returns are 9.6%, minus 13.4%, 15.3%, 7.6% and 10.6%, and its geometric average is about 5.4% a year.

The benchmark also answers a second question: what would your actual deposits have earned if they had gone into the blend? To find out, run the same cash flows through the benchmark's yearly returns and take the XIRR. In the benchmark, Marcus's S$164,000 grows to about S$203,700, an XIRR of about 5.3% a year.

You now have four numbers. The time-weighted comparison tells you whether your choice of holdings beat the blend, and the money-weighted comparison tells you what your timing did. Over the same period the two can give opposite verdicts, and both are true. On time-weighted returns, Marcus's 5.6% against the benchmark's 5.4% means his holdings beat the blend by about 0.2 points a year. On money-weighted returns, his 5.2% against the benchmark's 5.3% means he trailed by about 0.2 points a year once timing is counted, because the S$70,000 that caught the 18% fall cost him slightly more than his holdings gained.

Writing your summary sentence

A good summary sentence names both numbers and the reason for the gap, and claims no more than five years of data can support. Marcus wrote his under the tab. It says his holdings beat the benchmark by about 0.2 points a year, his money earned about 0.2 points a year less than the same deposits would have earned in the benchmark, and the gap was mostly due to when he added the S$70,000.

A finished tab has every cash flow in the period with its date, the values before and after each cash flow, the yearly returns, both averages, an XIRR, the benchmark's yearly returns, the benchmark XIRR on your own flows, and one summary sentence.

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. Then write one sentence on how your result compares with your benchmark.

Course

Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).