How to Build Your Own Financial Spreadsheet

**Today’s guest post comes from Lars Kroijer, a former hedge fund manager based in London who has contributed to my site a few times now.

In a world where online financial advice is omnipotent and financial calculators and portfolio management systems are free and easy to find, you might wonder why you should bother to do any math or projections on your own. The internet can do that for you.

I have a slightly different perspective. It has become so easy to avoid doing any real work on our financial planning that I fear many have lost (or failed to ever gain) the ability to understand how and why all the numbers fit together. They may intuitively understand that they need to put money together over the long run to gain the benefit of compounding, but finding out how much, and for how long is a mystery that they use the internet, bought software, or a financial advisor to solve. I think that is a shame, as building this kind of financial modelling spreadsheet is pretty simple if you know how. And by demystifying the financial modelling you’ll much better appreciate your specific circumstances.

Why you should build your own financial spreadsheets

Appreciating that someone who does not have decades of background in finance may find the task daunting I decided to build a financial spreadsheet starting with a completely blank spreadsheet.

I start out assuming you are a 23-year old who is putting money together for retirement at age 67. How much will you be left with if you put aside $1,000 a year for investing? What about $2,000. What if your investments compound not at 3%, but at 6%? What if you don’t want if you have a lower risk portfolio? How would that impact your expected outcome? But those are actually pretty random assumptions that in any case can be changed by simply entering a new number in the model.

We can keep adding complexity to the model, and by working through the spreadsheet you will naturally be asking a lot of the right questions. As an example, we may want to understand how the risk of the equity markets will impact the range of outcomes we can expect from a long-term exposure to those markets. A very fair question without an easy answer. Answering that question involves questioning what standard deviation to use if you can even really use the standard deviation, fat tails, and so on. There are often no easy answers, but you will typically be far better off having thought of these issues than ignoring them.

What to include in your financial spreadsheet

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

  • What is annual contribution / use of capital?
  • What do you invest in?
  • What is the expected compounding return (CAGR). What is the average annual return (understand the difference)?
  • What is the risk of the returns? How do you best model that? The standard deviation is a good start, but has some issues.
  • Make sure to incorporate inflation (perhaps make returns “real”), fees, transaction costs, and potentially taxes. But even if you ignore these the model is well worth building.
  • Understand the challenges of adding many asset classes. It introduces correlation between all the different asset classes which is hard to predict and constantly changing, sometimes as a result of market changes. But understanding these issues will also lead you to understand some of the biggest challenges in predicting portfolio return ranges (and things like the banking crises if you are feeling ambitious).

But please don’t be overwhelmed. At the end of the day, I think there is a big advantage to appreciating how simple a lot of the financial software packages really are. I believe that when we are done with the spreadsheet you’ll understand 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. This will serve you well.

You can find the first video in the series here. Please try to follow the series to the end for to fully understand how to invest.

Final word

As always, I’d really like to hear your views. Please comment on what you’d like to see added to the model or explained better – either here or on the YouTube videos. I’d like to try to make the model as accessible and useful as possible and your feedback would be greatly appreciated.

By |2017-12-02T20:13:27+00:00November 23rd, 2017|My Money, Personal Finance|

Leave A Comment