CTC Breakup | Learn to prepare Salary Breakup in MS Excel

Hi Friends in my previous Article we learn what is CTC and what is the CTC structure, Now we will learn how to prepare CTC breakup, what is statutory Payroll compliances and also we will learn to prepare CTC Annexure in MS Excel.

Explanation of CTC breakup

As an HR professional we need to prepare CTC Annexure (CTC Breakup), also for an employee He or She must should understand the details of the salary offered to them.

The salary breakup is the segregation of the CTC amount consideration with parameters set by the Government. This is a details statement of the total compensation paid to employees.

This contains various component like Basic Salary, Dearness Allowance, House Rent Allowance, Special Allowance etc.

Pay Heads of CTC Breakup

Salary is comprised with three key mechanisms

1.Basic Salary


3.Deductions (PF, PT, ESIC, TDS, Other Deductions)

What is Basic Salary?

Basic salary of any CTC component is a fixed amount which is around 40%-50% of Total Salary. Basic Salary also may differ according to type of industry and designation of employees. Being the core components of Salary many Payroll compliance contribution is based upon the Basic Salary.

For instance – Provident fund 12% of Basic, Gratuity 4.81% of Basic etc. Basic Salary is fully Taxable

What is other Allowances?

In accordance with company policy, Industry and job profile allowances are provided to employees which is above the Basic component.

Hence Allowance may differ from company to company.

For some allowance there are provision for Tax Exemption like For HRA exemption Sec 10(13) A etc these are Scheduled Reimbursement), some allowances are those for them there is no any specific name listed in the exemption list in Income Tax, for example Mobile Allowances etc these are unscheduled Reimbursement).

Now we will see in detail.

1.Dearness Allowance

To mitigate the impact of inflation some amount of percentage of Basic is paid to employee as per the Government defined rate.

2.House Rent Allowances

Employees living in rented home can take the tax benefit by opting the House Rent allowances for claiming the tax benefit employee must need to furnish Rent Receipt.

3. Leave Travel Allowance (LTA)

LTA is very beneficial component in terms of saving the Tax.

As this allows to claim the expenses made on Domestic travel under Section 10(5) and mode of travel may be Air, Railways or Public Transport.

4. Books and Periodicals Allowance 

Employee avail this allowance to meet the expenses incurred for purchase Books, Newspaper and periodicals. Employee get the exemption in Tax to the extent of the Actual amount spent towards the purchase of books and periodicals

5. Mobile Allowance

Employee avail this allowance to meet the expenses incurred for paying the bills of Mobile/Telephone. Employee get the exemption in Tax to the extent of the Actual amount spent against these Telephone/Mobile bill payments.

Here we can say

Gross Salary = Basic + Allowances

What is Deductions?

To figure out the Net Payment we subtract some amount from the Gross Salary.

Those deductions are as below.

1.Prvident Fund


3.Professinal Tax

4.Income Tax (TDS)

5.Salary Advances

CTC Annexure

Here is the complete Ms Excel with formula, you may also download this Excel workbook. Link is given in the video’s description

Here is the complete Ms Excel with formula, you may also download this Excel workbook link is given below


Here you can see CTC is given INR 6 Lac in Cell B2.Basic Salary has been considered 50% of cell B2 which is INR 3,00,000.House Rent allowances has been considered 40% of Basic which is INR 1,50,000.LTA has been considered on month of Basic amount. Books and Periodicals Allowance is as per the Company Policy. Mobile Allowance & Internet Bills is also as per the company Policy. The Total of Above allowance is considered as Gross Salary

Retirals parts/Employer Contributions

Employer Provident Fund considered 12% of Basic it may also INR 1800 Per month. Gratuity has been considered 4.81% of Basic

Statutory Bonus 8.33% of Basic, here basic may vary from state to state as per the minimum Wages of that state. Employer ESIC 3.25% if Gross is Less than or Equal to INR 21,000 then it is applicable.

Here I would like to aware you that the Special Allowance INR 14,580 is the balancing figure which has been derived by subtracting all allowances and Retiral Benefits from the CTC amount.

NET Salary

Now we will talk about the NET Salary.

Likewise employer employee also contribute some statuary compliances like Provident fund which is 12% of Basic, ESIC which is 0.75% of Gross if Gross is Less than 21,000.Profession Tax according to that state.

So here you have seen how to prepare the CTC annexure, You may also watch our complete video on YouTube.

Leave a Comment

Your email address will not be published. Required fields are marked *