Outputs:

SQL: [IDI_Community].[inc_paid_parental].[paid_parental_202603]
SAS: libname cm_paid_parental dsn=IDI_community_srvprd schema=inc_paid_parental; run ;
How to access a code module in the Data Lab: Read here

Inputs:

Purpose of Paid parental leave module

The purpose of this module is to construct the total paid parental leave income (PPL) for people in New Zealand.

Key concepts

Paid parental leave income

Paid parental leave income includes all PPL payments paid by Inland Revenue. PPL is a Ministry of Business, Innovation and Employment (MBIE) product and Inland Revenue administers the registrations and entitlement payments on behalf of MBIE. PPL payments are made to parents and caregivers who are off work following the arrival of a new baby, a child under 6 coming into their care, or when recovering from a pregnancy. PPL payments are made for up to 26 weeks, and match the parent or caregiver’s regular income, up to a maximum payment.

Total income spell

A total income spell is the minimum period per income type. PPL in practice fortnightly, on a Tuesday.

Source frequency

Source frequency is the frequency with which an income type is stored in the data. Source frequency for PPL income from ir_ems table is monthly.

Gross income amount

Gross income amount is the taxable income before tax that a person receives.

Observed tax

Tax recorded as paid to IRD in the IDI. Tax on PPL is available in the IR EMS table as PAYE. This is the amount of tax that has been has been deducted from source (eg PAYE and tax filing).
Note: observed tax deduction is not the same as actual tax liability since this can only be calculated once all income sources have been identified over a tax period. This information has not been included in the initial version of the module.

Granularity

The modules are setup to be event format: One row per event, events have a person, start and end dates, and descriptive information. The variables below will uniquely identify each event in the above table:

Entity id: snz uid
Period: start end, end date
Event information: income source, taxable income indicator

Data sources

Employer monthly schedule: ir_clean.ird_ems

Employer monthly schedule (EMS) table contains employee level data from the tax returns (also called Employment Information on IR website) filed by employers. The EMS form is also used by government agencies to report government transfer payments to individuals that are taxed at source. They include income-tested benefits, New Zealand superannuation, student allowances, paid parental leave, and accident compensation payments. EMS table is built using EIE data provided by IR.

EMS is available in the LBD/IDI. This is a monthly tax return filed by all businesses with paid employees, which summarises the monthly wage and salary payments made to each of their employees, and the “pay-as-you-earn” (PAYE) income tax deductions made. EMS is a mandatory reporting requirement for all employing firms.

Since April 2019, Inland Revenue has shifted from monthly filings under the EMS to compulsory payday filing under the Employment Information - Employee (EI-E) system. This follows a transition period since April 2018 in which firms could select whether to file monthly or through the payday filing system. For consistency, Stats NZ has made a processed version of the EI-E data available, allocating payday information to calendar months and including these records as part of the EMS tables.

Note:

• Employers are able to file late returns and/or amend EMS returns relating to prior periods.
• The return period associated with an EMS is not always the same as the month in which a person was employed because it records the month in which they were paid.
• Regular earnings of some self-employed persons can be reported in the EMS as wages or salaries. The earnings of independent contractors in the EMS are known as withholding or schedular payments. (These are excluded from this module)
• Each record in the EMS corresponds to a job (an employer-employee relationship) and includes the employee’s tax code and employment start or end dates if they are in the month in which they were paid.
• No cleaning is done before the data leaves IR in the EIE file. The data is as it is submitted by employers and/or drawn directly from IRs admin system. If errors in the information provided by employers is identified (either by IR or the employer), then the underlying record will be updated, The next file will show this change. All data points in the files supplied by IR are either drawn directly from a return/other file a customer has submitted, or it is system generated. From the EIE file for example, information about the type of return, processing date, return version, customer type/subtype etc is system generated. Information about pay periods, employment periods, earnings, deductions, and contributions come directly from the return.
• All data points in the files supplied by IR are either drawn directly from a return/other file a customer has submitted, or it is system generated. From the EIE file for example, information about the type of return, processing date, return version, customer type/subtype etc is system generated. Information about pay periods, employment periods, earnings, deductions, and contributions come directly from the return.

