Rogine Arsula Ibuno

Payroll Automation (Philippine Payroll Standard) Overview ..................................................................................................................... 2 Getting Started ............................................................................................................ 3 Basic Payroll Format Components ............................................................................... 3 Employee ID Number ................................................................................................. 3 Employee Name ........................................................................................................ 4 Hiring Date ................................................................................................................ 4 Employee Designation and Department ...................................................................... 5 Monthly Basic Pay ..................................................................................................... 5 Daily Rate and Hourly Rate ......................................................................................... 6 Tardiness/Absences Deduction Computation .............................................................. 8 Incentives and Bonus Calculation............................................................................. 15 Non – Taxable Allowance Calculation/De Minimis ...................................................... 16 Gross Pay Calculation .............................................................................................. 17 Taxable Income Calculation ..................................................................................... 18 Classification of Withholding Tax .............................................................................. 19 Computation of Government Mandatory Contributions .............................................. 22 SSS Contribution Computation ............................................................................. 22 Philhealth (PHIC) Contribution Computation.......................................................... 26 Pag-Ibig (HDMF) Contribution Computation ........................................................... 28 Computation of Net Pay ........................................................................................... 30 Payslip Generation .................................................................................................... 32 Government Mandated Contribution Summary Report .............................................. 43 File Saving and Record Keeping ................................................................................. 44 Payslip Distribution using Power Automate................................................................ 45 Disclaimer ................................................................................................................. 70 1 Overview Managing payroll in the Philippines goes beyond simply computing employee salaries. It involves staying compliant with ever-evolving government regulations, accurately handling mandatory contributions like SSS, PhilHealth, and Pag-IBIG, and making sure employees are paid correctly and on time. For many businesses, especially those experiencing growth, this process can quickly become complex and time-consuming. This is where payroll automation can make a meaningful di erence. By streamlining calculations, minimizing manual errors, and reducing repetitive work, automation transforms payroll into a more manageable and reliable process. This manual is designed to be a helpful starting point for exploring payroll automation tailored to Philippine standards. Whether you're an HR professional, a small business owner, or someone tasked with handling payroll without a dedicated system, this guide can help you reduce errors, save time, and focus more on what truly matters—supporting and empowering your workforce. If your company doesn't yet have a payroll system in place, this manual o ers a practical alternative using Microsoft Excel. Inside, you'll find ready-to-use formats, helpful formulas, guidelines for computing government-mandated benefits, and step-by-step instructions for generating and distributing automated payslips. We’ll also introduce tools and programs to help make the process even more e icient. With that, let’s begin this journey together—toward making payroll simpler, more e icient, and less overwhelming, one step at a time. 2 Getting Started Now that you’ve seen the big picture, let’s begin with the basics. This section covers the key parts of the Philippine payroll process—from setting up employee records to calculating mandatory deductions. Before we dive in, please keep in mind that the payroll components we’ll discuss are not set in stone. Each company has its own unique setup and needs, so feel free to adjust, add, or remove components to fit your organization’s specific requirements. In this given example, the payroll cycle follows the Bi – Monthly Payout which is usually First Cycle 1 – 15 and Second Cycle 16 – 30/31. This is also prepared through Microsoft Excel Format. Basic Payroll Format Components Start by opening your Excel workbook, renaming Sheet 1 to “Payroll Register,” and entering the following details: Employee ID Number A unique code assigned to each employee for identification and record-keeping. Typically, an ID No. given to each employee. Make sure that there are no duplicate values in this column. Example: 3 Employee Name The full legal name of the employee as registered in company records. Example: Hiring Date The o icial starting date of employment. This is important for benefits eligibility and seniority calculations. Example: 4 Employee Designation and Department The employee’s job title or position within the company. Also, put the business unit or division where the employee works. This helps categorize payroll costs by department. Example: Monthly Basic Pay The fixed salary agreed upon monthly before any deductions or bonuses. This will also serve as our basis for the daily rate and hourly rate computation. 5 Example: Daily Rate and Hourly Rate Since we have now identified the basic rate of the employees, we can now proceed with the computation of Daily Rate and Hourly Rate. The Daily Rate serves as the basis for calculating absences, holiday pay, and other daily-based compensation. It also helps determine the pay for employees who are compensated on a per-day basis. Meanwhile, the Hourly Rate is used as the standard for computing overtime pay, tardiness deductions, and other compensation that follows an hourly scheme. If your employee is paid on a monthly basis, use the following guidelines to compute the daily rate: 6 Source: https://cda.gov.ph/wp-content/uploads/2019/07/coopsday-DOLE-BWC-FAQs-on-Labor-Standards.pdf This means that the standard number of working days in a year, if the employee is not paid on weekends or two rest days, is 261 days (262 days in a leap year). Example: To compute the daily and hourly rates for a monthly-paid employee, follow this standard procedure:  Determine the Monthly Basic Salary Begin with the employee's fixed monthly salary. For example, let’s use ₱30,000.00 as the monthly rate.  Compute the Annual Salary Multiply the monthly salary by 12 months to get the total annual compensation: ₱30,000 × 12 months = ₱360,000 (Annual Salary)  Get the Daily Rate Divide the annual salary by the standard number of working days in a year. For employees who are not paid on weekends, this is typically 261 working days: ₱360,000 ÷ 261 = ₱1,379.31 (Daily Rate)  Compute the Hourly Rate Assuming an 8-hour workday, divide the daily rate by 8: ₱1,379.31 ÷ 8 = ₱172.41 (Hourly Rate) 7 Illustration: Computation Step Formula Result Monthly Salary Given ₱30,000.00 Annual Salary ₱30,000 × 12 ₱360,000.00 Daily Rate ₱360,000 ÷ 261 ₱1,379.31 Hourly Rate ₱1,379.31 ÷ 8 ₱172.41 Note: This method assumes the employee is paid only for weekdays (Monday to Friday) and not on weekends or rest days. If your company has a di erent working day setup, adjust the divisor accordingly (e.g., 313 for 6-day work weeks). Additionally, computing the daily rate and hourly rate for High Level Positions (e.g Managers, Supervisors) is not necessary since their monthly basic rate is fixed and typically not entitled for Overtime Pay, Holiday Pay, etc. unless there is an internal agreement which supersedes this guideline. To illustrate this in excel format, you can use the following: Excel Formulas: Daily Rate Column Hourly Rate Column - =(F10*12)/261 =G10/8 Tardiness/Absences Deduction Computation To help with computing deductions for missed hours or days, you can begin by setting up a dedicated worksheet to record each employee's time entries (We can name this worksheet to Time Keeping). Once the timekeeping sheet is prepared, you may link it to your payroll 8 register. This setup can make it easier to calculate deductions automatically when time records are entered, using the data and formulas you've put in place. Example: To link the timekeeping worksheet with the Payroll Register, you can use the IFERROR, INDEX, and MATCH function. This allows you to pull each employee’s total hours worked by matching their name or ID with the appropriate column. Once retrieved, the formula can be extended to multiply the total hours by the employee’s hourly rate to calculate the corresponding deduction. Since this value represents a deduction, it is advisable to present it as a negative figure in your payroll register for clarity and accuracy. 9 After we have linked the two worksheets, it will look like this Formula to input in Cell I8, =IFERROR(INDEX('TimeKeeping'!$C$7:$D$10,MATCH('Payroll Register'!$A8, 'Time Keeping'!$A$7:$A$10, 0), MATCH('Payroll Register'!I$7, 'Time Keeping'!$C$6:$D$6, 0))*-'Payroll Register'!$H8,0) You can drag the formula downwards and sideways to apply the same on other columns. This formula is designed to: 1. 2. 3. 4. Look up an employee's timekeeping value (e.g., hours of tardiness or absence). Multiply that value by the employee's hourly rate (from the Payroll Register). Convert the result into a negative value (since it's a deduction). Return 0 if there's any error in the lookup. Breakdown: 🔹 INDEX('Time Keeping'!$C$7:$D$10, …, …)   Searches in the range C7:D10 of the Time Keeping sheet. This is the matrix you're pulling data from. 🔹 MATCH('Payroll Register'!$A8, 'Time Keeping'!$A$7:$A$10, 0)   Looks for the employee ID or name from cell A8 of the Payroll Register. Matches it in the first column of the Time Keeping sheet (A7:A10). 10  This determines the row number for the INDEX. 🔹 MATCH('Payroll Register'!I$7, 'Time Keeping'!$C$6:$D$6, 0)    Matches the column header (e.g., “Tardiness” or “Absences”) from cell I7 in the Payroll Register. Matches it to the column headers in C6:D6 of the Time Keeping sheet. This determines the column number for the INDEX. 🔹 *- 'Payroll Register'!$H8   Multiplies the looked-up time value by the hourly rate from H8. The minus sign (-) makes the result negative, which is appropriate for deductions. 🔹 IFERROR(..., 0)  If any error occurs (e.g., no match is found), return 0 instead of an error message. For this formula to work, ensure that the column name of these deductions in Time Keeping worksheet matches with the column names of these deductions in Payroll Register Worksheet. 1. Overtime, Holiday Pay (Premium Pay Computation) These are additional compensations given to employees who work beyond regular hours or during special/non-working days. Overtime pay is calculated based on the hourly rate plus a prescribed premium. Holiday pay, on the other hand, is computed using governmentmandated rates depending on whether the holiday is regular or special, and whether the employee worked or not. Presented below are samples of these premium pays and holiday pays, with its corresponding rates as per DOLE: Pay Code Hour Type DOLE Multiplier REG (Regular Day) BASIC 1.00 OT 1.25 ND 0.10 NDOT 0.125 BASIC 1.30 OT 1.69 ND 0.13 NDOT 0.169 BASIC 1.00 OT 2.60 RD (Rest Day) RH (Regular Holiday) 11 Pay Code RR (Reg. Holiday + Rest Day) SH (Special Holiday) SR (Special Holiday + Rest Day) DH (Double Holiday) DR (Double Holiday + Rest Day) Hour Type DOLE Multiplier ND 0.20 NDOT 0.26 BASIC 2.60 OT 3.38 ND 0.26 NDOT 0.338 BASIC 0.30 OT 1.69 ND 0.13 NDOT 0.169 BASIC 1.50 OT 1.95 ND 0.15 NDOT 0.195 BASIC 2.00 OT 3.90 ND 0.30 NDOT 0.39 BASIC 2.90 OT 5.07 ND 0.39 NDOT 0.507 Key Reminder: Companies have the option to go higher than the DOLE-prescribed rates, as long as they do not go below the mandated minimums. Any agreement to provide higher rates should be well-documented—through contracts, employee handbooks, or company policies—to avoid disputes or confusion later. To apply this in our current worksheet setup, begin by entering the total number of hours each employee has rendered under the appropriate pay code categories within the Timekeeping Worksheet. Be sure to input the corresponding DOLE-prescribed rates for each pay code in their designated cells as well—this will make it easier to link and automate the pay computations in the Payroll Register. 12 To link this to our Payroll Register, and automate the computation, we can input this formula in Cell L8 of Payroll Register Worksheet: =($H8*IFERROR(INDEX('Time Keeping'!$E$7:$I$10,MATCH('Payroll Register'!$A8,'Time Keeping'!$A$7:$A$10,0),MATCH('Payroll Register'!L$7,'Time Keeping'!$E$6:$I$6,0)),0))*'Payroll Register'!L$6 You can drag the formula downwards and sideways to apply the same on other columns. This formula is designed to: 1. Retrieving the number of hours worked: Using INDEX and MATCH to find the intersection of the employee's row and the specific day's column in the 'Time Keeping' sheet. 2. Handling errors: If the employee ID or date isn't found, IFERROR ensures the formula returns 0 hours instead of an error. 3. Calculating pay: Multiplying the hours worked by the hourly rate ($H8) and the multiplier ('Payroll Register'!L$6). 🔍 Breakdown 🔹 Hourly Rate ($H8):  This references the hourly rate for the employee in row 8. The dollar sign before the column ($H) makes the column reference absolute, ensuring it doesn't change when the formula is copied across columns. 🔹 Hours Worked Retrieval:  INDEX('Time Keeping'!$E$7:$I$10, row_num, column_num): 13   MATCH('Payroll Register'!$A8, 'Time Keeping'!$A$7:$A$10, 0):   Finds the row number in the 'Time Keeping' sheet where the employee ID from 'Payroll Register'!$A8 matches. The range A7:A10 contains employee IDs. The 0 specifies an exact match. MATCH('Payroll Register'!L$7, 'Time Keeping'!$E$6:$I$6, 0):   This function retrieves the value at the intersection of a specific row and column within the range E7:I10 on the 'Time Keeping' sheet. Finds the column number in the 'Time Keeping' sheet where the day or date from 'Payroll Register'!L$7 matches. The range E6:I6 contains dates or day labels. The 0 specifies an exact match. IFERROR(..., 0):  If the INDEX function results in an error (e.g., if no match is found), IFERROR returns 0 instead of an error. 🔹 Multiplier ('Payroll Register'!L$6):  This references a multiplier value, such as overtime rate or shift di erential, located in row 6 of the current column (L). The dollar sign before the row ($6) makes the row reference absolute, ensuring it doesn't change when the formula is copied across rows. 14 The output would be like this: For this formula to work, ensure that the column name of these Premium Pays in Time Keeping worksheet matches with the column names of these Pay Codes in Payroll Register Worksheet. Incentives and Bonus Calculation Set-up a designated column for this in the payroll register worksheet. Typically, these are subject for Taxation. You can create a list for this, and use a “Vlookup” function to integrate this on the payroll register. The VLOOKUP function in Excel and Google Sheets allows you to search for a value in the first column of a table and return a corresponding value from another column in the same rows. Basic VLOOKUP Syntax =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])   lookup_value: The value you want to search for. table_array: The range of cells that contains the data. 15   col_index_num: The column number in the table_array from which to retrieve the value. range_lookup: Optional. Use FALSE for an exact match or TRUE for an approximate match. Example Suppose you have a table listing employee IDs and their corresponding names: Employee ID Name 101 Alice 102 Bob 103 Charlie This formula searches for the value 102 in the first column of the range A2:B4 and returns the corresponding value from the second column, which is "Bob". Illustration: Non – Taxable Allowance Calculation/De Minimis De minimis benefits are minor perks given to employees that are not subject to tax, as long as they don't exceed prescribed limits. 16 Examples include:        Rice Subsidy: Up to ₱2,000 per month or one 50 kg sack of rice. Uniform and Clothing Allowance: Not exceeding ₱6,000 per year. Medical Cash Allowance: Up to ₱1,500 per semester or ₱250 per month. Laundry Allowance: Not exceeding ₱300 per month. Achievement Awards: Non-cash awards up to ₱10,000 per year. Holiday or Anniversary Gifts: Up to ₱5,000 per employee per year. Monetized Unused Vacation Leave: Up to 10 days per year for private employees. These benefits are outlined in BIR Revenue Regulations No. 11-2018 We can also use the same procedure as stated at Item 9. Illustration: Gross Pay Calculation Gross pay is the total compensation an employee earns before deductions. It includes basic salary, allowances, overtime pay, holiday pay, and other earnings such as bonuses or incentives. This amount serves as the basis for computing government-mandated deductions like SSS, PhilHealth, Pag-IBIG, and income tax. 17 Illustration: Taxable Income Calculation Taxable income refers to the portion of an employee's earnings subject to income tax. It includes basic salary, regular allowances, and other forms of compensation, but excludes non-taxable benefits like the 13th-month pay (up to ₱90,000), de minimis benefits, and government-mandated contributions. Bonuses and incentives are taxable if they exceed the non-taxable thresholds or are considered part of regular income. 18 Illustration: (Note: some columns from previous illustrations are hidden to fit on the screen) Classification of Withholding Tax Withholding tax on compensation in is the amount of income tax that an employer deducts from an employee’s salary every payday. It is based on the employee’s taxable income, using the BIR’s prescribed tax tables or withholding tax rates. This system allows for the advance payment of income tax, which is then credited against the employee’s annual tax due. 19 Presented below is the updated Tax Table presented by the BIR: Source: https://www.bir.gov.ph/WithHoldingTax To incorporate this in our given example computation, we are going to use the “Semi Monthly” computation scheme since our payout is TWICE a month. Excel Formula used: =(IF((W8)>333333,ROUND((((W8)-333333)*-),2),IF((W8)>83333,ROUND((((W8)83333)*-),2),IF((W8)>33333,ROUND((((W8)33333)*-),2),IF((W8)>16667,ROUND((((W8)16667)*0.2+937.5),2),IF((W8)>10417,ROUND((((W8)-10417)*0.15),2))))))) 20 Explanation: This is a nested IF formula that applies the graduated income tax rates depending on the amount in W8 (monthly taxable income). Here's how each tier works: Tax Brackets and Explanation: Taxable Monthly Income (W8) Tax Computation Over ₱333,333 35% of excess + ₱91,770.70 ₱83,334 – ₱333,333 30% of excess + ₱16,770.70 ₱33,334 – ₱83,333 25% of excess + ₱4,270.70 ₱16,668 – ₱33,333 20% of excess + ₱937.50 ₱10,418 – ₱16,667 15% of excess over ₱10,417 ₱10,417 or less 0% – No tax  Functions Used: ROUND(..., 2): Rounds the result to 2 decimal places for currency format.  (W8 - base): Calculates the excess income over the lower limit of the bracket.  * rate: Applies the corresponding tax rate to the excess.  + base tax: Adds the fixed tax for the bracket (based on TRAIN tax table). This formula calculates the monthly withholding tax using the Philippine BIR's graduated tax rates, ensuring that each taxpayer pays the correct tax based on their monthly taxable income (W8) under the TRAIN Law. 21 Illustration: (Note: some columns from previous illustrations are hidden to fit on the screen) Computation of Government Mandatory Contributions Computation of government mandatory contributions involves calculating the required monthly contributions to SSS, PhilHealth, and Pag-IBIG based on an employee’s monthly salary. Each agency has its own contribution table or rate, and the total contribution is usually shared between the employer and the employee. These deductions are mandatory and are withheld from the employee’s salary every payroll period. SSS Contribution Computation To start incorporating this in our given illustration, we can use the V look-up function to determine the amount of contribution. First, we must add a worksheet where we can place our SSS Contribution Table, ensure that this in an excel format. We can name this worksheet “SSS Contribution Table”. 22 Illustration: Secondly, let’s set-up a designated column in our Payroll Register worksheet which will illustrate our amount of basis for the contribution computation. This will serve as our look-up value from our SSS Table. The amount in this column should exclude Bonuses and incentives that are non-recurring, discretionary, or performance-based unless they are given regularly and form part of the employee’s regular compensation. Please apply this computation only if it is on the second cycle of payroll (e.g January 16 – 31, 2025 Payroll Cycle) since this format aims to apply the deduction every after the last cycle of the month. Just for the presentation purposes, even that the given example is covering just the First Cycle (January 1 – 15, 2025) we have applied this computation. 23 Illustration: (Note: some columns from previous illustrations are hidden to fit on the screen) Excel Formula used: Determining the amount of SSS Contribution Employee Share =IFERROR(VLOOKUP(Z8,'SSS Contribution Table'!$A$4:$J$64,10,1),0) Explanation:  Z8: This is the cell containing the employee’s monthly salary or compensation amount.  'SSS Contribution Table'!$A$4:$J$64: This is the range of the SSS table stored on another worksheet named 'SSS Contribution Table', from columns A to J and rows 4 to 64. The first column (A) likely contains salary brackets. 24  10: This tells Excel to return the value from the 10th column in the table range (column J), which might represent the total SSS contribution or the employee's share.  1 (or TRUE): This specifies an approximate match, which means Excel will look for the closest lower value in the first column if an exact match is not found—ideal for range-based lookups like SSS salary brackets.  IFERROR(..., 0): This wraps the VLOOKUP to return 0 if there’s an error (e.g., if the salary doesn't fall within the table range), preventing Excel from showing an error message. In summary: This formula finds the appropriate SSS contribution amount based on the salary in cell Z8, and returns 0 if the lookup fails. Apart from this, we also must determine the amount of SSS MPF Employee Share, we will be using the same SSS Table and Excel function =IFERROR(VLOOKUP(Z8,'SSS Contribution Table'!$A$4:$K$64,11,1),0) Explanation:  Z8: This is the cell that contains the monthly salary or compensation amount to be used for the lookup.  'SSS Contribution Table'!$A$4:$K$64: This is the range of the SSS Contribution Table located in another worksheet named "SSS Contribution Table", covering columns A to K (11 columns) and rows 4 to 64.  11: This tells Excel to return the value from the 11th column (Column K) of the selected range. This column likely contains a specific SSS-related value—possibly the employer’s share, employee’s share, or total contribution, depending on how the table is structured.  1 (or TRUE): This enables an approximate match, meaning Excel will search for the nearest lower value if there’s no exact match—ideal for salary ranges.  IFERROR(..., 0): This ensures that if the lookup fails (e.g., salary is outside the defined ranges), the formula will return 0 instead of showing an error message like #N/A. This formula looks up the salary in Z8 against a salary bracket in the SSS contribution table, then returns the value from column 11 (likely a specific contribution value), and if it can't find a match, it returns 0. 25 Illustration: (Note: some columns from previous illustrations are hidden to fit on the screen) Philhealth (PHIC) Contribution Computation The basis of PhilHealth contribution is the employee’s monthly basic salary, which includes only the regular pay and excludes bonuses, overtime, or other variable income. PhilHealth uses a percentage-based system to compute monthly contributions. As of recent guidelines, the contribution rate is 5% of the employee's monthly salary, shared equally between the employer and the employee (i.e., 2.5% each). However, there is a salary ceiling—meaning if the employee earns more than ₱100,000 per month, the contribution is capped at ₱2,500, regardless of any income beyond that. This cap ensures fairness and prevents excessive contribution amounts for higher-income earners. 26 Excel Formula used: Determining the amount of PHIC Contribution Employee Share =IF((ROUND((F8*2*5%)/2,2))>=2500,2500,(ROUND((F8*2*5%)/2,2))) Explanation:  G8: Contains the semi-monthly salary of the employee (i.e., half of the monthly salary).  G8 * 2: Converts the semi-monthly salary to the full monthly salary.  * 5%: Applies the PhilHealth rate (currently 5% of monthly salary).  / 2: Divides the result by 2 to get the semi-monthly share of the PhilHealth contribution.  ROUND(..., 2): Rounds the amount to 2 decimal places (currency format).  IF(... >= 2500, 2500, ...): Checks if the calculated monthly contribution exceeds ₱2,500 (the current maximum allowed by PhilHealth): o If yes, returns ₱2,500. o If no, returns the actual computed value. This formula ensures the employee’s PhilHealth contribution (semi-monthly) is correctly calculated based on their salary, while enforcing the ₱2,500 monthly cap as per current PhilHealth guidelines. 27 Illustration: (Note: some columns from previous illustrations are hidden to fit on the screen) Pag-Ibig (HDMF) Contribution Computation As of February 2024, the Pag-IBIG Fund (Home Development Mutual Fund) updated its contribution scheme. The monthly contribution is calculated as 2% of the employee's monthly salary, with a maximum fund salary ceiling of ₱10,000. This means that both the employee and the employer contribute 2% of ₱10,000, resulting in a maximum monthly contribution of ₱200 each, totaling ₱400 per month. For employees earning ₱1,500 or less per month, the contribution rates are 1% for the employee and 2% for the employer, leading to a lower total contribution. Therefore, while the maximum contribution is capped at ₱200 per month for both employee and employer, the actual contribution is proportional to the employee's salary, following the specified rates. Excel Formula used: 28 Determining the amount of HDMF Contribution Employee Share =IF(F8>=10000,200,100) Explanation:  F8: This is the cell that contains the employee’s monthly salary.  IF(F8 >= 10000, 200, 100): o If the employee’s salary is ₱10,000 or more, the contribution is set to ₱200 (maximum allowed under current rules). o If the salary is less than ₱10,000, the contribution is set to ₱100 (which typically corresponds to 1% of a lower salary, based on some simplified schemes). This formula assigns ₱200 Pag-IBIG contribution for salaries ₱10,000 and above, and ₱100 for those below ₱10,000, following a simplified or fixed contribution scheme. Illustration: (Note: some columns from previous illustrations are hidden to fit on the screen) 29 Computation of Net Pay Net pay—also known as take-home pay—is the amount an employee actually receives after all deductions are taken from their gross pay. Here's an expanded explanation of how net pay is computed in the Philippine payroll setup: Formula: Net Pay = Gross Pay – Total Deductions Start with Gross Pay: Gross pay includes: o Basic salary o Overtime pay o Holiday pay o Allowances o Bonuses or incentives (if regular) o Any other earnings Deduct Government-Mandated Contributions: These are required by law and withheld every payroll period: o SSS (Social Security System) o PhilHealth o Pag-IBIG o These are typically based on fixed tables or percentage of monthly salary with caps. Deduct Withholding Tax: o o Based on monthly taxable income, following the BIR’s graduated tax table under the TRAIN Law. Computed after deducting SSS, PhilHealth, and Pag-IBIG from gross pay (since these are tax-exempt). Other Deductions (if applicable): o Loans (SSS loan, Pag-IBIG loan, company loans) The remaining amount after all deductions is the net pay, which is the amount credited to the employee’s payroll account or given in cash. 30 Illustration: (Note: some columns from previous illustrations are hidden to fit on the screen) 31 Payslip Generation After completing the payroll computation, the next step is generating the employee payslip. A payslip is an o icial document that provides a detailed breakdown of an employee's earnings and deductions for a specific pay period. It serves as a record of how the net pay was arrived at, ensuring transparency and helping employees understand their compensation. Let’s start by formatting Payslip to your liking, below is a given sample. Illustration: Here, Employee Number plays an important role. Hence, we have to ensure that its value is unique for each employee. This Employee Number on the Payslip will enable our format to become dynamic. 32 By integrating Excel Formulas and changing the Employee Number, other values will change accordingly based on our source data. I will be giving the Excel Formulas used for each section of this payslip. Please see excel tab named “Payslip Excel Formulas” on our Payroll Worksheet. Since we have now formatted our payslip, we can now proceed with converting these to individual pdf files. To proceed we have to use Visual Basic for coding feature: Step 1. Decide where you wanted to save the generated PDF Payslip In this case, I wanted it to be saved on my Drive (D:), within that drive, make a folder where we can save the files and name it to your liking. Let’s say, “Payslip Files.” 33 Step 2. Open the folder you have made and remember its address This is necessary for our coding later. You may click this highlighted portion, and the address appeared would be the data we needed. Step 3. Open Visual Basic On your Payroll worksheet, you can press Alt + F11 to open Visual Basic Application 34 Step 4. Go to Insert > Module Step 5. After the module has been opened, you’ll see this interface 35 Step 6. Pasting the script On the highlighted area, paste this script: Sub PayslipGenerator() For i = 8 To 11 Sheet5.Range("C4").Value = Sheet2.Cells(i, "A").Value DoEvents Application.Calculate Sheet5.ExportAsFixedFormat xlTypePDF, "D:\Payslip Files\" & Sheet5.Range("C4").Value & ".pdf" Next MsgBox "Payslip Generation Completed" End Sub 36 Explanation Sub PayslipGenerator() This starts the macro named PayslipGenerator (this line is based on how you named your Sheet where the Payslip Format is Placed). It tells Excel to begin running the set of instructions below. For i = 8 To 11 This starts a loop that goes through rows 8 to 11 in Sheet2 (Payslip Register). It repeats the steps inside the loop for each row — first row 8, then 9, then 10, and finally 11. Since our first data is from row 8 (Employee 10101) and ends in row 11 (Employee 10104). In the event that you will be adding additional employees, just change the 11 to corresponding row number where that last employee is placed. Sheet5.Range("C4").Value = Sheet2.Cells(i, "A").Value This takes the value from column A of Sheet2, at the current row (i), and puts it into cell C4 of Sheet5 (Payslip Generator). Think of this as selecting an employee from the list and loading their info into the payslip form. DoEvents This allows Excel to catch up and refresh what's happening in the background. It makes sure Excel doesn't skip steps or freeze before moving on. Application.Calculate This tells Excel to recalculate all formulas in the workbook. It makes sure that the payslip updates correctly based on the new value in C4. Sheet5.ExportAsFixedFormat xlTypePDF, "D:\Payslip Files\" & Sheet5.Range("C4").Value & ".pdf" This exports the Sheet5 (payslip) as a PDF file. The file is saved in the folder D:\Payslip Files\ (this is the part where I’ve mentioned earlier to take note the saving address of the generated payslips) and named based on whatever is in Sheet5!C4 (like an employee name or ID). 37 Next This tells Excel to go back to the start of the loop and do the same thing for the next row (e.g., go from row 8 to 9, and so on). MsgBox "Payslip Generation Completed" After the loop finishes, this shows a pop-up message saying that all payslips have been generated successfully. You can personalized this part, “Payslip Generation Completed”, you can either say – “Generation Done” or in anyway you like. Just revise this part on the script. End Sub This marks the end of the macro. Step 7. Adding a command button Adding a command button in Excel lets you run your macro with just one click—no need to go into the Developer tab every time. On you Payslip Generator Sheet: Simple Steps to Add a Command Button 1. Go to the "Developer" tab in Excel. o If you don’t see it, enable it via: File > Options > Customize Ribbon > Check "Developer" 38 2. Click “Insert” in the Developer tab. 39 3. Under “Form Controls”, choose the Button (first icon). 4. Click and drag on your sheet to draw the button. We can rename this button to your liking, just right click on it and select “Edit Text”. In this Case let’s name it, “Generate Payslip.” 40 5. After you release, a window will pop up asking which macro to assign. 6. Select PayslipGenerator and click OK. 41 Now What Happens? Assuming that you are now done with preparing your payroll, and everything was set, when you click the button, Excel will automatically run your macro — in this case, it will generate and save the payslips as PDFs on our assigned destination - D:\Payslip Files\. If we visit this location, generated PDF Version of Payslips should be the same as to the number of the employees, and the file name should be on its designated Employee Number. Please take note, if you are on the process of Generating Payslip for an another cycle, you need to move these files to another Folder, for it when you generate new batches of payslip, it will override the old files. 42 Government Mandated Contribution Summary Report A Government Mandated Contribution Summary Report is a document that shows the total amounts deducted from employees’ salaries for required government contributions. This usually includes deductions for SSS, PhilHealth, and Pag-IBIG in the Philippines (or similar agencies in other countries). The report summarizes both the employee's share and the employer's share of these contributions over a specific period—such as monthly or yearly. It helps ensure that all mandatory contributions are properly recorded, calculated, and reported for compliance with government regulations. Illustration: 43 File Saving and Record Keeping Since we’ve already discussed most of the key components of this manual, please make sure to save the file as a macro-enabled workbook (with the .xlsm extension) to ensure all macros and automated features function properly. When preparing the payroll for the next cycle, it's recommended to create a copy of the current file and use that version for editing. This helps preserve the original setup and prevents any accidental loss or changes to the working template. 44 Payslip Distribution using Power Automate E ortlessly send employee payslips with just a few clicks using Power Automate. This smart solution automates payslip distribution through email—saving time, reducing errors, and ensuring secure, ontime delivery every payroll cycle. To do this: Step 1. Create a list of Employee reflecting Employee Number, Name, Name to Address on the email, Email Address, and File Name of their corresponding payslip. On the file name for the attachment, it should be the same as your file name on the saved payslips at D:\Payslip Files\. 45 Step 2. Convert the list you prepared to a table Click “Insert” Click on “Table” Then Highlight your entire data and Click “Ok” 46 Then Give this table a name, in this case, “EmailReferences” take note don’t add a space if it’s two words. Step 3. Save this file on your One Drive 47 Step 4. Also in One Drive, create a folder where you are going to save the PDF Versions of Payslips ready for distribution through email. You can copy the payslips you have saved in D:\Payslip Files\ to this new folder you have created in Onedrive. You can also save here the Email References table you have created. Step 5. Log In to your Microsoft Account and go to Power Automate 48 Step 6. Go to Create Step 7. Choose Instant Cloud Flow 49 Step 8. Set a name for your Flow. In this Case, “Payslip Distribution Automation”, then click on Manually Trigger a Flow, and choose Create after. Step 9. Click on New Designer Button, then Switch without Saving 50 Step 10. Click on New Step Step 11. Go to All then select List rows present in a table (if you can’t find it, you may use the search bar) This will connect our prepared table earlier containing the data of the employees saved in One Drive. 51 Step 12. On the Location’s Tab drop down Menu, select Onedrive for Business. On the Document Library select OneDrive. Step 13. Click on the Folder Icon on the File Tab. Then on the options, click on the Right Arrow Icon of the Folder Name you have created earlier in OneDrive where you have saved your Payslip Copies. 52 Step 14. Click On the file name of the Excel Table you have created earlier. Step 15. On the Table tab, just select the Table name of the excel file we have created earlier. 53 Step 16. Click on Next Step and on the search bar look for List Files in folder. Step 17. On folder icon of Folder tab, click on the arrow right option of Root, then click on the arrow right option of the folder name in Onedrive where you saved your payslips (Payslip Demo), then select the folder inside the Payslip Demo folder where you saved the Payslip (Payslip Copy) 54 Step 18. On the upper right corner of the List files in folder flow you’ll see Three Dotted option. Click on that, then proceed on the settings and set the threshold to 1000. This number is just random; we’re setting the threshold to 1000 to enable the flow to send emails to the maximum of this number. We will not have a problem if the number of your employees is smaller than this figure. After the threshold was set, click on DONE. 55 Step 19. Click on Next Step and on the search bar look for Apply to Each. Step 20. On the search bar for Selecting an Output, search for Value. 56 Step 21. Click on Add an Action and search for Apply to each 57 Step 22. On the select output option, look for Value with excel symbol Step 23. Click on Add an Action 58 Step 24. Look for Condition Step 25. Then on a Choose a Value option, Select Name 59 Step 26. On the Choose a Value option on the right side, Select Column 5 since if we take a look on our Email References Table, Attachments column are in Column 5 Step 27. Under IF Yes condition, click on Add an Action 60 Step 28. On the search bar, look for Get File Content Step 29. On the File tab look for ID 61 Step 30. Click on Add an Action Step 31. On the search bar, look for Send Email (V2) 62 Then, Step 32. In the TO tab select the column number where the email addresses in our Email References table is placed, in our given example it is in Column 4 63 Step 33. In the Subject Tab put these details Step 34. In the BODY Tab put these details, the email is customizable. You can change whatever you wanted to include in the email. 64 Step 35. In the Attachments Name – 1 tab put this detail Step 36. In the Attachments Content – 1 tab put this detail 65 Step 37. Let’s add an Email Address which will be the sender of the email. On the Send Email (V2) upper right corner, there’s a three dotted option. Click on that and look for Add New Connection. Then Log-in the email address. Step 38. We can now proceed in saving this Flow 66 Step 39. After we have created and saved our flow, the interface should look like this Step 40. To run the process, follow these following procedures 67 Then Click “Done” After that this flow has been run, you may now check your sent items on your email, and the payslips should appear as SENT to each of the employee 68 Granting that you are now ready to send again a copy of the payslips for the next payroll cycle, just log in to your Microsoft 365 account and look for Power Automate, then go to “My Flows.” Then, look for the flow you have created and click on it And when you’re ready to send, just repeat the Steps on Step 40. 69 Disclaimer This manual is intended to serve as a practical guide for preparing a simple, Excel-based automated payroll system. It covers the fundamental components of payroll computation, sample formats, relevant Excel formulas, basic scripting, and automation for payslip generation and distribution. Please note that this manual does not provide:  An in-depth discussion of the labor and payroll-related regulations governing the Philippines;  Detailed explanations of taxation laws or compliance;  Technical breakdowns of Excel functions, formulas, or any integrated scripts and programs. The objective of this guide is to o er a visual and procedural framework to help streamline your payroll preparation process. Users are encouraged to consult relevant professionals or authorities for legal, taxation, or technical guidance as needed. 70