Guidance

Other Employment Related Securities: technical note

Updated 5 April 2023

1. Changes to the 2022 to 2023 Employment Related Securities (ERS) templates

The changes which have been made to the 2022 to 2023 ERS templates are summarised by the relevant template:

• Other options
  • Question 7 ― column title amended, validation amended and completion now mandatory
  • Question 8 ― completion now mandatory
  • Question 36 ―column Title amended
  • Question 41 ― completion now mandatory
• Other acquisitions
  • Question 7 ― column Title amended, validation amended and completion now mandatory
  • Question 8 ― completion now mandatory
  • Question 23 ― column Title amended
• Other restricted securities
  • Question 7 ― column Title amended, validation amended and completion now mandatory
  • Question 8 ― completion now mandatory
  • Question 14 ― column Title amended
• Other benefits
  • Question 7 ― column Title amended, validation amended and completion now mandatory
  • Question 8 ― completion now mandatory
• Other convertible
  • Question 7 ― column Title amended, validation amended and completion now mandatory
  • Question 8 ― completion now mandatory
• Other notional
  • Question 7 ― column Title amended, validation amended and completion now mandatory
  • Question 8 ― completion now mandatory
• Other enhancements
  • Question 7 ― column Title amended, validation amended and completion now mandatory
  • Question 8 ― completion now mandatory
• Other sold
  • Question 7 ― column Title amended, validation amended and completion now mandatory
  • Question 8 ― completion now mandatory

1.1 Overview

This document is a guide for anybody who needs to report events as part of their ERS annual return and want to create their own ERS files to capture share related event information during the tax year. You’ll need to create your own file if you do not wish to use the HMRC templates.

When creating your files, you’ll need to provide all the information that’s relevant to the particular ERS return that you’re making. If the column does not match the technical specification detailed in this guide, it will not be possible to submit the file successfully.

1.2 ERS file attachments

A file can only be accepted in the following formats:

• ODS format (.ods template downloaded from GOV.UK)
• ODS format (.ods spreadsheet created by the user, but matching the HMRC template specification)
• CSV format (.csv file(s) created by the user, originating from the HMRC ODS downloaded templates, which meets the HMRC template specification)
• CSV format (.csv file(s) created by the user from scratch, must match the HMRC template specification)

The majority of spreadsheet software solutions can use ODS and CSV files. Office packages such as Microsoft Excel, Google Sheets and OpenOffice can convert their native formats into ODS and CSV files.

2. General information about the required format of ODS templates

If you choose to create your own version of an ODS template rather than download the HMRC ODS template, the specifications must meet HMRC requirements. The required formatting is detailed in this guide. Each file consists of a number of sheets (sometimes called tabs). Each sheet will contain specific share event information for that scheme.

You cannot change the name of any ODS sheet name that you submit. Changing the name of a sheet will result in an error and you will not be able to successfully upload your file. For more detail on the specific sheet names read section 3.2.

2.1 ODS File names

You must use file names must be less than 240 characters in length. For ODS templates there are no other restrictions on the file name.

2.2 Column Information

You can find information describing the format of each column in section 3 Table information to create a template..

2.3 Template layout

The data in any user created ODS file must meet the required specifications of the HMRC ODS templates. All data must start in Cell A10, any data provided prior to this cell cannot be checked for formatting and will not be identified by HMRC and therefore will not be validated.

2.4 Table headings explained

Section 3 contains tables for each scheme type and name. There are six column headings on each table and you must follow the specifications detailed in this section to create your ODS template.

The following information explains the six column headings of the tables.

Column title

All column titles must match the required HMRC formatting detailed in section 3. You must make sure all punctuation and grammar is matched. If the column does not accurately match the technical specification, it will not be possible to submit the file successfully.

Data type

This details whether your response to the question should be numbers, characters (that is letters), or a combination of numbers and characters, and also details the maximum amount of numbers and characters you can provide per cell.

Numbers will either be whole or decimal, the data type column in the section 3 tables show what format they should be in. The maximum size of the number is the whole number part plus any decimal.

For example, ‘NUM11V2’ means data can be up to 14 characters in length. ‘NUM11’ means you can have a maximum of 11 numbers, followed by the ‘V’ which represents the decimal place and the ‘2’ following the ‘V’ means you can have up to 2 numbers following the decimal place, for example (12345678910.12).

Characters (letters) are represented by the term CHAR followed by a number, for example ‘CHAR3’, this is the maximum number of characters (letters) a filed can contain.

Formatting

This details where specific formatting is required and what the formatting should be. If this formatting does not match the technical specification this will result in errors and until corrected you will be unable to successfully upload your ERS return.

Example data

This gives an example of the data format required.

