The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →The right Excel formula for basic salary depends on the figure you are starting with: CTC, gross salary, annual basic pay, or days worked. There is no universal rule that basic salary must be 50% of CTC; use the percentage or calculation method specified by your employer or payroll policy.
Choose the formula for the salary figure you have
| Starting information | Excel formula | What it calculates |
|---|---|---|
| Annual CTC and an approved basic percentage | =Annual_CTC*Basic_Percentage |
Annual basic salary, if the percentage applies to total CTC. |
| Monthly CTC and an approved basic percentage | =Monthly_CTC*Basic_Percentage |
Monthly basic salary, if the percentage applies to monthly CTC. |
| Annual basic salary | =Annual_Basic/12 |
A simple monthly conversion assuming 12 equal salary periods. |
| Monthly gross salary and a complete list of allowances | =Gross_Salary-SUM(Allowances) |
Basic salary, if all other earnings are listed in the same period. |
| Monthly basic salary and days worked | =Monthly_Basic*Days_Worked/Payroll_Divisor |
Basic pay earned for part of a month, using the employer’s divisor. |
| Gross salary and employee deductions | =Gross_Salary-Total_Employee_Deductions |
Net salary before any other adjustments. |
| U.S. annual salary and pay frequency | =Annual_Salary/Pay_Periods_Per_Year |
Basic salary per paycheck; use the pay-period count on the employer’s payroll schedule. |
For example, if annual CTC is ₹600,000 and an employer-approved basic allocation is 50%, annual basic is =600000*50%, or ₹300,000. The 50% is an input assumption, not a universal legal or payroll rule. Indian salary structures vary by employer and applicable rules; see the Indian Institute of Cost and Management and Zoho’s explanation of basic salary.
Know which salary number you are calculating
Basic salary is the foundational pay component, not automatically the full salary or the amount deposited into an employee’s bank account. Gross salary is earnings before employee deductions; net salary is what remains after those deductions. In India, CTC can also include employer-side costs and benefits. The exact components depend on the employer and jurisdiction.
- Basic salary: The fixed foundational pay component.
- Gross salary: Basic pay plus applicable allowances and other earnings, before employee deductions.
- Net salary: Gross salary minus employee deductions.
- CTC: An employer-cost figure that may include employer contributions, benefits, or contingent items as well as earnings.
- Employer contributions: Employer-side costs that may be included in CTC; do not automatically subtract them from employee gross pay.
A useful conceptual flow is CTC → gross earnings (basic + allowances + variable earnings) → employee deductions → net salary. It is not a universal accounting layout: employers may define CTC and salary components differently. India’s Income Tax Department guidance on income from salary describes salary broadly; basic salary is only one component. For U.S. terminology, the IRS explains gross pay and net pay.
Recommended Free Tools
Build a basic salary calculator in Excel
Use one period consistently throughout the sheet. The example below uses monthly amounts except for annual CTC and annual basic.
| Cell | Label | Example or formula |
|---|---|---|
| B2 | Annual CTC | 600000 |
| B3 | Basic percentage | 50% |
| B4 | Annual basic salary | =B2*B3 |
| B5 | Monthly basic salary | =B4/12 |
| B6 | Days worked | 22 |
| B7 | Payroll divisor | 30 |
| B8 | Basic earned this month | =B5*B6/B7 |
| B9 | HRA | 12500 |
| B10 | Other allowances | 8000 |
| B11 | Gross salary | =SUM(B8:B10) |
| B12 | Employee deductions | 4000 |
| B13 | Net salary | =B11-B12 |
- Enter numeric values in the input cells. Apply currency formatting to money and percentage formatting to B3; do not type currency symbols into the underlying numbers.
- Enter annual CTC in B2 and the employer’s approved basic percentage in B3.
- In B4, enter
=B2*B3. In B5, enter=B4/12for a simple monthly conversion. - Enter eligible days worked and the payroll divisor in B6 and B7. In B8, use
=B5*B6/B7. - Enter monthly allowances in B9 and B10. In B11, use
=SUM(B8:B10)to total basic earned and allowances. - Enter employee deductions in B12. In B13, use
=B11-B12.
Excel formulas begin with = and can use cell references, arithmetic operators, and functions such as SUM. See Microsoft’s overview of formulas, basic Excel tasks, and Excel as a calculator.
Work through the example
This illustrative India-oriented example assumes annual CTC of ₹600,000, an employer-approved basic allocation of 50%, monthly HRA of ₹12,500, other monthly allowances of ₹8,000, employee deductions of ₹4,000, 22 eligible days, and a 30-day divisor. The employer’s policy must support each assumption; the figures do not establish a standard salary structure.
- Annual basic:
=600000*50%gives ₹300,000. - Monthly basic:
=300000/12gives ₹25,000. - Basic earned for the partial month:
=25000*22/30gives ₹18,333.33. - Gross salary:
=18333.33+12500+8000gives ₹38,833.33. - Estimated net salary:
=38833.33-4000gives ₹34,833.33.
Prorate basic pay using the correct divisor
The formula is =Monthly_Basic*Eligible_Days/Payroll_Divisor. For a worksheet, keep the divisor in its own input cell rather than embedding 30 or 26 in the formula. Payroll may use calendar days, a fixed 30-day or 26-day basis, working days, or another method defined by policy or contract; none is universally correct.
Rank #2
- Used Book in Good Condition
Use eligible paid days, not automatically days present. A new joiner, leaver, or employee with unpaid leave may have a different eligible-day count. Whether unpaid leave reduces basic pay, allowances, or both depends on the employer’s policy, so calculate affected components separately when needed.
Keep earnings, deductions, and employer costs separate
For a component-based monthly sheet, gross earnings can be totaled with a formula such as =SUM(Basic:Other_Earnings). Depending on the salary structure, earnings may include:
- Basic salary and dearness allowance, where applicable
- HRA, transport or conveyance allowance, and other allowances
- Overtime, commission, and bonus in the period when earned or paid
Employee deductions may include tax withholding, employee retirement contributions, insurance, local payroll taxes, loan or advance recovery, and other authorized deductions. Keep employer retirement contributions, insurance contributions, gratuity provisions, and employer-paid benefits in a separate employer-cost section. They may be part of CTC, but they are not automatically deductions from employee gross pay.
Do not spread an annual bonus across every monthly payslip unless the purpose is budgeting. If overtime is applicable, keep it separate from basic salary; for example, calculate it as =Overtime_Hours*Overtime_Rate.
Account for pay frequency and tax limits
=Annual_Salary/12 is a simple monthly conversion, not a promise that every payroll period will equal one-twelfth of annual compensation. Weekly, biweekly, semimonthly, daily, and hourly pay require the applicable schedule or rate. For a U.S. salary, divide by the number of pay periods specified by the employer’s payroll schedule.
Do not use one generic tax percentage as a universal payroll formula. U.S. federal income-tax withholding depends on pay-period earnings, payroll period, and information supplied on Form W-4; the IRS publishes methods and tables in Publication 15-T and related guidance in Publication 505. The IRS’s 2026 employer tax guide, Publication 15, also illustrates that payroll guidance is period-specific. India’s tax treatment depends on the applicable regime, financial year, components, exemptions, deductions, and current law; consult the Income Tax Department’s salary guidance rather than hard-coding rates without a dated, jurisdiction-specific basis. Withholding is not necessarily the same as final annual tax liability.
Make the workbook safer to reuse
Wait for required inputs
To leave annual basic blank until both CTC and the percentage are supplied, use =IF(OR(B2="",B3=""),"",B2*B3).
Catch a missing divisor
To show a message instead of dividing by zero, use =IF(B7=0,"Enter divisor",B5*B6/B7). =IFERROR(B5*B6/B7,0) is another option, but replacing all errors with zero can conceal a bad input.
Round at the right point
Use =ROUND(B5*B6/B7,2) to round the result to two decimal places, or =ROUND(B5*B6/B7,0) to round to a whole currency unit. Microsoft’s function guidance explains ROUND. Retain full precision in intermediate calculations unless payroll policy specifies otherwise, then round at the required stage; rounding components early can make displayed totals differ from the payroll result.
Keep policy inputs fixed when copying formulas
If the basic percentage is in B3 and employee CTC is in A2, use =A2*$B$3 when copying the formula down. The dollar signs keep the percentage reference fixed. In an Excel Table, structured references such as =[@[Annual CTC]]*[@[Basic %]] can make formulas easier to maintain as rows are added.
Add checks for suspicious entries
- Flag a negative deduction with
=IF(B12<0,"Invalid deduction",B12). - Flag gross pay below basic earned with
=IF(B11<B8,"Check: gross below basic","OK"). - Label every amount as monthly, annual, or another pay period to prevent mixing incompatible figures.
- Inspect the range Excel selects when using AutoSum, especially if the figures are not contiguous.
Troubleshoot common formula errors
#VALUE!
A salary or percentage may be stored as text, or the formula may refer to a text label. Check the cell’s value in the formula bar, remove typed currency symbols if present, enter a numeric value, and apply currency formatting. Use VALUE() only when the text follows a consistent numeric format.
#DIV/0! or a blank result
The divisor or pay-period count is zero or missing. Enter the required value in the designated input cell and use an explicit check such as =IF(B7=0,"Enter divisor",B5*B6/B7).
Free tools Windows power users keep installed
One-click scans. No signup required.
Best Value
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
An implausible total
Check for annual/monthly mismatches, 50 entered instead of 50%, allowances counted twice, employer contributions placed among employee deductions, bonus included in every month, or a percentage applied to the wrong base.
A copied formula changes unexpectedly
Use an absolute reference for a policy input, such as $B$3, rather than a relative reference such as B3.
A circular reference appears
This can happen when one component is calculated from a total that already includes that component—for example, calculating basic from CTC while employer contributions in CTC are themselves calculated from basic. Calculate the base component first and keep policy assumptions in separate input cells. If the relationship is genuinely circular, use a documented iterative model or solve the relationship algebraically rather than hiding the circularity.
When a spreadsheet is not enough
A manual sheet is useful for learning, budgeting, and straightforward estimates. It is not automatically a compliant payroll system. If you need current statutory calculations, tax tables, multiple jurisdictions, wage ceilings, benefits, arrears, overtime rules, audit trails, payslips, or filings, use a maintained payroll system or qualified payroll guidance. Any workbook used to pay employees needs documented assumptions, protected inputs, and review when policy or law changes.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
