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.