Mandatory, Conditional Mandatory or Optional

This shows if a response to the question (in column 1) is either Mandatory (M), Conditional Mandatory (CM) or Optional (O).

Mandatory means a response is always required regardless of any other information provided.

Conditional Mandatory means a response is only required if you have provided the relevant response on a previous question. For example, if a share option has been granted in a CSOP then the ‘Date of Grant’ would need to be reported.

Optional means this field can be left blank but should be completed if you know the information requested.

CSV  position

‘CSV position’ is only applicable for CSV files.

3. Table information to create a template

The following sections contains the information you require to create your own ODS templates.

3.1 Other ODS file downloads

If you choose to use the HMRC ODS template, you can download the Other ERS schemes and arrangements: end of year template.

3.2 ‘Other’ Sheet titles

The OTHER spreadsheet contains 9 sheets (sheet titles must be entered exactly as shown in the table).

Sheet number	Title
1	Other_Grants_V4
2	Other_Options_V4
3	Other_Acquisition_V4
4	Other_RestrictedSecurities_V4
5	Other_OtherBenefits_V4
6	Other_Convertible_V4
7	Other_Notional_V4
8	Other_Enhancement_V4
9	Other_Sold_V4

3.3 Description of asset lookup (Other_Acquisition)

This table will help you complete Other_Acquisition question 19.

Number	Description
1	Shares
2	Rights under contracts of insurance
3	Debentures, debenture stock, loan stock, bonds certificates of deposit or other instruments
4	Warrants
5	Certificates and other instruments conferring rights in respect of securities
6	Units in a collective investment scheme
7	Futures
8	Rights under contracts of difference
9	Alternative finance arrangements

3.4 Security type lookup (for use in Other_Acquisition)

This table will help you complete Other_Acquisition question 25.

Number	Description
1	Restricted
2	Unrestricted
3	Convertible

3.5 Nature of restriction lookup (for use in Other_Acquisition)

This table will help you complete Other_Acquisition question 26.

Number	Description
1	Forfeiture
2	Restriction on freedom to retain or dispose of securities
3	Potential disadvantage to individual in respect of the securities

3.6 Nature of artificial reduction lookup (for use in Other_Acquisition)

This table will help you complete Other_Acquisition question 36.

Number	Description
1	A non commercial transaction
2	Scheme or arrangement designed to avoid tax of NICs
3	Inter-company transaction on a non arm’s length transaction

3.7 ‘Other’ Grant of options formatting guide (Other_Grants_V4)

This table details the information and the format in which it is required when creating your own ODS template. Although the questions in this table are shown vertically, when creating your ODS the questions must be entered horizontally with the relevant information entered under each question column and each row showing the data of each individual employee you are reporting on.

For example this sheet has 4 questions therefore the questions should be shown from cell A to D in row 9, and if you report changes to 3 employees this would require the information to be entered in 3 rows, from row 10 to row 13.

Column title	Data type	Formatting	Example data	Mandatory/ Conditional Mandatory or Optional	CSV position
1. Date of grant (yyyy-mm-dd)	DATE	yyyy-mm-dd	2014-08-30	M	1
2. Number of employees granted options	NUM6	—	123	O	2
3. Unrestricted market value of a security at date of grant £ e.g. 10.1234	NUM13V4	4 decimal places	10.2356	O	3
4. Number of securities over which options granted e.g. 100.00	NUM11V2	2 decimal places	120.56	O	4

3.8 ‘Other’ option events formatting guide (Other_Options_V4)

This table details the information and the format in which it is required when creating your own ODS template. Although the questions in this table are shown vertically, when creating your ODS the questions should be entered horizontally with the relevant information populated under each question column and each row showing the data of each individual employee you are reporting on.

For example this sheet has 42 questions therefore the questions should be shown from cell A to AP in row 9, and if you report changes to 3 employees this would require the information to be entered in 3 rows, from row 10 to row 13.

