C&A

🎯 Situation:

• Ms. Shruti Sharma was promoted on 01.07.2023
  
  

• Her DNI (Date of Next Increment) was also 01.07.2023
  
  

• She opted to have her pay fixed from the date of next increment, which is 01.07.2023
  
  

✅ What the Rules Say (Rule 13 of CCS Revised Pay Rules, 2016):

When promotion and DNI fall on the same date (e.g., 01.07.2023), the officer can opt for pay fixation under FR 22(I)(a)(1) from that same date — and is eligible for the increment first in the lower post, then fixation in the higher post.

🔁 So the steps are:

1. Grant increment in Level 10 (from ₹67,000 → ₹69,010 approx.)
   
   

2. Then fix pay in Level 11 at the next cell equal to or above ₹69,010
   👉 In this case, the next cell is ₹71,800 in Level 11.
   
   

3. Date of next increment (DNI) shifts to 01.01.2024 — as per Rule 10
   
   

✅ Summary:

• Her pay was correctly fixed on 01.07.2023 at ₹71,800
  
  

• Her next increment date shifts to 01.01.2024 (after 6 months) as per rule
  
  

• This is rule-compliant and correct as per GOI orders

✅ Step 1: Set Up Headers for the Due & Drawn Statement

In a new sheet or starting from a new section, set up the following headers:

Cell = What to Type

A1 = Month

B1 = Pay Level

C1 = Basic Pay

D1 = Dearness Allowance (DA)

E1 = HRA

F1 = Transport Allowance

G1 = Total Pay (Due)

H1 = Total Pay (Drawn)

I1 = Arrear

These columns will help us compare what was due (based on revised pay) and what was actually drawn (based on old pay), and finally calculate the arrears.

✅ Step 2: Enter Month-wise Data Rows

We'll begin by entering months from July 2023 to March 2024.

In Cells A2 to A10, type:

Cell = What to Type

A2 = July 2023

A3 = August 2023

A4 = September 2023

A5 = October 2023

A6 = November 2023

A7 = December 2023

A8 = January 2024

A9 = February 2024

A10 = March 2024

🧮 Excel Entries for July 2023 (Row 2 as an example):

✅ Step 3: Corrected Row for July 2023 (Row 2)

Cell = What to Enter (Formula / Value) = Description

B2 = 11 = Pay Level (Revised)

C2 = 71800 = Basic Pay (Due)

D2 = =C2*46% = DA @ 46%

E2 = =C2*30% = HRA @ 30%

F2 = =7200 + 7200*46% or =7200*1.46 = TA with DA

G2 = =C2+D2+E2+F2 = Total Pay (Due)

H2 = =67000 + 67000*46% + 67000*30% + 7200*1.46 = Total Pay (Drawn) on old pay

I2 = =G2-H2 = Arrear

✅ Step 4: Fill Data for January to March 2024 (DA @ 50%)

Now for Rows 8 to 10 (Jan, Feb, Mar 2024), use the same structure — just update DA from 46% to 50%.

Let’s use Row 8 (January 2024) as an example:

Cell = What to Enter = Description

B8 = 11 = Pay Level (Revised)

C8 = 71800 = Basic Pay (Due)

D8 = =C8*50% or =C8*0.50 = DA @ 50%

E8 = =C8*30% = HRA @ 30%

F8 = =7200*1.50 or =7200 + 7200*50% = TA with DA

G8 = =C8+D8+E8+F8 = Total Pay (Due)

H8 = =67000 + 67000*50% + 67000*30% + 7200*1.50 = Total Pay (Drawn) on old pay

I8 = =G8-H8

Arrear

✅ Now, drag the formulas from Row 8 down to Row 10 (for Feb and Mar 2024).

✅ Step 5: Total Arrear & Certificate for DDO

➤ Total Arrear Calculation

In any empty cell (say I11), type:

Cell = What to Type = Purpose

I11 = =SUM(I2:I10) = This gives total arrear from July 2023 to March 2024

➤ DDO Certificate (as per format)

Scroll down to any empty row (say Row 13 or below) and type this in Cell A13 (merge across columns if needed):

Certified that the arrear amount of ₹__________ (as per above due & drawn statement) has been drawn correctly and is admissible under rules.

Step 1: Set up headers (Start from Row 1)

Cell = Content

A1

Month No.

B1

Month & Year

C1

Opening Balance

D1

EMI (₹15,000)

E1

Interest @8.5%

F1

Principal Paid

G1

Closing Balance

Step 2: Enter Starting Values

• In A2, type: 1
  
  

• In B2, type: 01-Jul-2018
  
  

• In C2, type: =1500000 (initial loan)
  
  

• In D2, type: =15000 (monthly EMI)
  
  

