You will be able to calculate your return in SGD so that FX moves and withholding are included.
Rachel's broker app says her US holdings are up 9% this year. Her own spreadsheet, which she built from her records in lesson 7.1, says something else. She wants to know which number is true, and why they differ.
Both are true answers to different questions. The app answers what the holdings did in US dollars, perhaps with a simple calculation that ignores when the money went in. Rachel cares about what her Singapore dollars did, since she earns and spends in Singapore dollars. This lesson shows you how to get that number.
The first rule is to record every purchase in both currencies: the US dollar cost, and the Singapore dollar amount that actually left your account to pay for it. Take the second from your conversion record or trade confirmation.
Do not convert old purchases at today's exchange rate. That would hide the currency move from lesson 2.3, Currency risk is not the same as conversion cost, and give you a return that never happened. Using the rate you actually got also captures the spread you paid, because it is built into that rate.
Record each dividend at the net amount you received, after withholding. If you record the gross figure, your income looks 30% higher than it was, as lesson 3.1 showed. Convert it to Singapore dollars at the rate you converted at, or at the day's rate if you kept it in US dollars, and note which.
Your Singapore dollar return then includes everything: price changes, net dividends, currency moves, and every cost you paid along the way, because each one changed either what went out or what came back.
Imagine you put in S$4,000 in January and another S$4,000 in July. At year end the holding is worth S$8,700. A simple calculation says you made S$700 on S$8,000, or 8.75%. But the July money was only invested for half the year. The simple figure treats it as if it had been there all year, so it understates how hard the money worked.
The fix is a money-weighted return: the single yearly rate that, applied to each sum for exactly as long as it was invested, produces the final value. In a spreadsheet, the XIRR function calculates it from a list of dates and amounts. Both Excel and Google Sheets have it.
Here are Rachel's made-up figures for one year.
On 15 January she bought US$3,000 of a fund, converting at S$1.34, so S$4,020 left her account. On 15 July she bought another US$3,000 at S$1.36, costing S$4,080. On 15 December she received a dividend of US$60 gross, with US$18 withheld, leaving US$42 net, which she converted at S$1.35 to S$56.70. On 31 December her holding was worth US$6,500, and the rate was S$1.33, so it was worth S$8,645.
In her sheet, money going in is negative and money coming back is positive. The two purchases are minus S$4,020 and minus S$4,080. The dividend is plus S$56.70. The year-end value goes in as if she had sold on 31 December: plus S$8,645. Each amount sits next to its date.
The XIRR of those four rows is about 10.6% a year.
Compare the other answers. In US dollars, she put in US$6,000 and ended with US$6,500 plus US$42 of dividends, a simple gain of about 9.0%. In Singapore dollars, ignoring timing, she put in S$8,100 and ended with S$8,701.70, a simple gain of about 7.4%. The money-weighted figure is higher than the simple Singapore dollar figure because half her money was invested for only half the year and did well in that time.
Three numbers for one year. The XIRR in Singapore dollars is the one that answers Rachel's question, because it counts the currency, the withholding, the costs and the timing.
The method only works if the rows are complete. Every purchase, sale, dividend and fee that moved money in or out needs a row. A missed purchase makes your return look better than it was, and a missed dividend makes it look worse. That is why the records in lesson 7.1 come first.
If you add money regularly, XIRR handles it without any extra work. Each contribution is a row with its date. That is the main reason to use it over the simple calculation, which gets more wrong the more often you add money.
Your activity is to enter your last year of overseas transactions into a sheet with a date column and a Singapore dollar amount column, add the year-end value as the final row, and calculate your XIRR.
Enter your last year of overseas transactions into a sheet with dates and SGD amounts, then calculate your XIRR.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).