Column title	Data type	Formatting	Example data	Mandatory/ Conditional Mandatory or Optional	CSV position
1. Date of event yyyy-mm-dd	DATE	yyyy-mm-dd	2014-08-30	M	1
2. Is the event in relation to a disclosable tax avoidance scheme? (yes/no)	CHAR3	yes/no	yes	M	2
3. If yes, enter the eight-digit scheme reference number (SRN)	NUM8	IF YN DOTAS_REF = Yes then Mandatory else NULL	99999999	CM	3
4. Employee first name	CHAR35	—	John	M	4
5. Employee second name (if applicable)	CHAR35	—	S	O	5
6. Employee last name	CHAR35	—	Smith	M	6
7. National Insurance number	CHAR9	The NINO should be in the format AANNNNNNA, the last character should be alpha	QQ123456A	M	7
8. PAYE reference of employing company	CHAR14	—	123/XZ55555555	M	8
9. Date of grant of option subject to the reportable event yyyy-mm-dd	DATE	yyyy-mm-dd	2014-08-30	M	9
10. Grantor company name	CHAR120	—	Company	M	10
11. Grantor company address line 1	CHAR27	—	1 Beth street	M	11
12. Grantor company address line 2	CHAR27	—	Bucknall	O	12
13. Grantor company address line 3	CHAR27	—	Stoke	O	13
14. Grantor company address line 4	CHAR18	—	Staffordshire	O	14
15. Grantor company country	CHAR18	—	United Kingdom	O	15
16. Grantor company postcode	CHAR8	—	SE1 2AB	O	16
17. Grantor Company Registration Number (CRN), if applicable	CHAR10	—	AC097609	M	17
18. Grantor company Corporation Tax reference, if applicable	CHAR10	—	1234567890	O	18
19. Grantor company PAYE reference	CHAR14	—	123/XZ55555555	O	19
20. Name of the company whose securities under option	CHAR120	—	Company	M	20
21. Company whose securities under option — address line 1	CHAR27	—	1 Beth street	M	21
22. Company whose securities under option — Address line 2	CHAR27	—	Bucknall	O	22
23. Company whose securities under option — Address line 3	CHAR27	—	Stoke	O	23
24. Company whose securities under option — Address line 4	CHAR18	—	Staffordshire	O	24
25. Company whose securities under option — Country	CHAR18	—	United Kingdom	O	25
26. Company whose securities under option — Postcode	CHAR8	—	SE1 2AB	O	26
27. Company Reference Number (CRN) of company whose securities under option	CHAR10	—	AC097609	O	27
28. Corporation Tax reference of company whose securities under option	CHAR10	—	1234567890	O	28
29. PAYE reference of company whose securities under option	CHAR14	—	123/XZ55555555	O	29
30. Were the options exercised? (yes/no). If yes go to next question If no go to question 38	CHAR3	yes/no	yes	O	30
31. Total number of securities employee entitled to on exercise of the option before any cashless exercise or other adjustment e.g. 100.00	NUM11V2	2 decimal places	120.00	O	31
32. If consideration was given for the securities, the amount given per security £ e.g. 10.1234	NUM13V4	4 decimal places	10.1234	O	32
33. If securities were acquired, Market Value (see note in guidance) of a security on the date of acquisition £ e.g. 10.1234	NUM13V4	4 decimal places	10.0000	O	33
34. If shares were acquired, are the shares listed on a recognised stock exchange? (yes/no). If yes go to question 37 If no go to next question	CHAR3	yes/no	yes	O	34
35. If shares were not listed on a recognised stock exchange, was valuation agreed with HMRC? (yes/no)	CHAR3	yes/no	yes	O	35
36. If yes, enter the HMRC valuation reference given	CHAR 10	—	12345678 or qq123456	CM	36
37. If the shares were acquired, total deductible amount excluding any consideration given for the securities £ e.g. 10.1234. Then go to question 40	NUM13V4	4 decimal places	10.0000	O	37
38. If securities were not acquired, was money or value received on the release, assignment, cancellation or lapse of the option? (yes/no). If yes go to next question If no, no further information required on this event.	CHAR3	yes/no	yes	O	38
39. If yes, amount of money or value received £ e.g. 10.1234	NUM13V4	4 decimal places	10.0000	O	39
40. Was a NICs election or agreement operated? (yes/no)	CHAR3	yes/no	yes	M	40
41. Was PAYE operated? (yes/no)	CHAR3	yes/no	yes	M	41
42. Was any adjustment made for amounts subject to apportionment for residence or duties outside the UK (yes/no)	CHAR3	yes/no	yes	O	42

3.9 ‘Other’ Acquisition of securities formatting guide (Other_Acquisition_V4)

This table details the information and the format in which it is required when creating your own ODS template. Although the questions in this table are shown vertically, when creating your ODS the questions should be entered horizontally with the relevant information populated under each question column and each row representative of data per individual employee you are reporting on.

For example this sheet has 40 questions therefore the questions should be shown from cell A to AN in row 9, and if you report changes to 3 employees this would require the information to be entered in 3 rows, from row 10 to row 13.

