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.

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s