C&A

https://docs.google.com/spreadsheets/d/1Mj5430_IZpMKXUZ-LLeVK_tbwAW9lF6z/edit?usp=sharing&ouid=117509685449591239102&rtpof=true&sd=true

Income Tax Calculation Sheet which is to be shared.xlsx

Ms. Shruti Sharma, working as Assistant Director in the Department of Economic Affairs, Delhi was drawing basic pay of Rs. 67,000 in pay matrix Level 10 w.e.f. 1.1.2023 with DNI on 1.7.2023. 

She was promoted to the post of Deputy Director in pay matrix Level 11 w.e.f. 1.7.2023 retrospectively vide Office Order No. Estt.II/2023 dated 01.04.2024. 

She opted to get her pay fixed from the date of next increment and his pay was fixed at the rate of Rupees 71,800 per month with date of next increment on 01.01.2024. 


2. Rates of allowances:

(i) Dearness allowance @ 46% from 1.7.2023 to 31.12 2023 and @ 50% from 1.1.2024 onwards.

(ii) H R A @ 30% of basic pay.

(iii) Transport allowance @ Rs. 7,200 p.m. plus dearness allowance as admissible.


3. Calculate the net amount of arrear of pay and allowances from 1.7.2023 to 31.03.2024 payable to Ms. Shruti Sharma by preparing a due and drawn statement on MS-Excel and record the requisite certificate as required to be recorded on the arrear bill by the D.D.O.

Answer:

Certificate:

🎯 Situation:


✅ 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:

✅ Summary:



✅ 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.



H.B.A. INTEREST CALCULATION (Marks 10)
Calculate the amount of interest (by IBB method) payable by Shri Jagsharan, Section Officer on House Building Advance taken by him from the following particulars- Basic pay Rs. 87,400/- (Level-10) Amount of Advance Rs. 15,00,000/- paid in two equal instalments. First instalment of Rs.5,00,000/- in December, 2017 and the second instalment of Rs.10,00,000/- in April, 2018. Recovery started from the salary of July, 2018. Number of instalments 100 Rate of Interest 8.5% [It is presumed that all the instalments will be evenly recovered every month in future and the house shall be kept insured during entire period when the loan remains outstanding against the Govt. servant]    Answer: 

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


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


Step 4: Drag Down the Formulas


Step 5: Calculate Total Interest Paid


Ms. Aarti Gupta joined as Research Officer (in Level-8), Department of Science & Technology on 15 th June, 2017 & posted at Delhi. Calculate her Income Tax liability as per old tax regime for the Financial Year 2023-24 with the following particulars: (Pay Matrix table attached)

1. Basic pay Rs. 60,400 p.m. as on 01.01.2023 and DNI in July 2023

2. DA @ 42% w.e.f 1.1.2023, 46% w.e.f 1.7.2023 and 50% w.e.f 1.1.2024

3. Seeks Rent exemption & paid Rs. 9000/-p.m. in Delhi

4. Entertainment Allowances Rs.8000/-

5. Medical Bills reimbursement Rs. 20,000/-

6. Extra Rs.25000/- contribution towards NPS

7. National saving Certificate Rs. 25,000/- and paid tuition fee for a child @

Rs.4000 p.m

8. Housing Loan repayment per annuam - Rs. 50,000/- (Principal) and Rs.

75,000/- (Interest)

9. Education loan for higher education of her husband and paid Rs. 10,000/- as interest on this loan to the bank.

10. Interest on Saving Bank Rs. 12,000/-

11. Supporting a dependent (Severely handicapped) by incurring an expenditure of Rs.1,30,000/-

12. CGEGIS @ 60 p.m.

13. CGHS @ 650 p.m.

14. Expenditure incurred towards the parents (Senior Citizen Rs.40,000/-) not

covered under CGHS

15. Donation in PM National Relief Fund Rs. 10,000/- per annum.


Answer:

🧾 Income Details


💸 Deductions




A1 = Particulars


B1 = Monthly Amount (₹)


C1 = Months


D1 = Annual Amount (₹)




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

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

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

👉 A4: "Total Basic Pay"


💥 STEP 3: Calculate Dearness Allowance (DA)

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

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

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

👉 A8: "Total DA"

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

We’ll calculate least of the following three:

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:


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

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

Excel sheet for calculation of income tax


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)