British Columbia pension report worksheet column mappings and calculations

The following tables describes how Sparkrock 365 determines the values that appear in columns on the British Columbia Pension Report Worksheet page based on the report specification criteria and the type of record.

Municipal Pension Plan (MPP)

The following table describes how Sparkrock 365 determines the values that appear in columns on the British Columbia Pension Report Worksheet page for Municipal Pension Plan (MPP) records.

Column Name Source of Data and Calculations
Employee SIN The Social Insurance No. field that appears on the respective Employee Card page.
Employee Name A combination of the Last Name and First Name fields that appear on the respective Employee Card page. For example, for an employee with the First Name of Steven and the Last Name of Smith, the Employee Name field displays Smith, Steven. Sparkrock 365 does not consider the value of the Employee Full Name Format field on the Advanced HR Setup page.
Employee No. The No. field that appears on the respective Employee Card page.
EE Group The Pension Employee Group field that appears on the respective HR Position Class Card page of the HR position that is specified on the HR Employee Assignment Card page of the employee.
Type Set to RG.
Pensionable Salary Sum of the Base Taxable Amount field for records that appear on the Payroll Ledger Entries page for the specified pay periods and MPP Pay Codes, grouped by the employee and HR Emp. Assignment Entry No..

To determine the pay periods, Sparkrock 365 uses the Pay Periods field on the British Columbia Pension Reporting Load Lines page and the Payment Date for records that appear on the Payroll Ledger Entries page.

To determine the MPP Pay Codes, Sparkrock uses the values that are specified on the Advanced HR Setup page, on the British Columbia Pension Setup FastTab, in MPP Pay Codes.
Pensionable Service Sparkrock 365 uses different calculations depending on whether the employee is paid biweekly or monthly. Sparkrock 365 uses the following 3 step calculation for employees who are paid biweekly:
  1. Result 1 = Actual Hours Worked in Pay Period / Default Hours Available in Pay Period x (Employment Duration Type / Total Pay Periods in a Year)
  2. Result 2 = Result 1 x Vacation Percentage
  3. Pensionable Service = Result 1 + Result 2

Sparkrock 365 uses the following calculation for employees who are paid monthly:

Pensionable Service = Actual Hours Worked in Pay Period / Default Hours Available in Pay Period x (Employment Duration Type / Total Pay Periods in a Year)

The calculation is performed on a per pay period basis, and then is summed for the one or more pay periods that were specified on the British Columbia Pension Reporting Load Lines page.

To determine the Actual Hours Worked, Sparkrock 365 uses the records that appear on the Compensation Ledger Entries page grouped by HR Emp. Assignment Entry No. for the one or more pay periods that were specified on the British Columbia Pension Reporting Load Lines page.

To determine the Default Hours Available, Sparkrock 365 uses the value of the Hours/Week field on the respective HR Employee Assignment Card. For employees who are paid biweekly, the Hours/Week value is multiplied by 2 to determine the result for each pay period.

To determine the Employment Duration Type, Sparkrock 365 uses the value in the Employment Duration Type field on the respective HR Position Class Card page, for example, 6 Month = 13, 8 Month = 17, 10 Month = 22, 11 Month = 24, 12 Month = 24. For employees who are paid monthly, regardless of whether the employee is paid for 10 or 12 months, the Employment Duration Type equals the Total Pay Periods in a Year, resulting in a value of 1.

To determine the vacation percentage, Sparkrock 365 uses the value in the Vacation Percentage field on the respective HR Position Class Card page.
Contributory Service The calculation is different depending on whether the employee is paid biweekly or monthly. For employees who are paid biweekly, to determine the value, Sparkrock 365 checks payroll ledger entries for contributions in each month for the specified pay periods and for the MPP Pay Codes that are specified on the Advanced HR Setup page. For each month that has a contribution, Sparkrock 365 counts 1 month.

Sparkrock 365 performs the calculation on a per pay period basis, and then sums the amount for the period that was specified on the British Columbia Pension Reporting Load Lines page.

To determine the pay periods, Sparkrock 365 uses the Pay Periods field on the British Columbia Pension Reporting Load Lines page and the Payment Date on the payroll ledger entries.

In situations where the pay period overlaps to the next month, for example, June 22, 2024 to July 7, 2024, to determine the month to count, Sparkrock 365 uses the value of Posting Date on the HR Pay Cycle Periods page.

