This article on building a financial spreadsheet is by former hedge fund manager Lars Kroijer, an occasional contributor to Monevator. He also wrote Investing Demystified.

We’re living in a world where financial advice is omnipresent online. All kinds of calculators and portfolio management systems are easy to find and free to use.

You might wonder then why you should bother to do any maths or projections of your own. Something on the Internet can take care of all that, right?

I have a different perspective. I fear it’s now so easy to avoid doing any real work on our financial planning that many of us have lost – or never gained – a real understanding of how and why all the numbers fit together.

People may intuitively understand that they need to put money aside over the long run in order to benefit from the power of compounding. But finding out how much and for how long? It’s a mystery that they use websites, software and apps, or financial advisors to solve.

That’s a shame, because building this kind of financial modelling spreadsheet is pretty simple if you know how. And by demystifying the financial modelling, you’ll get a much better handle on your own circumstances.

The answer to all some of your questions

Appreciating that anybody who has not spent decades in finance may find the task daunting, I decided to build a DIY financial model spreadsheet from a completely blank slate.

To get started, I assume you are a 23-year old who is putting money together for retirement at age 67. I then built the first version of the spreadsheet to start answering some interesting questions.

For example, how much will you end up with if you put aside £1,000 a year in equities? What about £2,000? What if equities compound not at 3%, but at 6%? What if you don’t want to put it all in equities, but choose to diversify some of your money into a lower risk asset? How would that impact your expected outcome?

These may all be random sounding numbers. But the point is you can easily change them to reflect your own questions by simply entering a new number in the model once you’re up and running.

You can also keep adding complexity to your model. And by working through the spreadsheet as you do so, you will naturally be asking a lot of the right questions.

Say we want to understand how the risk of the equity markets will impact the range of outcomes that we can expect from a long-term exposure to shares. That’s a very fair thing to ask, without an easy answer! Doing so involves questioning what standard deviation to use – and if you can even use the standard deviation, fat tails, and so on.

There are often no simple answers, but you will typically be better off having thought through these issues rather than ignoring them.

What should you include in your spreadsheet?

If you ignore my offer to build a spreadsheet along with me on my YouTube channel and instead decide to build your own alone, please at least include the following:

• What is the annual contribution / use of capital?
• What are you invested in?
• What is the expected compound annual return (CAGR).
• What is the average annual return? (And do you understand the difference?)
• What is the risk of the returns? How do you best model that? (The standard deviation is a good start, but it has some issues.)
• Incorporate inflation (or perhaps make returns ‘real’, which is to say inflation-adjusted).
• You can also include fees, transaction costs, and potentially taxes.

Understand the challenge of adding many asset classes. Doing so introduces correlation between all the different asset classes, which is hard to predict and constantly changing, sometimes as a result of market changes.

Thinking through these issues will lead to you appreciating some of the biggest challenges in predicting portfolio return ranges (and in trying to model events like the banking crisis, if you’re feeling ambitious).

Simpler than it looks

Please don’t be overwhelmed. If you build your spreadsheet slowly and methodically you might be surprised by what a flexible and useful tool you can create.

And at the end of the day, I think there is a big advantage to appreciating how simple a lot of the financial software packages you might otherwise use really are.

When you are done building your spreadsheet, I believe you’ll understand much more about your risk and what you can expect in the future than you would by studying the outputs page from an online broker, or a summary sheet from a financial planner.

That knowledge should serve you well.

Video on building your own financial spreadsheet

Below is the first video in a series that should help you get started with building your own model spreadsheet. You will also find other relevant videos on my YouTube channel.

As ever with my Monevator articles, I’d really like to hear your views. Please comment below on what you’d like to see me add to the model or explain better. I’d like to try to make the model as accessible and useful as possible. Your feedback here would be very helpful!

Lars Kroijer’s book Investing Demystified is available from Amazon. He is donating any money earned to medical research. He also wrote Confessions of a Hedge Fund Manager.

What caught my eye this week.

I have a soft spot for Baby Boomers who appreciate that the younger generation really does have it much harder than they did, financially speaking.

Boomers like Jim, who writes on his blog SexHealthMoneyDeath that:

…if I was a youth today, I’d be damn angry about it. Especially if I read blogs like mine where Baby Boomers in their fifties ponder early retirement and the most tax efficient ways to drawdown their pensions while wondering if they can be bothered taking two long haul holidays a year?

A Baby Boomer, that is, like me who was given a fantastic free education, who took out a mortgage as soon as he started working (required deposit, fifteen hundred quid and a 95% loan) and sat back to watch property rocket over his working lifetime.

Nice work, if you can get it. Which you no longer can.

But while there’s a little bit more inter-generational understanding these days – at least in terms of words, if not actions – Millennials are still regularly hearing guff like the reason they can’t buy a house is because they eat too much smashed avocado on toast. Seriously.

The LA Times notes that the Australian millionaire who doled out that advice actually started his own business with a $34,000 handout from his grandfather.

Good on him for making a success of it, but equally $34,000 buys a lot of avocado on toast – even at the $19 a pop he seems to find it selling for.

At least the youth have Buzzfeed, which collected a super set of Tweets to cheer them up, such as:

Millennials are so spoilt with their smartphones & tablets. All we had at their age was the ability to buy property in Central London.

— tom jamieson (@jamiesont) October 18, 2015

[continue reading…]

