Spreadsheet Retirement Options: Build a UK Pension Projection Tool for Employees Leaving a Small Business

Hook: Stop guessing — give employees a clear, UK-focused pension decision tool

Small employers often face the same painful questions: employees leaving ask “what should I do with my pension?” and HR teams scrambles to explain options in jargon. Manual replies, inconsistent spreadsheets and one-off emails waste time and increase risk. Build a simple, repeatable pension planner that shows projected growth, transfer/leave options and decision implications — translated into UK practice from common US 401(k) thinking — and you’ll save hours, reduce mistakes and give staff confidence.

The big picture (most important first)

In 2026, the best small-business HR teams treat pension communication as a standardised workflow: a short, data-driven projection plus a concise decision matrix. This article shows how to build an easy Excel-based pension planner tailored for UK employees leaving a small business. You’ll get practical formulas, scenario modelling, governance tips and a downloadable template bundle to implement immediately.

Why this matters in 2026

• Pension Dashboard adoption has continued to accelerate since 2024–25, making member data more accessible. Employers can leverage clearer statements when explaining options.
• Regulatory focus on transfer value advice means employers should avoid prescriptive financial advice — instead provide personalised projections and signpost independent guidance (MoneyHelper, IFA).
• Fintech and automation let small employers build quick, repeatable tools (Power Query, simple macros) to bring HR workflows up to enterprise standards without heavy IT projects.

What the planner does — at a glance

Design the spreadsheet to answer three employee questions in under five minutes:

1. What is my current pension pot worth now and at retirement under reasonable assumptions?
2. What are the practical options when leaving a job (leave, transfer to new employer, transfer to SIPP, request transfer value, or defer)?
3. What are the financial trade-offs and governance steps for each option?

Principles (translated from US 401(k) thinking to UK pensions)

US 401(k) workflows emphasise portability, consolidation and scenario comparison. Translate that into the UK context by focusing on:

• Portability — ability to transfer DC pots (not automatic for DB schemes).
• Consolidation — simplify multiple pots vs. keep with proven fund manager.
• Net outcomes — model post-fee, pre-tax and after-tax outcomes (pension tax relief and lifetime allowance considerations matter in specific cases).
• Advice signposts — unlike typical US 401(k) choices, UK transfers from DB schemes often need regulated advice; always signpost independent advice.

Step-by-step: Build the pension planner (practical)

1. Page structure and governance

Create separate sheets for:

• Inputs — member details, current pot, contribution rates, salary, age, retirement age, assumptions.
• Calculations — projection model, fee impact, scenario engine.
• Scenarios — default, conservative, optimistic, and transfer vs leave comparisons.
• Output — one-page summary to give to employee (export to PDF) and an HR checklist.
• Data — lookup tables for CPI, assumed returns, and contribution bands.

2. Key inputs (what you must ask the employee)

• Age and planned retirement age.
• Current pension pot value (round to nearest £).
• Scheme type: DC (defined contribution) or DB (defined benefit) — DB requires advisor signposting.
• Current contribution rates (employee and employer) and any planned increases.
• Estimated annual salary growth (if modelling ongoing contributions).
• Assumed annual return(s) and ongoing charges (TER/OCF).
• Inflation assumption (for real-value outputs).

3. Projection formula (compound growth)

Use a compound growth model to project the pot at retirement. For a simple DC pot with contributions, calculate year-by-year or use a closed-form formula.

Basic year-by-year model (recommended for transparency):

1. Start with current pot for Year 0.
2. For each year: add annual net contributions, then apply net return (gross return less charges).
3. Repeat until retirement age.

Example Excel implementation snippets:

• Net annual return (cell Inputs!B10): =B8 - B9 where B8 = assumed gross return (e.g. 5%), B9 = ongoing charges (e.g. 0.75%).
• Yearly projection (Calculations sheet, cell C5): =C4 + Contribution + (C4 + Contribution) * NetReturn

4. Model transfer vs leave scenarios