Column title	Data type	Formatting	Example data	Mandatory/ Conditional Mandatory or Optional	CSV Position
1. Date of event (yyyy-mm-dd)	DATE	yyyy-mm-dd	2014-08-30	M	1
2. Is the event in relation to a disclosable tax avoidance scheme? (yes/no)	CHAR3	yes/no	yes	M	2
3. If yes enter the eight-digit scheme reference number (SRN)	NUM8	IF YN DOTAS_REF = Yes then Mandatory else NULL	12345678	CM	3
4. Employee first name	CHAR35	—	John	M	4
5. Employee second name (if applicable)	CHAR35	—	S	O	5
6. Employee last name	CHAR35	—	Smith	M	6
7. National Insurance number	CHAR9	The NINO should be in the format AANNNNNNA, the last character should be alpha	QQ123456A	M	7
8. PAYE reference of employing company	CHAR14	—	123/XZ55555555	M	8
9. Name of the company whose securities acquired	CHAR120	—	Company	M	9
10. Company whose securities acquired — address line 1	CHAR27	—	1 Beth street	M	10
11. Company whose securities acquired — address line 2	CHAR27	—	Bucknall	O	11
12. Company whose securities acquired — address line 3	CHAR27	—	Stoke	O	12
13. Company whose securities acquired — address line 4	CHAR18	—	Staffordshire	O	13
14. Company whose securities acquired — country	CHAR18	—	United Kingdom	O	14
15. Company whose securities acquired — postcode	CHAR8	—	SE1 2AB	O	15
16. Company Reference Number (CRN) of company whose securities acquired	CHAR10	—	AC097609	O	16
17. Corporation Tax reference of company whose securities acquired	CHAR10	—	1234567890	O	17
18. PAYE reference of company whose securities acquired	CHAR14	—	123/XZ55555555	O	18
19. Description of security. Enter a number from 1 to 9. Follow the link in cell A7 for a list of security types	NUMBER	See Description of asset lookup (Other_Acquisition)	1	M	19
20. If the securities are not shares enter ‘ no’ and go to question 24 If the securities are shares, are they part of the largest class of shares in the company? (yes/no)	CHAR3	yes/no	yes	M	20
21. If the securities are shares, are they listed on a recognised stock exchange? (yes/no) If no go to question 22, If yes go to question 24	CHAR3	yes/no	yes	CM	21
22. If shares were not listed on a recognised stock exchange, was valuation agreed with HMRC? (yes/no)	CHAR3	yes/no	yes	CM	22
23. If yes, enter the HMRC valuation reference given	CHAR 10	—	12345678 or qq123456	CM	23
24. Number of securities acquired e.g. 100.00	NUM11V2	2 decimal places	120.55	O	24
25. Security type. Enter a number from 1 to 3, (follow the link at cell A7 for a list of security types). If restricted go to next question. If convertible go to question 32. If both restricted and convertible enter 1 and answer all questions 26 to 32. If neither restricted nor convertible go to question 29.	Drop Down	See security type lookup (for use in Other_Acquisition)	2	M	25
26. If restricted, nature of restriction. Enter number from 1-3, follow the link at cell A7 for a list of restrictions	NUMBER	See Nature of restriction lookup (for use in Other_Acquisition)	3	CM	26
27. If restricted, length of time of restriction in years (if less than a whole year, enter as a decimal fraction, for example 0.6)	NUM6V2	2 decimal places	123	O	27
28. If restricted, actual market value per security at date of acquisition £ e.g. 10.1234 (no entry should be made if an election to disregard ALL restrictions is operated)	NUM13V4	4 decimal places	10.5236	O	28
29. Unrestricted market value per security at date of acquisition £ e.g. 10.1234	NUM13V4	4 decimal places	10.5236	O	29
30.If restricted, has an election been operated to disregard restrictions? (yes/no)	CHAR3	yes/no	yes	CM	30
31. If an election has been operated to disregard restrictions, have all or some been disregarded? (enter all or some)	CHAR4	Enter all or some	All	CM	31
32. If convertible, market value per security ignoring conversion rights £ e.g. 10.1234	NUM13V4	4 decimal places	10.5623	O	32
33. Total price paid for the securities £ e.g. 10.1234	NUM13V4	4 decimal places	100.1256	O	33
34. Was the price paid in pounds sterling? (yes/no)	CHAR3	yes/no	yes	M	34
35.Was there an artificial reduction in value on acquisition? (yes/no) If ‘yes’ go to question 36, if ‘No’ go to question 37	CHAR3	yes/no	yes	M	35
36. If there was an artificial reduction in value, nature of the artificial reduction Enter a number from 1 to 3. Follow the link in cell A7 for a list of types of artificial restriction	Drop Down	See nature of artificial reduction lookup (for use in Other_Acquisition)	2	CM	36
37. Were shares acquired under an employee shareholder arrangement? (yes/no)	CHAR3	yes/no	yes	M	37
38. If shares were acquired under an employee shareholder arrangement, was the total actual market value (AMV) of shares £2,000 or more? (yes/no)	CHAR3	yes/no	yes	CM	38
39. Was PAYE operated? (yes/no)	CHAR3	yes/no	yes	M	39
40. Was any adjustment made for amounts subject to apportionment for residence or duties outside of the UK (yes/no)	CHAR3	yes/no	yes	M	40

