엑셀(Excel) 급여대장 서식과 공제액 계산 수식을 작성해 보자.

안녕하세요. 일상에서 얻는 정보를 모두 기억할 수 없기 때문에 일상에서 일어나는 에피소드들의 정보들을 기록하여 기억하고자 하는 시점에 기록된 정보를 활용할 수 있도록 글을 쓰고자 하는 이작가입니다.

저 역시 지나간 모든 과거의 경험들을 전부 기억할 수 없기 때문에 경험을 통해 얻은 지식과 정보를 기록함으로써 필요한 시점에 과거의 경험의 정보를 활용해 보기 위해 이 글을 작성합니다.

기록된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다.

급여대장 서식과 4대보험, 소득세, 주민세 공제액 차감액 계산 수식 작성 방법 썸네일 이미지.

엑셀(Excel) 작업으로 급여 계산을 하는경우가 종종 있을 수 있는데, 앞서 출근시간과 퇴근시간을 기준으로 근로 시간과 주휴 수당을 계산하는등의 급여 계산과 관련한 몇가지 수식 작성 방법에 대해 작성한 글들을 오늘 내용과 함께 참고 해 보시면 좋을것 같습니다.

엑셀(Excel) 근로 소득세를 자동 계산해주는 엑셀 수식을 작성해 보자.

엑셀(Excel) 날짜를 입력하면 요일과 몇주차인지 한번에 알 수 있는 수식을 작성해 보자.

엑셀(Excel) 입력한 날짜의 해당 월 시작일부터 종료일까지 자동으로 작성되는 수식을 작성해 보자.

엑셀(Excel) 시간 계산 출퇴근시간, 근로시간, 근무시간, 급여시간 계산

엑셀(Excel) 요일별, 근로 시간별로 적용되는 급여 계산 시간으로 환산 계산 수식을 작성해 보자.

엑셀(Excel) 엑셀 급여 계산 주휴수당 계산 수식을 작성해 보자.

엑셀(Excel) 4대보험 계산 국민연금, 건강보험, 고용보험, 산재보험 계산 수식을 작성해 보자.

엑셀(Excel) 비 근로 시간 계산 점심, 저녁 심야 시간 규칙적으로 발생하는 시간 계산 수식 작성을 해보자.

엑셀(Excel) 연차 남은 개수 사용 개수 발생 개수 수식을 작성해 보자.

엑셀(Excel) 주민 등록 번호로 나이 계산 수식 작성을 해보자.

급여 대장은 출근시간과 퇴근시간으로 근로 시간으로 급여를 계산하는 방식이 아닌 주로 월급제에 해당하는 근로자의 급여를 계산하기 위한 급여 테이블 또는 급여 대장 서식으로 별도의 근로 시간을 체크 하여 급여를 계산하는 방식보다는 조금 간편하게 서식을 만들 수 있고, 공제액 또한 유동적인 급여 총액이 아니고 대부분 고정적인 급여이기 때문에 출근시간과 퇴근시간을 기준으로 급여를 계산하는 현장직, 일용직, 아르바이트, 생산직등의 급여계산보다는 수월하다고 할 수 있을것 같습니다.

그럼 급여 대장 서식과 급여 공제액 또는 차감액을 계산 하는 수식 작성 함께 확인해 보기로 하겠습니다.

1. 급여대장 샘플 예제 서식 작성.

급여대장 샘플 예제 서식은 A열부터 나이, NO, 이름, 부서, 직책, 부양가족수(소득세 계산시 필요), 기본급, 수당1, 수당2, 상여금, 총급여액(세전급여), 운전(차량지원비, 주유비등 비과세 항목), 식대, 자녀수당(비과세 수당등), 비과세공제액합계, 국민연금, 건강보험, 장기 요양보험, 고용보험, 소득세, 주민세, 과세공제액합계, 실지급급여(총급여액-과세공제액)으로 W열까지 각 항목들을 작성 해 보았습니다.