Set up parallel columns for each option. Example options and what to capture:

• Leave with current provider — capture provider fees, fund performance history, guaranteed benefits (if any).
• Transfer to new employer scheme — expected fees and fund choices in the new scheme; whether employer contributions will continue automatically.
• Transfer to SIPP — show wider investment choices, different fee structures and potential for higher fees or DIY risk.
• Request cash (if allowed) — only available post-pension flexibility or at scheme rules; show tax consequences and loss of future compounding.

Apply the same projection logic to each option but use different fees and contribution assumptions. Present projected pot, expected annual drawdown (if modelling income), and a simple metric: Projected pot at retirement — after fees.

5. Add net outcome and sensitivity

Employees respond to clear numbers. For each scenario show:

• Projected pot at retirement (nominal).
• Projected pot in today’s money (real terms using inflation assumption).
• Estimated annual income if purchased as an annuity or used with drawdown (use a conservative conversion rate or link to provider illustrations).
• Range under sensitivity: -1% / base / +1% annual returns to show outcome spread.

6. Decision matrix and non-financial factors

Money isn’t the only factor. Include a short checklist for HR and the employee:

• Is the scheme DB? If yes, advise seeking regulated advice before transfer.
• Are there guaranteed benefits or death-in-service terms to preserve?
• Does the employee prefer centralising pots for easier management?
• Are there potential employer match continuation rules at the new employer?
• Does the employee intend to move abroad (impact on tax and access)?

Example case — walk-through

Sarah, age 42, is leaving a small business. Her DC pot is £28,000. She contributes 5% of salary (£32,000), her employer 3%. She plans to retire at 67. Use base assumptions: 5% gross return, 0.8% ongoing charges, 2.5% inflation.

Projection steps:

1. Calculate annual contributions: Sarah 5% of £32k = £1,600; employer 3% = £960; total = £2,560 per year.
2. Net return = 5% - 0.8% = 4.2%.
3. Yearly compound from age 42 to 67 (25 years) with annual contributions gives a projected pot (use the spreadsheet to run exact figures). Under these assumptions Sarah’s pot grows to roughly £100k–£120k depending on rounding and contribution increases.

Now compare options:

• Leave in current scheme: fees low, stable funds — projected pot = £110k (nominal).
• Transfer to SIPP with higher fees but broader choices — projected pot = £104k.
• Transfer to new employer with superior match — if employer match rises to 4% in new job, projected pot could be £125k.

Present these side-by-side in the Output sheet and add red/amber/green flags for non-financial risk (e.g., DB transfer = red until advice taken).

Automation and polish (quick wins for small businesses)

To save time and scale the tool across HR workflows, add these features:

• Data validation on Inputs to reduce errors (drop-downs for scheme type, age ranges).
• Power Query to import statement CSVs from pension providers where available — map pot values and recent returns automatically.
• Simple macro to generate the one-page PDF summary that HR can email to the leaver (one-click export).
• Scenario buttons (small VBA) to toggle Conservative/Base/Optimistic assumptions without editing cells.

Compliance and trust — the non-negotiables

Small employers must avoid giving regulated financial advice. Use the planner to provide factual projections, not recommendations. Always:

• Include a clear disclaimer: this is an educational projection and not regulated financial advice.
• Signpost MoneyHelper, The Pensions Advisory Service, and independent financial advisers for transfer advice — especially for DB transfers.
• Document the HR process: who ran the projection, date, and confirmation the employee received signposting information.

Tip: For DB schemes, highlight “seek regulated advice” prominently — many transfers are irreversible and costly if mishandled.

2026 trends to build into your planner

Design the template to be future-ready by incorporating recent trends:

• Pension Dashboard connectivity: prepare for auto-population of member pot values as more providers join the dashboards full rollout (late 2024–2025 has driven momentum into 2026).
• Fee transparency: build fields for OCF/TER and demonstrate their long-term impact — members increasingly care about fees and net returns.
• ESG & investment preferences: include tick-boxes indicating whether employees prefer ESG-labelled options — many new schemes offer simple switches.
• Automated scenario generation: use small macros or LAMBDA functions (Excel 365) to generate multiple scenarios quickly.