3.10 ‘Other’ Restricted securities post acquisition events formatting guide (Other_RestrictedSecurities_V4)

This table details the information and the format in which it is required when creating your own ODS template. Although the questions in this table are shown vertically, when creating your ODS the questions should be entered horizontally with the relevant information populated under each question column and each row showing the data of each individual employee you are reporting on.

For example this sheet has 20 questions therefore the questions should be shown from cell A to T in row 9, and if you report changes to 3 employees this would require the information to be entered in 3 rows, from row 10 to row 13.

Column Title	Data type	Formatting	Example data	Mandatory/ Conditional Mandatory or Optional	CSV Position
1. Date of event (yyyy-mm-dd)	DATE	yyyy-mm-dd	2014-08-30	M	1
2. Is the event in relation to a disclosable tax avoidance scheme? (yes/no)	CHAR3	yes/no	yes	M	2
3. If yes, enter the eight-digit scheme reference number (SRN)	NUM8	IF YN DOTAS_REF = Yes then Mandatory else NULL	12345678	CM	3
4. Employee first name	CHAR35	—	John	M	4
5. Employee second name (if applicable)	CHAR35	—	S	O	5
6. Employee last name	CHAR35	—	Smith	M	6
7. National Insurance number	CHAR9	This should be in the format AANNNNNNA, the last character should be alpha	QQ123456A	M	7
8. PAYE reference of employing company	CHAR14	—	123/XZ55555555	M	8
9. Date securities originally acquired (yyyy-mm-dd)	DATE	yyyy-mm-dd	2014-08-30	M	9
10. Number of securities originally acquired e.g. 100.00	NUM11V2	2 decimal places	120.22	O	10
11. For disposals or lifting of restrictions, total chargeable amount £ e.g. 10.1234	NUM13V4	4 decimal places	10.2356	O	12
12. For lifting of restrictions, are the shares listed on a recognised stock exchange? (yes/no)	CHAR3	yes/no	yes	CM	13
13. If shares were not listed on a recognised stock exchange, was valuation agreed with HMRC? (yes/no)	CHAR3	yes/no	yes	CM	14
14. If yes, enter the HMRC valuation reference given	CHAR 10	—	12345678 or qq123456	CM	15
15. For variations, date of variation (yyyy-mm-dd)	DATE	yyyy-mm-dd	2014-08-30	CM	16
16. For variations, Actual Market Value (AMV) per security directly before variation £ e.g. 10.1234	NUM13V4	4 decimal places	10.2589	O	17
17. For variations, Actual Market Value (AMV) per security directly after variation £ e.g. 10.1234	NUM13V4	4 decimal places	10.2589	O	18
18. Has a National Insurance Contribution election or agreement been operated (yes/no)	CHAR3	yes/no	yes	M	21
19. Was PAYE operated? (yes/no)	CHAR3	yes/no	yes	M	22
20. Was any adjustment made for amounts subject to apportionment for residence or duties outside the UK (yes/no)	CHAR3	yes/no	yes	M	23

3.11 ‘Other’ Receipt of other benefits from securities post acquisition formatting guide Other_OtherBenefits_V4)

This table details the information and the format in which it is required when creating your own ODS template. Although the questions in this table are shown vertically, when creating your ODS the questions should be entered horizontally with the relevant information populated under each question column and each row showing the data of each individual employee you are reporting on.

For example the table has 13 questions therefore the questions should be shown from cell A to M in row 9, and if you report changes to 3 employees this would require the information to be entered in 3 rows, from row 10 to row 13.

Column title	Data type	Formatting	Example data	Mandatory/ Conditional Mandatory or Optional	CSV Position
1. Date of event (yyyy-mm-dd)	DATE	yyyy-mm-dd	2014-08-30	M	1
2. Is the event in relation to a disclosable tax avoidance scheme? (yes/no)	CHAR3	yes/no	yes	M	2
3. If yes enter the eight-digit scheme reference number (SRN)	NUM8	IF YN DOTAS_REF = Yes then Mandatory else NULL	12345678	CM	3
4.Employee first name	CHAR35	—	John	M	4
5. Employee second name (if applicable)	CHAR35	—	S	O	5
6. Employee last name	CHAR35	—	Smith	M	6
7. National Insurance number	CHAR9	This should be in the format AANNNNNNA, the last character should be alpha	QQ123456A	M	7
8. PAYE reference of employing company	CHAR14	—	123/XZ55555555	M	8
9. Date securities originally acquired (yyyy-mm-dd)	DATE	yyyy-mm-dd	2014-08-30	M	9
10. Number of securities originally acquired e.g. 100.00	NUM11V2	2 decimal places	120.12	O	10
11. Amount or market value of the benefit £ e.g. 10.1234	NUM13V4	4 decimal places	10.2356	O	11
12. Was PAYE operated? (yes/no)	CHAR3	yes/no	yes	M	12
13. Was any adjustment made for amounts subject to apportionment for residence or duties outside the UK (yes/no)	CHAR3	yes/no	yes	M	13

