Spreadsheets are essential to assist the decisions you take about money. I’ve evolved mine over the last 25 years to reflect different stages, priorities and understanding about how, when and why I fund financial independence.
I’m releasing my current model (with example data) which others are welcome to use however they wish for their own planning. This is for information purposes only. You should assume that what works for me probably doesn’t work for you. You should also expect errors (it’s a spreadsheet after all). You should not buy or sell anything on the basis of this model. Finally, I am not a financial adviser, have no training in this area, have no knowledge of your personal circumstances and no interest in your financial outcomes.
With those caveats and warnings, if you still wish to have a look at it, you’ll find full details below on how to install it, tailor the input assumptions and interpret the output.
The model is for a one or two person household and utilises the main tools available in the UK for financial independence planning (State Pension, Defined Benefit pension, ISA, SIPP, savings accounts, general trading accounts).
There are many other asset classes such as property, gold, crypto, wine etc. I don’t know how to model these for future lifestyle needs, so if you want to include them, you’ll need to extend the model yourself. Similarly, if you live outside the UK, you will need to research what the UK terms are and see if a similar concept exists in your country.
The principal idea is to break down the value of your current invest-able assets into three components:
If you are currently in drawdown, the model will also give an analysis of your cash position. It’ll show how much cover you have for your immediate non-discretionary needs, and how much you would need to convert to cash from your investments to allow you to sleep at night.
The current withdrawal rate is shown for information only (this will vary wildly over time, especially as state pension kicks in). Some people get hung up on withdrawal rates and use that as an input to financial planning. For me, it’s an output, and not an especially interesting one. But, if it was above 10%, I had no discretionary funds left and still a number of years left then I’d probably want to review my lifestyle desires, total return assumptions, investment strategy (or indeed all three).
Finally, the estimated portfolio returns over the next 12 months are shown for information. This is based on the long term assumptions in the model. But it can be useful to see whether your actual investment strategy is performing above or below your financial model.
It’s better to be conservative in your assumptions in my opinion. If your portfolio is consistently underperforming relative to your assumptions, then you might want to dial them down to get a more realistic view of your future. However, there will be good and bad years over the long term, so knee jerk reactions are not required unless you have evidence that the long term trend is way off.
The read only spreadsheet is available as a googlesheet here. It’s best to view it on a laptop browser.
The best way to modify it is with a Google account. If you have one, Press Sign In
on the top right (if not already) , then select File->Make A Copy
and you’ll get your own version in your google drive. This will be not be viewable by anyone, unless you choose to share the link.
The model will also work on Excel. You select File->Download->Microsoft Excel
and save to your local drive. The only difference with the Excel version is that the “Calculate Plan” button won’t work and you’ll have to manually adjust the Non-Discretionary Lifestyle Fund Multiple
until the plan appears. More on that below!
You enter information relevant for you in the yellow boxes on the sheet named Projections
. As you start to enter the assumptions, you might see the current plan disappear. Don’t worry about this, it’ll get recalculated when you’re ready.
Most of the boxes should be self explanatory in columns B and C. Some additional notes on specific assumptions are:
B11
and B12
to zero and cell H11
to 2100. Then vary the rate of inflation and press calculate plan. The percentage allocations for the three target layers of your plan will be the same.Once you have entered the personal and investment assumptions in columns B and C, it’s time to review your assumptions about future government policy. Some points to note on these assumptions:
The final part of the input process is the most crucial. You need to estimate your annual non-discretionary lifestyle spend in today’s money. If you are already in drawdown, this should be easy to calculate from your bank account. If you are some way off, you will have to make an assessment on the unknowable desires of your future self.
Finally, enter the number of years spend that you want to hold in cash, or cash equivalents. This is purely what helps you sleep at night. For some it’s six months. For me it’s four years.
The final assumption is the Non-Discretionary Lifestyle Fund Multiple. This is simply a multiplication factor applied to your desired spend to provide a fund that will pay out until the age you project to, according to the assumptions you have made.
You know this number is correct when the fund runs out in the correct year and there is also only a fraction less than or equal to 1 left in the pot at that time.
The “Current Plan” will be displayed in the main screen when the “Non-discretionary Fund multiple is correct – this occurs when the three boxes next to it are green. If you need to adjust it up or down, a message will be displayed in blue telling you.
If you are using Google Sheets, you can press the “Calculate Plan” button, accept the permissions, and it will do this adjustment for you. Users of Excel will need to do this manually.
The left hand side of the plan summarises some facts about your investments. It can be useful in a household of two to see how balanced or unbalanced they are.
The advantage of having balance is that you can maximise future tax allowances across two people. So in the accumulation period especially, this might guide you as to where to place new contributions.
The right hand side of the plan splits your invest-able assets into the three key components mentioned at the start: The non-discretionary fund, the tax fund and the discretionary fund.
If you are in accumulation, you may see an orange box. This shows the shortfall that you need to make up in today’s money. Of course, the shortfall will also grow in future years with inflation. So, an estimated monthly savings amount is given which should help inform you about the level of contributions still needed. If this looks unsustainable, then recheck your assumptions, review your investment strategy or temper your desires to something more affordable.
If you are in drawdown, you may see a red box. This is an urgent problem that needs to be addressed. You do not have enough funds to meet your non-discretionary needs. Again, you can review your assumptions and investment strategy to fix a shortfall (although remember “fixing it” in a spreadsheet has to be followed by actions and results that mirror the fixed assumptions). Again, tempering your desires may be a solution to getting rid of the red box.
If you don’t see any amber or red in your plan, then you are on track. The key metric for me in this situation is the amount of discretionary spend. I might choose to blow it all at once, in which case I know that in future I will only be able to maintain my current lifestyle in line with inflation.
Or, I could choose to give myself an “annual bonus” for a fixed number of years and turbo charge my lifestyle. That approach doesn’t really work for me. I lead an extremely active life with 8-9 projects and a diary filled up months in advance. This is exactly how I wish to allocate time, but unfortunately it doesn’t involve spending money. That’s why I prefer to see my discretionary pot as lump sum to be spent at irregular intervals.
Once you have set this up, you don’t put it in a drawer, set up direct debits and never think about it again.
I review the plan once a quarter. Because it only consists of a few numbers, it’s very easy to maintain. Most of the core information doesn’t change, only the current values of the portfolio.
So although I review often, I rarely change my investment strategy, or drawdown schedule. The only thing I’ll really take a view on is when to top up the cash bucket.
As stated at the outset, this model works for me, but that doesn’t mean it will work for you. I was in accumulation for 23 years from 1996 to 2019 when I retired age 51. I hope to live for another 30+ years and I project until age 100 with conservative assumptions.
The beauty of the model for me is that it is grounded only in the present. You use present values for spend and actual values for the portfolio. It fundamentally answers the question “Do I have enough asset cover today to meet all my needs in future?”.
Some people project their funds to some date in the future. The problem with that, for me, is that the information is very difficult to action. If I project that my fund will be worth £1.2m in 2040, what does that actually mean for actions I do, or don’t, need to take today. Waiting until 2040 to check is not a good option.
So that’s my model. Make of it what you will.