Should you see a worker in the financial services sector kicking a wall today, or muttering slowly whilst tearing up a newspaper, or taking off their shoes and walking into the sea, Reggie Perrin style, then spare a thought (and perhaps a few coppers) for the poor dabs.

Because fund giant Vanguard – famed for its cheap trackers and its all-in-one LifeStrategy products – is now enabling UK investors to invest in its own funds directly.

This means we can now put money into its funds without having to use a third-party platform such as Hargreaves Lansdown.

And with Hargreaves’ shares having fallen as much as 5% on the news, City traders seem to believe Vanguard’s launch will spark a renewed price war.

Which is nice – for us investors, anyway. Not so good for rivals.

• Vanguard says it will levy an annual fee of 0.15% on up to £250,000 invested with it.

• There will be no account fee on the excess above £250,000.

• The platform fee is thus capped at £375 a year for even the largest portfolios.

• The minimum lump sum investment is £500. Monthly savings start at £100 a month.

• There are no initial or dealing charges, and no cost levied by Vanguard for transfers in.

Vanguard’s 0.15% platform fee will levied be on top of the ongoing charges that you pay for whatever Vanguard funds you own.

For example, if you opened your Vanguard account by investing £20,000 into its 60% LifeStrategy fund, you’ll pay:

Fund charge of 0.22% x £20,000 = £44

Account fee of 0.15% x £20,000 = £30

Total annual fee = £74

Some Vanguard funds boast even lower fees. For instance, its FTSE 100 Index Unit Trust charges just 0.06%. So £20,000 invested into that fund on the Vanguard platform would cost a total annual levy of 0.21%, or £42 a year.

Vanguard is currently offering ISAs and non-ISA accounts, but no SIPP. It plans to add a SIPP option to the platform “within the next year”.

The new Vanguard site has the website address www.vanguardinvestor.co.uk. (Vanguard says it will eventually retire its existing UK website).

The FAQ is pretty comprehensive, if you have any particular questions.

The Vanguard platform looks hard-to-beat cheap

Vanguard is not the first fund provider to enable us to invest directly into its funds without using a platform.

For instance, I’ve held a small proportion of my portfolio directly in Legal & General tracker funds for many years. (And no, it’s not cheapest! Even us investment bloggers have our foibles. Besides, I favour platform diversification).

But Vanguard’s offering is more transparent (and cheaper) than Legal & General’s, as fans of the US group would expect.

With Legal & General you pay much higher ongoing charges than with some competitor trackers, but there’s no platform fee. In contrast, Vanguard is explicitly breaking out the platform fee. This enables you to compare costs more directly.

The big question then: How do those costs compare with the others you’ll find in our comparison table?

Well, I’m not the Monevator expert on platform charges. Also, anyone who has tried to navigate the nightmare of comparing one particular portfolio invested on a particular platform with another platform knows there can be more quirks, hidden corners, and labyrinthine passages than you’d find in a medieval city.

Caveats and costs lurk around every corner!

That said, I believe Vanguard’s new offer looks pretty darn compelling.

For those wanting to hold Vanguard funds it looks substantially cheaper to do so directly via its new investment platform than via a percentage-fee charging rival.

For fixed-fee brokers, it could still be cheaper to go with one of the alternatives if you have a large portfolio. You’ll have to run your own numbers.

But for small portfolios of Vanguard funds, going direct again looks cheapest.

Holding Vanguard ETFs with rivals instead of Vanguard funds ((That is, unit trusts aka mutual funds aka OEICS.)) with Vanguard could also be cheaper for some investors with large portfolios.

For instance, our table notes that AJ Bell caps its quarterly platform fee for shares (including ETFs) at a maximum of £7.50 for an ISA, or £25 for a SIPP. That means platform charges are capped at £30 and £100 a year respectively.

Do remember that’s before ETF dealing fees and the ongoing charges on your Vanguard ETFs. Still, there could be savings to be had on larger portfolios.

Low cost barbarians at the gate

To be honest it’d be nice to find some bigger holes to poke in this new Vanguard platform.

You see, for our lauding its cheap funds – and for celebrating the pressure it puts on the wider industry to drive down costs – we’ve been accused in the past of being a sneaky Vanguard promotional site.

In reality, Vanguard has never even advertised with us. And it didn’t care to send us a press release to announce its new service.

Ho hum.

Fact is though, with this very competitively priced platform, Vanguard is likely to provide massive disruption to the incumbents.

While we like to argue the toss between this and that tracker fund, the average person would do fine just investing in Vanguard’s cheap passive offerings, or even its all-in-one options such as LifeStrategy and its target date funds.

Heck, it even has (low cost) active funds if you really must.

Rivals have some thinking to do. The lack of a Vanguard SIPP account at launch gives them some breathing space there. Some may be able to find a niche serving harder-to-please clients, such as expat investors.

But most are probably going to fall back on star active fund managers and heavy advertising to try to keep the Vanguard threat at bay. They should be aided in this by rival fund groups, who have just as much to lose from Vanguard gobbling up yet more market share.

Low-cost rival platforms are in trouble because they’re not cheap enough. Higher-cost offerings touting wunderkind active funds have the flow of history against them. Only a handful of these platforms were making good profits to begin with.

Monevator readers are in clover!

Finally, one of my spies in The City has sent in this documentary footage of the kerfuffle that the launch has already kicked off. (Sensitive readers should look away now).