Copy-Paste Excel Formulas for DA Arrear Calculation
You can build a DA arrear sheet in Excel in a few minutes. Set up the columns as A: Basic Pay, B: Old DA %, C: New DA %, D: Gross Arrear, E: NPS Deduction, F: Net Payable, enter your values in row 2, and paste these 7th CPC formulas.
Monthly Gross DA Arrear
=ROUND(A2 * (C2 - B2) / 100, 0)
NPS Deduction (10%)
=ROUND(D2 * 0.10, 0)
Net Payable Arrear
=D2 - E2
Total Arrear for Many Months
Fill the formulas down one row per month and total the net column:
=SUM(F2:F13)
Here, A2 is Basic Pay, B2 is the old DA rate and C2 is the new DA rate, each written as a number such as 58 and 60, not as 58%. The formula multiplies basic pay by the rise in DA, divides by 100 and rounds to the nearest rupee, as accounts offices do.
How to Use the Web Excel Simulator
Enter the basic pay, old and new DA rates and the number of months. The grid above shows the month-by-month DA arrear with NPS and net payable. Press the export button to download the grid as a CSV file that opens in Excel, or download the blank template to enter several basic pay values. If you only need a total, use the DA arrear calculator. For the current DA, see the online DA calculator or the home page.
Frequently Asked Questions (FAQ)
How do I calculate DA arrears in Excel?
Put Basic Pay in A2, the old DA % in B2 and the new DA % in C2. Use =ROUND(A2*(C2-B2)/100,0) for the monthly gross arrear, =ROUND(D2*0.10,0) for NPS and =D2-E2 for the net amount. Multiply by the number of months or fill the formula down one row per month.
Is the downloaded CSV file compatible with Excel?
Yes. The CSV opens directly in Microsoft Excel, LibreOffice Calc and Google Sheets. Use the export button for your own figures, or download the blank template with the formulas already filled in.
Is NPS deducted from DA arrears?
For employees under the National Pension System, the 10% employee contribution applies to DA, so it is deducted from arrears as well. Switch the NPS option off if you are under the old pension scheme. Our DA arrear calculator gives a quick total without a table.
Why can the DA columns differ by ₹1 from the gross arrear?
Old DA and new DA are each rounded to the nearest rupee, while the gross arrear is rounded once using the formula on the difference. Offices normally follow the formula, so use the gross arrear column.