Migrate Auto-Enrolment Pension data¶
This guide outlines the steps to set up and migrate all Auto Enrolment (AE) pension information. Once configured, automatic enrolment will operate entirely automatically in paiyroll® for each pay run.
The import process uses heuristics and may not be 100% accurate. You will therefore need to verify that the AE migration is correct for each employee. The quickest way to do this is to download the Pay Items: Navigate to Pay Items, then click Download… to download a payitems.csv file. Open the file using a spreadsheet program and then filter on column B definition with AE to exclude all other Pay Items. Verify the following for each worker:
Worker percentage (
inputs-3-value)Scheme (
inputs-4-value)Migration category (
inputs-5-value)Enrol date (
inputs-8-value)Migration date (
inputs-9-value)
If you need to make and save changes, simply copy the filtered AE Pay Items into a new file and edit the cells. Then upload this file by navigating to Pay Items and then click Upload….
Pension Scheme¶
Add a Pay Definition for the pension scheme using the Pension scheme Pay Template. You will normally require a single scheme, but you can add as many schemes as needed - for example, if directors receive higher contributions than employees.
First set the pension provider using the Scheme.
Then set all the scheme input values according to your scheme.
Auto-Enrolment Pay Definition¶
Add a Pay Definition using the AE Pay Template.
There are two earnings inputs QE (Qualifying Earnings) and PE (Pensionable Earnings) along with 2 busses with the same names in the system:
QE Qualifying Earnings input is connected to the QE Buss and used for Banded earnings AE schemes.
PE Pensionable Earnings input is connected to the PE Buss and used for Unbanded earnings AE schemes (either basic or total earnings for workplace pension)
Each buss is actually identical and it is all other contributing Pay Definition outputs connected to the buss that determines which element(s) of pay are included. By default all Pay Definitions drive both PE and QE busses. For example, a Salary Pay Definition would contribute to both QE and PE; but Overtime Pay Definitions should be configured as included for QE but not PE. That way, if a company use banded and unbanded AE schemes, then Overtime pay will be included in pay for banded schemes but not in unbanded schemes. Two busses conveniently support banded scheme running alongside unbanded or non-AE scheme(s). Select the buss that aligns with your AE scheme and ensure ensure all Pay Definitions required output to that buss - any which should not contribute have their outputs de-selected.
Auto-Enrolment Pay Items¶
You will need to add an AE Pay Item to each employee.
Inputs should be set as follows:
No tax relief is nearly always un-ticked as employees are generally eligible for pension tax relief. Tick only if exceptionally the employee is not eligible.
Awaiting NI is only for schemes that need to know about overseas nationals awaiting their NI.
Worker % inherits the default value from the AE Pay Definition when adding an employee Pay Item. This may be changed on a per-employee basis.
Worker adj and Company adj allow additional one-off amounts or corrections to be made. They can also be used for instances where an employee wants to pay a bonus into the pension scheme directly or sacrifice the amount as an employer contribution on top of the normal contributions. These are usually set as a default of zero.
Note
The Worker adjustment must be entered as a gross value. This would would generally be 1.25 x the net amount as most workers benefit from 20% tax relief. e.g. -100 gross worker adjustment equates to -80 net worker adjustment. (This is similar to 5% gross pension where a workers pays 4% net).
Scheme must refer to a scheme created above, but can be set separately on each employee.
Refind ref is required if you wish to process a refund. Leaving this blank instructs the system to nor process a refund.
Reference is only for scheme such as Teachers Pensions that require this. Otherwise leave blank.
For each employee who was previously auto-enrolled, opted-in, deferred or opted-out, their Pay Item inputs need to be set appropriately:
Migrated status should only be set for imported employees and not new employees. Values are Auto enrolled, Opted-out, Opted-in, Deferred or Not enrolled.
Migrated date must be set if the employee was previously auto-enrolled, opted-in or deferral was applied.
Enter values for Opt-out date and Opt-in date if applicable.
The table below explains how the migrated inputs are set:
AE Pay Item input |
Worker was Enrolled |
Worker was Opted-out |
Worker was Opted-in |
Worker in Deferral |
Worker Not Enrolled after deferral |
|---|---|---|---|---|---|
Migrated Status |
Auto-enrolled |
Opted-out |
Opted-in |
Deferred |
Not Enrolled |
Migrated Date |
Date auto-enrolled |
Date auto-enrolled |
Date opted-in |
Date of deferral |
Date of deferral |
Opt-out date |
Date opted-out |
||||
Opt-in date |
Date opted-in |
Important
The two migrated inputs will be cleared after a payrun when the state has been set. Do not re-enter the values unless a mistake has been made and you need to apply new values. Ensure that the default re-use policy Use once, then set to zero is not modified on these inputs for paiyroll® to operate correctly.
If either Auto-enrolled or Opted-out migrated Status are selected, the deferral date will be set to None.
There are two ways to add Pay Items:
For a small number of employees you can use the Pay Item Editor by updating an Employee and clicking Update Pay Items…. This is preferred over adding Pay Items individually.
For a large number of employees, you will find it much quicker to create a Pay Item AE upload file as described below:
Auto Enrolment Pay Item Upload file¶
You will need to create a sheet like this to upload AE Pay Item input values:
username |
definition |
f |
No tax relief |
Awaiting NI |
Worker % |
Worker adj |
Company adj |
Scheme |
Migrated status |
Refund ref |
Reference |
Opt-out date |
Migrated date |
Opt-in date |
works_id |
name |
||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
AE |
m1 |
no |
no |
3 |
0 |
0 |
NEST AE |
0001-01-01 |
Finn, Huckleberry |
|||||||||
AE |
m1 |
no |
no |
3 |
0 |
0 |
NEST AE |
0001-01-01 |
Havisham, H |
|||||||||
AE |
m1 |
no |
no |
5 |
0 |
0 |
NEST AE |
0001-01-01 |
Poirot, Hercule |
|||||||||
AE |
m1 |
no |
no |
10 |
0 |
0 |
SmartPension AE |
0001-01-01 |
And The Forty Thieves, Ali Baba |
|||||||||
AE |
m1 |
no |
no |
3 |
0 |
0 |
NEST AE |
0001-01-01 |
Bond, James |
|||||||||
AE |
m1 |
no |
no |
3 |
0 |
0 |
NEST AE |
0001-01-01 |
Crusoe, Robinson |
|||||||||
AE |
m1 |
no |
no |
5 |
0 |
0 |
NEST AE |
0001-01-01 |
Holmes, Sherlock |
|||||||||
AE |
m1 |
no |
no |
10 |
0 |
0 |
NEST AE |
0001-01-01 |
Tell, William |
Click to download a sample AE payitems.csv sheet.
There are two approaches to create this sheet:
You can create this manually as a single sheet
Use a multi-sheet spread-sheet file which can automatically lookup enrol and opt-out dates from other sheets as outlined below.
A multi-sheet file is created starting with the last sheet as follows:
Auto enrol dates (sheet 5)
You will need auto-enrol dates for all employees who are enrolled, opted-in or have subsequently opted-out. You can obtain these dates from the existing payroll software or more likely from your pension providers portal. Once you have these dates, insert as a new sheet from which to derive the AE dates for sheet 1.
Don’t forget to exclude anyone with an enrol date in the pay period - you will likely want paiyroll® to do this for you.
Dates must be in ISO format YEAR-MONTH-DAY e.g. 2020-12-25. Use Format –> More Number formats to select this date format.
Opt-out dates (sheet 4)
All employees who have opted out will need a migrated enrol status of opted-out. Collect these dates from your payroll software or pension provider. Don’t forget that you also need the enrol dates for anyone who has opted out.
If an employee has opted-out in the pay period, then do not use a migrated Opted-out status. Instead set the migrated status to Auto-enrolled and add an opt-out date.
All remaining employees (sheet 3)
Finally you will need to construct a list of all employees who are neither enrolled or opted-out. One technique is to create a list of all employees and then look them up in sheet 3 and 4. Sort this this list in reverse order and any that are not found will then form this final list.
E-mail and frequency lookup (sheet 2)
Generally, auto-enrol and opt-out date files will use NI number or Works ID for each employee. You will need to lookup e-mail addresses using works ID or NI numbers. Create a sheet with at least these 2 or 3 columns (e-mail, NI number, works ID). You can use intermediate step sheets to make the mapping more convenient by performing lookup steps:
Use Excel MATCH to find a value in a column. Then wrap this in an INDEX to find the corresponding row:
=INDEX('Employee Details'!AM:AM,MATCH(A2,'Employee Details'!N:N,0))Use IF to convert frequency to the required format:
=IF(C2="Weekly","w1","m1")
Upload summary (sheet 1)
This sheet will contain all the Pay Items and is derived from the other sheets. You can use shading to show which parts of sheet 1 come from the other worksheets. Note that the Opt-out date column on sheet 1 is not the same as the enrol date column.
Ensure you have no more than the allowed number of decimal places for specific inputs. For example, even if cells are formatted to display 2 places, the underlying calculation may result in more decimal digits. If necessary use the ROUND function:
=ROUND('Sheet2'!E2*125,2)
Once you have created a Auto Enrolment Pay Item Upload file, navigate to Pay Items, click Upload… and follow the wizard instructions. By default the first sheet is presented in the upload view.
Report Definition¶
Add a Report Definition for the pension provider and reference all schemes. Some report definitions generate files which can be uploaded (e.g. CSV) while others use a web service (API).
Collect Enrollment information¶
NEST Pensions¶
Log into your NEST Pension account
Click on Workers or Manage Workers
Click on Excel icon marked Download results which will download a file called EMP000912345_ACTIVE.csv
Click View inactive workers
Click on Excel icon marked Download results which will download a file called EMP000912345_INACTIVE.csv
NOW Pensions¶
Log into NOW Pensions account
On the home page select Payroll
Click on the Download employee action file (EAF) button which will download a file called eaf.csv
Peoples Pension¶
Log into Peoples Pensions account
Select Manage employees in the grey menu on the left.
On the Manage employees details view click the button Download Results which will download a file called Peoplesexport.csv