DA Arrear Calculation

Ministry of Finance has been issued Order to enhance DA from July, 2012 [click here to see Order]. Here we will discuss about some skill to prepare DA arrear statement in Microsoft Excel. DA Arrear calculation in Excel is not so hard, only simple calculation method will give you accurate result and you may prepare any large statement for your office DA Arrear Bill. Here you will find some skill to prepare your DA Arrear statement in perfect way to make in traditional arrear statement where one should sign with revenue stamp to complete official obligation.

Click on pictures to see procedure:-

Stage 1
At First we need to prepare a database for last month salary entitlement July.
  • Database must contain Name, Post, Pay in PB, Grade Pay to calculate dearness allowance.
  • You may add Employee No. or Employee code, Group, Division, Section, Office Name, Bank Account No. (for bank advice) as per your official need.
  • Your database must contain any one unique value field i.e. Employee No., Name or Sl. No. which not to be repeated in records. Suppose that if you using name field as unique field then add First (I) (II) for any duplicate or repeated name for your office.
  • You may import your salary database from your normal pay bill programme. You should use only last month database of arrear bill.
Now open a blank Excel sheet and create field name as example given below or open your imported data sheet or open your salary database or copy paste your salary database in new sheet delete any unwanted field or column:-

Note: 1. Place every salary component from Column “J” on which Dearness Allowance to be calculated as per your official entitlement.
2. You may not use “From” “To” date column if you have not any official with changes in monthly salary due to EOL, HPL, Promotion. You may hide further these columns if you feel so.
3. You may use separate data sheet i.e. section wise, group wise, office wise for different bill as per your need.

After Column M
Put formula in column “N” to Column “U” as shown above in every corresponding cell of 2nd row of your sheet. Please keep Column “V” & “W” blank for total rows will use in next stage.
Select/copy 2nd row’s formula and drag/paste in rest corresponding rows to complete full month.

There is nothing special in above stated procedure. You should complete all data for the last month of arrear i.e. September and should check value of “Month” column “F” during dragging or copy/pasting it should be same value for particular month.

Now change your Month value with 01-07-2012 [July-12] in Column "F"


Stage 2
After completing the preparation of July months’ data in all ways i.e. data and formulae
- make a copy of all rows of July month data with selection & Ctrl+C command and paste [Ctrl+V] it just below the July month without leaving any blank rows.
- Change the value of “Month” Column “F” with “Aug-2012” in new pasted data
- Again repeat above action: – make a copy of all rows of last month data and paste it just below earlier pasted data [for the Month of “Sep-12” ]
Change the value of “Month” Column “F” with “Sep-2012”

Now you have prepared database for all months check the entitlement for all staff for all months. For broken period for any employee in any month due to promotion/EOL/HPL/Upgradation insert duplicate row of concerned employee just below and change period in column “G” & “H” [From/to ] and change entitlement in Column “I” to “M” as required. You may do check accuracy at this stage or later.

As repeated earlier should check value of Month, it should be same in corresponding monthly database.

All above processes are normal.

Stage 3
Now purpose of writing of this article will starting now, be read carefully its easy to understand and use:-
Now copy again Last Month Database [Sep-12] which was prepared in stage2 and Paste it just below the Sep-2012 month data and change the month value Oct-12.
These new copied rows will be used to show Total for every employee, therefore you should delete/clear all value of Column “G” [From] “PB” to Column “W” [Net Payable Arrear]
Type “Total” value in Column “G”
Type formula to sum above three rows value in every column from Column “N” to “U”. If your Oct data is starting from rows 10 you may type in =sum(n7:n9) in cell N10.
Now Column “V” & “W” will be used here which was left blank in Stage 1 : Type formula =round(U10*10%,0) in Column “V” & Type formula =U10-V10 in column “W”

Select/Copy this cell and drag/paste it from Column “O” to Column “W”.
After completing the first row of your Oct/Apr month select/copy the cells of this row from Column “G” to Column “W” and drag/paste it to all rows of rest Oct/Apr month data.
Avoid any improper value displayed in Column “G” to Column “W”

Stage 4
Need of this stage : if you want an blank row after every employee details, otherwise skip this stage
Now copy all rows again Last Month Database [Sep-12] which was prepared in stage 2 and paste it just below the Oct-12 database
Change Column “F” “Month” column with value Nov-12
Empty all Cell from Column “G” to Column “W” of newly pasted database of Nov-12