• In E2, type: =C2*8.5%/12 (monthly interest)
  
  

• In F2, type: =D2-E2 (Principal Paid = EMI - Interest)
  
  

• In G2, type: =C2-F2 (Closing Balance = Opening - Principal Paid)
  
  

Step 3: Add Formulas for Following Rows (Row 3 onwards)

• In A3, type: =A2+1 (Month No. Increment)
  
  

• In B3, type: =EDATE(B2,1) (Next Month)
  
  

• In C3, type: =G2 (Opening = Previous Closing)
  
  

• Copy D2 to D3 (same EMI)
  
  

• In E3, type: =C3*8.5%/12
  
  

• In F3, type: =D3-E3
  
  

• In G3, type: =C3-F3
  
  

Step 4: Drag Down the Formulas

• Select Row 3 (A3 to G3)
  
  

• Drag down till Month No. 100 (Row 101)
  
  

Step 5: Calculate Total Interest Paid

• Below the schedule (say, in E102), type: =SUM(E2:E101)
  
  

• Label it in D102 as Total Interest Paid

🧾 Income Details

1. Basic Pay:
   
   

   • ₹60,400 per month till June 2023
     
     

   • DNI in July 2023 → ₹62,300 (based on Level-8 matrix assumption)
     
     

2. DA:
   
   

   • 42% from Jan 2023 to June 2023
     
     

   • 46% from July 2023 to Dec 2023
     
     

   • 50% from Jan 2024 to March 2024
     
     

3. HRA Exemption
   
   

4. Allowances:
   
   

   • Entertainment, Medical Reimbursement, etc.
     
     

💸 Deductions

1. Section 80C
   
   

   • NSC, Tuition Fees, Housing Loan (Principal), NPS (extra contribution)
     
     

2. Section 24(b)
   
   

   • Housing Loan Interest
     
     

3. Section 80E
   
   

   • Education Loan Interest
     
     

4. Section 80TTA
   
   

   • Savings Interest
     
     

5. Section 80DD
   
   

   • Handicapped Dependent
     
     

6. Section 80D
   
   

   • CGHS + Parent’s Medical Expense
     
     

7. Section 80G
   
   

   • PM Relief Fund (100% deduction)

A1 = Particulars

B1 = Monthly Amount (₹)

C1 = Months

D1 = Annual Amount (₹)

STEP 2: Basic Pay & DA (Rows 2–4)

👉 A2: "Basic Pay (Jan–Jun 2023)"

• B2: =60400

• C2: =6

• D2: =B2*C2
  
  

👉 A3: "Basic Pay (Jul–Mar 2024)"

• B3: =62300

• C3: =9

• D3: =B3*C3
  
  

👉 A4: "Total Basic Pay"

• D4: =D2+D3

💥 STEP 3: Calculate Dearness Allowance (DA)

👉 A5: "DA @42% (Jan–Jun)"

• B5: =60400*42%
  
  

• C5: =6
  
  

• D5: =B5*C5
  
  

👉 A6: "DA @46% (Jul–Dec)"

• B6: =62300*46%
  
  

• C6: =6
  
  

• D6: =B6*C6
  
  

👉 A7: "DA @50% (Jan–Mar)"

• B7: =62300*50%
  
  

• C7: =3
  
  

• D7: =B7*C7
  
  

👉 A8: "Total DA"

• D8: =D5+D6+D7
  
  

🏠 STEP 4: HRA Calculation (Exemption under Section 10(13A))

We’ll calculate least of the following three:

1. Actual HRA received
   
   

2. Rent paid – 10% of salary
   
   

3. 50% of salary (for metro city like Delhi)
   
   

For now, add just input data here:

| A9 | HRA Paid | B9: =9000 | C9: =12 | D9: =B9*C9 = ₹1,08,000 |

💰 STEP 5: Add Other Income Components

Row = Cell A = Cell D

10 = Entertainment Allowance = =8000

11 =  Medical Reimbursement = =20000

12 = Interest from Savings Account = =12000

🧾 STEP 6: Gross Income

| A13 | Gross Salary + Other Income
| D13 | =D4 + D8 + D9 + D10 + D11 + D12

📉 STEP 7: Deductions (80C, 80D, etc.) Section (Row 15 onwards)

We'll start a new section from Row 15 onward:

Row 15:

| A15 | Deductions under Chapter VI-A | Leave B–C blank |

Section 80C:

| A16 | NSC | D16: =25000 | | A17 | Tuition Fee (₹4000×12) | D17: =4000*12 | | A18 | Housing Loan Principal | D18: =50000 | | A19 | NPS Extra Contribution | D19: =25000 | | A20 | 80C Subtotal (Max ₹1.5L) | D20: =MIN(D16+D17+D18+D19,150000) |

💊 STEP 8: Other Deductions