음영이 있는셀은 직접 입력해 줘야 하는 데이터값으로 보통은 한번 입력하면 잘 수정되지 않는 값이기도 합니다.

음영이 없는셀은 수식을 작성하여 직접 입력한 데이터값들을 참조하여 급여를 계산하기 위한 계산 결과값을 표기하도록 수식을 작성할것입니다.

수식을 입력한 각 셀들이 참조 하는 영역은 다음과 같이 정리해 볼 수 있습니다.

① K열 / 총급여액(세전급여)
→ 기본급 + 수당1 + 수당2 + 상여금

② O열 / 비과세 공제액 합계
→ 운전 + 식대 + 자녀수당

③ P열 / 국민연금
→ 나이가 60세 이상이면 0,
총급여액이 5,530,000원 이상이면 5,530,000원 * 4.5%,
총급여액이 350,000원 이하이면 350,000원 * 4.5%
위 3가지 조건 모두 해당하지 않는다면 (총급여액 - 비과세 공제액 합계) * 4.5%
※ 국민연금 요율은 2022년 11월 현재 9%를 적용하고
그 중 근로자 적용 요율 4.5%를 정수 또는 소수로 변환하면 0.045로 계산.

④ Q열 / 건강보험
→ (총급여액 - 비과세 공제액 합계 ) * 3.495%
※ 건강보험 요율은 2022년 11월 현재 6.99%를 적용하고
그 중 근로자 적용 요율 3.495%를 정수 또는 소수로 변환하면 0.03459로 계산.

⑤ R열 / 장기 요양 보험
→ 건강보험 * 6.135%
※ 장기요양보험 요율은 2022년 11월 현재 12.27%를 적용하고
그 중 근로자 적용 요율 6.135%를 정수 또는 소수로 변환 하면 0.06135로 계산.

⑥ S열 / 고용보험
→ (총급여액 - 비과세 공제액 합계) * 0.9%
※ 고용보험 요율은 2022년 11월 현재 실업급여 요율을 적용하되 인원에 따른 요율을 참고하고
0.9%를 정수 또는 소수로 변환 하면 0.009로 계산.

⑦ T열 / 소득세
→ (총급여액 - 비과세 공제액 합계) 금액과 부양가족수를 근로소득간이세액표와 비교하여 해당하는 금액으로 계산.
※ 샘플 예제 서식 "근로소득간이세액표"시트 참조.

⑧ U열 / 주민세
→ 소득세 * 10%
※ 10%를 정수 또는 소수로 변환 하면 0.1로 계산.

⑨ V열 / 과세 공제액 합계
→ 국민연금 + 건강보험 + 장기요양보험 + 고용보험 + 소득세 + 주민세

⑩ W열 / 실 지급 급여 (총급여액 - 과세 공제액)
총급여액 - 과세 공제액 합계

※ 국민연금, 건강보험, 장기요양보험, 고용보험, 소득세, 주민세는 원단위 절사.

2. 샘플 예제 서식 급여 계산 수식 작성.

샘플 예제 서식에서 수식을 작성하여 직접 입력한 데이터 값을 참조 하여 결과값을 계산 하기 위해 수식을 작성하는 각각의 3행 셀을 기준으로 수식을 작성하면 다음과 같이 작성 할 수 있습니다.

① K열 / 총급여액(세전급여)
→ =SUM(G3:J3)

② O열 / 비과세 공제액 합계
→ =SUM(L3:N3)

③ P열 / 국민연금
→ =ROUND(IF($A3>=60,0,
IF(SUM($K3,-$O3)>=5530000,5530000*0.045,
IF(SUM($K3,-$O3)<=350000,350000*0.045,
SUM($K3,-$O3)*0.045))),-1)

④ Q열 / 건강보험
→ =ROUND(SUM($K3,-$O3)*0.03495,-1)