3.12 ‘Other’ Convertible Securities Post-Acquisition formatting guide (Other_Convertible_V4)

This table details the information and the format in which it is required when creating your own ODS template. Although the questions in this table are shown vertically, when creating your ODS the questions should be entered horizontally with the relevant information populated under each question column and each row showing the data of each individual employee you are reporting on.

For example the table has 15 questions therefore the questions should be shown from cell A to O in row 9, and if you report changes to 3 employees this would require the information to be entered in 3 rows, from row 10 to row 13.

Column Title	Data Type	Formatting	Example data	Mandatory/ Conditional Mandatory or Optional	CSV Position
1. Date of event (yyyy-mm-dd)	DATE	yyyy-mm-dd	2014-08-30	M	1
2. Is the event in relation to a disclosable tax avoidance scheme? (yes/no)	CHAR3	yes/no	yes	M	2
3. If yes, enter the eight-digit scheme reference number (SRN)	NUM8	IF YN DOTAS_REF = Yes then Mandatory else NULL	12345678	CM	3
4. Employee first name	CHAR35	—	John	M	4
5. Employee second name (if applicable)	CHAR35	—	S	O	5
6. Employee last name	CHAR35	—	Smith	M	6
7. National Insurance number	CHAR9	The NINO should be in the format AANNNNNNA, the last character should be alpha	QQ123456A	M	7
8. PAYE reference of employing company	CHAR14	—	123/XZ55555555	M	8
9. Date securities originally acquired (yyyy-mm-dd)	DATE	yyyy-mm-dd	2014-08-30	M	9
10. Number of securities originally acquired e.g. 100.00	NUM11V2	2 decimal places	120.22	O	10
11. For receipt of money or value, enter amount or market value of the benefit £ e.g. 10.1234 Then go to question 14	NUM13V4	4 decimal places	10.1111	O	11
12. For conversion, disposal or release of entitlement to convert, total chargeable amount £ e.g. 10.1234	NUM13V4	4 decimal places	10.2222	O	12
13. Has a National Insurance Contribution election or agreement been operated (yes/no)	CHAR3	yes/no	yes	M	13
14. Was PAYE operated? (yes/no)	CHAR3	yes/no	yes	M	14
15. Was any adjustment made for amounts subject to apportionment for residence or duties outside the UK? (yes/no)	CHAR3	yes/no	yes	M	15

3.13 ‘Other’ Discharge of notional loans post-acquisition formatting guide (Other_Notional_V4)

This table details the information and the format in which it is required when creating your own ODS template. Although the questions in this table are shown vertically, when creating your ODS the questions should be entered horizontally with the relevant information populated under each question column and each row showing the data of each individual employee you are reporting on.

For example this sheet has 13 questions therefore the questions should be shown from cell A to M in row 9, and if you report changes to 3 employees this would require the information to be entered in 3 rows, from row 10 to row 13.

Column Title	Data Type	Formatting	Example data	Mandatory/ Conditional Mandatory or Optional	CSV Position
1. Date of event (yyyy-mm-dd)	DATE	yyyy-mm-dd	2014-08-30	M	1
2. Is the event in relation to a disclosable tax avoidance scheme? (yes/no)	CHAR3	yes/no	yes	M	2
3. If yes, enter the eight-digit scheme reference number (SRN)	NUM8	IF YN DOTAS_REF = Yes then Mandatory else NULL	12345678	CM	3
4. Employee first name	CHAR35	—	John	M	4
5. Employee second name (if applicable)	CHAR35	—	S	O	5
6. Employee last name	CHAR35	—	Smith	M	6
7. National Insurance number	CHAR9	The NINO should be in the format AANNNNNNA, the last character should be alpha	QQ123456A	M	7
8. PAYE reference of employing company	CHAR14	—	123/XZ55555555	M	8
9. Date securities originally acquired (yyyy-mm-dd)	DATE	yyyy-mm-dd	2014-08-30	M	9
10. Number of securities originally acquired e.g. 100.00	NUM11V2	2 decimal places	120.22	O	10
11. Amount of notional loan discharged £ e.g. 10.1234	NUM13V4	4 decimal places	10.2222	O	11
12. Was PAYE operated? (yes/no)	CHAR3	yes/no	yes	M	12
13. Was any adjustment made for amounts subject to apportionment for residence or duties outside the UK? (yes/no)	CHAR3	yes/no	yes	M	13

