안녕하세요. 행아아빠 입니다.
오늘 엑셀 내용은 세후 급여 (실지급 급여) 금액으로 세전 급여 금액을 추정해 보는 수식을 작성해 보려고 합니다.
많은 분들이 급여를 지급받는데 있어서 중요한 것은 세전 급여 보다는 세금을 제외한 나머지 실 수령 금액이 얼마나 되는지가 중요한 부분일 것 같습니다. 또한 네트급여를 적용하는 사업장에서도 해당 근로자가 수령하고자 하는 금액에 대해서 세전 급여를 계산해야 함에 있어서 귀찮은 부분이 아닐 수 없을 것 같습니다.
사실 네트급여는 세금부분에 대해 부정적인 영향이 많기 때문에 지향하는 방식은 아니라고는 합니다만, 내가 실제로 수령하는 급여 금액에 대한 세전 급여가 얼마 정도 되는지 추정치를 확인한다면 면접, 이직, 희망 급여등등에 활용할 수 있는 지표가 될 수 있을것 같습니다.
■ 세후 급여로 세전 급여 추정 금액을 확인 하기 위한 서식 만들기.
우선은 세후 급여만으로 세전 급여 금액을 추정위한 수식을 작성하기 위해서 기본적인 서식 틀을 작성해야 겠습니다.
먼저 근로 소득세를 계산 하기 위해서 "근로 소득 간이 세액표"시트를 하나 만들어서 근로 소득 간이 세액표를 붙여 넣어 주세요.
"근로 소득 간이 세액표" 다운로드 경로는 홈텍스 → 조회/발급 → 기타조회 → 간이세액표 → 근로소득간이세액표 → 근로소득 간이세액표(조견표) 엑셀 파일 형식으로 다운로드 할 수 있습니다.
다운로드 받은 근로소득간이세액표를 근로소득간이세액표에 붙여 넣어주고, 수식을 작성 할 수 있도록 기본 서식에서 약간 변경해 주었습니다.
실제 내 통장에 찍히는 세후 급여를 입력하면 4대보험과 소득세, 주민세가 계산되어 세전 급여 추정액이 계산 될 수 있도록 기본 서식을 간단하게 작성해 주었고, 위 이미지는 미리 수식과 임의의 세후 급여를 입력한 것 입니다.
또한 근로 소득세는 부양가족수에 따라서 금액이 달라지기 때문에 부양가족수가 본인 포함 1명이 넘는다면 부양가족 수를 입력해 주시면 됩니다. 단, 부양가족수를 입력하지 않는다면 부양가족수는 기본적으로 1명으로 인식하도록 근로 소득세의 수식이 작성 되어 있습니다.
■ 세후 급여로 세전 급여 추정액 자동 계산 수식 작성 하는 방법.
세후 급여로 세전 급여 추정액을 계산 하기 위해서 작성해야 할 수식은 국민연금, 건강보험, 장기요양보험, 고용보험, 근로 소득세, 주민세를 계산하고 계산한 금액들과 세후 급여를 더해서 세전 급여 추정액을 계산 할 수 있습니다.
그럼 국민연금부터 계산 할 수 있는 수식을 작성해 보겠습니다.
▶ 국민연금 계산 수식.
=IFERROR(ROUNDDOWN(SUM(B7*$C$6),-1),0)
→ 국민연금은 세전 급여 금액의 4.5%를 곱해서 계산 할 수 있습니다. 다만, 원단위 절사를 위해서 RoundDown함수와 결과값 오류를 깔끔하게 표기 하기 위해서 Iferror함수를 이용해서 수식을 작성해 주시면 되겠습니다.
▶ 건강보험 계산 수식.
=IFERROR(ROUNDDOWN(SUM(B7*$D$6),-1),0)
→ 건강보험 역시 세전 급여 금액의 3.43%를 곱해서 계산 할 수 있고, 원단위 절사를 위해 RoundDown함수와 결과값의 오류를 깔끔하게 표기 하기 위해서 Iferror함수를 이용해서 수식을 작성해 주시면 되겠습니다.
▶ 장기요양보험 계산 수식.
=ROUNDDOWN(SUM(D7*$E$6),-1)
→ 장기요양보험은 건강보험 금액에서 11.52%를 곱해서 계산 할 수 있고, 수식은 원단위 절사를 위해서 RoundDown함수를 사용해서 작성해 주시면 되겠습니다. 또한 Iferror함수를 이용해서 결과값의 오류를 원하는 결과값으로 표기 할 수도 있겠지만 해당 서식에서는 특이한 오류가 나타나는 것이 없어서 Iferror함수를 사용하지는 않았습니다.
▶ 고용보험 계산 수식.
=IFERROR(ROUNDDOWN(SUM(B7*$F$6),-1),0)
→ 고용보험은 세전 급여에서 0.8%를 곱해서 계산 할 수 있고, 원단위 절사를 위해서 RoundDown함수와 결과값 오류를 원하는 값으로 표기 하기 위해서 Iferror함수를 사용해서 수식을 작성해 주시면 되겠습니다.
▶ 근로 소득세 계산 수식.
=IFERROR(
IF(B7<=10000000,VLOOKUP(B7/1000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(J7="",1,J7),근로소득간이세액표!$A$5:$M$5,0),1),
ROUND(
IF(AND(B7>1000000,B7<=14000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(J7="",1,J7),근로소득간이세액표!$A$5:$M$5,0),1)+
((M37-10000000)*0.98)*0.35,
IF(AND(B7>14000000,B7<=28000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(J7="",1,J7),근로소득간이세액표!$A$5:$M$5,0),1)+
(((M37-14000000)*0.98)*0.38)+1372000,
IF(AND(B7>28000000,B7<=30000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(J7="",1,J7),근로소득간이세액표!$A$5:$M$5,0),1)+
(((M37-28000000)*0.98)*0.4)+6585600,
IF(AND(B7>30000000,B7<=45000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(J7="",1,J7),근로소득간이세액표!$A$5:$M$5,0),1)+
((M37-30000000)*0.4)+7369600,
IF(AND(B7>45000000,B7<=87000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(J7="",1,J7),근로소득간이세액표!$A$5:$M$5,0),1)+
((M37-45000000)*0.42)+13369600,
IF(B7>87000000,VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(J7="",1,J7),근로소득간이세액표!$A$5:$M$5,0),1)+((B7-87000000)*0.45)+31009600)))))),-1)),0)
→ 근로 소득세 계산은 미리 다운로드 받아서 붙여놓은 근로소득간이세액표를 참조해서 계산 될 수 있도록 수식을 작성해 주시면 되는데, 월 세전 급여가 1,000만원 이상인 고액 급여의 경우에는 각각의 구간에 따라 계산 될 수 있도록 각각의 구간에 해당하는 계산식과 1,000만원 이하 급여에 대한 근로소득세는 1,000만원 이하의 구간별 테이블에서 금액을 불러올 수 있도록 수식을 작성해 주시면 되겠습니다.
▶ 주민세 계산 수식.
=ROUNDDOWN(G7*0.1,-1)
→ 주민세는 근로소득세의 10%를 곱해주어 계산 할 수 있습니다. 수식은 원단위 절사를 위해서 RoundDown함수를 이용해서 작성했고, 특이한 오류값은 표기 되는 경우가 없어서 Iferror함수는 작성해 주지 않았습니다.
▶ 세전 급여 추정액 계산 수식.
=I7+SUM(C7:H7)
→ 세전 급여 추정액은 간단하게 직접 작성해준 세후 급여 + (국민연금 + 건강보험 + 장기요양보험 + 고용보험 + 근로 소득세 + 주민세)로 계산할 수 있습니다.
세후 급여에서 세전 급여 추정액을 계산하기 위해서 역으로 계산 하는 수식이 별다른 방법이 있는 것은 아니고, 세전 급여에서 세후 급여를 계산하는 것과 동일하게 4대보험과 근로 소득세, 주민세를 계산해서 이를 더해 주어서 세전 급여 추정액을 계산 할 수 있습니다.
다만!! 주의할 점은 보통의 엑셀 기본 셋팅대로라면 세후 급여로 세전 급여를 계산하기 위해서 급여를 역으로 계산하는 수식을 작성하다 보면 순환참조 오류가 발생할 경우가 생기게 됩니다.
순환참조 오류는 세전 급여를 계산해야 하는 수식으로 작성되어 있기 때문에 값이 나오지 않는 상태에서 4대보험을 계산하기 위해서 세전 급여를 참조해야 하기 때문에 발생하게 됩니다.
이러한 계산을 하기 위한 순환참조 오류를 해결 하기 위한 방법은 다음과 같이 변경해 주시면 되겠습니다.
변경 경로는 ① 파일 → ② 옵션 → ③ 수식 → ④ 반복계산사용 체크 → ⑤ 확인을 눌러 주시면 순환참조 오류 없이 해당 수식에 따라서 계산 할 수 있겠습니다.
'엑셀' 카테고리의 다른 글
| Vlookup 엑셀 함수 결과값 여러개일 때 순서대로 표기 하는 수식 작성 방법. (0) | 2021.06.24 |
|---|---|
| 텍스트로 입력된 날짜 0000-00-00 형식으로 표기 하는 엑셀 수식 작성 방법. (0) | 2021.06.23 |
| 엑셀 재고 관리 서식 만들기와 자동 계산 함수와 수식 작성 방법. (제품명-제품명으로 하위 제품명이 있는 재고 관리 엑셀 서식) (0) | 2021.06.11 |
| 엑셀 재고 관리 서식 만들기와 재고 자동 계산 함수 수식 작성 방법. (1) | 2021.06.06 |
| 엑셀 함수 정리-직장인을 포함하여 엑셀을 사용한다면 엑셀 자동 계산을 하기 위해서 꼭 알아야 할 실무 엑셀 함수. (0) | 2021.05.25 |
댓글