Step 2 of the spreadsheet model involves setting up lines that refer to what pension you already have, what your target should be, and therefore what additional savings you’ll need to make to get to the target.
Add three lines with the title Pension Target, Pension Available and Additional Target as shown in the image below.
Next thing is to set an inflation adjusted Pension Target. This is very personal to you – some experts say you only need half of your working salary. Other people might want to have their salary minus their current mortgage. It will need to be realistic, and it might depend on what kind of lifestyle you intend to lead when you do retire. The single, most important thing is that you allow for inflation. So, in my example if we say I’d like to retire on a pension of £25000 in today’s money and I’d like to retire at the age of 60. In Cell N5, type in the formula:
As you can see, this means that in real terms, the £25000 is actually going to be £31706 when I get to 60. That’s assuming information follows our notional predictions of course.
In a future blog, I’ll explore how we can work with multiple pensions, maturing in different years, but for today we’ll focus just on exploring the realities of one pension. Whether it’s a defined benefit scheme, or some other form of pension, a statement will have given you an indication of how much that pension will be worth. It will also give you information on how that pension will increase in value. Normally, that would be in line with one of the inflation figures such as RPI or CPI.
In our example, let’s say we have a final salary (defined benefit) pension that states a value of £12000 per annum in today’s money. Again, in cell N6, type in:
Next, in N6, type in:
As you can see, it’s showing a shortfall of £16487.14. If you drag rows highlight N5 to N7 as a block and then drag out towards the right, it will fill in your formulas across the other cells.
You may well end up with something that looks a little unsightly such as this:
All the hash’s are indicating is that it’s too large to display in the box. The reality is we probably don’t care about the pennies, so highlight all of that area and click on the Decrease Decimal button – it’s the rightmost button in the block shown below.
Personally, I prefer to colour my spreadsheets as it helps to focus my eyes on which bits I can change, the target areas, and were some of the calculations are really happening.
So, for me I’m going to colour row 1,5,6 and 7 in Yellow and rows 2-4 as blue. This gives me a spreadsheet that focuses me on some key areas, and blocks out some of the elements that I really can’t change (such as getting older!). At this stage, don’t stress that the pension isn’t enough. We’ll layer in other pensions (if you have them) and the state pension in a future blog article. For now, your spreadsheet should be looking something like this:
I hope you’ve found today’s instalment useful – if there are any questions you have on Excel please be sure to get in touch.
You will need some spreadsheet software – I use Microsoft Excel which comes as part of an Office 365 subscription. Throughout my blog posts, I’ll give you some hints and tips on how I created my pensions spreadsheets using Excel. However, it’s perfectly possible to create the same thing with other tools (some of them free). Examples include Google Sheets if you’re happy using a spreadsheet within a browser, or OpenOffice if you’re looking for something free to download and use on your PC or Mac.
When you first create your pensions spreadsheet you should regularly save your work, but keep remembering to give it version numbers each time you save. That way, if you do make a mistake or decide an earlier version was better you can go back to it. It will also let you create different “models” for your retirement and then do a compare and contrast.
The first step is to create a baseline that shows you the effect of inflation. Nobody can truly predict what inflation is going to be over the long term, so all you can do is use historical trends. Having the inflation baseline allows you to quickly do other calculations that show you how much something is really going to be worth in the future, or how much something was worth in the past once you take inflation into account.
A spreadsheet can have multiple sheets, so use Sheet1 to store the over-riding “assumptions” and then use Sheet2 for doing all the forward looking calculations. For the assumptions, you can name a cell, so rather than it be Sheet1!B2, you can just reference it as “Inflation”. This makes it easier in the long run to remember what particular cells are, and what calculations based on them actually mean. Rather than looking blankly at a formula that includes Sheet1!B2 you’ll be able to easily see that it’s actually Inflation. Below, you can see that by leaving your cell B2 highlighted, you can give it in a name in the Cell box that’s highlighted.
Next, on Sheet 2 you need to type in the start of some basic information to give you that baseline. Let’s start with the Year, your age, your partners age, and the start of your inflation baseline as shown below:
In Cell B4, there is a calculation which is
So, to create the baseline, you should now highlight cells B1 to C3 – in the bottom right hand corner of this you should see a small square which allows you to drag it along and fill in all the cells that you drag over. This automatically creates all the data for future years and future ages based on what it sees in those first two columns.
Next, highlight cells B4 and C4, and ensure it’s formatted as currency. Then click on Cell C4, and drag that out towards the right and you can see it automatically calculating the effect. You should now have a spreadsheet that looks something like this:
This shows the effects of inflation over time. The spending power of your £1 now, will need to be £1.20 by the time we get to 2026. Conversely, your £1 in 2026 is actually only worth £1/£1.20 in today’s money (about 84p). So, this is a handy baseline to have at the top of your spreadsheet through which you can base a number of assumptions. You don’t want to plan your retirement thinking you need a pension pot of £100,000 when really it’s £120,000 once you allow for inflation.
I hope you’ve enjoyed my first blog post – things will get more complex as we go along.