Practical notes

Period

PPL information is from 2002 onwards.

Negative amounts

No exclusions made from EMS for negative amounts.

Staged approach to include tax within the total income module

Stage 1 of module creation will only include gross income amounts. Stage 2 (pending) will include observed tax deducted at source since they are available in EMS and MSD tables. This can be used to calculate the tax at a tax year level which includes tax deducted at source and from tax filings. Stage 3 (pending) aims to derive tax at a monthly level. This is complex and will require support from SME to apply rules to derive tax. This can be used to calculate tax for any time period.

Sources of validation

Core Crown spending on superannuation and welfare benefits in New Zealand - Figure.NZ. This website only provides an amount for year 2023. This amount is close to the module output for 2023.

References

1. Document : experimental-income-estimates-from-linked-admin-data-methods-and-results-revised.pdf Experimental estimates of income from linked administrative data | Stats NZ
2. Paid parental leave Paid parental leave
3. Parental leave - Employment New Zealand Parental leave | Employment New Zealand

Key Contacts

Module business rules

Defining spell start and end date:

Start and end dates are based on EMS dates. The return period column in EMS is considered to be the end date and the start of that month will be the start date.

Person identifier snz_uid

A global unique identifier created by Statistics NZ. There is a snz_uid for each distinct identity in the IDI. This identifier is changed and reassigned each refresh.

Minimum income threshold

Currently no minimum threshold applied.

Interoperability with other modules

This module is in the business landscape so could be that it is most closely associated with business profile modules and employment spells. It is also in the socioeconomic landscape where income is a determinant of positive social outcomes, so associated with social housing, education modules, health modules, driving modules, employment assistance modules etc.

Taxable income indicator

Taxable income indicator to be hard coded in modules. Within paid parental leave income module, all income is taxable.

Code logic

The code is based on the rules used for APC and derived data tables in the IDI.

1. We use ir_ems table to obtain paid parental leave. We do this by extracting all monthly data from ir_ems that have ir_ems_income_source_code “PPL”, which stands for paid parental leave.
2. Aggregate the ir_ems data by year, snz_uid and income source code and sum up the monthly amounts. These monthly amounts will be the paid parental leave amounts.

Parameters

The following parameters should be supplied to this module to run it in the database:

1. {targetdb}: The SQL database on which the spell datasets are to be created.
2. {idicleanversion}: The IDI Clean version that the spell datasets need to be based on.
3. {targetschema}: The project schema under the target database into which the spell datasets are to be created.
4. {projprefix}: A (short) prefix that enables you to identify the spell dataset easily in the schema, and prevent overwriting any existing datasets that have the same name.

Variable Descriptions

Module Version & Change History

Code

:setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "{idicleanversion}"

/* target db */
use $(targetdb);

/* Drop the final output table if it exists */
DROP TABLE IF EXISTS [$(targetschema)].[$(projprefix)_paid_parental];
GO

SELECT [snz_uid]
    , DATEFROMPARTS(YEAR(ir_ems_return_period_date), MONTH(ir_ems_return_period_date), 01) AS [period_start_date]
    , EOMONTH(DATEFROMPARTS(YEAR(ir_ems_return_period_date), MONTH(ir_ems_return_period_date), 01)) AS [period_end_date]
    , 'monthly' AS source_frequency
    , 'PPL-EMS' AS income_source
    , 1 AS taxable_income_indicator
    , SUM(ir_ems_gross_earnings_amt) AS gross_income
INTO [$(targetschema)].[$(projprefix)_paid_parental]
FROM $(idicleanversion).ir_clean.ird_ems
WHERE YEAR(ir_ems_return_period_date) BETWEEN 2000 - 1 AND YEAR(GETDATE())
AND ■■■■■■■■■■■
AND ir_ems_income_source_code = 'PPL'
GROUP BY snz_uid
    , DATEFROMPARTS(YEAR(ir_ems_return_period_date), MONTH(ir_ems_return_period_date), 01);