안녕하세요. 행아아빠 입니다.
오늘 설명드릴 엑셀 내용으로는 2021년 급여 계산 자동 양식 만들기!! 입니다.
행아아빠 YouTuBe - https://www.youtube.com/channel/UCQlocK7yFlXLZFt-VTxVaAg
행아아빠 네이버Tv - https://tv.naver.com/hangadaddy
출,퇴근 시간을 기준으로 급여를 계산하는 현장직, 아르바이트 등의 시급제의 경우
"(근로시간 * 시급) + 주휴수당 - 세금(4대보험 및 소득세) = 월 급여"로 계산 할 수 있을 것 같습니다.
따라서 엑셀로 급여계산 양식을 만들기 전에
주휴수당과 4대보험 요율 그리고 근로소득세에 대해 간략히 알아보도록 하겠습니다.
■ 주휴수당
▼ 네이버 지식 백과사전에서 주휴수당을 검색하면 아래와 같은 내용을 확인 할 수 있습니다.
| 1주 동안 규정된 근무일수를 다 채운 근로자에게 유급 주휴일을 주는 것이다. 즉, 주휴일에는 근로 제공을 하지 않아도 되며, 1일분의 임금을 추가로 지급받을 수 있다. 월급 근로자의 경우 월급에 주휴수당이 포함되어 있지만, 시간제 근로자 등의 경우 "1주일 15시간 이상" 근무 여부에 따라 주휴수당 지급 여부가 결정된다. 주휴수당은 일주일에 15시간 이상 일하는 근로자에게 일주일에 하루씩 유급휴일을 주는 제도를 말한다. 근로기준법 제55조에 따르면 사용자는 일주일동안 소정의 근로일수를 개근한 노동자에게 1주일에 평균 1회 이상의 유급휴일을 주어야 하며, 이를 주휴일이라 한다. 주휴수당은 이 주휴일에 하루치 임금을 별도 산정하여 지급해야 하는 수당을 말한다. 주휴일은 상시근로자 또는 단기간 근로자에 관계없이 일주일에 15시간 이상 근무한 모든 근로자가 적용 대상이 된다. 월급 근로자의 경우 월급에 주휴수당이 포함되어 있지만 시간제 근로자 등의 경우 "1주일 15시간 이상" 근무 여부에 따라 주휴수당 지급 여부가 결정된다. |
이를 간단하게 정리해 보면
1. 1주동안 회사와 계약한 소정근로일 (규정된 근무일수)을 채우고,
2. 그 근로시간이 15시간 이상일때 주휴수당을 지급해야 한다는 것으로 볼 수 있겠습니다.
예를들면
A는 회사와 1주일에 5일 출근하고 3시간씩 근무하기로 계약 했다면
5일을 결근없이 출근하고 3시간씩 모두 근무를 해야만
5일 * 3시간 = 15시간이 되기 때문에 주휴수당을 받을 수 있습니다.
만약,
회사와 3일 출근하고 3시간씩 근무하기로 계약을 했다면
3일을 결근없이 출근을 한다고 해도 3일*3시간=9시간이기 때문에
주휴수당을 받을 수 없게 되는 것 입니다.
중요한 것은 회사와 1주일에 몇일을 출근 하기로 했는지와
1주일에 근로시간이 15시간을 넘는지가 중요 하다고 할 수 있습니다.
또한
연차사용과 회사사정으로 인하여 근무하지 못했다면 주휴수당은 지급되어야 하고,
개인사정으로 근무하지 못한 경우라면 주휴수당은 받을 수 없습니다.
그리고
1주일간 계약을 하고 월요일부터 금요일까지 근무 후 퇴사를 하게 되면
주휴수당은 발생하지 않습니다.
▶ 1주일은 7일을 의미 하기 때문에
비록 1주일 계약 근로일수를 채웠다 하더라도 7일의 계속적 근무가 이루어지지 않았기 때문에
퇴사일에 해당하는 주에는 주휴수당이 발생되지 않는 것으로 볼 수 있습니다.
■ 5인 미만 사업장 주휴수당 지급 의무
주휴수당은 5인 미만 사업장에도 적용 됩니다.
따라서 주휴수당 미지급은 임금체불로 이어질 수 있으며 1주일 이상 근무가 지속되었는데도
주휴수당을 받지 못한다면 고용주는 임금체불로 인한 2년 이하의 징역, 1천만원 이하의 벌금을 받게 됩니다.
■ 주휴수당 계산 방법
주휴수당을 계산하는 방법은 다음의 2가지 경우로 생각해 볼 수 있습니다.
1. 1주 근로시간이 40시간 미만일 경우
(1주 총근로시간 / 40시간) * 8시간 * 시급
2. 1주 근로시간이 40시간 이상일 경우
8시간 * 시급
■ 근로소득세
근로소득세는 간단하게
"근로소득에 대해 부과되는 조세"라고 할 수 있습니다.
말 그대로 일을 하고 얻는 대가라고 할 수 있을 것 같습니다.
가장 대표적인 것이 급여일 것 입니다.
근로소득세는
근로소득간이세액표에 따라서 총 급여액의 구간과 부양가족 수에 따라서 세액을 산출 할 수 있습니다.
근로소득세는
2021년 02월 고액 연봉자 구간 추가와 최고세액 구간 추가, 자녀세액 공제 대상이 변경되었고,
2021년 02월 17일 이후 원천징수 분 부터 적용되는 것으로 국세청 홈텍스에서 확인해 보실 수 있습니다.
▼ 국세청 홈텍스 내용의 의하면 아래와 같이 확인해 볼 수 있습니다.
| <개정내용> ○ 소득세 과세표준 10억원 초과 구간 신설. 최고세율 상향(42% → 45%) 및 자녀세액공제 기준 조정. (20세 이하 자녀수 → 7세 이상 20세 이하 자녀수)에 따라 근로소득 간이세액표 개정. ※ 원천징수 의무자가 매월분의 근로소득을 지급하는 때에는 『소득세법』 제 134조 및 같은법 시행령 제 194조에 따라 근로소득 간이세액표(시행령 별표2)에 의하여 원천징수 하도록 규정하고 있습니다. ※ 근로소득 간이세액표는 연말정산시 추가 납부 등에 따른 근로자의 부담을 분산하기 위해 월 급여수준과 공제대상 부양가족 수 별로 매월 원천징수 해야 하는 세액을 정한 표 입니다. 근로자는 원천징수세액을 근로소득간이세액표에 따른 세액의 80%, 100%, 120% 중에서 선택할 수 있으며 (원천징수의무자에게 '소득세 원천징수세액 조정신청서'를 제출하여야 함), 원천징수방식을 변경한 이후에는 재변경 전까지 계속 적용하여야 합니다. (단, 변경한 과세기간에는 재변경 불가) ※ 근로소득 간이세액표에 따른 세액보다 적게 원천징수, 납부하는 경우 과소납부한 세액에 대하여 원천징수납부불성실가산세가 부과되므로 유의하시기 바랍니다. |
근로소득간이세액표 조견표 다운로드 방법은
국세청 홈텍스 검색 → 조회/발급 → 기타조회 → 간이세액표 → 근로소득간이세액표
메뉴 순서로 들어가서 확인과 함께 한근파일, 엑셀파일, PDF파일로 다운로드 하실 수 있습니다.
■ 자녀 세액 공제 대상 변경
자녀 세액 공제 기준이 변경된 것은
"20세 이하 자녀수"에서 "7세 이상 20세 이하 자녀 수"로 변경 되었습니다.
2019년부터 아동수당 지급 대상을 7세 미만 모든 아동으로 확대하면서 자녀 세액 공제 대상이 변경 되었지만
근로소득간이세액표 조견표가 개정되지 않아,
원천징수에서는 누락되어 연말정산시 과도하게 세금이 부과되는 것으로 느껴 졌을 수 있습니다.
■ 고액 연봉자 구간 추가와 최고 세율 구간 추가
▼ 기존 고액 연봉자의 근로소득간이세액표 구간의 변경 내용을 살펴 보면 다음의 표과 같이 확인 할 수 있습니다.
단위 : 만원
|
2020.02월 이전 |
2020.02월 개정 |
2021.02월 최근 개정 |
계산 방법 |
|
1,000 ~ 1,400 |
1,000 ~ 1,400 |
1,000 ~ 1,400 |
1천만원인 경우의 해당 세액 + 1천만원을 초과하는 금액의 98% 금액의 35% |
|
1,400 ~ 2,800 |
1,400 ~ 2,800 |
1,400 ~ 2,800 |
1천만원인 경우의 해당 세액 + 1,372,000원 + 1,400만원을 초과하는 금액의 98% 금액의 38% |
|
2,800 ~ 4,500 |
2,800 ~ 3,000 |
2,800 ~ 3,000 |
1천만원인 경우의 해당 세액 + 6,585,600원 + 2,800만원을 초과하는 금액의 98% 금액의 40% |
|
4,500 초과 |
3,000 ~ 4,500 |
3,000 ~ 4,500 |
1천만원인 경우의 해당 세액 + 7,369,600원 + 3,000만원을 초과 하는 금액의 40% |
|
4,500 초과 |
4,500 ~ 8,700 |
1천만원인 경우의 해당 세액 + 13,369,600원 + 4,500만원을 초과하는 금액의 42% |
|
|
8,700 초과 |
1천만원인 경우의 해당 세액 + 31,009,600원 + 8,700만원을 초과하는 금액의 45% |
위의 표에서 보는 것처럼
고액 급여 구간과 구간별 고정 공제 금액, 그리고 구간별 공제 세율이 최대 45%로 변경 되었습니다.
■ 4대보험 요율
1. 국민연금
▶ 18세 이상 60세 미만 국내 거주 국민
단, 공무원, 군인, 사립학교 교직원 제외.
▶ 요율 : 9% (근로자 4.5%, 사업주 4.5%)
▶ 기준소득월액은 최저 32만원에서 최고 503만원까지의 범위로 결정하게 됩니다.
따라서 신고한 소득월액이 32만원보다 적으면 32만원을 기준 소득월액으로 하고,
503만원보다 많으면 503만원을 기준소득월액으로 합니다.(2020.07.01 기준)
2. 건강보험
▶ 요율 : 6.86% (근로자 3.43%, 사업주 3.43%)
3. 장기요양보험
▶ 요율 : 건강보험료 기준 11.52% (근로자 11.52%의 50%, 사업주 11.52%의 50%)
4. 고용보험
▶ 대상 : 1인 이상의 근로자를 고용하는 모든 사업 또는 사업장.
▼ 요율
| ① 실업급여 | 근로자 0.8%, 사업주 0.8% |
| ② 150인 미만 기업 | 사업주 0.25% |
| ③ 150인 이상 우선지원 대상기업 | 사업주 0.45% |
| ④ 150인 이상 ~ 1,000인 미만 기업 | 사업주 0.65% |
| ⑤ 1,000인 이상 기업, 국가 지방자치단체 | 사업주 0.85% |
| ※ 우선지원 대상기업 : 제조업 500인 이하, 광업 300인 이하, 건설업 300인 이하 외 | |
5. 산재보험
▶ 대상 : 근로자를 사용하는 모든 사업장, 당연적용 사업장에서 근무하는 모든 근로자 적용.
따라서 근로자는 해당 사항이 없습니다.
이렇게 주휴수당, 근로소득세, 4대보험 요율에 대해서 확인해 보았습니다.
■ 최저임금 변경
2020년 최저시급 8,590원에서
2021년 최저시급은 8,720원으로 상승 되었습니다.
따라서 이를 월단위로 환산해 보면 209시간 * 8,720원 = 1,822,480원으로
주휴수당 포함한 월 최저 임금은 1,822,480원이 되겠습니다.
참고로 월 209시간이라는 것은
주 40시간의 경우 주휴수당을 포함하여 209시간이라고 하는데 1년은 12개월의 경우
매월 30일, 31일의 일수 또는 4주, 5주 등의 주수가 다르기 때문에 한달을 365일/12개월/7일=4.345주로 보고
((8시간*5일)+주휴수당 8시간)*한달 4.345주 = 208.56시간이 나오게 되며,
이를 반올림 하여 209시간으로 보는 것 입니다.
위에서 주휴수당과 근로소득세, 4대보험요율, 최저임금까지 각각의 계산방법과
수식을 작성하기 위한 조건들을 확인하기 위해서 간략하게 확인해 보았습니다.
이제 시급제에 해당하면서 출,퇴근 시간의 근로시간으로 급여를 계산할 수 있는 양식과
자동으로 계산 되도록 수식을 작성해 보도록 하겠습니다.
■ 급여 자동 계산을 위한 양식 만들기
▼ 아래와 같이 근로시간과 주휴수당, 4대보험 및 소득세 등을 자동으로 계산 할 수 있는 양식을 임의로 작성했습니다.
시트는 총 3개 시트로 이루어져 있습니다.
1. 기본양식 시트
▲ 기본양식 시트에서 빨간 네모박스는 지접 입력 해 주셔야 할 부분이고,
나머지 부분은 모두 수식으로 입력되는 부분으로 작성된 수식에 따라서 자동으로 계산 되는 부분 입니다.
2. 근로소득간이세액표 시트
▲ 근로소득간이세액표 시트는 해당시트를 참조하여 소득세를 자동으로 계산하기 위해서
국세청 홈텍스에서 다운로드 받은 근로소득간이세액표를 붙여 넣은 시트 입니다.
■ 다운로드 경로
국세청 홈텍스 검색 → 조회/발급 → 기타조회 → 간이세액표 → 근로소득간이세액표
3. 참조 시트
▲ 참조시트는 총 3가지 카테고리로 작성되어 있습니다.
■ 2021년 공휴일 표
날짜를 주말 이외의 공휴일 지정을 위한 기본적으로 빨간날을 의미하는 날짜를 입력해 주었고,
추가적으로 사용자에 맞게끔 회사창립일 등의 빨간날로 적용되는 날짜가 있다면 작성해 주시면 되겠습니다.
■ 4대보험요율
4대보험 요율은 변동사항이 있을때 마다 수식 자체를 변경하는 것이 번거롭기 때문에
변동사항이 있을때 수식을 다시 작성하다 실패하여 문의 주시는 분들이 많아서
참조시트에서 각 보험명칭별로 변동사항이 있을때 변동사항 내용을 이곳에서 변경해 주시면
수식을 변경할 필요 없이 변경 작성한 내용으로 자동계산에 적용 될 수 있도록 작성해 보았습니다.
■ ※국민연금
국민연금은 최저액과 최고액의 범위, 그리고 나이의 범위가 정해져 있는데
이것 역시 변동사항이 있을때 수식을 변경하는 것 보다는 이곳 참조시트에서 변동사항이 있는 부분만 수정해 주어
자동으로 계산 될 수 있도록 작성해 주시면 되겠습니다.
■ 기본양식 시트에 자동으로 계산 될 부분 수식 작성하기
시트는 총 3가지로 이루어져 있지만 기본양식 시트에 자동으로 계산 될 수 있도록 참조하는 시트들로
근로소득간이세액표 시트와 참조 시트로 나누어서 기본 사항들을 입력해 둔 것으로 보시면 될 것 같습니다.
자!! 이제 기본양식에 수식을 하나씩 작성해 보도록 하겠습니다.
날짜를 자동으로 입력하는 수식 작성 방법.
▲ 우선은 날짜를 자동으로 입력 할 수 있도록 수식을 작성해 주세요.
날짜는 그림에서 보는 것 처럼
이름 옆에 작성된 연도와 월을 참조하여 날짜가 자동으로 작성되도록 수식을 작성 할 것 입니다.
월의 시작일인 1일은 수식이 간단하지만 2일부터 해당월의 마지막 일자까지는 수식이 조금 다르기 때문에
잘 확인해 주시길 바라겠습니다.
월의 시작일인 1일의 수식은
DATE함수를 이용해서 다음과 같이 작성해 주시면 되겠습니다.
=IFERROR(DATE(E2,F2,1),"")
E2셀은 연도인 2021을 참조하고,
F2셀은 월을 가리키는 3을 참조하여 월의 시작일인 1일을 수식을 작성해서 표기할 수 있습니다.
IFERROR함수를 추가로 사용해서
연도와 월의 셀이 공란이거나 해당 수식에 오류가 있는 경우에는 공란으로 표시하기 위해서
추가적으로 작성해 주시면 양식 자체에 지저분한 수식오류를 표시하지 않도록 할 수 있겠습니다.
▲ 다음으로 월의 시작일 다음날인 2일부터는
IF, MONTH, DAY함수를 이용해서 다음과 같이 작성할 수 있겠습니다.
=IFERROR(IF(MONTH(D5+DAY(1))=MONTH(D5)+1,"",D5+DAY(1)),"")
▲ 2일에 작성한 수식을 나머지 셀에 복사해서 붙여 넣어 주시면 되겠습니다.
월이 31일을 넘어가는 달은 없기 때문에 D35셀까지 수식복사해 주시면 되겠습니다.
수식복사는 범위를 지정해서 동일하게 복사 할 수 있는 단축키로는 CTRL+D를 동시에 눌러서 복사해 주셔도 되고,
마우스로 복사하고자 하는 곳까지 드래그 하여 수식복사를 할 수 있겠습니다.
이제 날짜를 자동으로 입력시킬 수 있는 수식 작성은 완료 되었습니다.
여러분이 작성해 주실 것은 E2셀과 F2셀의 연도와 월만 직접 입력시켜 주시면 날짜는 자동으로 입력 되겠습니다.
요일과 주차, 그리고 출근일수 수식 작성 방법.
요일과 주차는 앞에서 작성한 날짜를 참조해서 자동으로 표기 될 수 있게 수식이 작성됩니다.
▲ 요일의 수식은 TEXT함수를 이용해서 다음과 같이 간단하게 작성 할 수 있습니다.
=TEXT(D5,"AAA")
주차의 수식은
1년중 해당날짜가 몇주차에 해당하는지 확인하는 WEEKNUM함수를 이용해서 다음과 같이 작성할 수 있습니다.
=IFERROR(WEEKNUM(D5)-WEEKNUM(D5-DAY(D5)+1)+1,"")
▲ 작성한 수식은 A35, B35셀까지 수식복사 해 주시면 요일과 해당날짜가 해당월의 몇주차에 해당하는지
확인 할 수 있는 수식을 간단하게 작성 할 수 있습니다.
다음으로 해당주차의 출근일수가 몇일인지 확인할 수 있는 수식을 작성합니다.
▲ 출근일수 수식은 IF함수와 COUNTIFS함수 그리고 AND함수를 이용해서 참조시트에 작성한 공휴일표와
기본양식에서 주차, 날짜, 출근 카테고리를 참조하여 다음과 같이 작성할 수 있습니다.
=IF(AND(COUNTIFS(참조!$C$2:$C$21,기본양식!D5)=1,COUNTIFS($E$5:$E$35,"<>",$B$5:$B$35,B5)<1),1,
COUNTIFS($E$5:$E$35,"<>",$B$5:$B$35,B5))
▲ 수식을 작성하셨다면 C35셀까지 수식을 복사 해 주시면 되겠습니다.
출근일수 수식을 작성하는 이유는 참조시트에서 작성한 공휴일에는 근무는 없지만
개인사유가 아닌 공휴일이기 때문에 주휴수당을 지급하는 일수를 계산하기 위해서 입니다.
근로시간
총근로시간, 기본근로시간, 연장근로시간, 심야(특근)근로시간 수식 작성 방법.
▲ 이번에는 근로시간이 계산 될 수 있는 수식을 작성해 보겠습니다.
근로시간은 총 4가지로 구분 할 수 있습니다.
1. 총근로시간 : 퇴근시간-출근시간-비근로시간
2. 기본근로시간 : 퇴근시간 - 출근시간 중 최대 8시간 (급여산정시 *1배로 계산하게 되는 근로시간)
3. 연장근로시간 : 기본근로시간 이외의 추가 근로시간 (급여산정시 *1.5배로 계산하는 근로시간)
4. 심야(특근)근로시간 : 기본근로시간과 연장근로시간 이외의 22시부터 익일 오전 06시 사이까지의 근로시간
(급여산정시 *2배로 계산하는 근로시간)
여기서 많이 오해 할 수 있는 부분으로 연장근로시간과 심야(특근)근로시간을 급여 산정 근로시간으로 계산 할때에는
두가지 방법이 있을 수 있습니다.
1. 연장근로시간 전체를 (22:00 ~ 익일06:00시 사이의 근로시간 포함) *1.5배 한 후에
심야(특근)근로시간을 *0.5배로 계산.
2. 연장근로시간 중 심야(특근)근로시간을 제외한 근로시간만을 *1.5배 계산하고,
심야(특근)근로시간을 *2배로 계산.
총근로시간 수식
총근로시간 수식은 퇴근시간 - 출근시간 - 비근로시간(점심,저녁,심야)로 계산 할 수 있습니다.
다만, 퇴근시간이 24:00를 넘어가는 경우에는 출근시간보다 퇴근시간이 작은 경우가 생기게 되는데
이때에는 퇴근시간-출근시간+1을 해 주어야 시간계산이 될 수 있습니다.
총근로시간의 수식은 다음과 같이 작성할 수 있습니다.
=IFERROR(
IF(OR($E5="",$F5=""),"", (IF($E5<$F5,($F5-$E5)*1440,IF($E5>$F5,(1+$F5-$E5)*1440)))/60)-SUM($G5,$H5,$I5),"")
기본근로시간 수식
기본근로시간 수식은 기본근로시간 자체가 8시간을 초과할 수 없기 때문에
총근로시간 수식에서 연장근로와 심야(특근)근로시간을 차감해 주어 작성 할 수 있습니다.
추가적으로 연차일 경우 비고란에 연차라고 작성할 경우에는 8시간으로 표기 해 주어야 함을 고려했을때
수식은 다음과 같이 작성할 수 있습니다.
=IFERROR(
IF(P5="연차",8,(IF(E5<F5,(F5-E5)*1440,IF(OR(E5="",F5=""),"",IF(E5>F5,(1+F5-E5)*1440)))/60)-G5-H5-L5-I5-M5),"")
연장근로시간 수식
연장근로시간 수식은
기본근로시간 8시간 이후의 근로시간에서 심야근로시간을 차감한 시간을 연장근로시간으로 볼 수 있기 때문에
기본근로시간이 8시간을 초과하는지 아닌지에 따라서 다음과 같이 작성할 수 있습니다.
=IFERROR(
IF(IF(OR(E5="",F5=""),"",
IF(AND(E5<F5,((F5-E5)*1440)/60-G5<=8),0,IF(AND(E5<F5,((F5-E5)*1440)/60>8),((F5-E5)*1440)/60-8-H5-G5,IF(E5>F5,((1+F5-E5)*1440)/60-8-G5-H5)))-M5-I5)<0,0,IF(OR(E5="",F5=""),"",
IF(AND(E5<F5,((F5-E5)*1440)/60-G5<=8),0,
IF(AND(E5<F5,((F5-E5)*1440)/60>8),((F5-E5)*1440)/60-8-H5-G5,IF(E5>F5,((1+F5-E5)*1440)/60-8-G5-H5)))-M5-I5)),"")
심야(특근)근로시간 수식
심야(특근)근로시간의 수식은 22시부터 익일 오전 06시까지에 해당하는 근로시간이므로
TIME함수와 MAX, MIN함수를 이용해서 다음과 같이 작성할 수 있습니다.
=IFERROR(
(IF(OR(E5="",F5=""),"",IF(F5<E5,MIN(TIME(6,0,0),F5)+1,MAX(TIME(22,0,0),F5))-MAX(E5,TIME(22,0,0))))*(1440/60)-I5,"")
▲ 근로시간에 대한 수식을 모두 작성하셨다면 J, K, L, M열의 35행까지 수식복사 해 주시면 되겠습니다.
실제 근로시간을 급여계산 시간으로 환산하기 위한 수식과 일 급여 수식 작성 방법.
▲ 이번에는 앞서 작성한 각각의 근로시간을
급여로 산정하기 위한 시간으로 환산해서 합계를 구하는 수식을 작성 하겠습니다.
실제 근로시간과 급여계산시간은 차이가 발생하는데
실제 근로시간이 만약 10시간일때 기본근로시간은 8시간, 연장근로시간은 2시간이라고 가정할때
급여를 산정하기 위한 근로시간으로 환산하게 된다면
(8시간 *1)+(2시간*1.5)=11시간이 되기 때문에 실제 근로시간과 급여계산시간에는 차이가 발생하게 됩니다.
그럼 이제 급여 계산을 위한 근로시간으로 환산하기 위한 수식을 작성할때 조건은 무엇인지 확인해 보면
1. 해당 주차의 총 근로시간이 40시간 미만이면서, 토요일 근무인 경우.
2. 해당 주차의 총 근로시간이 40시간 이상이면서, 토요일 근무인 경우.
3. 해당 주차의 총 근로시간이 40시간 미만이면서, 일요일 근무인 경우.
4. 해당 주차의 총 근로시간이 40시간 이상이면서, 일요일 근무인 경우.
5. 추가적으로 연차를 사용하는 경우.
일반적으로 ▲ 위 5가지 경우로 정리해서 수식을 작성하려고 합니다.
왜 해당 주차의 총 근로시간이 40시간을 기준으로 토요일, 일요일 근무인 경우로 나누었는지에 대해서는
근로시간 자체가 평일만 확정된 것이 아니고, 휴무일이 평일에 있는 업종의 경우에는 평일 휴무라면
주말 근무가 생기게 되기 때문에 주 5일 근무일 경우 8시간 * 5일 = 40시간이 되기 때문에
비록 주말 근무를 했더라도 평일에 휴무일 경우 주5일 근무가 성립되지 않기 때문에
해당 주차의 총 근로시간이 40시간 미만, 혹은 이상을 기준으로 정했습니다.
위의 경우들을 고려하여 수식을 작성하면 다음과 같이 작성할 수 있습니다.
=IF(AND(E5="",F5="",),"",
IF($E5="연차",8,
IFERROR(
IF(AND(SUMIFS($J$5:$J$35,$B$5:$B$35,$B5)<40,COUNTIFS(참조!$C$2:$C$21,기본양식!$D5)=1),SUM(($K5*1)+($L5*1.5)+($M5*2)),
IF(AND(SUMIFS($J$5:$J$35,$B$5:$B$35,$B5)>=40,COUNTIFS(참조!$C$2:$C$21,기본양식!$D5)=1),SUM(($K5*2)+($L5*2)+($M5*2)),
IF(AND(SUMIFS($J$5:$J$35,$B$5:$B$35,$B5)<40,$A5="토"),SUM(($K5*1)+($L5*1.5)+($M5*2)),
IF(AND(SUMIFS($J$5:$J$35,$B$5:$B$35,$B5)>=40,$A5="토"),SUM(($K5*1.5)+($L5*2)+($M5*2)),
IF(AND(SUMIFS($J$5:$J$35,$B$5:$B$35,$B5)<40,$A5="일"),SUM(($K5*1)+($L5*1.5)+($M5*2)),
IF(AND(SUMIFS($J$5:$J$35,$B$5:$B$35,$B5)>=40,$A5="일"),SUM(($K5*2)+($L5*2)+($M5*2)),SUM(($K5*1)+($L5*1.5)+($M5*2),))))))),"")))
▲ 수식이 작성되었다면 N35셀까지 수식복사 해 주시면 되겠습니다.
▲ 이번에 작성할 수식은 일급여 계산 수식입니다.
일 급여는 정말 단순하게 앞에서 작성한 급여계산시간*시급으로 수식은 다음과 같이 작성할 수 있습니다.
=IFERROR(SUM(N5*$L$39),"")
▲ 수식이 작성되었다면 O35셀까지 수식복사 해 주시면 되겠습니다.
근로시간, 급여계산시간, 일급여, 출근일수 각각의 합계와
총급여액, 그리고 차인지급액 수식 작성 방법.
윗부분은 모두 작성했으니 아랫부분에 자동으로 계산되어야 할 부분을 수식으로 하나씩 완성해 보도록 하겠습니다.
▲ 우선 36행의 합계행은 모두 위에서 작성한 결과값들의 합계라고 보면 되겠습니다.
따라서 SUM함수를 이용해서 간한하게 합계를 내주시면 되겠습니다.
다만, 출근, 퇴근시간의 E,F열은 셀병합해 놓은 셀로
시간이 작성된 것을 기준으로 몇일을 출근했는지 합계를 내는 수식으로 COUNTIFS함수로 카운팅하여
출근일수 합계를 계산 할 수 있습니다. 그 수식은 다음과 같이 작성할 수 있습니다.
=COUNTIFS(E5:E35,"<>")&"일"
총급여액 수식 작성 방법.
▲ 급여의 총액은 일급여합계 + 주휴수당으로 합계를 낼 수 있습니다.
따라서 각각의 합계를 SUM함수를 이용해서 단순한 합계를 내 주시면 되는데 그 수식은 다음과 같습니다.
=SUM(O36+E47)
차인지급액 수식 작성 방법.
다음으로 차인지급액 수식은
총급여액인 급여+주휴수당 금액에서 4대보험과 소득세 합계를 차감해 주시면 되겠습니다.
그 수식은 다음과 같이 작성 할 수 있습니다.
=N37-SUM(L45+L48)
주휴수당 수식 작성 방법.
▲ 주휴수당의 부양가족수와 주차는 직접 입력해 주시면 되겠습니다.
부양가족수는 사실 주휴수당을 계산하기 위한 부분은 아니고,
소득세를 계산하기 위해 작성해 주는 것으로 양식을 만들때
마땅한 자리가 없어서 이곳에 작성칸을 만들게 되었습니다.
▲ 이제 주휴수당이 계산 될 수 있는 수식을 작성해 보겠습니다.
주휴수당을 계산하기 위해서는 먼저 근무인정일수가 계산 되어야 합니다.
근무인정일수는 제가 임의적으로 지칭한 단어로 크게 의미를 두지는 않으셔도 되겠습니다.
이것은 만약, 공휴일 또는 근로자 개인사유 이외의 사유로 휴무를 갖게 되는 경우로
참조시트에서 공휴일 표에 작성된 날짜가 포함되는 경우에는 근무인정일수를 합산해 주기 위해 작성한 것 입니다.
왜 이것을 합산해야 하는지는 주휴수당의 조건을 위에서 확인한 사항이니 다시 한번 확인해 주시길 바라겠습니다.
따라서 수식은
해당 주차에서 총 몇일을 출근했는지 그리고 공휴일을 합산한 근무일수는 몇일인지를 가져와야 합니다.
그래서 앞에서 출근일수라는 C열을 작성하게 되었습니다.
근무인정일수의 수식은 다음과 같이 작성할 수 있겠습니다.
{=MAX(IF($B$5:$B$35=D41,$C$5:$C$35))}
위 수식에서 주의할 점은 배열수식으로 작성되었기 때문에
수식을 작성한 후에 ENTER키만으로 수식을 종료하면 안되고CTRL+SHIFT+ENTER키를 동시에 눌러 종료 해 주셔야만 배열수식 기호인 { } 해당기호로 종료 할 수 있습니다.배열수식의 기호인 대괄호는 직접 입력해 주시면 수식이 인식하지 않기 때문에 주의해 주시길 바라겠습니다.
▲ 해당 수식을 작성하셨다면 46행까지 수식복사 해 주시면 되겠습니다.
▲ 다음으로는 주휴수당의 각 주차마다의 금액을 계산하는 수식을 작성해야 합니다.
주휴수당을 계산하는 조건은 앞에서도 설명 드렸으니 수식만 작성해 보면 다음과 같이 작성할 수 있습니다.
=IF(OR(SUMIFS($J$5:$J$35,$B$5:$B$35,D41)<15,B41<5),0,
IF(AND(B41>=5,SUMIFS($J$5:$J$35,$B$5:$B$35,D41)>=15,SUMIFS($J$5:$J$35,$B$5:$B$35,D41)<40),
((SUMIFS($J$5:$J$35,$B$5:$B$35,D41)/40)*8)*$L$39,
IF(AND(B41>=5,SUMIFS($J$5:$J$35,$B$5:$B$35,D41)>=40),
8*$L$39)))
▲ 수식을 작성하셨다면 수식을 복사 해 주시길 바라겠습니다.
▲ 다음으로는 각 주차마다의 근로시간의 합계를 계산하는 수식입니다.
각 주차마다의 근로시간의 합계는 앞서 작성한 각 주차마다의 주휴수당 금액을 산정하기 위해 작성했는데,
이미 금액부분의 수식을 작성할 때 근로시간을 참조하여 계산하기 위해서
총근로시간에서 각 주차마다의 근로시간의 합계가 계산 될 수 있도록 포함해서 수식을 작성했기 때문에
크게 의미 없을지도 모르겠지만 수식을 작성해 보면 다음과 같이 작성할 수 있겠습니다.
=IF(SUMIFS($J$5:$J$35,$B$5:$B$35,LEFT(D41,1))=0,0,SUMIFS($J$5:$J$35,$B$5:$B$35,LEFT(D41,1)))
▲ 수식을 작성하셨다면 수식복사해 주세요.
▲ 마지막으로 작성한 수식으로 계산된 값들을 SUM함수를 이용해서 각각의 합계를 내 주시면 되겠습니다.
4대보험과 소득세 수식 작성 방법.
▲ 마지막으로 급여에서 공제 되는 금액으로 4대보험과 소득세를 계산 할 수 있는 수식 입니다.
시급 부분은 직접 타이핑으로 입력해 주시면 되겠습니다.
국민연금, 건강보험, 장기요양보험, 고용보험, 그리고 소득세와 지방소득세 순으로 수식을 확인해 보도록 하겠습니다.
국민연금 수식
국민연금의 조건 역시 위에서 설명 드렸습니다만,
최저 18세 ~ 최고 60세
최저액 320,000원 ~ 최고액 5,030,000원까지 범위 안에서 계산 할 수 있습니다.
다만, 해당 양식에서는 나이는 별도로 고려하지 않았음을 참고해 주시길 바라겠습니다.
총 급여액의 범위에 대한 조건만을 고려했을 때 수식은 다음과 같이 작성할 수 있습니다.
=ROUNDDOWN(
IF($N$37=0,0,
IF($N$37<=참조!$E$12,참조!$E$12*(참조!$G$3/100),
IF($N$37>=참조!$F$12,참조!$F$12*(참조!$G$3/100),
IF(AND($N$37>참조!$E$12,$N$37<참조!$F$12),$N$37*(참조!$G$3/100))))),-1)
해당 시트에서 참조하는 셀은 총급여액만을 참조하고, 나머지 계산은 참조시트에서 참고하여 계산하게 됩니다.
따라서, 국민연금의 요율 또는 총급여액의 최저액, 최고액이 변경된다면
참조시트에서 각각 변경 내용을 변경 작성해 주시면 해당수식은 변경하지 않고 변경된 내용으로 계산 되는 수식 입니다.
건강보험 수식
건강보험 역시 요율이 변경된다면 참조시트에서 변경된 요율만 참조시트에서 변경해 주시면
수식을 변경하지 않고 변경된 내용을 계산에 반영 시킬 수 있습니다.
건강보험의 수식은 다음과 같이 간단하게 작성할 수 있습니다.
=ROUNDDOWN(N37*(참조!$G$4/100),-1)
장기요양보험 수식
장기요양보험도 요율이 변경된다면 수식은 변경하지 않고, 참조시트에서 변경된 요율만 변경하여 작성해 주시면 됩니다.
장기요양보험의 수식은 다음과 같이 간단하게 작성 할 수 있습니다.
=ROUNDDOWN($L$41*(참조!G5/100),-1)
고용보험 수식
고용보험도 마찬가지로 요율이 변경된다면 참조시트에서 변경된 요율만 변경해서 작성해 주시길 바라겠습니다.
고용보험의 수식은 다음과 같이 간단하게 작성할 수 있겠습니다.
=ROUNDDOWN($N$37*(참조!G6/100),-1)
근로소득세 수식 작성 방법.
근로소득세는 국세청 홈텍스에서 다운로드 받은 근로소득간이세액표를 참조해서 수식을 작성해야 합니다.
따라서 아래 경로를 따라 근로소득간이세액표를 엑셀파일 형식으로 다운받아 합니다.
▼ 근로소득간이세액표 다운로드 경로
국세청 홈텍스 검색 → 조회/발급 → 기타조회 → 간이세액표 →근로소득간이세액표
근로소득세 수식은
고액급여자의 경우에만 별도로 계산하는 수식을 작성하고,
1,000만원 이하의 월 급여자는 표에서 참조하여 소득세가 계산 될 수 있도록 다음과 같이 작성 할 수 있습니다.
=IFERROR(
IF(N37<=10000000,VLOOKUP(N37/1000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(E39="",1,E39),근로소득간이세액표!$A$5:$M$5,0),1),
ROUND(
IF(AND(N37>1000000,N37<=14000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(E39="",1,E39),근로소득간이세액표!$A$5:$M$5,0),1)+
((N37-10000000)*0.98)*0.35,
IF(AND(N37>14000000,N37<=28000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(E39="",1,E39),근로소득간이세액표!$A$5:$M$5,0),1)+
(((N37-14000000)*0.98)*0.38)+1372000,
IF(AND(N37>28000000,N37<=30000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(E39="",1,E39),근로소득간이세액표!$A$5:$M$5,0),1)+
(((N37-28000000)*0.98)*0.4)+6585600,
IF(AND(N37>30000000,N37<=45000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(E39="",1,E39),근로소득간이세액표!$A$5:$M$5,0),1)+
((N37-30000000)*0.4)+7369600,
IF(AND(N37>45000000,N37<=87000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(E39="",1,E39),근로소득간이세액표!$A$5:$M$5,0),1)+
((N37-45000000)*0.42)+13369600,
IF(N37>87000000,VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(E39="",1,E39),근로소득간이세액표!$A$5:$M$5,0),1)+((N37-87000000)*0.45)+31009600)))))),-1)),0)
지방소득세 수식 작성 방법.
지방소득세는 근로소득세를 참조해서 계산하기 때문에 다음과 같이 간단하게 수식을 작성 할 수 있습니다.
=IFERROR(ROUNDDOWN((L46*0.1),-1),0)
▲ 공제 합계는 간이소득세 합계와 4대보험 공제 합계를 단순히 SUM함수로 합계를 내 주시면 되겠습니다.
=SUM(L45,L48)
여기까지 2021년 월 급여 자동 계산을 위한 모든 수식을 작성 완료 했습니다.
이제 해당 양식에 출근, 퇴근시간과 비근로시간만 작성해 주셔서
해당 근로자의 월 급여 계산을 수월하게 할 수 있기를 바라겠습니다.
오늘 내용도 많은 분들께 도움이 될 수 있기를 바라며, 오늘 하루도 좋은 하루 되시길 바라겠습니다.
※ 참고로 저 역시 해당 양식의 수식을 완성하기 위해서 한번에 작성하지는 못합니다.
많이 고민하고 하나 하나 수식의 결과값과 경우의 수에 따라 계산 오류가 발생되는지
확인 후에 수식을 하나로 합치는 과정으로 수식을 작성하게 됩니다.
그러니 지금 이 글을 읽고 계신 모든 분들도 조금의 고민과 시간을 투자 한다면 모두 작성 하실 수 있는 수식이니
업무에 필요한 수식을 하나씩 작성하셔서 업무시간이 효율적으로 단축 될 수 있기를 바라겠습니다.
| 행아아빠 YouTuBe | www.youtube.com/channel/UCQlocK7yFlXLZFt-VTxVaAg |
| 행아아빠 네이버TV | tv.naver.com/hangadaddy |
| 수식의 오류, 수정, 요청사항은 댓글로 부탁드리겠습니다. 더불어 해당 내용이 도움이 되셨다면 행아아빠의 YouTuBe, 네이버TV 구독과 좋아요, 공유해 주시면 감사하겠습니다. |
|
▼ 2021년 급여 자동 계산 양식 파일 다운로드
'엑셀' 카테고리의 다른 글
| 엑셀 함수 수식 쉽게 작성하기 위한 조건과 방법!! (0) | 2021.04.22 |
|---|---|
| 주휴수당 계산법 - 엑셀 주휴수당 계산기 만들기 & 엑셀 함수 수식 작성 방법! (0) | 2021.04.19 |
| 엑셀 최대값, 최소값을 구하기 위한 조건이 있다면?? [ MAX함수 + IF함수, MIN함수 + IF함수] (0) | 2021.03.27 |
| 엑셀 원하는 표 / 테이블에만 행 또는 열 삽입 - 전체 행/열 삽입이 아닌 원하는 부분만 행과 열 삽입 방법. (0) | 2020.04.24 |
| 엑셀 판매일보 자동 계산 양식 만들기 - 쇼핑몰 온라인마켓 판매일보 (2) | 2020.04.23 |
댓글