Why is this different?

Quite simply – all of the pension calculators that I came across are too simple to meet my own needs and most of the people who need them.  Few of us have had one job, or one pension throughout our lives and you need to take each of them into account to build up a true picture of what your pension could be like.

There are numerous online calculators that will total up your pension pot based on your monthly savings or tell you your likely annuity based on your predicted pension pot.  Some of the better calculators I’ve found are:

Hargreaves Lansdown Pension Calculator

Candid Money – have a variety of calculators

The Pensions Advisory Service

However, everybody is different and usually has a financial situation that’s way more complex than those calculators allow.  For example, you may have one or more final salary pensions and some private pensions.  You might also have property, or even more than one property.  What you do with each of these and when you do it can allow you to optimise your pension.

I also learnt that pensions are very tax efficient, so in some cases it’s better in the long run to invest more in your pension than it is to pay off your mortgage.  This seems counter-intuitive but that’s what my spreadsheet model has allowed me to calculate.  The spreadsheet will allow you to model lots of “what if” scenarios – my own motivation for doing that was thinking “I really don’t want to be doing this same 9-5 job until I’m 67”

At this stage, there’s not going to be any advice on which pensions to choose or how to cash in on your pension – that’s best left of others with more expertise.  Over time I will start and research that and share my thoughts with you, but seek professional advice if you’re unsure.

I hope this helps to explain my thought processes. In my next post we’ll get back to building up the spreadsheet data.

Where do I start?

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.

screenshot2

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:

screenshot1

In Cell B4, there is a calculation which is

screenshot-3

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:

screenshot4

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.