3.14 ‘Other’ Artificial enhancement of market value formatting guide (Other_Enhancement_V4)

This table details the information and the format in which it is required when creating your own ODS template. Although the questions in this table are shown vertically, when creating your ODS the questions should be entered horizontally with the relevant information populated under each question column and each row showing the data of each individual employee you are reporting on.

For example this table has 14 questions therefore the questions should be shown from cell A to N in row 9, and if you report changes to 3 employees this would require the information to be entered in 3 rows, from row 10 to row 13.

Column Title	Data Type	Formatting	Example data	Mandatory/ Conditional Mandatory or Optional	CSV Position
1. Date of event (yyyy-mm-dd)	DATE	yyyy-mm-dd	2014-08-30	M	1
2. Is the event in relation to a disclosable tax avoidance scheme? (yes/no)	CHAR3	yes/no	yes	M	2
3. If yes, enter the eight-digit scheme reference number (SRN)	NUM8	IF YN DOTAS_REF = Yes then Mandatory else NULL	12345678	CM	3
4. Employee first name	CHAR35	—	John	M	4
5. Employee second name (if applicable)	CHAR35	—	S	O	5
6. Employee last name	CHAR35	—	Smith	M	6
7. National Insurance number	CHAR9	The NINO should be in the format AANNNNNNA, the last character should be alpha	QQ123456A	M	7
8. PAYE reference of employing company	CHAR14	—	123/XZ55555555	M	8
9. Date securities originally acquired (yyyy-mm-dd)	DATE	yyyy-mm-dd	2014-08-30	M	9
10. Number of securities originally acquired e.g. 100.00	NUM11V2	2 decimal places	120.22	O	10
11. Total unrestricted market value (UMV) on 5th April or date of disposal if earlier £ e.g. 10.1234	NUM13V4	4 decimal places	10.3333	O	11
12. Total UMV ignoring effect of artificial increase on date of taxable event £ e.g. 10.1234	NUM13V4	4 decimal places	10.2222	O	12
13. Was PAYE operated? (yes/no)	CHAR3	yes/no	yes	M	13
14. Was any adjustment made for amounts subject to apportionment for residence or duties outside the UK? (yes/no)	CHAR3	Yes/no	yes	M	14

3.15 ‘Other’ Securities sold for more than market value post-acquisition formatting guide (Other_Sold_V4)

This table details the information and the format in which it is required when creating your own ODS template. Although the questions in this table are shown vertically, when creating your ODS the questions must be entered horizontally with the relevant information entered under each question column and each row showing the data of each individual employee you are reporting on.

For example this sheet has 14 questions therefore the questions should be shown from cell A to N in row 9, and if you report changes to 3 employees this would require the information to be entered in 3 rows, from row 10 to row 13.

Column Title	Data Type	Formatting	Example data	Mandatory/ Conditional Mandatory or Optional	CSV Position
1. Date of event (yyyy-mm-dd)	DATE	yyyy-mm-dd	2014-08-30	M	1
2. Is the event in relation to a disclosable tax avoidance scheme? (yes/no)	CHAR3	yes/no	yes	M	2
3. If yes, enter the eight-digit scheme reference number (SRN)	NUM8	IF YN DOTAS_REF = Yes then Mandatory else NULL	12345678	CM	3
4. Employee first name	CHAR35	—	John	M	4
5. Employee second name (if applicable)	CHAR35	—	S	O	5
6. Employee last name	CHAR35	—	Smith	M	6	—
7. National Insurance number	CHAR9	This should be in the format AANNNNNNA, the last character should be alpha	QQ123456A	M	7
8. PAYE reference of employing company	CHAR14	—	123/XZ55555555	M	8
9. Number of securities originally acquired e.g. 100.00	NUM11V2	2 decimal places	120.33	O	9
10. Amount received on disposal £ e.g. 10.1234	NUM13V4	4 decimal places	10.2222	O	10
11. Total market value on disposal £ e.g. 10.1234	NUM13V4	4 decimal places	10.2222	O	11
12. Expenses incurred £ e.g. 10.1234	NUM13V4	4 decimal places	10.2222	O	12
13. Was PAYE operated? (yes/no)	CHAR3	yes/no	yes	M	13
14. Was any adjustment made for amounts subject to apportionment for residence or duties outside the UK? (yes/no)	CHAR3	yes/no	yes	M	14

4. Creating a CSV file(s)