| A21 | Housing Loan Interest (Sec 24b) | D21: =75000 | | A22 | Education Loan Interest (Sec 80E) | D22: =10000 | | A23 | Handicapped Dependent (80DD) | D23: =125000 (Fixed limit if ≥ 80% disability) | | A24 | CGHS (₹650×12) | D24: =650*12 | | A25 | Parents’ Medical (Senior) (80D) | D25: =40000 | | A26 | PMNRF Donation (80G - 100%) | D26: =10000 | | A27 | Savings Interest (80TTA) | D27: =MIN(10000,D12) (Max ₹10,000) |

| A28 | Total Deductions | D28: =D20+D21+D22+D23+D24+D25+D26+D27 |

📌 STEP 9: Net Taxable Income

| A30 | Net Taxable Income | D30: =D13-D28 |

🧮 STEP 11: Create Tax Slabs Section (Start from Row 32)

Row = Cell A = Cell D (Formula / Value)

32 = Tax Slab Calculation = (leave blank)

33 = Income up to ₹2,50,000 = =MIN(D30,250000)

34 = Income ₹2,50,001 – ₹5,00,000 = =IF(D30>250000, MIN(D30-250000,250000), 0)

35 = Income ₹5,00,001 – ₹10,00,000 = =IF(D30>500000, MIN(D30-500000,500000), 0)

36 = Income above ₹10,00,000 = =IF(D30>1000000, D30-1000000, 0)

🧾 STEP 12: Apply Tax Rates

Row = Cell A = Cell E (Tax)

33 = @0% = =D33*0

34 = @5% = =D34*5%

35 = @20% = =D35*20%

36 = @30% = =D36*30%

37 = Total Tax Before Rebate = =E33+E34+E35+E36

🎁 STEP 13: Rebate under Section 87A (If Income ≤ ₹5,00,000)

| A38 | Rebate u/s 87A (Max ₹12,500) |
| D38 | =IF(D30<=500000, MIN(12500,E37), 0)

💰 STEP 14: Tax Payable After Rebate

| A39 | Tax After Rebate |
| D39 | =E37-D38

🔢 STEP 15: Add 4% Health & Education Cess

| A40 | Cess @ 4% | D40: =D39*4% | | A41 | Total Tax Payable | D41: =D39+D40 |

✅ What’s Covered:

1. Accurate monthly salary breakup with DA changes & DNI.
   
   

2. Allowances (HRA, Entertainment, Medical, etc.)
   
   

3. HRA exemption structure as per Rule 2A (to be applied after calculating salary).
   
   

4. All relevant deductions:
   
   

   • 80C (with ₹1.5 lakh cap)
     
     

   • 80D (medical + CGHS)
     
     

   • 80DD (disabled dependent)
     
     

   • 80E (education loan)
     
     

   • 80G (PM relief fund)
     
     

   • 80TTA (savings interest)
     
     

5. House loan interest deduction under Section 24(b) (₹75,000)
   
   

6. Rebate u/s 87A included (up to ₹12,500 for income ≤ ₹5 lakh)
   
   

7. Tax slab calculation with correct rates:
   
   

   • 0% up to ₹2.5 lakh
     
     

   • 5% from ₹2.5 to ₹5 lakh
     
     

   • 20% from ₹5 to ₹10 lakh
     
     

   • 30% above ₹10 lakh
     
     

8. Health & Education Cess @ 4%
   
   

🔍 You just need to double-check one thing manually:
For HRA exemption under Section 10(13A), we need to compute least of the following:

• Actual HRA received = ₹9,000 × 12 = ₹1,08,000
  
  

• Rent paid – 10% of Salary (Basic + DA)
  
  

• 50% of Salary (since posted in Delhi)
  
  

To apply that accurately, you may want to create a small HRA section separately in the Excel sheet (I can help with that too).

Cell = What to Enter

A1 = Taxable Income

B1 = (Enter taxable income manually, e.g., 850000)

A3 = Slab

B3 = Amount in Slab (₹)

C3 = Tax Rate

D3 = Tax (₹)

A4 = Up to ₹2,50,000

B4 = =MIN(250000, B1)

C4 = 0%

D4 = =B4*0%

A5 = ₹2.5L – ₹5L

B5 =IF(B1>250000, MIN(B1-250000,250000), 0)

C5 = 5%

D5 =B5*5%

A6 = ₹5L – ₹10L

B6 = =IF(B1>500000, MIN(B1-500000,500000), 0)

C6 = 20%

D6 = =B6*20%

A7 = Above ₹10L

B7 = =IF(B1>1000000, B1-1000000, 0)

C7 = 30%

D7 = =B7*30%

A8 = Total Tax Before Rebate/Cess

D8 = =SUM(D4:D7)