Metrics HR should track

Turn this tool into a reporting asset. Track these KPIs monthly or quarterly:

• Number of exit pension projections run.
• Average projected pot increase by employer match scenarios.
• Number of referrals to independent advice.
• Time saved per exit compared with manual emails (estimate).

Common pitfalls and how to avoid them

• Giving advice: Keep output factual and add advice signposting. Never recommend a specific transfer unless your business employs a regulated adviser.
• Ignoring fees: Overlooking charges underestimates the impact dramatically over decades.
• One-size-fits-all assumptions: Always run sensitivity scenarios to show outcome ranges.
• Poor record-keeping: Save the summary PDF and log who ran the projection to protect your business and the employee.

Case study: Small employer implements the planner

Bright Oak Accountants (12 employees) used the template bundle to standardise exit conversations in 2025. They integrated provider CSVs through Power Query, set up a single-click PDF summary and trained HR to run projections. Results in 6 months:

• HR time on exit pensions fell by 60%.
• Employee satisfaction with exit guidance rose in internal surveys from 58% to 87%.
• Two employees opted to consult an IFA before transferring a DB-style benefit — a prudent outcome highlighted in their compliance log.

Downloadable bundle and how to use it

We’ve packaged a ready-to-use template: an Inputs sheet, Calculations, Scenario engine, and a one-page Output summary plus a short HR checklist and PDF export macro. The bundle includes a short tutorial (10–12 minute video) that walks HR through a live example and shows how to connect Power Query to provider CSVs.

Use the template as-is for immediate value or customise assumptions to match your company’s benefit design.

Final checklist before you roll it out

1. Ensure the disclaimer and signposting text are prominent.
2. Test the tool with three real exit cases to validate assumptions.
3. Train at least one HR contact to run projections and archive PDFs.
4. Document a simple governance step: who can change assumptions (limit to finance/HR lead).
5. Keep a record of referrals to independent advice.

Actionable takeaways

• Standardise exit pension conversations with a one-page projection and decision matrix.
• Model multiple scenarios (leave, transfer to new employer, SIPP) and show net, after-fee outcomes.
• Automate repetitive tasks (micro-app templates + one-click PDF) to free HR time.
• Always signpost independent advice for complex transfers, especially DB schemes.

Closing: Put this into action today

Small employers can deliver professional, compliant pension guidance without hiring an adviser — by using a standardised pension planning template that focuses on clear projections, scenario comparison and signposting to regulated advice where required. In 2026, with better data access via dashboards and low-cost automation tools, there’s no excuse for ad hoc guidance.

Ready to implement: Download our UK Pension Planner template bundle (Excel + quick tutorial) to run your first exit projection in under 10 minutes. Equip HR with a repeatable process, reduce risk and empower employees to make informed decisions about their retirement.

Call to action

Download the template bundle now and get a complimentary 15-minute onboarding guide for your HR lead. Click to get started and make every exit conversation consistent, compliant and data-driven.

Related Reading

• Toolkit: Forecasting and Cash‑Flow Tools for Small Partnerships (2026 Edition)
• Micro-App Template Pack: 10 Reusable Patterns for Everyday Team Tools
• No-Code Micro-App + One-Page Site Tutorial: Build a Restaurant Picker in 7 Days
• Bluesky & Social Features Every Print Shop Should Know
• Micro Apps and Ephemeral HTTPS: Issuing Short-Lived Certificates for Rapid Prototyping
• Wet-Dry Vacs: The Home Cook’s Secret Weapon for Messy Meal Prep
• BBC + YouTube deal: What platform partnerships mean for gaming creators and fairness in reach
• The Placebo Problem: Separating Hype from Health in Wellness Food Claims