If you have a large amount of data or are a Mac user and you decide to create a CSV file, the following sections cover how to do this. Note if you are a Mac user you may find the ODS template is not compatible and therefore will have to create a CSV of your own see section 4.4.

If you are able to download the HMRC ODS template you can use this as the basis for your CSV. The advantages of producing your CSV from the HMRC ODS Downloaded template means all the formatting will be applied to the sheet.

All column titles in your CSV file must match the required HMRC formatting detailed in section 3.

Once you have created the CSV if you save and reopen it, the formatting may be lost.

4.1 Creating CSV files for your annual return from the HMRC ODS Template

When creating CSV files from the HMRC ODS Template, you first must download the relevant HMRC ODS template.

When you create a CSV you’ll need to produce one CSV file per ‘sheet’ that you’ll be using in a return. When starting with the downloaded ODS template only complete one sheet at a time, before saving as a CSV and moving on to complete a different sheet (if applicable) .

For example, the ODS template for scheme type ‘ SIP’ contains 2 separate sheets, if you need to complete more than one of these sheets, you must complete one sheet at a time and save it before re-opening the ODS template to populate the subsequent sheet(s).

Each sheet you make entries within must be saved using the relevant file and sheet name. The system will ask you to upload all the CSVs for each registered scheme at the same time.

4.2 CSV sheet and File names

CSV file names must consist of the name of the sheet being submitted, followed by ‘.csv’. For example Other_Grants_V4.csv’.

You can read about all relevant file names in section 3.2. The sheet name will be displayed when you open the HMRC ODS Template, this is case sensitive and must be used on your CSV sheet name as well as the CSV file name. This will be a different sheet and file name for each sheet you submit.

4.3 Saving as a CSV file

Save each CSV file that you create within a local drive or on your desktop. You’ll need to upload this file as part of your annual return, this should be somewhere that you can easily locate when you use the ERS return service.

To change your ODS template into a CSV, this is done at the point of saving the document. To save the file as a .csv file:

• select ‘file’ and ‘save as’
• input the file name as described in section 4.2, making sure you put .csv at the end of the file name e.g. ‘Other_Grants_V4.csv’

Select ‘CSV (Comma delimited)’from the ‘Save as type’ dropdown menu as shown in the screen shot, then click ‘Save’.

This image shows how to save as a CSV file in Excel.

4.4 Creating your own CSV from scratch

If you are unable to download or open the ODS you will need to create your own CSV. When creating CSV files to use in your upload, you’ll need to produce one CSV file per ‘sheet’ that you’ll be using. The system will allow you to upload all the CSVs for each registered scheme at the same time.

For example, an Other scheme could include up to 9 separate CSV files. When using the system you’ll be prompted to upload all the files that you’ve completed.

When creating a CSV file from the ODS template, you need to delete all the prefilled information from the first 9 rows (the guidance and headers). If you’re using excel to create your CSV, your data must start from cell A1 and must follow the expected sequence and formatting relative to the type of return you’re making. The remainder of data required in the CSV must follow the expected sequence and formatting depending on the type of return you are making.

This screenshot gives an example of what a CSV document will look like when created in excel (you can also create this in Libre office). Once you have populated all relevant cells you need to save this as a CSV file. CSV filenames must consist of the name of the sheet being submitted, followed by ‘.csv’. For example, ‘CSOP_OptionsGranted_V4.csv’, note this is case sensitive.

This image shows tables in an Excel spreadsheet.

Tables describing the format of each column (by scheme type and by sheet name) can be found in the tables from section 3.7. The last column in each table shows the position where each specific column must appear in each specified CSV file.

4.5 Formatting the files

As commas are being used to separate the data, you must not include extra commas within a cell. For example an address that contained a comma would be rejected.

All data must be comma separated. If you need to include data that contains extra commas in it (for example, in the name of a company) then you must enclose that piece of data within double quotes.

4.6 Group schemes

Your return may include other companies who share in the benefits of that scheme. The way that we refer to these will depend on the type of security that you’re using.

For the OTHER return, we’ll ask you if ‘any other companies are covered by this return?’

The ERS returns service gives you 2 methods for providing this information. You can upload it in a CSV file or enter it manually, there is a maximum limit of 150 rows of data you can include for group schemes.

You’ll need to read section 4.7 if you’re using a CSV file to provide the information.

4.7 Appendix G: Submitting group company information as a CSV

The following table shows the information that a CSV upload of group company information can contain.

Position	Name	Format
1	Company name	Text (120)
2	Address line 1	Text (27)
3	Address line 2	Text (27)
4	Address line 3	Text (27)
5	Address line 4	Text (18)
6	Zip/Postcode	Text (8)
7	Country	Text (18)
8	Company Reference Number	Text (10)
9	Company Tax Reference	Text (10)