Tagged: Spreadsheet

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.


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.