⑤ R열 / 장기요양보험
→ =ROUND(SUM($Q3*0.06135),-1)

⑥ S열 / 고용보험
→ =ROUND(SUM($K3,-$O3)*0.009,-1)

⑦ T열 / 소득세
→ =IFERROR(IF(SUM($K3,-$O3)<770000,0,

IF(AND(SUM($K3,-$O3)>=770000,SUM($K3,-$O3)<=10000000),
VLOOKUP(SUM($K3,-$O3)/1000,근로소득간이세액표!$A$5:$M$652,MATCH(IF(OR($F3="",$F3=0),1,$F3),근로소득간이세액표!$A$5:$M$5,1),1),

IF(AND(SUM($K3,-$O3)>10000000,SUM($K3,-$O3)<=14000000),
(VLOOKUP(SUM($K3,-$O3)/1000,근로소득간이세액표!$A$5:$M$652,MATCH(IF(OR($F3="",$F3=0),1,$F3),근로소득간이세액표!$A$5:$M$5,1),1))
+(((SUM($K3,-$O3)-10000000)*0.98)*0.35),

IF(AND(SUM($K3,-$O3)>14000000,SUM($K3,-$O3)<=28000000),
(VLOOKUP(SUM($K3,-$O3)/1000,근로소득간이세액표!$A$5:$M$652,MATCH(IF(OR($F3="",$F3=0),1,$F3),근로소득간이세액표!$A$5:$M$5,1),1))
+1372000+(((SUM($K3,-$O3)-14000000)*0.98)*0.38),

IF(AND(SUM($K3,-$O3)>28000000,SUM($K3,-$O3)<=30000000),
(VLOOKUP(SUM($K3,-$O3)/1000,근로소득간이세액표!$A$5:$M$652,MATCH(IF(OR($F3="",$F3=0),1,$F3),근로소득간이세액표!$A$5:$M$5,1),1))
+6585600+(((SUM($K3,-$O3)-28000000)*0.98)*0.4),

IF(AND(SUM($K3,-$O3)>30000000,SUM($K3,-$O3)<=45000000),
(VLOOKUP(SUM($K3,-$O3)/1000,근로소득간이세액표!$A$5:$M$652,MATCH(IF(OR($F3="",$F3=0),1,$F3),근로소득간이세액표!$A$5:$M$5,1),1))
+7369600+((SUM($K3,-$O3)-30000000)*0.4),

IF(AND(SUM($K3,-$O3)>45000000,SUM($K3,-$O3)<=87000000),
(VLOOKUP(SUM($K3,-$O3)/1000,근로소득간이세액표!$A$5:$M$652,MATCH(IF(OR($F3="",$F3=0),1,$F3),근로소득간이세액표!$A$5:$M$5,1),1))
+13369600+((SUM($K3,-$O3)-45000000)*0.42),

IF(SUM($K3,-$O3)>87000000,
(VLOOKUP(SUM($K3,-$O3)/1000,근로소득간이세액표!$A$5:$M$652,MATCH(IF(OR($F3="",$F3=0),1,$F3),근로소득간이세액표!$A$5:$M$5,1),1))
+31009600+((SUM($K3,-$O3)-87000000)*0.45))))))))),0)

⑧ U열 / 주민세
→ =ROUND(SUM(T3*0.1),-1)

⑨ V열 / 과세 공제액 합계
→ =SUM(P3:U3)

⑩ W열 / 실 지급 급여(총급여액 - 과세 공제액)
→ =SUM(K3-V3)

샘플 예제 서식에서는 비과세 공제액 포함하여 기본급, 수당1, 수당2, 상여금으로 구분하였고 비과세 공제액 합계의 범위는 운전(자가운전), 식대, 비과세 항목에 해당하는 수당으로 자녀수당등으로 정하고 있기 때문에 사용자의 환경에 따라 총 급여액의 범위를 추가 하거나 축소 또는 열을 삭제 할 수 있고, 추가 하거나 삭제 하는 범위의 셀 주소를 수식에서 제거해주면 해당 수식을 그대로 사용할 수 있습니다.

