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.
Comments on this entry are closed.
Good point about modelling future scenarios. I put together a simple spreadsheet when trying to work out if we could afford school fees, by releasing a lump sum if we sold our home in London and lived in a tent (well, bungalow in Norfolk, same difference). Meant I could plug in current school fees and expected higher rate of inflation, compare to different rates of return depending on whether I saved or invested, and project forwards. Grand total was eye-watering – but lower than if we’d attempted to pay out of income.
I’m one of those that rely on the various online calculators and am often confused by the variety of outputs. Often wanted to try to build a spreadsheet and this could be a really useful. Look forward to trying out the demo over the bank holiday , thanks.
I may break out Excel and give this a go but if you are lazy like me go with Morningstar Portfolio Manager. Even the free version is pretty good.
Mind you I could do with examples/explanations of some of its features so if anyone knows of tutorials, videos etc for the UK version pl post.
Seamus
I have a multi-page spreadsheet for my investments, and did have one for my future planning, but found it too limiting for what-if scenarios, as information propagation was getting too tricky. I re-wrote it as a php program with a web interface and graphical output. I did show it to other people, but there was little interest, so I withdrew it from the world
I’ve thought about building my own speadsheet but am a bit of a loss in terms of finding what kind of annuity my pension will be able to buy. Anyone know any good links to work this out dependant on pot size?
@YamiKuriboh,
To get started maybe have a look here:
https://www.moneyadviceservice.org.uk/en/tools/annuities
or here
http://www.which.co.uk/money/pensions-and-retirement/options-for-cashing-in-your-pensions/guides/annuities/annuity-rates
Regards, Pinch
Here is a sheet assuming a constant real rate of return. Not financial advice! This is similar (not identical) to one I use myself (age may have been altered). It covers drawdown to age 100 so shows what you should aim to have at retirement.
https://docs.google.com/spreadsheets/d/1xXgB-8G7M8sQQG3yDy0EsoB966csabxu3_8df6Mcz34/edit?usp=sharing
Couldn’t agree more with you Lars!
It’s one thing looking at some figures that are magically spit out from some software, but creating the formulae yourself to realise how they all link together takes your understanding to a whole new meaning. Just make sure you get them right I guess 🙂
A fascinating series of videos. I’ve not used excel for this kind of statistical financial modelling, so have learned a few new tricks already.
I think an episode on parameter choice and result interpretation is needed though; my current conclusion based on the first 8 episodes is that I will never be able to afford to retire even if I become a multi-millionaire! (or die promptly after retirement)
It’s a pet hate of mine also that most of the calculators out there, just spew about a number. Without actually working out where that number has come from it’s at best useless at worst dangerous. It’s often not obvious if amounts are in current nominal terms, future nominal terms, future terms allowing for inflation etc.
So I’m all for suggesting people doing some calculations, whether it be on a back of an envelope or using a spreadsheet.
But of course using a spreadsheet helps to build on your financial model over time and look at scenarios. The back of the envelope calculation usually ends up in recycling with the corn flakes packet. The other real advantage of creating your own spreadsheet is that you can tailor it to your own position and leave out all the income sources and complications that don’t apply to you.
There is no getting round that you probably need some numeracy skills to do this, or access to a friend with some numeracy skills. But the emphasis should be on SOME numeracy skills, and I think numeracy level may determine how far you develop the spreadsheet.
I’ve got my own excel spreadsheet. It has developed into a bit of a beast. But it is an important concept that, at least to start with, however numerate you are, you should attempt to make some simplifying assumptions and keep it simple and not lose sight of what you are trying to achieve. I’ve also produced an excel spreadsheet for some friends to help with their planning.
As an example of my approach to creating a spreadsheet, I’ll assume I am looking at this as a financial independence spreadsheet (so I am ignoring future income such as earnings).
The starting point is that I list all my assets and future income on a worksheet. So it might includes state pension, defined benefit pensions and investments and savings.
I then have a worksheet to work out what income I will need in retirement. I do this by breaking down all my expenditure into categories. I do this myself but this is one area where some of the tools out there work quite well such as
https://www.moneyadviceservice.org.uk/en/tools/budget-planner
Now start your future projections (year 1 is the next year, year 2 is the following year and so on).
It is important to keep everything in real terms.
So for example if we are looking at state pension then assume that it will go up with inflation say (for explanation purposes at least), and put in year n (where n leads to an age above SPA) your state pension amount in TODAY’S terms say £159.55pw (but don’t guess get a state pension statement) and put the identical figure of £159.55pw in year n+1 also.
Where income isn’t fully inflation rated e.g. some deferred final salary pensions, then identify the extent of this both before and after the scheme pension date) so you can adjust in subsequent years.
You can then start to build up a simple cashflow model of future years.
Income is state pension, final salary pensions etc. many of these will only apply from a later year e.g. SPA so may be zero in year 1.
And expenditure is what you are need to live on each year (from the budgeting tool for example). This figure may be identical in every year 1,2,3 etc (as we are doing this all in real terms so factoring out inflation). From this work out the shortfall of income over expenditure in year 1.
Then make a deduction from your investments to cover the shorfall of income over expenditure in year 1.
Now move to year 2:
You need an input % of your investment return above inflation which you can change (perhaps 2%). And then your value of investments in year 2 = investments in year 1 (after deduction for shortfall of income over expenditure) x 1.02 (say if 2% is the real return)
As indicated earlier your income amounts in year n+1 are identical to year n where they are inflation linked ish (e.g state pension) or adjusted down if they aren’t (by the annual amount below inflation they may increase).
From this you can work out yearly cashflow figures and how much of your investments are left .
The explanation is oversimplified but hopefully you get the idea.
Doing this sort of projection can give you huge insight into your financial position. It can tell you if you have reached financial independence (and on what future real returns assumption that that judgement is based). By playing with the real return assumption you can see how vulnerable you are to a period of poor investment returns which will help with your asset allocation. And so on.
My own spreadsheet includes an updated figure of the past real return achieved on my investments (in my case 4.8% above RPI inflation currently). I also calculate what my investments would have grown to if a 0% real return had been achieved. Most of the value of my current investment value comes from real returns above inflation, even after factoring out inflation. Knock 2 – 2.5% say off the real return achieved and that would have had a massive affect on my current investment value in my case. I think of that as a typical outcome had I not used a passive strategy (because of the higher costs). If we assume future real investment returns will be lower in the future then the affect will be even worse in the future.
The hard thing I found with spreadsheets was IF THEN clauses, as too often you had decision points based on age or other thresholds, so it wasn’t a simple matter of rolling forward the row above with a %age change. As I made my stash as a Fortran programmer on supercomputers, I turned to a DO loop solution which allowed decisions to be made in sequence, allowing better flows of cash in and out of ISAs and SIPPS depending on age. As a concession to the modern world I wrote it in Javascript, but is a Fortran program in style.
Nice video – I am now going to adopt the investment banker style of blue inputs
Loved the book, loving the videos, chapeau Lars..
In my own spreadsheet the roll up IS very straightforward, so for example to copy the key formula across completely unadjusted, L38 (the investments remaining figure for year n+1) is
=L37*(1+$C$4)-L$24+$E38
where
l37 = investments at end of year n
c4 is the real return % assumed on investments
l24 is annual expenditure in year n+1 (in todays terms)
e38 = total income from state pensions + DB pensions net of tax in year n+1 (in todays terms)
e38 itself is calculated by adding the todays terms income amounts that are set out in columns by age and applying current tax thresholds (which assumes that personal allowances go up with inflation)
The column of todays terms income amount for state pension for example is zero up to SPA and £159pw *52 after SPA.
It works in my case because I can extract all the money from my SIPP within my personal allowance and so I can assume all my investments can be cashed in to top up income to the expenditure required level, with no further tax to pay. So I just need to allow for tax on state pensions and DB pensions
But in any case if my SIPP had been bigger, it would be easy to make an adjustment for tax on getting the money out of the SIPP. That adjustment will depend on circumstances, but that’s where a personalised spreadsheet comes into its own, as the simplifying assumption needed to keep things simple without losing any material accuracy varies from individual to individual.
The real advantage of doing it this way is that you make it very difficult to make material. errors. There is a column of income and expenditure payments which are easy to check and once you’ve checked them , it is just down to getting the basic formula above correct.
Also need to overlay systemic risk on top, those that can’t be modelled with annually sampled random numbers. As LK points out, it’s correlations which make things tricky. Think Japan 1989. As I recall their problems were compounded by excess savings and aging population. Now where else have I heard that? Bizarrely, as I am on the brink of retirement and will have to invest large sums, I am worried about inflation and deflation, how even handed is that?
I find Excel too clunky and limited for doing asset return simulations (Monte Carlos, multi-asset variance-covariance analysis) or for managing my own portfolio and the cashflows it throws off. I’ve moved to using the free QuantLibXL addin (http://quantlib.org/quantlibxl/index.html). Excel then becomes an easy to use front-end with the hard work done by the addin. It’s C++/C# libraries allow me to build and coerce cashflow objects in Excel/VBA. It also has a decent suite of stat/math functions. It’s particularly suited to fixed income portfolios and rather similar to what I use at work.
Outside of my own excel tools, I quite like the Flexible Retirement Planner (http://www.flexibleretirementplanner.com/wp/). It’s for US investors but can be fairly easily moulded to a UK investor’s requirements.
They really should teach this stuff in ICT classes from year 9 – 11.
@Grand
Amen to that!
thank you so much for all the nice words. Really appreciate it. The series has been about striking the balance between making it simple, but still useable. Having seen risk models at even very large financial firms it is sometime shocking how there are basic built-in assumptions that can easily be challenged. In my view, the biggest challenge to this model is the difficulty of adding asset classes (non-constant correlations and how they go to one in horrible markets), and non-normal return distribution. In future video I’ll talk about these issues and how you can try to address it (however imperfectly).
I’d really love to hear if there are specific cases you’d like me to adapt the model to suit. Just to show how easy it typically is.
If any of you are in London on June 22 and want to come to the book launch party for 2nd edition of Investing Demystified pls send me an email on lars@kroijer.com.
I’m looking forward to getting to sit down with your series. I have built many spreadsheets (and low tech-web apps) from scratch but there’s always more to consider. And doing compound interest in a tidy way is not a skill of mine.
I would be interested to see how the model could be adapted to incorporate scenarios where additional expenses appear on the scene, i.e. school fees, care home fees etc.
It would be useful to see how these sorts of things impact total net worth over time, i.e. you could fiddle around with inputs like monthly cost, start date, end date and see what the knock-on effect was on your portfolio
Book launch? I have already bought it, lent it out and am trying to get it back!
YamiKuriboh – I had the same problem – so I “cheated” by calling my broker (who offers annuities at competitive rates) and simply asked what my money would buy on that day – the numbers do change as the economic environment (and interest rates), changes. After all, the annuity issuers calculate how much money they can make on your annuity, by investing in financial instruments (stocks, bonds, etc.). Good luck.
Can you explain why you use a standard deviation of 25% and what other values you would consider appropriate and why? Fat tails; hmm… I need to do some Googling. I feel this a really crucial step — including the variance — that I’ve been ignoring previously.
I only began saving properly (Saving Rate>50%) just over a year ago aged 26. This video series was an eye-opener to my previously feeble Excel attempts.
CMF
Can you explain why you have used a 25% standard deviation; what other values/ range would be appropriate and why? Fat tails; hmm… I need to do some Google-ing. I have always appreciated that variance would exist but not known how to include it with data tables and so on.
I began saving properly (Saving Rate >50%) just over a year ago aged 26. This is a great video series to further my education and help one of the lucky Millennials plan for the future.
CMF
ZXSpectrum48k – I recommend Vu-Calc by Psion (c) 1982.
🙂
Standard deviation is a measure of spread about the mean. Usually implies a ‘normal’ distribution for probability is being used but not always. Trouble is history shows that the standard distributions ( in this case the source of the random numbers) don’t give the possibility of a nasty downward lurch enough weight.
Was wondering if would be possible add a determinatinistic ‘ slump to the model. Perhaps a 30% drop in first year then linear recovery after a further three years or something? Then this could be injected at a time point (overriding the random number) and the results compared with the baseline to show sensitivity?
Of course the modelled period would need to be much greater than the duration of the dip.