You will be able to combine one-off and yearly charges in a single model that includes regular monthly investing.
Nobody actually invests the way lesson 7.1, Why a 1% yearly gap becomes a large sum, assumed. Wei Ling did not put in S$10,000 once and walk away. She started with a lump sum and then added a few hundred dollars a month through the bank's app, and every top-up carried a sales charge. Arjun plans the same with his robo-advisor, minus the sales charge. To compare their real costs, the model has to handle a starting sum, monthly top-ups, a one-off charge on each purchase and a yearly cost, all at once.
This lesson builds that model. Every figure is an example, and the return is an assumption that nobody can promise.
Lesson 2.1, One-off charges: buying, switching and selling, showed that a sales charge comes off the money before it buys units. If it is charged on every purchase, it comes off each monthly top-up as well as the starting sum.
So in the model, a sales charge reduces two inputs. A 2% sales charge turns a S$10,000 starting sum into S$9,800 invested, and a S$300 monthly top-up into S$294 invested. If an option has no charge on top-ups, only the starting sum is reduced. If it charges a flat fee per transaction, work out what percentage of each purchase that fee is and use that.
Yearly charges, the expense ratio plus any platform or advisory fee, come out of the balance every year. As in lesson 7.1, the simple way to model them is to subtract them from the return. If the return before costs is 6% a year and total yearly costs are 1.75%, the money grows at 4.25% a year.
Because you are now saving monthly, convert that to a monthly rate by dividing by 12, as in How money works, lesson 6.2, Future value: what a sum or monthly saving grows to. The net monthly rate is the return minus total yearly cost, divided by 12. For 4.25% a year, that is about 0.354% a month.
The FV function from that same lesson does the rest. It takes the rate per period, the number of periods, the payment each period and the starting sum, with money you pay in entered as negatives.
Take Wei Ling's bank fund from module 2 and Arjun's robo portfolio from module 4. Both start with S$10,000, add S$300 a month for 20 years, which is 240 months, and earn 6% a year before costs.
Bank fund: 2% sales charge on every purchase, total yearly cost 1.75%. Type =FV((6%-1.75%)/12, 240, -300*0.98, -10000*0.98). The result is S$133,809.15.
Robo portfolio: no sales charge, all-in yearly cost 0.78%. Type =FV((6%-0.78%)/12, 240, -300, -10000). The result is S$154,833.21.
Both put in the same money: S$10,000 plus 240 payments of S$300, which is S$82,000.
Notice that both options use the same 6% before costs. This is the most important rule of the model. If you give one option 7% because its marketing says so, and another 5%, the model stops being a cost comparison and becomes a guess about which will perform better. Keep the gross return identical for every option, so that cost is the only thing that differs.
That also tells you how to read the result. The model does not say what either option will be worth in 20 years. It says how much the difference in costs alone is worth, if both earn the same before costs. You can change the 6% to 4% or 8% and rerun it. The dollar figures change, but the cheaper option stays ahead.
To turn the results into a cost, add one more line: the same contributions with no costs at all. Type =FV(6%/12, 240, -300, -10000). The result is S$171,714.31. No real option gets you this, so treat it as a yardstick.
Subtract each option from it. The bank fund ends S$37,905.16 short of the yardstick. The robo portfolio ends S$16,881.10 short. That shortfall is the total cost of each option over 20 years. It includes the charges and also the growth those charges would have earned. The gap between the two options is S$21,024.06, on the same S$82,000 paid in.
Wei Ling looked at that last number for a while. She had thought of her fund as costing 2% when she bought and something under 2% a year after that, and both statements were true.
A good layout puts each option in its own column, with rows for starting sum, monthly contribution, sales charge on purchases, total yearly cost, gross return, years, ending value and the shortfall against the no-cost line. Then every number you might change is in a cell of its own, and the FV formula refers to those cells.
In the activity below you will extend your spreadsheet from lesson 7.1 in exactly this way, so it can take a starting sum, a monthly contribution, a sales charge and a yearly cost for each option.
Extend your spreadsheet to handle a starting sum, a monthly contribution, a sales charge and a yearly cost for each option.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).