For employees who are paid monthly and who contributed, the contributory service is 12.00 for an Employment Duration Type of 12 month and 10.00 for an Employment Duration Type of 10 month.
Contributions Divide the value of Pensionable Salary by the EE Pension Rate field on the respective HR Position Class Card page. For example, if Pensionable Salary is $10,000 and EE Pension Rate is 10, Contributions = $10,000/10 = $1,000.
Employee Contributions Sum of the Total Amount field of records that appear on the Payroll Ledger Entries page for the one or more MPP pay codes for the specified Pay Periods, grouped by Employee No. and HR Emp. Assignment Entry No., displayed as a positive value.

To determine the one or more MPP pay codes, Sparkrock 365 uses the values that are set up in MPP Pay Codes on the Advanced HR Setup page.

To determine the pay periods, Sparkrock 365 uses the Pay Periods field on the British Columbia Pension Reporting Load Lines page.
Employer Contributions Sum of the Total Amount field of records that appear on the Payroll Ledger Entries page for the one or more MPP employer contribution pay codes for the specified pay periods grouped by Employee No. and HR Emp. Assignment Entry No., displayed as a positive value.

To determine the one or more MPP employer contribution pay codes, Sparkrock 365 uses the values that are set up in MPP Employer Contribution Pay Codes on the Advanced HR Setup page.

To determine the pay periods, Sparkrock 365 uses the Pay Periods field on the British Columbia Pension Reporting Load Lines page.
Start Date The earliest pay period that is specified in the Pay Periods field on the British Columbia Pension Reporting Load Lines page.
End Date The latest pay period that is specified in the Pay Periods field on the British Columbia Pension Reporting Load Lines page.
Description Sparkrock 365 leaves the field blank.
Date Sparkrock 365 leaves the field blank.
Is Processed Specifies whether manual adjustments to the line have been processed. Processed lines appear on the British Columbia Pension Adjustments page.

Teachers Pension Plan (TPP)

The following table describes how Sparkrock 365 determines the values that appear in columns on the British Columbia Pension Report Worksheet page for Teachers Pension Plan (TPP) records.

Column Name Source of Data and Calculations
Employee SIN The Social Insurance No. field that appears on the respective Employee Card page.
Employee Name A combination of the Last Name and First Name fields that appear on the respective Employee Card page. For example, for an employee with the First Name of Steven and the Last Name of Smith, the Employee Name field displays Smith, Steven. Sparkrock 365 does not consider the value of the Employee Full Name Format field on the Advanced HR Setup page.
Employee No. The No. field that appears on the respective Employee Card page.
EE Group The Pension Employee Group field that appears on the respective HR Position Class Card page of the HR position that is specified on the HR Employee Assignment Card page of the employee.
Type Set to RG.
Pensionable Salary Sum of the Base Taxable Amount field on the Payroll Ledger Entries page for the specified pay period and TPP Pay Codes, grouped by Employee No. and HR Emp. Assignment Entry No..

To determine the pay periods, Sparkrock 365 uses the Pay Periods field on the British Columbia Pension Reporting Load Lines page and the Payment Date for records that appear on the Payroll Ledger Entries page.

To determine the TPP Pay Codes, Sparkrock uses the values that are specified on the Advanced HR Setup page, on the British Columbia Pension Setup FastTab, in TPP Pay Codes.
Pensionable Service The calculation of Pensionable Service differs depending on whether the value of Pension Position Class that appears on the respective HR Position Class Card page is equal to TTOC.

All pensionable service calculations are performed on a per pay period basis, and then summed for the date range that is specified on the British Columbia Pension Reporting Load Lines page.

Pension Position Class is not equal to TTOC

When the value of Pension Position Class that is associated with the one or more assignments is is not equal to TTOC, and the Posting Date on the related one or more compensation ledger entry records falls within the date range that is specified on the British Columbia Pension Reporting Load Lines page, the formula for Pensionable Service is as follows:

Pensionable Service = Days Worked in Pay Period / No. of Days in Pay Period

The calculation of Pensionable Service is dependent on the following fields:

  • Days Worked in Pay Period: Based on records that appear on the Compensation Ledger Entries page, grouped by Employee No. and HR Emp. Assignment Entry No. for the specified Pay Period on the British Columbia Pension Reporting Load Lines page that match the Payment Date of records on the Payroll Ledger Entries page.
  • No. of Days in Pay Period: Based on records that appear on the Compensation Ledger Entries page, grouped by Employee No. and HR Emp. Assignment Entry No. for the specified Pay Period on the British Columbia Pension Reporting Load Lines page that match the Payment Date of records on the Payroll Ledger Entries page.

Pension Position Class is equal to TTOC

When the value of Pension Position Class on the HR position class that is associated with the one or more assignments is equal to TTOC, and the Posting Date on the related one or more compensation ledger entry records falls within the date range that is specified on the British Columbia Pension Reporting Load Lines page, Sparkrock 365 performs different calculations for Pensionable Service depending on the respective HR pay cycle Type of Biweekly or Monthly.