또한 근로소득간이세액표 시트를 포함하여 예제 서식을 작성하였기 때문에 근로 소득세의 수식을 참고 하여 근로소득세의 고액 근로소득구간이 추가 되거나 변경되는 부분을 추가로 작성할 수 있습니다.

그리고 "참고"시트를 추가 하였지만 참고 시트는 아무것도 작성하지 않은채로 두었는데 참고 시트에 근로자의 기본 정보 주민등록번호, 직책, 부서, 기본급, 수당등을 작성해 놓는다면 참고 시트에 작성된 정보들을 참조하여 수식으로 계산할 수 있는 구간이 많아져 직접 입력하는 데이터값은 점점 줄어들게 될것입니다.

특히 샘플 예제 서식에서는 A열에 나이를 직접 입력해주어야 하는 데이터값으로 두었는데 참고 시트에 근로자 기본 정보로 주민등록번호를 작성해 둔다면 매년 근로자의 나이를 직접 입력해주지 않더라도 참고 시트에 작성해둔 주민등록번호를 참조 하여 나이를 계산 하는 수식을 작성하여 좀 더 편리하게 이용할 수 있는 급여대장 서식을 완성 시킬 수 있습니다.

앞서도 말했지만 이전에 작성한 다음의 글들을 이번 내용과 함께 참고 해 보면 자신의 엑셀(excel) 사용 환경에 따라 서식을 작성하고 수식 역시 작성 할 수 있는 가이드 역활이 될것으로 기대해 봅니다.

엑셀(Excel) 주민 등록 번호로 나이 계산 수식 작성을 해보자.

엑셀(Excel) 연차 남은 개수 사용 개수 발생 개수 수식을 작성해 보자.

엑셀(Excel) 4대보험 계산 국민연금, 건강보험, 고용보험, 산재보험 계산 수식을 작성해 보자.

엑셀(Excel) 근로 소득세를 자동 계산해주는 엑셀 수식을 작성해 보자.

이상으로 "글쓰는이작가의 엑셀(Excel) 시간"은 마무리 하고,
"급여대장 서식과 4대보험, 소득세, 주민세 공제액 차감액 계산 수식 작성 방법" 예제 샘플을 하단에 첨부하였으니 필요한곳에서 활용할 수 있는 수식으로 적용해 보시길 바라겠습니다.

긴 글 읽어주신 여러분들께 감사 말씀 드리며 많은 도움이 될 수 있기를 바라겠습니다.

※ 급여대장 서식과 4대보험, 소득세, 주민세 공제액 차감액 계산 수식 작성 방법 샘플 예제 서식 다운로드.

글쓰는이작가 샘플 예제 서식 - 급여대장 서식과 4대보험, 소득세등 과세 공제액 계산 수식 작성..xlsx

0.06MB

'엑셀(Excel)' 카테고리의 다른 글

엑셀(Excel) 정해진 데이터 값 이외 데이터 값 입력 방지 드롭다운 목록 만들기 엑셀 데이터 유효성 검사 기능을 사용해 보자. (0)	2022.11.24
엑셀(Excel) 경력기간 또는 00년00월00일 + 00년00월00일 날짜 합산 텍스트가 포함된 날짜 합계 수식 작성을 해보자. (1)	2022.11.24
엑셀(Excel) 시작일자(날짜)로부터 종료일자(날짜)까지 경과일 계산 하는 수식을 작성해 보자. (0)	2022.11.22
엑셀(Excel) 주민 등록 번호로 나이 계산 수식 작성을 해보자. (0)	2022.11.21
엑셀(Excel) 재고 관리 서식과 입고 출고 계산 수식을 작성해 보자. (0)	2022.11.19

글쓰는이작가