How I built my own DIY pensions dashboard

What's the best way to work out if you'll have enough pension when you retire? Start by finding out what your retirement nest eggs are currently worth. It can be a challenge keeping track of the different pots of pension cash you accumulate over your working life, so here's how to use a simple spreadsheet to see at a glance how much pension you've built up so far.

I've been writing about pensions for more than 20 years and over this period I've built up a collection of seven different pension plans with my various employers. 

Setting up a spreadsheet to keep tabs on them is one of the best hacks I've discovered – so now I've joined MSE, it's time to share this with you.  

Even if your pension picture seems straightforward, as you notch up your years of wage slavery it could get more complex. Job changes mean some Brits collect more than 10 pots of pension cash over their working lives. Staying on top of what you've got is no mean feat.

So it's great news that the Government has backed the development of a new online service to allow individuals to see at a glance what all their pensions – state, workplace and personal/private – are worth. But while early versions of the 'pensions dashboard' could be launched this year, it may be four years before all pension data is included. 

For MoneySavers, however, there's no need to wait. My 10-point guide to building your own pensions dashboard is below.

Need a pensions primer? See our pension need-to-knows and check out MSE's lowdown on how the state pension works.

Step-by-step DIY dashboard guide  

1. Open a new spreadsheet. Many people will have Excel, but a similar spreadsheet program will work just as well.

2. Dig out your latest pension statements. You should receive an annual statement automatically from each of your pension providers – if you don't, ask it to send you one. You may also have online access for your pension plans, or be able to sign up for it. And if you've lost track of a pension, the Government's online pension tracing service may be able to help with contact details. See our MSE News story on how to track down lost pensions.

3. Find the value of your pension plans. Look for the most recent value of each of your 'defined contribution' (DC) pension plans or funds – also known as 'money purchase'. Most workplace and all personal pensions are this type of pension, where the value depends on how much money is paid in and how your investments perform.

Don't confuse plan/fund values with the annual pension income you might be paid when you retire. We'll look at roughly what income your pot of pension cash might equate to in point 6 below. Also, if you have any final salary pensions – a type of pension which promises a specific income and is still widespread in public sector jobs – put those statements to one side for now.

4. List your pensions and what they're worth in your spreadsheet. List your DC pension providers and their current pound values in your spreadsheet, as in the graphic below. It may also make sense to add contact details, so you don't have to rummage through paperwork if you want to phone the provider.

This list could also help your family or loved ones in sorting out your finances should you die. However, beware of storing account numbers (let alone passwords or similar) in your DIY dashboard, in case the wrong person gains access.

5. Total up your plan values. Create an automatically calculated total at the bottom of your column of pension values. Hey presto! Now you know the total current value of your DC pension plans – £100,000 in our example. To repeat, this isn't the same as the annual pension you'll get – it's a pot of cash from which you'll be drawing a pension income. We'll show you how to estimate the value of these funds in income terms at step 6.

Here's how your DIY dashboard should look after steps 4 and 5:

6. How much pension could my fund buy? Forecasting the future pension your DC funds might produce when you retire is like gazing into a crystal ball. While annual statements will include projections of your retirement income, these are often based on complex assumptions that may not prove realistic.

A simple alternative calculation is to divide the current value of your funds by 25. This'll give you a rough idea of the annual pension income your DC plans might provide if you were due to retire today. Treat it as an indication of the pension saving progress you're making. So, in our example, if your funds amount to £100,000, this could equate to an annual pension of £4,000.

This calculation is based on a so-called 'safe withdrawal rate' – a rule of thumb for the income a typical pension plan-holder should be able to take without running out of money before they die. Academic research has put this safe withdrawal rate at 4% a year, hence the divide-the-total-by-25 calculation, though some experts now think 3.5% or less is safer. In that case, you would divide the total by about 30.

7. Add any final salary pensions. As well as DC pensions, you may have a final salary pension which will bulk up your retirement income. Add the annual pension figure from your latest statement to your spreadsheet, as shown in the graphic below.

8. Add the state pension. The full basic state pension is £168.60 a week in 2019/20 – £8,767.20 for the year – although there's a number of reasons why you might not qualify for the full amount. You can check how much state pension you've built up based on your national insurance record via the Government's Check Your State Pension service. Add this annual amount to your spreadsheet, beneath the income figures from points 6 and 7. Then create a total of the figures from points 6, 7 and 8 to give a rough estimate of how much you might receive from all your pension sources if you were due to retire today.

Here's how your spreadsheet should look by step 8 – showing what all your sources of pension income add up to if you were due to retire today (we've hidden the column of contact numbers to keep everything clearer and simpler):

9. Still not enough? Disappointed with the pension you've built up so far, even when you include the state pension? Bear in mind this is just a rough snapshot. If you're still some way from retirement, some or all of these pound values may increase as you continue to pay money in (or, for the state pension, make further national insurance contributions), or if your plans see some investment growth. This could mean a higher pension when you actually come to retire – though of course your DC pensions could also be hit by a stock-market fall. 

10. How can I use my dashboard to manage – and boost – my pensions? Each time you receive a new statement from one of your pension providers, or access it online, update the value on your spreadsheet. The total value of all your pensions will then update automatically. Hopefully, over time this total will increase. You could also add a range of extra info to your dashboard to help monitor and manage your pensions.

This could include:

  • What each pension is invested in, eg, "UK Stock Market Tracker". This should help you quickly see where you are invested overall.
  • Current plan/fund costs. This could help highlight where you might be able to make savings by switching funds or transferring pensions into a cheaper plan.
  • Pension values at the end of each year as well as the current value. To help compare the performance of pensions and so spot potential duds.

Et voila! Here's what your DIY pensions dashboard could look like if you add the optional extras of step 10: