This article on building a financial spreadsheet is by former hedge fund manager Lars Kroijer [1], an occasional contributor [2] to Monevator. He also wrote Investing Demystified [3].
We’re living in a world where financial advice is omnipresent online. All kinds of calculators [4] 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 [5] 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 [6]. 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 [7] 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 [8] 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 [3] is available from Amazon. He is donating any money earned to medical research. He also wrote Confessions of a Hedge Fund Manager [9].