Stage 5
This is Last stage.
Hide your data from printing:
- Select
Sort: Now select your prepared database from start to end
i.e. Cell A1 to last row of Column “W” using mouse or
using Shift [first click in cell A1 & press Shift button then scroll down and select cell of Column “W”]
and after proper selection filter your database by clicking Data Menu/Tab > Sort
and Sort your database on your unique value column/field [as earlier discussed in stage1]
for sorting Click Data [menu/tab] > Sort> check on My data has header > select field Emp No.
and see your Excel Work Sheet – You have done your job

At last you have to adjust column width, page layout, Number formatting in all value column use your Excel Knowledge for that.

Download Example sheet to view the result [select File>download]

Must read following useful Excel tips:-
Convert Rupees in word in Excel Sheet

Enter multiple lines in excel cell

Stay connected with us via Facebook, Google+ or Email Subscription.

Subscribe to Central Government Employee News & Tools by Email [Click Here]
Follow us: Twitter [click here] | Facebook [click here] Google+ [click here]

1 comment:



Don't hesitate to express yourself. Please write your valuable comments.

This Month's Popular Posts

Top Generated search terms/ keywords used by our visitors to  arrive in Central Government Employee News.
* 7th Pay Commission – Estimated Pay Scales shows substantial increase in salary for CG Employees
* Projected 7th CPC Pay for each cadre/Post, Pension and all service matter: Submission of oral evidence by BPMS
* Expected Pension / Family Pension Table in 7th Pay Commission
* 7th Pay Commission will bring fortune for central government employees
* Decisions taken National Joint Council of Action meeting held on 16.02.2015 on 7th CPC, DA Merger issues
* Importance of other allowances in 7th CPC report
* Modi Government is set to big announcement on Budget in view of 7th Pay Commission Read more
* 7th CPC may recommend no increment for lazy central government employees
* Budget demand for Seventh Pay Commission Read more
* Expected DA DR from July 2015: AICPIN released for Feb, 2015
* Government refused interim relief and merger of dearness allowance, assured for 7th CPC report on time
* Meetings Schedule of 7th Pay Commission with Union/Association
* 7th Pay Commission is likely to seek extension, delay in report is expected
* What central government employees can expect from the 7th Pay Commission
* Proposal to 7th CPC: 3.7 multiplication factor, Minimum Wage of Rs.26000/-, 5% Increment rate, 5 promotions, HRA - 60%,40%,20%, Wage ratio 1:8, CEA for Higher Studies, Minimum Pension 67%
* 7th Pay commission may drop the Pay Band-Grade Pay System
* Budget: Government may relax LTC norms to boost tourism
* New Pension Scheme in Railways may be scrapped
* Pension can’t be less than 50% of pay: Supreme Court
* Guidelines for Educational Qualifications and Experience for framing/ amendment of Recruitment Rules
* Income Tax Exemption Limit in the Budget 2015-16
* Budget will decide actual date of implementation of 7th Pay Commission
* Seventh Pay Commission likely to submit report in October 2015
* Schedule for Admission in Kendriya Vidyalayas for the year 2015-16 Click for form & more
* Budget 2015: Income tax expectations for the salaried from PM Narendra Modi's government
*  Meeting by 7th CPC
* Latest on One Rank One Pension 
* Revised Railway Concessional Certificate Forms
Adhar Based Biometric Attendence System
Update on Bank Staff Wages Revision  
Usage of Other Bank's ATM: RBI Clarification
* Time to reform CGHS: An article  
* Pre-2006 Pensioner Case: Law Ministry's Advice
* Latest on Income Tax
* 7th CPC News   Terms of Reference  Composition  Demands
* Cadre Restructuring * Income Tax CBEC  * CAT Order
* Interim Relief   * Merger of DA  *  7th CPC Interim Report
* MACP on Promotional Hierarchy: Latest Update
* Pension Arrears from 01.01.2006 as per Court Order
* Frequently Asked Questions on Service Matters
* Job on Deputation/Absorption/Contract for Retired/Working
List of Gazetted/Restricted Holidays 2015
* One Rank One Pension Orders * Impact of DA Merger

All Time Popular Posts

Recent Comments