Comprehensive Guide to Arrears Calculation and Salary Increment Using Excel - Expert Insights

Dear Mr. Avika Kapoor,

I am working as an HR Assistant, and I would like to request details regarding the following:

Our company has decided to provide an 8% increment on gross salary effective from OCT-2009 to MAR-2010. We adjusted the salaries accordingly starting from Feb-2010.

We now need to calculate arrears for the past 4 months. Could you please clarify whether I should consider the Loss of Pay (LOP) while calculating the arrears for this period?

I am looking forward to your reply.

Regards,
Ragunathan
0994434273

Mr. Ragunathan,

You have to consider the attendance of the respective months. For example, if you paid the December 09 salary for 24 days, this increment is applicable for 24 days only and not for 31 days. Is it clear now?

Thank you.

Hi,

It's simple.

On a worksheet in Excel, the eligible amount as per the increased salary should be calculated, called as eligible pay. The first column is for the month, the second column for the basic, the third column for DA, and subsequently one column each for other allowances. The gross eligible pay will be calculated.

Do the same thing for the salary drawn for each month in question. The gross pay drawn will come.

Next, calculate the difference between what was eligible and what amount was drawn, column-wise for each component, and also for the total pay in separate columns. These amounts of difference will indicate arrears.

In other words, the basic pay eligible - what is drawn for all components of pay. The same thing should be done finally from the gross eligible salary, subtract the gross salary drawn for every month.

The last step is to total the columns, and the total for the difference between gross salary eligible - gross salary drawn will give you the arrears of pay when the hike in salary is effected with retrospective effect. However, if any employee avails leave without pay and allowance or avails half-pay leave, etc., the same must be adjusted.

This method will give you item-wise arrears, month-wise arrears, and periodic arrears. The work must be done meticulously.

Dr. Vasudeva Reddy, Hyderabad.

Hi,

I am working as an HR Assistant, and I want to know the details for arrears calculation in salary. How do I calculate it in Excel format from August 2009 to March 2010?

Hi All,

Arrears calculation will not stop at Gross. We need to calculate PF, ESI, Superannuation based on Salary Increment, and the payments have to be made accordingly.

Regards,
PK

Yes, as said by Kalapoorna, but first, how to arrive at the arrears amount. Once they are calculated, the other usual things are to be adjusted.

Good question and quite well answered! Upgrade yourself or be left behind! I am amazed to see how you people have reached such an important position in HR.

Hi,

Any loss of pay includes all benefits arising out of pay such as increments and seniority, unless specified otherwise. Hence, the increments get postponed accordingly, and no arrears can be paid for the period of LOP.

Yogesh