안녕하세요. 행아아빠 입니다.
오늘 엑셀 내용으로는
"2021년 엑셀 급여대장 자동 계산 서식을 만드는 방법과 자동 계산 수식 작성 방법."입니다.
급여대장은 보통 월 급여로 지급되는 근로자, 즉 시급제 급여 계산이 아닌 월 급여가 정해져 있는 근로자의 급여를 계산 하고 관리 하기 위해 사용하는게 대부분이며, 시급제 근로자와 같이 출근시간과 퇴근시간 연장근로시간, 심야근로시간등과 같은 근로시간을 추가로 계산 하는 부분이 없기 때문에 급여마감때 아마도 시급제 근로자 보다는 월 급여 근로자, 연봉제 근로자 와 같은 근로자의 급여를 계산 하는 것이 좀 더 수월 할 수 있을 것 입니다.
하지만, 이러한 월단위 급여를 지급하는 근로자들도 회사마다 계산 방법과 수당의 종류가 각각 다를 수 있겠지만 저는 통상적으로 생각할 수 있는 부분들만 고려해서 양식을 만들고 그에 대한 공제 금액들과 지급금액등을 자동으로 계산될 수 있도록 수식을 작성해 보려고 하는 것 이기 때문에 해당 양식에서 필요 없는 부분은 빼고, 추가 해야할 부분은 추가하여 본인에게 맞게 양식을 수정하여 사용하시는 것이 좋을 것 같습니다.
물론 해당 양식에 추가할 것도 삭제할것도 없다면 그대로 쓰셔서 무방 하겠습니다.
수식을 작성하기 위해서 사용할 엑셀 함수는 IF함수, And함수, Or함수, Vlookup함수, Look함수, Datedif함수, Mid함수, Text함수, 원단위 절사를 위한 RoundDown함수, Match함수외 기본함수를 이용해서 수식을 작성해 보도록 하겠습니다.
오늘 내용도 많은 분들께 도움이 될 수 있기를 바라겠습니다.
행아아빠 YouTuBe :
행아아빠 네이버Tv :
1. 월단위 근로자 급여계산 방법.
보통은 월단위 근로자의 급여 계산 방법으로 정해진 월 급여 + 각종 수당 - 4대보험 - 소득세,주민세 - 추가 공제금이 있을 경우 추가 공제금으로 계산 할 수 있겠습니다.
보통은 월 급여를 급여명세서상 과세급여와 비급여 항목으로 나누어지게 되고, 과세급여에 대한 항목에 대해서만 4대보험과 소득세 등을 공제 하게 되는것을 염두해 두었으니 참고해 주시면 될 것 같습니다.
2. 급여대장 양식 만들기와 사용방법.
우선 급여대장의 시트는 총 4가지로 구성되어 있습니다.
첫번째 시트에는 4대보험 요율 정보, 공휴일, 연차산정, 근로자의 기본 정보로 구성되어 있고,
두번째 시트에는 근로자 연차사용을 작성 할 수 있는 서식으로 이루어져 있습니다.
세번째는 급여대장으로 근로자의 급여 항목에 대한 기본적인 데이터를 입력해 주면 4대보험과 소득세 등의 공제금액을 자동으로 계산 할 수 있는 수식에 의해서 자동 계산 되어 집니다.
네번째는 급여 소득세를 계산하기 위해서 근로소득간이세액표를 국세청에서 다운받은 표를 붙여 넣었습니다.
위와같이 시트는 총 4가지로 구성해 놓았고, 여기서 중요한 것은 첫번째 시트의 데이터와 세번째 시트 "급여대장" 서식이 되겠습니다.
설정시트에서는 수식을 따로 작성해 줄 것은 아니고, 4대보험의 요율이 변동이 되면 변경된 요율로 변경하여 작성을 해 주시면 "급여대장" 시트에서 변경된 요율로 자동 적용되어 계산 되어지므로 요율이 변경되었다고 해서 수식 전체를 수정해야 하는 번거로움은 없습니다.
또한 국민연금의 경우에는 최저액과 최대액의 범위가 존재 하는데 해당글을 작성하는 시점에서는 320,000원 ~ 5,030,000원까지로 범위가 정해져 있으므로 최저액과 최대액이 변경된다면 "설정"시트에서 변경하여 "급여대장"시트에서 국민연금을 계산할 때 반영 시킬 수 있겠습니다.
그리고 근로자의 기본 데이터를 입력해 주어야 합니다. 근로자의 이름, 부서, 직책, 기본급여, 입사일, 주민등록번호 등을 입력해 주시게 되면 "급여대장"시트에서 반영시킬 수 있는 내용들은 여기에 작성된 데이터를 참조해서 자동으로 입력시킬 수 있는 부분은 입력시키거나 계산 할 수 있습니다. 위 이미지는 제가 임의로 작성한 데이터임을 참고해 주시면 되겠습니다.
다음으로 "급여대장" 시트로 넘어와 보면 입력해야 할 카테고리 메뉴가 많습니다만, 사용자가 입력해 주어야 할 부분은, 파란색 텍스트로 작성된 성명, 기본급, 직책수당, 연장 및 야근 수당, 상여금, 휴가비, 연차수당, 비과세부분의 자가운전, 식대, 자녀수당, 부양가족수 이렇게 작성을 해 주시면 옅은색상으로 음영처리된 부분으로 소속부서, 직책, 나이, 세전과세급여, 공제계, 4대보험 (국민연금, 건강보험, 장기요양보험, 고용보험), 소득세, 주민세, 공제후 실지급액, 세전 연봉, 연단위 퇴직적립금, 월단위 퇴직적립금은 "설정"시트에서 작성해 주신 내용과 현재 급여대장 시트에서 기본적으로 작성해주신 데이터를 참조하여 자동으로 표기되거나 계산되어 입력될 부분입니다.
3. 급여대장 자동 계산 수식 작성 방법.
그럼 이제 급여대장에 작성되어 자동으로 표기되기도 하고, 자동으로 금액이 계산 되는 부분의 수식을 어떻게 작성했는지 확인해 보도록 하겠습니다.
성명은 직접 입력해 주시면 되는 부분이고, 작성한 성명을 기준으로 "설정"시트에 작성한 근로자 기본 정보 데이터를 참조해서 해당 이름 근로자의 부서, 직책을 표기하고, 나이를 계산해서 표기 할 수 있습니다.
소속부서를 입력하기 위한 수식으로는 IF함수와 Lookup함수를 사용해서 다음과 같이 작성 할 수 있습니다.
=IFERROR(
IF(LOOKUP(1,1/(설정!$G$4:$G$977=급여대장!$B5),설정!$H$4:$H$977)="","",LOOKUP(1,1/(설정!$G$4:$G$977=급여대장!$B5),설정!$H$4:$H$977)),"")
다음으로 직책을 표기하는 수식도 참조하는 위치만 다를뿐 동일하게 작성해 주시면 되는데 그 수식은 다음과 같이 작성 할 수 있겠습니다.
=IFERROR(
IF(LOOKUP(1,1/(설정!$G$4:$G$977=급여대장!$B5),설정!$I$4:$I$977)="","",LOOKUP(1,1/(설정!$G$4:$G$977=급여대장!$B5),설정!$I$4:$I$977)),"")
이제 나이를 계산해서 입력하는 수식을 작성해야 하는데, 나이는 "설정"시트에서 작성한 근로자의 주민등록 번호를 기준으로 나이를 계산하는 수식을 작성해 주시면 되겠습니다. 나이를 계산하기 위한 수식으로는 IF함수, Or, And함수, Datedif함수, Mid함수, Lookup함수, Text함수, Left함수등을 사용해서 다음과 같이 작성 할 수 있겠습니다.
=IFERROR(
IF(AND($B5="",$C5="",$D5=""),"",
DATEDIF(IF(OR(MID(LOOKUP(1,1/(설정!$G$4:$G$980=급여대장!$B5)*(설정!$H$4:$H$980=급여대장!$C5)*(설정!$I$4:$I$980=급여대장!$D5),설정!$N$4:$N$980),8,1)="1",MID(LOOKUP(1,1/(설정!$G$4:$G$980=급여대장!$B5)*(설정!$H$4:$H$980=급여대장!$C5)*(설정!$I$4:$I$980=급여대장!$D5),설정!$N$4:$N$980),8,1)="2"),19&TEXT(LEFT(LOOKUP(1,1/(설정!$G$4:$G$980=급여대장!$B5)*(설정!$H$4:$H$980=급여대장!$C5)*(설정!$I$4:$I$980=급여대장!$D5),설정!$N$4:$N$980),6),"00-00-00"),20&TEXT(LEFT(LOOKUP(1,1/(설정!$G$4:$G$980=급여대장!$B5)*(설정!$H$4:$H$980=급여대장!$C5)*(설정!$I$4:$I$980=급여대장!$D5),설정!$N$4:$N$980),6),"00-00-00")),TODAY(),"Y")),"")
세전 과세급여의 수식은 단순하게 앞에 작성해 주신, 기본급, 각종수당, 휴가비 등을 Sum함수로 합계를 내 주시면 되겠습니다. 어렵고 복잡한 수식은 아니기 때문에 각종 수당이 추가 된다면 수당 내용에 대해 열을 추가하고 세전 과세급여의 수식만 Sum함수의 범위만 수정해 주시면 되겠습니다. 해당 수식은 다음과 같이 작성 할 수 있습니다.
=SUM(F5:K5)
급여에서 비과세 적용되는 자가운전, 식대, 자녀수당 등은 보통 비과세 적용하므로 비과세로 빼 놓았으나 항목이 맞지 않거나 추가되면 필요없는 항목은 지우거나 추가로 필요한 항목은 추가하셔서 사용자에 맞게 사용하시면 되고, 직접 작성해 주시면 되는 부분입니다.
공제되어야 할 부분의 수식은 국민연금부터 작성을 해 보면 국민연금은 나이가 18세 미만이거나 60세 이상이라면 0원으로 계산하고, 18세 이상 ~ 60세 미만이면서 비급여 항목을 제외한 세전 과세급여가 320,000원 이하라면 320,000원이라면 320,000원 * 4.5%해 주시면 되고, 5,030,000원 이상이라면 5,030,000원 * 4.5%해 주시면 되겠습니다. 그리고 320,000원 ~ 5,030,000원 사이라면 과세 급여 * 4.5%를 해 주시면 되겠습니다. 따라서 수식으로 국민연금을 계산하기 위해 작성해 보면 다음과 같이 작성 할 수 있겠습니다.
=ROUNDDOWN(
IF($B5="",0,
IF(AND($E5="",$L5<=설정!$C$10),설정!$C$10*설정!$C$3/2,
IF(AND($E5="",$L5>=설정!$C$11),설정!$C$11*설정!$C$3/2,
IF(AND($E5="",$L5>설정!$C$10,$L5<설정!$C$11),$L5*설정!$C$3/2,
IF(OR($E5<18,$E5>=60),0,
IF(AND($E5>=18,$E5<60,$L5<=설정!$C$10),설정!$C$10*설정!$C$3/2,
IF(AND($E5>=18,$E5<60,$L5>=설정!$C$11),설정!$C$11*설정!$C$3/2,
IF(AND($E5>=18,$E5<60,$L5>설정!$C$10,$L5<설정!$C$11),$L5*설정!$C$3/2)))))))),-1)
건강보험은 비급여 항목을 제외한 과세급여의 3.43%이므로 수식을 작성해 보면 다음과 같이 작성 할 수 있습니다.
=IF($B5="",0,
ROUNDDOWN($L5*설정!$C$4/2,-1))
장기요양보험은 건강보험의 11.52%이므로 수식으로 작성해 보면 다음과 같이 작성 할 수 있습니다.
=IF($B5="",0,
ROUNDDOWN($S5*설정!$C$5,-1))
고용보험은 비급여 항목을 제외한 과세급여의 0.8%이므로 수식을 작성해 보면 다음과 같이 작성 할 수 있습니다.
=IF($B5="",0,
ROUNDDOWN($L5*설정!$C$6/2,-1))
소득세의 경우에는 비급여 항목을 제외한 과세급여가 근로소득간이세액표에서 10,000,000원 이하라면 근로소득간이세액표와 부양가족수에 해당하는 테이블구간에 해당하는 금액으로 계산해 주시면 되고, 10,000,000원을 초과한다면 1,000만원 ~ 1,400만원, 1,400만원 ~ 2,800만원, 2,800만원 ~ 3,000만원, 3,000만원 ~ 4,500만원, 4,500만원 ~ 8,700만원, 8,700만원 초과 구간으로 나누어서 근로소득간이세액표에서 정하고 있는 비율에 따라 소득세를 계산해 주시면 되겠습니다. 따라서 소득세의 수식을 작성해 보면 다음과 같이 작성할 수 있겠습니다.
=IF($B5="",0,
IFERROR(
IF($L5<=10000000,VLOOKUP($L5/1000,근로소득간이세액표!$A$6:$M$652,MATCH(IF($P5="",1,$P5),근로소득간이세액표!$A$5:$M$5,0),1),
ROUND(
IF(AND($L5>1000000,$L5<=14000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF($P5="",1,$P5),근로소득간이세액표!$A$5:$M$5,0),1)+
(($L5-10000000)*0.98)*0.35,
IF(AND($L5>14000000,$L5<=28000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF($P5="",1,$P5),근로소득간이세액표!$A$5:$M$5,0),1)+
((($L5-14000000)*0.98)*0.38)+1372000,
IF(AND($L5>28000000,$L5<=30000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF($P5="",1,$P5),근로소득간이세액표!$A$5:$M$5,0),1)+
((($L5-28000000)*0.98)*0.4)+6585600,
IF(AND($L5>30000000,$L5<=45000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF($P5="",1,$P5),근로소득간이세액표!$A$5:$M$5,0),1)+
(($L5-30000000)*0.4)+7369600,
IF(AND($L5>45000000,$L5<=87000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF($P5="",1,$P5),근로소득간이세액표!$A$5:$M$5,0),1)+
(($L5-45000000)*0.42)+13369600,
IF($L5>87000000,VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF($P5="",1,$P5),근로소득간이세액표!$A$5:$M$5,0),1)+(($L5-87000000)*0.45)+31009600)))))),-1)),0))
주민세는 소득세의 10%이므로 다음과 같이 수식을 작성할 수 있겠습니다.
=ROUNDDOWN((V5*0.1),-1)
공제할 금액을 수식을 작성해서 계산했기 때문에 실제 지급할 급여는 세전 과세급여 + 비과세 항목 합계 - 공제합계를 해 주시면 실제 지급할 급여를 계산 할 수 있기 때문에 수식을 작성해 보면 다음과 같이 간단하게 작성 할 수 있습니다.
=L5+SUM(M5:O5)-Q5
세전 연봉을 계산하기 위해서는 과세급여 * 12해 주시면 손쉽게 세전 연봉을 계산할 수 있고, 연단위 퇴직적립금은 과세급여 자체가 될 것이고, 월 단위 퇴직적립금은 과세급여 / 12를 해 주시면 간단히 계산 할 수 있겠습니다. 따라서 각각의 수식을 차례로 작성해 보면 다음과 같이 매우 간단하게 작성 할 수 있겠습니다.
세전 연봉 : =L5*12
연단위 퇴직 적립금 : =Y5/12
월단위 퇴직 적립금 : =ROUND(SUM(Z5/12),0)
이상으로 2021년 적용된 소득세율과 4대보험 요율을 적용해서 2021년 급여대장을 자동으로 계산 할 수 있는 서식과 수식을 작성해 보았습니다.
해당 서식은 사용자에 맞게 필요한 항목은 추가하고 필요하지 않은 항목은 삭제하거나 숨기기 하여 사용하시면 될 것 같습니다.
오늘 내용도 많은 분들께 도움이 될 수 있기를 바라겠습니다.
그럼 오늘 하루도 좋은 하루 되시길 바라겠습니다.
▼ 2021년 급여대장 서식 다운로드
'엑셀' 카테고리의 다른 글
| 엑셀 재고 관리 서식 만들기와 재고 자동 계산 함수 수식 작성 방법. (1) | 2021.06.06 |
|---|---|
| 엑셀 함수 정리-직장인을 포함하여 엑셀을 사용한다면 엑셀 자동 계산을 하기 위해서 꼭 알아야 할 실무 엑셀 함수. (0) | 2021.05.25 |
| 엑셀 경력 기간(재직 기간) 계산 서식 만들기와 수식 작성 방법! (2) | 2021.05.08 |
| 엑셀 4대 보험, 근로 소득세 자동 계산 수식 작성 방법과 서식! (2) | 2021.05.01 |
| 엑셀 퇴직금 계산 방법과 퇴직 소득세 자동 계산 수식 작성/서식(2021년 종합소득세율 적용) (0) | 2021.04.29 |
댓글