Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate net pay in Excel and Google Sheets
Topic:Finance basicsExcel & Google Sheets
=A2-SUM(B2:E2)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Lay out one column per pay component: A = Gross pay, B = Income tax, C = Provident fund / social security, D = Insurance, E = Other deductions.
- 2In the net-pay cell (F2) enter =A2-SUM(B2:E2) — gross minus the total of all deduction columns.
- 3For a deduction that is a percentage of gross, compute it in its own column, e.g. income tax at 20% in B2 as =A2*0.2, then let SUM pick it up.
- 4For tiered or slab tax, replace the flat percentage with a lookup or nested IF (see the tips) so the rate follows the salary band automatically.
- 5Format F2 as Currency and copy the formula down for every employee row.
Tips
- Worked example: gross 60000, tax 9000, PF 1800, insurance 500 gives =60000-SUM(9000,1800,500) = 48700 net.
- Keep tax and PF rates in a small reference table and point the formulas at it (=A2*$H$2) so a rate change updates every row at once.
- Slab tax in one cell: =IF(A2<=250000,0,IF(A2<=500000,(A2-250000)*0.05,(A2-500000)*0.2+12500)) — swap the bands and rates for your jurisdiction.
- Wrap the result in ROUND to avoid stray paise or cents: =ROUND(A2-SUM(B2:E2),2).
- Guard against a negative net (deductions exceed gross) with =MAX(0,A2-SUM(B2:E2)).
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What is the difference between gross pay and net pay?
Gross pay is the full amount before any deductions. Net pay, or take-home pay, is what is left after tax, provident fund or social security, insurance and other deductions are subtracted.
How do I calculate a deduction as a percentage of gross?
Put the percentage deduction in its own cell — for 20% income tax on gross in A2, use =A2*0.2 — then include that cell in the SUM of deductions.
How do I handle slab or bracket-based tax?
Replace the flat percentage with a nested IF, or an IFS or lookup, that returns the correct rate for each salary band, then subtract it like any other deduction.
How do I stop net pay going negative?
Wrap the subtraction in MAX(0, ...): =MAX(0, A2-SUM(B2:E2)) clamps the result at zero if total deductions ever exceed gross.
More on Finance basics
See all →How-to
How to calculate compound interest
Calculate a compounded closing balance from principal, annual nominal rate, compounding frequency and years, then separate the interest earned.
How-toHow to calculate the effective annual interest rate
Convert a positive nominal annual rate and a whole-number compounding frequency into an effective annual rate with EFFECT.
How-toHow to calculate the break-even point in units
Divide fixed costs by unit contribution margin and round up to whole units, with an explicit message when the margin is nonpositive.
How-toHow to calculate tiered commission
Apply different commission rates by sales tier using IFS. Works in Excel 2019/365 and Google Sheets.
How-toHow to calculate gross margin
Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.
How-toHow to calculate cost per unit
Divide total cost by total units to find cost per unit using a simple formula in Excel or Google Sheets.