Retirement planner spreadsheet (UK): State Pension, NI record, pots and budget
A UK retirement planner spreadsheet has to answer five questions: when you reach State Pension age, how much State Pension your record gives you, whether gaps in your National Insurance record are worth filling, what your other pensions pay and from when, and whether the budget still balances once the salary stops. Each answer comes from a GOV.UK service, a pension statement or your bank account, with no assumption about investment returns. The rules below are as published on GOV.UK when checked on 20 October 2026.
What the planner must answer
| Block | What you type | What the sheet works out |
|---|---|---|
| My details | Dates of birth, stop-work dates, take-home pay | State Pension date per person; months without pension after stopping work |
| State Pension | Forecast, qualifying years, gaps and their cost | Yearly and monthly value, extra amount per gap filled, effect of deferring |
| Other pensions | Each pension, its latest statement, its planned start date | Income by start date; pots that start before State Pension age |
| Budget | Today's income and spending | Three periods: today, after the first person stops, after both stop |
| Timeline | Nothing | Every dated step, sorted, with a status |
State Pension age and forecast
GOV.UK's State Pension age timetable (page updated 15 May 2014) gives 66 for people born between 6 October 1954 and 5 April 1960, a phased rise from 66 years and 1 month to 66 years and 11 months for those born between 6 April 1960 and 5 March 1961 (reached between 2026 and 2028), and 67 for anyone born between 6 March 1961 and 5 April 1977; under the Pensions Act 2007 the age rises to 68 between 2044 and 2046. A third review was announced in July 2025 (GOV.UK collection first published 21 July 2025), and the GOV.UK tool warns that "the State Pension age is regularly reviewed, so the results of this tool may change in the future".
The GOV.UK forecast service then shows how much you could get, when, and how you could increase it. The forecast is a figure to type, never to calculate. Two references from GOV.UK: the full new State Pension is £241.30 a week, and you need 10 qualifying years to get any new State Pension and 35 for the full rate if your record started after April 2016; if you were contracted out before then, you will usually need more than 35. More in State Pension age: how to check yours and what the forecast tells you.
Deferring, in one line
For those reaching State Pension age on or after 6 April 2016, GOV.UK states that every 9 weeks you defer adds 1% to your weekly payment for life, "just under 5.8% for every 52 weeks", with a minimum of 9 weeks; the extra usually rises each year with the Consumer Price Index. The planner takes the weeks as an input.
National Insurance record and gaps
The Check your National Insurance record service shows what you have paid up to the start of the current tax year (6 April 2026), your credits, the years that are not qualifying years, whether voluntary contributions would help, how your forecast would change and what they would cost. The planner's columns are typed from it, not estimated.
Two rules sit next to them. You can only pay voluntary contributions for the past 6 years, with a deadline of 5 April each year: GOV.UK's example is that you have until 5 April 2032 to make up gaps for the tax year 2025 to 2026. And voluntary contributions "do not always increase your State Pension, for example if you were contracted out", so check the forecast first. The options are set out in National Insurance record gaps: how to check and what the options are.
Pension pots in one table
List every workplace and personal pension on one tab, one row each: scheme, provider, type (defined contribution or defined benefit), date and amount of the latest statement, planned start date. Four GOV.UK points belong on that tab.
- Lost pensions: the Pension Tracing Service gives a scheme's contact details from an employer's or provider's name, but "they cannot tell you whether you have a pension, or what its value is".
- Earliest access: not normally before 55; the policy paper on increasing the normal minimum pension age (published 4 November 2021) sets the rise to 57 from 6 April 2028. Each row carries the scheme's earliest date.
- Lump sum: you can usually take up to 25% of a pension as a tax-free lump sum, up to £268,275. Record it; do not model tax.
- Guidance: if you are over 50 you can book a Pension Wise appointment, which covers defined contribution pensions, and MoneyHelper gives free guidance on retirement savings options.
The before/after budget
The budget tab has three columns: income and spending today, after the first person stops working, and after both have stopped. Income lines are the take-home pay that remains, each State Pension forecast from its start date, and each pot from its planned date. Spending lines are your current categories, with a note of what changes (no commuting, a mortgage that ends).
Two checks belong in it. Pension Credit, for people over State Pension age on a low income, tops up weekly income to £238 for a single person and £363.25 for a couple (GOV.UK). The Winter Fuel Payment for winter 2026 to 2027 is between £100 and £300 for people born on or before 27 June 1960; HMRC takes it back if your own income is over £35,000, and in Scotland you cannot get it (the Pension Age Winter Heating Payment may apply instead). The sheet flags weekly income after both stop that is at or below the Pension Credit figure; it cannot decide eligibility.
Fictitious example: a couple with £3,600 of monthly take-home pay spends £3,100 today. After the first person stops, pay drops to £1,900, one pot adds £450 and spending falls to £2,900: a shortfall of £550 a month until the first State Pension starts. After both stop, two State Pensions and two pots give £2,950 against £2,800. It shows how big the gap is and for how long, not when to retire. See Retirement budget worksheet: before and after, in one table.
Dated steps
The last tab lists every step, dated from the dates of birth and stop-work dates, so one change moves them all.
- Check your State Pension age on GOV.UK; record the date and the day you checked.
- Get your forecast and your National Insurance record; type the figures, including the cost of each gap.
- Before 5 April each year, review the oldest of the six open years and decide, after checking the forecast, whether to fill it.
- Once over 50, book a Pension Wise appointment if you have a defined contribution pension.
- Get a statement from every scheme, trace lost pensions, note each earliest access date.
- Build the three-column budget, run the two help checks, then enter your deferral and drawing dates.
FAQ
Can the spreadsheet calculate my State Pension?
No. The amount depends on your full National Insurance history, including any contracting out before April 2016. The GOV.UK forecast gives the figure; the spreadsheet stores, converts and dates it.
Should I pay voluntary contributions to fill gaps?
Check your forecast first, because GOV.UK states that voluntary contributions "do not always increase your State Pension". The planner shows the cost and the extra weekly amount side by side, with the 5 April deadline, and makes no recommendation.
Does the planner work for a couple?
Yes: each person has their own dates, forecast, pots and stop-work date. The budget then has three periods, because two people rarely stop on the same day.