Salary Structure Calculator Excel — Reverse-Engineer Base Pay, Allowances & Social Insurance from Annual Salary

“If the annual salary is ₩40 million, what’s the base pay and how much does the company actually spend?” This is the most common question during hiring, yet the math is never straightforward. This template takes one annual salary figure and automatically breaks it down into base pay, allowances, social insurance contributions, and take-home pay.

Key Features

💰 Annual Salary → Base Pay Breakdown

• Enter total annual salary → auto-decompose into base pay, meal allowance, transport allowance
• Distinguish items included in ordinary wages vs. excluded
• Auto-calculate ordinary hourly wage from monthly contractual hours
• Auto-verify minimum wage compliance

🏦 Social Insurance Auto-Calculation

• Apply rates for National Pension, Health Insurance (incl. long-term care), Employment Insurance, and Industrial Accident Insurance
• Separate employee and employer contributions
• Auto-total the employer’s true cost (salary + employer insurance burden)

📊 Compensation Comparison Table

• Compare up to 20 salaries side by side
• Design pay structures by rank or position
• Auto-total labor cost budget

📋 Monthly Pay Slip Preview

• Gross monthly pay, deduction items, and estimated take-home pay
• Income tax and local income tax based on simplified tax tables

How to Use

Step 1: Verify Insurance Rates

Check that the current year’s social insurance rates are correct in the ‘Settings’ sheet. Rates change annually, so update them at the start of each year.

Step 2: Set Compensation Mix

Configure the ratio of base pay, meal allowance, and transport allowance. Defaults are base pay 80%, meal allowance ₩100,000, and transport allowance ₩100,000.

Step 3: Enter Annual Salaries

Enter each employee’s annual salary in the ‘Calculator’ sheet. All fields compute automatically.

Tips

Use for Hiring Budget Planning

Seeing the employer’s total cost — salary plus employer-side insurance — helps set realistic hiring budgets. Typically, expect an extra 10–12% on top of the stated salary.

Salary Negotiation Simulation

Adjust the annual salary figure to preview how take-home pay changes — useful during salary reviews or offer negotiations.

Best Practices

Update Social Insurance Rates Every January Without Fail

Korean social insurance rates change annually — National Pension, Health Insurance, and Long-Term Care Insurance rates are announced between November and January each year. Set a recurring calendar reminder for the first business day of January to update the Settings sheet. Using outdated rates can lead to under-withholding, resulting in year-end corrections that frustrate employees and complicate payroll reconciliation.

Structure Allowances to Optimize Both Tax Efficiency and Legal Compliance

The meal allowance (up to ₩200,000/month) and transport allowance are tax-exempt, which benefits employees. However, be careful about setting non-taxable allowances too high relative to base pay — if base pay drops below a certain threshold, it can affect ordinary wage calculations for overtime, severance, and annual leave allowance payouts. A safe ratio is keeping base pay at 75-80% of total compensation.

Run Simulations Before Announcing Salary Adjustments

Before communicating raises or restructuring pay bands, use the comparison table to model the impact on both employee take-home pay and total employer cost. A 5% raise in gross salary does not translate to a 5% increase in take-home pay due to progressive tax brackets and insurance contribution thresholds. Showing employees the actual take-home impact builds trust during salary review conversations.

FAQ

Is the meal allowance tax exemption reflected?

Yes, up to ₩200,000 per month is treated as tax-exempt. You can adjust the exemption limit in the Settings sheet.

Is severance included?

This template assumes severance is separate. For all-inclusive annual salary schemes, subtract the severance portion (1/13) before entering the figure.

Can I calculate pay for mid-month hires?

This template uses full monthly pay. Pro-rated calculations for mid-month starters need to be handled separately.

How does ordinary wage affect overtime and severance calculations?

Ordinary wage is the legally defined base for calculating overtime pay (1.5x), night work premium, holiday premium, and severance. It includes base pay and any fixed, regular allowances — but excludes variable bonuses and irregular payments. This template clearly separates ordinary wage components from non-ordinary items so you can verify your overtime and severance liabilities are calculated correctly.

What happens when an employee’s salary crosses the National Pension income cap?

National Pension contributions are capped at a monthly income of ₩5.9 million (as of 2026). If an employee’s monthly pay exceeds this threshold, the pension contribution does not increase further. The calculator automatically applies this cap, so you will notice the pension contribution percentage effectively decreases for higher earners. This is important for budgeting total employer costs across different salary bands.