When the Type is Biweekly, the formula is as follows:

(Hours/Days) / (Total Eligible Days in a Biweekly Pay Period) x (Total Eligible Days in a Biweekly Pay Period / Default Days/Year)

Sparkrock 365 uses the value of Hours/Days from the compensation ledger entries and the value of Default Days/Year from the HR position that is linked to the assignment. The total eligible days in a biweekly pay period is always 10.

When the Type is Monthly, the formula is as follows:

Pensionable Service = ((Hours / Days) / No. of Days in Pay Period) x ((No. of Days in Pay Period) / (No. of Days per Year))

Sparkrock 365 uses the values of Hours/Days and No. of Days in Pay Period from the compensation ledger entries and the value of No. of Days per Year from the HR employee assignment.
Contributory Service To determine the value, Sparkrock 365 checks for contributions in each month for the specified pay periods. For each month that has a record for the related HR Emp. Assignment Entry No. in payroll ledger entries for the pay code specified in TPP Pay Codes on the British Columbia Pension Setup FastTab on the Advanced HR Setup page, Sparkrock 365 counts 1 month.

To determine the pay periods, Sparkrock 365 uses the Pay Periods field on the British Columbia Pension Reporting Load Lines page.

If for any employee, there are 2 or more HR employee assignments during the reporting period, Sparkrock 365 reports contributory service for only 1 assignment line because for any individual employee, the contributory service value cannot be more than 1 for any month in a year.
Contributions Divide the value of Pensionable Salary by the EE Pension Rate field on the respective HR Position Class Card page. For example, if Pensionable Salary is $10,000 and EE Pension Rate is 10, Contributions = $10,000/10 = $1,000.
Employee Contributions Sum of the Total Amount field of records that appear on the Payroll Ledger Entries page for the one or more TPP pay codes for the specified Pay Periods, grouped by Employee No. and HR Emp. Assignment Entry No., displayed as a positive value.

To determine the one or more TPP pay codes, Sparkrock 365 uses the values that are set up in TPP Pay Codes on the Advanced HR Setup page.

To determine the pay periods, Sparkrock 365 uses the Pay Periods field on the British Columbia Pension Reporting Load Lines page.
Employer Contributions Sum of the Total Amount field of records that appear on the Payroll Ledger Entries page for the one or more TPP employer contribution pay codes for the specified pay periods grouped by Employee No. and HR Emp. Assignment Entry No., displayed as a positive value.

To determine the one or more TPP employer contribution pay codes, Sparkrock 365 uses the values that are set up in TPP Employer Contribution Pay Codes on the Advanced HR Setup page.

To determine the pay periods, Sparkrock 365 uses the Pay Periods field on the British Columbia Pension Reporting Load Lines page.
Start Date The earliest pay period that is specified in the Pay Periods field on the British Columbia Pension Reporting Load Lines page.
End Date The latest pay period that is specified in the Pay Periods field on the British Columbia Pension Reporting Load Lines page.
Description Sparkrock 365 leaves the field blank.
Date Sparkrock 365 leaves the field blank.
Is Processed Specifies whether manual adjustments to the line have been processed. Processed lines appear on the British Columbia Pension Adjustments page.

Retro pay

The following table describes how Sparkrock 365 determines the values that appear in columns on the British Columbia Pension Report Worksheet page for retro pay records.

Column Name Source of Data and Calculations
Employee SIN The Social Insurance No. field on the respective Employee Card page.
Employee Name A combination of the Last Name and First Name fields on the respective Employee Card page. For example, for an employee with the First Name of Steven and the Last Name of Smith, the Employee Name field displays Smith, Steven.
Employee No. The No. field on the respective Employee Card page.
EE Group Sparkrock 365 leaves the field blank.
Type Set to RT.
Pensionable Salary Sum of the Total Amount field for the retro pay entries on the Payroll Ledger Entries page for the specified pay periods.

To determine the pay periods, Sparkrock 365 uses the Pay Periods field on the British Columbia Pension Reporting Load Lines page.

To determine the retro pay entries to use in the worksheet calculations, Sparkrock 365 uses the one or more values that are set up on the Advanced HR Setup page, on the British Columbia Pension Setup FastTab, in the Retro Pay Codes field.
Pensionable Service Sparkrock 365 leaves the field blank.
Contributory Service Sparkrock 365 leaves the field blank.
Contributions Divide the value of Pensionable Salary by the EE Pension Rate field on the respective HR Position Class Card page.
Employee Contributions Sparkrock 365 leaves the field blank.
Employer Contributions Sparkrock 365 leaves the field blank.
Start Date The earliest pay period that is specified in the Pay Periods field on the British Columbia Pension Reporting Load Lines page.
End Date The latest pay period that is specified in the Pay Periods field on the British Columbia Pension Reporting Load Lines page.
Description Sparkrock 365 leaves the field blank.
Date Sparkrock 365 leaves the field blank.

