Interfacing, as we understand, is a catch-all term for the routine exchange of common data, in a pre-determined format, using Microsoft Excel. By using our interface service, you will benefit in several ways:
- Standardised data format
- Instant and secure delivery
- Less opportunity for human error, particularly if the data can be extracted straight from payroll
- Easy to check the accuracy and consistency of data at a glance
- One point of contact on our Systems Team and one method of providing data.
In addition to the above, our Systems Team benefits too:
- Regular and predictable flow of data
- Quick and efficient method of creating and maintaining member records
- Data generally of a higher quality and removes legibility issues
- Data is easy to archive and satisfy audit requirements
There is a spreadsheet for the seven interfaces we currently use to capture all the data we need to maintain the active members records:
- CARE Pay (Career Average Revalued Earnings) – provides cumulative pay-to-date PLUS any Assumed Pensionable Pay, for every employee currently on payroll. Data to include casuals regardless of whether they have been paid in that month or not.
- Starters – new entrants to the scheme in any given month. Interface to be used for bulk notification of starters instead of individual submission via Employer Self Service (ESS).
- Current Address – notify us of any employees who have recently changed their home address.
- Hour Changes – for part-time employees who have further changed their hours (including weeks factor changes for term-time employees) or for previously full-time members now going part-time.
- Service Breaks – confirmation of any strikes, parental leave, unpaid leave etc.
- Name Changes – notify us of any employees who have recently changed their name.
- Pay Reference Changes – notify us of a change in pay references.
The interfaces should be completed and provided on a monthly basis and sent to our secure mailbox: firstname.lastname@example.org
We also use a:
- Leaver’s spreadsheet – notify us of employee’s who leave, opt out and retire. This spreadsheet is to be completed as and when you have any leavers and sent to email@example.com
We must insist that interface contents adhere to a strict criteria set out in this guide. This is because we must complete several stages between receipt of an interface and a successful upload and record update. If the data isn’t formatted correctly (for example, an unrecognised column heading), the update process will fail and we cannot proceed until the error is identified.
When you commit to monthly interfacing, you will be responsible for sending data to us on a monthly basis without fail.
With the exception of CARE Pay, if you have no other data to submit in a given month, please use the options on the first tab of the interface template to confirm this.
Completing an Interface
When completing the interfaces, please follow these instructions:
- Alphanumeric content should be presented in capital letters wherever possible. We would prefer the cell formatting within Excel to be Text, while dates should adhere to Date/Short Date format (e.g. 01/01/2001).
- Formatting salaries and numbers or both as Number/Currency/Percentage is not necessary as we prefer them in Text, particularly when leading zeroes are involved.
- Special characters are not required so entries in a salary field do not require a £ prefix and percentages do not require ‘%’ as long as five decimal places have been used.
- Field headings in red text can be safely ignored as these are for internal use only.
- Employees with multiple employments should have each one represented separately with one line per employment. All interfaces relate to an employment as opposed to an employee with the address and name change interfaces being the exceptions.
All interfaces share some common data and details need to be provided as follows:
- NI Number – nine characters in length, adhering to the format XX000000X. Unique to a given employee and the primary identifier used within Peninsula Pensions.
- Surname and Forenames – employees full name in capital letters and long-form where possible (i.e. no initials).
- Job Reference (optional field) – while not unique, used in correlation with an employee number (Payroll Number) it ensures that the correct record is updated which is particularly important if the employee has multiple employments. If you use this field (strongly advised), you must inform us whenever the employment or number changes.
- Payroll Number – a unique identifier for each employee. No set format.
- Scheme – a three-digit number, indicating which pension fund the employee contributes to:
- 001 Devon County Council
- 004 Somerset Council
- 601 Police Officers (new scheme)
- 801 Firefighters (new scheme)
- Employer Number – a unique five-digit number, assigned to every employer contributing to the pension fund(s). Please contact us if you require confirmation of your number.
Member specific data isn’t present on every interface but when required, should be provided using the following format:
- Title – Mr, Mrs, Miss, Ms, Dr
- Sex – Male or Female
- DOB (Date of Birth) – Format as 01/01/2001.
- Address Line and Post Code – presented contiguously in sequence please so try to avoid data in Address Line 1, empty Address Line 2, data in Address Line 3. Do not include punctuation marks, hyphens and commas and abbreviations (e.g. FFF – First Floor Flat). Proper UK post code where appropriate.
- Job Title (optional field). A brief description of the employment in question.
The CARE interface is a rolling update of pensionable pay plus any Assumed Pensionable Pay, for every employee who contributes to the pension scheme. In addition to active employees (including casuals), it should also include employees who have left the scheme or employment part way through that month – though they can be removed from the following months data.
- LGPSMAIN From Date – 1st April of that financial year, or if an employment or contribution commences during the year, state the employment or contribution start date.
- LGPSMAIN To Date – the last day of the month for that pay period (i.e. 31/10/2018 for Octobers interface), or the date of leaving if the employment ceased or stopped contributing to the scheme.
- LGPSMAIN Pay – the pensionable pay received by the employee on this employment to date. Please note that the figures are cumulative – the interface is not a record of how much is paid in a given month.
The above also applies to the 50/50 entries, with the exception being an employee who switches to the 50/50 scheme part way through a year. In this case, all columns should be completed but the ‘50/50 From Date’ will be the start of the month immediately following the final monthly pay period where the employee contributed fully (MAIN).
Assumed Pensionable Pay (APP)
Assumed Pensionable Pay is used when a member moves to reduced or no contractual pay as a result of sickness or injury. It also applies during relevant paid child related leave (ordinary maternity, paternity or adoption leave, paid shared parental leave and any paid additional maternity or adoption leave) or whilst a member is on reserve forces service leave.
In these circumstances, the amount added to the cumulative pensionable pay should be the Assumed Pensionable Pay and not the actual pensionable pay received (if any). This ensures that the member is not adversely affected by the reduction in pay.
Please see the Assumed Pensionable Pay section of the Pensionable Pay page.
There is a video at the top of this page and on the Systems Development Team page to help you with completion of the CARE Pay interface.
This is used to notify us of newly appointed employees but also for those opting-in to the pension scheme or commencing an additional employment.
- Part-Time Ind – Part-time indicator, can be Y for Yes (part-time), C for Casual or left blank if the member is full-time
- Cont Rate – employee contribution rate, as per published contribution bands (between 5.5% and 12.5%). If a member is contributing at a reduced 50/50 rate, the contribution rate should be adjusted accordingly
- Date Joined Fund, Date Joined Employer, Employer Start Date, Date Com Pen Serv, Date Act/Rem Applicable, Date Pen/Rem Applicable – Contrary to the descriptions, every one of these dates should be the date of entry to the pension scheme on this employment
- Actual Rem/Pens Rem – The full-time equivalent salary (where full-time is equivalent to 37 hrs per week, 52 weeks per year) should be entered in both cells. In other words, this is the salary scale point or grade of the employee and not their actual pay. Somerset employers: see note below.
- Part-Time Hours – A percentage of whole-time (37 hrs per week), accurate to five decimal places (e.g. 18.50 hours per week would be presented as 50.00000). Should be left empty if the employee is Full-Time or Casual.
You should also take weeks factor into account if the employment is affected by school term-time hours. All Somerset employers, please see the note below.
Somerset Employers: Hours Worked – A percentage of whole-time (37 hrs per week), expressed to five decimal places (e.g. an employment of 18.50 hours per week would be presented as 50.00000). No adjustment should be made for the number of weeks worked each year.
Somerset Employers: FTE Salaries – Term-time employments should have their FTE adjusted or reduced according to the total number of weeks worked each year.
For example: Actual basic annual salary (adjusted to take into account the number of weeks worked each year), plus any additional pensionable recurring pay, multiplied by the basic hours worked (37), divided by actual hours worked each week.
Hour Change Interface
This is used to inform us every time an employee changes their hours from part-time to full-time, vice versa, or anything and everything in between.
Changes from contracted hours to casual employment should be included, but casual hours notifications are not required until the end of year as part of the annual return process.
Multiple hour changes for the same employee or employment may be recorded on the interface. This is particularly useful if you suspect a record is out of date and may be missing a few changes.
Please adhere to the data requirements outlined above in the Starter Interface section.
Service Breaks Interface
This is used to inform us of employees who had a period of unpaid leave and have not planned to cover the deficit via Additional Pension Contributions (APC). If an employee has elected to purchase an APC, they should not be included this interface. Please see the Additional Pension Contributions and Absences page of our website for guidance on how to notify us of these employees.
Please do not state future dates on this interface as it is a confirmation of past events only.
- Break From – The first day of unpaid service. Note that, particularly with Parental Leave, this date may well be different or later than the first day of actual absence.
- Break To – The final day of unpaid service.
- Reason – this is the reason for unpaid leave and should be one capital letter, using the following key:
- S = Strike
- M = Parental Leave (previously Maternity Leave)
- A = Leave of Absence (authorised)
- E = Education Break
- P = Paid Back (only applicable to pre-2014 LGPS)
- U = Unauthorised Absence
- Part Day – A simple Yes or No answer is required if the unpaid absence does not cover the entire working day. If no entry is made in this column, we will default to No.
This spreadsheet is used to notify us of employee’s who leave, opt out and retire. Please refer to the Guidance tab within the spreadsheet to understand what needs to be provided in each section.
It’s designed for bulk submission please include all the leavers for that month on the ‘Leavers’ tab. We recognise that some employers may not have the ability to extract data in bulk from the payroll system so there is an ‘Input’ page similar to the current leaver form which then transfers the data to the leaver spreadsheet when you press the submit button.
If you add data manually to the ‘Leavers’ tab (not using the ‘Input’ page), you will need to insert a line above your entry so that that formatting is still in place.
- Date of leaving – confirm the date of Leaving or date the member opted out. For Flexible Retirement cases, please state the last day before the change in grade and/or hours as agreed with the employee.
- Reasons for Leaving – use the drop-down to select the reason. Its important that you only use the choices available within the spreadsheet. Changing any wording causes problems for us when we upload the data to the members record.
A quick guide to each type of retirement is listed on the spreadsheet – please refer to the Leaving the LGPS page for more detailed information on each type and strain costs.
Please do not complete the leaver spreadsheet if members are leaving your employment and transferring to another employer under TUPE. Follow the process outlined on our website instead.
For any employer led retirements we will refer to the Employer Key Decisions form on our website for how to pay the strain costs and enhancing any benefits. If ‘on a case by case basis’ has been selected, your HR department will need to complete the HR Authorisation form on our website and send to: firstname.lastname@example.org
The columns in R to AC (yellow) are conditional and to be completed if the following reasons are selected:
- Opted out
- Death in Service
- Flexible Retirement
- Ill Health Retirement
- Variable/Casual employee – if yes is selected here, you need to only provide the CARE pay for these employees as most should be post 2014 membership. We will contact you separately if we need to request additional data for pre 14 membership. If you are updating the spreadsheet in bulk, we are happy for you to leave this section blank and just complete the LGPS 2014 CARE Pay section. If the member has pre-2014 membership we may need to request more information from you but will do that separately.
- Pension Contributions – If there is less than 2 years membership, you will need to provide the last three years pension contributions (AJ to AO) – we ask for 3 years as the date of leaving may span 3 financial years ending on 31st March.
- For example, membership is 01/03/2017 to 20/02/2019, we need contributions for 2016/17, 2017/18 and 2018/19
- If there is more than 2 years membership, you will only need to provide the final years pension contributions (AJ & AK)
LGPS 2008 Final Salary Pay
This section requires you to confirm the full-time equivalent pensionable pay for any pre 2014 benefits (Final Salary Pension) the member has built up. We ask that you check your records and complete this section for all leavers with pre-1st April 2014 membership.
Any pension built up to 31st March 2014 has the protection of a final salary link to the date of leaving. The final pay used to calculate this part of a member’s benefits will be the pensionable pay earned over the last 365 days of membership uprated to a full-time equivalent (FTE). For example, if a member left on 22nd August 2019, you would need to provide the pensionable pay for the period 23rd August 2018 to 22nd August 2019.
Non-contractual overtime must not be included in these figures as it’s not deemed pensionable under the 2008 definition of pensionable pay.
The total pensionable pay for the final 365 days of membership must be provided along with the best of the previous two years if higher – this is a standard pay protection provided by the regulations. For example, the date of leaving is 25/03/2015. Figures to be provided for the periods 26/03/2014 to 25/03/2015, 26/03/2013 to 25/03/2014 and 26/03/2012 to 25/03/2013
We no longer expect employers to provide a breakdown of the FTE figure for each change in hours. Instead, we are just asking you to provide an FTE figure for the total 365 days period and percentage of hours as at the date of leaving so any changes in hours or absences should be factored into your calculations. Therefore, you should only ever need to report a maximum of 3 lines.
Reduction or restriction in pay – If the member has suffered a drop in their permanent pensionable pay since 1st April 2008 and this falls within the 10 years before they leave, they can choose to use the best 3-year average pensionable pay in the last 13 years (ending on 31st March) in the calculation of their benefits.
Members must elect to apply this protection in writing to Peninsula Pensions at least a month before they leave. We will ask you to complete a separate Previous Years Pensionable Pay Request form if this applies.
*Important Note* Full-Time Equivalent (FTE) salaries and term-time hours are treated slightly differently depending on which pension fund the employer contributes to:
- Devon fund employers should adjust the Part-Time Hours to reflect term-time/weeks factors but leave the FTE salary alone.
- Somerset employers should adjust the FTE salary but leave the hours alone.
LGPS 2014 CARE Pay
CARE stands for Career Average Revalued Earnings which is simply a member’s actual pensionable pay for the period.
Non-contractual overtime and additional hours are deemed as pensionable under the 2014 LGPS scheme so can be included in the figures.
The total actual pensionable pay must be stated and include Assumed Pensionable Pay where appropriate – there is no indicator to confirm if the figure includes APP. Please see the Pensionable pay page for more information on Assumed Pensionable Pay (APP). The figures supplied will be accepted as final and will therefore overwrite pay previously provided via your monthly CARE data interface.
Systems Team Interaction
Assuming the interfaces are completed correctly, the process of updating records should be relatively smooth and seamless. However, the process does rely upon data being formatted correctly and on rare occasions we have had to return data to you for further attention.
Once an interface has been uploaded to the member records, there are often error lists to work through. The errors can range over several categories and many will be self-apparent and quickly sorted out by the Systems Team, but others will require clarification from you. We will contact you via email and request a turn-around time of two months, after which a chaser email will be sent. If no reply or resolution after another month passes, the Employer & Communications Team will get involved.
If you have any queries regarding the completion of the Excel interface templates, please email our shared mailbox: email@example.com
The Pensions Interfaces Mailbox is monitored throughout the working day, so may be used as a method of contacting the Systems Team with any general queries or questions about interfacing. If you prefer to contact the Systems Team member who looks after your data, please click on their name for their direct email address:
- Tom Bird – Devon County Council and Devon fund employers (non-schools)
- James Dyche – Devon fund schools
- Sophie Downer – Somerset Council, Somerset fund employers (non-schools), Police Officers and Civilians and Firefighters and Civilians
- Steve Bradford – Somerset fund schools
- Edward Hipkiss – District, Borough, City, Parish and Town Councils.
Alternatively, you can call 01392 383000 and ask for the person by name. Please note that the team should only be contacted specifically regarding the annual return or interface process and contents.