Rehab payments

The following table describes how Sparkrock 365 determines the values that appear in columns on the British Columbia Pension Report Worksheet page for rehab payment records.

Column Name Source of Data and Calculations
Employee SIN The Social Insurance No. field on the respective Employee Card page.
Employee Name A combination of the Last Name and First Name fields on the respective Employee Card page. For example, for an employee with the First Name of Steven and the Last Name of Smith, the Employee Name field displays Smith, Steven.
Employee No. The No. field on the respective Employee Card page.
EE Group Sparkrock 365 leaves the field blank.
Type Set to RH.
Pensionable Salary Sum of the Total Amount field for the rehab payment entries on the Payroll Ledger Entries page for the specified pay periods.

To determine the pay periods, Sparkrock 365 uses the Pay Periods field on the British Columbia Pension Reporting Load Lines page.

To determine the rehab payment entries to use in the worksheet calculations, Sparkrock 365 uses the one or more values that are set up on the Advanced HR Setup page, on the British Columbia Pension Setup FastTab, in the Rehab Pay Codes field.
Pensionable Service Sparkrock 365 leaves the field blank.
Contributory Service Sparkrock 365 leaves the field blank.
Contributions Divide the value of Pensionable Salary by the EE Pension Rate field on the respective HR Position Class Card page.
Employee Contributions Sparkrock 365 leaves the field blank.
Employer Contributions Sparkrock 365 leaves the field blank.
Start Date The earliest pay period that is specified in the Pay Periods field on the British Columbia Pension Reporting Load Lines page.
End Date The latest pay period that is specified in the Pay Periods field on the British Columbia Pension Reporting Load Lines page.
Description Sparkrock 365 leaves the field blank.
Date Sparkrock 365 leaves the field blank.

Regular (RG) Line consolidation

The following sections describe the specific scenarios for which Sparkrock 365 consolidates British Columbia Pension report regular (RG) lines.

Municipal Pension Plan (MPP) RG line consolidation

When loading records into the worksheet, Sparkrock 365 consolidates Municipal Pension Plan (MPP) lines if all of the following conditions are met:

  • Two or more assignments during the pension reporting period have a matching Employment Duration Type on the respective HR Position Class of the assignments. Each matching Employment Duration Type must be either 10 Month or 12 Month for all records. The Employment Duration Type is specified on the HR position class that is associated with the HR position that is linked to each assignment.
  • The Pension Employee Group is 04GENERL
  • The HR pay cycle codes that are associated with the assignments have a matching Type. Each matching Type must be either Biweekly or Monthly for all records. The Type is specified on the HR pay cycle code that is linked to each assignment.
  • The line Type is equal to RG.

The pension reporting period is based on the load lines date selection.

The consolidation involves a summation of the amounts for the following fields:

  • Pensionable Salary
  • Pensionable Service
  • Contributory Service
  • Contributions
  • Employee Contributions
  • Employer Contributions

Teachers Pension Plan (TPP) RG line consolidation

When loading records into the worksheet, Sparkrock 365 consolidates Teachers Pension Plan (TPP) lines if all of the following conditions are met:

  • Two or more assignments during the pension reporting period have a matching Pension Employee Group on the respective HR Position Class of the assignments. Each matching Pension Employee Group must be either 05GE10MO or 05GE12MO for all records. The Pension Employee Group is specified on the HR position class that is associated with the HR position that is linked to each assignment.
  • Two or more assignments during the pension reporting period have the same Employment Duration Type on the respective HR Position Class of the assignments. Each matching Employment Duration Type must be either 10 Month or 12 Month for all records. The Employment Duration Type is specified on the HR position class that is associated with the HR position that is linked to each assignment.
  • The HR pay cycle codes that are associated with the assignments have a matching Type. Each matching Type must be either Biweekly or Monthly for all records. The Type is specified on the HR pay cycle code that is linked to each assignment.
  • The line Type is equal to RG.

The pension reporting period is based on the load lines date selection.

The consolidation involves a summation of the amounts for the following fields:

  • Pensionable Salary
  • Pensionable Service
  • Contributory Service
  • Contributions
  • Employee Contributions
  • Employer Contributions

British Columbia pension reporting

Feedback

To send feedback about this page, select the following link:

feedback-product-docs@sparkrock.com