행아아빠입니다.
급여관리!!
보통은 회사에서 사용하는 프로그램을 이용해서 관리하는게 통상적일 수 있겠지만,
규모가 작거나, 아르바이트를 고용하는 개인사업자분들,
식당등을 운영하시는분들, 회사에서 엑셀로 급여관리 하시는분들!!등과 같이
급여 계산할때 어떻게 하고 계신가요??
저 역시도 과거 대략 400명 정도의 직원이 근무하는 회사에서
총무팀 직원으로 근무할때 급여날짜가 다가오면 급여마감을 하느라 일주일 내내 밤샘했던 기억이 있습니다..ㅠ
약 400명의 개개인의 출,퇴근시간을 일일이 기록하고, 그것을 토대로 근무시간을 또 계산하고,
기본근로시간, 야근, 특근, 수당등을 계산하는것이 너무 괴로운 작업이였죠.... 다른 업무로 많은데 말이죠....ㅋ
이번 엑셀 이야기는 엑셀로 급여관리할때 소득세를 자동으로 계산하기 위한 수식을 작성해 보려고 합니다.
부디 많은 분들께 전달되어 도움이 되셨으면 좋겠습니다.
■ 근로소득간이세액표??
근로소득세는 근로소득 구간과 부양가족수에 따른 근로소득간이세액표를 기준으로 산출할 수 있습니다.
이 근로소득간이세액표는 국세청 사이트에서 확인할 수 있고, 엑셀 파일 또는 PDF, 한글 파일로 다운로드 하실 수 있습니다.
인터넷 검색창에 "근로소득간이세액표"라고 검색하셔서 들어가거나, 아래 링크로 들어가셔서 다운로드 하시면 되겠습니다.
이 글을 보시는 분들은 엑셀로 소득세를 자동으로 계산하는 수식을 만들것이기 때문에 엑셀파일 형식으로 다운로드 해주시면 되겠죠??
■ 엑셀 함수로 근로소득세 계산하는 수식!!
근로소득간이세액표를 다운로드 하셨다면,
해당 파일을 출, 퇴근을 입력하는 급여관리 양식 또는 이제 막 엑셀로 급여관리를 하실 분이시라면,
빈 파일에 넣어주시면 되겠습니다.
▲ 저는 보시는것과 같이 시트 이름도 "근로소득간이세액표"라고 해서 변경해 주었습니다.
▲ 그리고 위 그림처럼 출, 퇴근을 입력할 수 있는 양식과
주휴수당, 4대보험, 소득세, 지방세, 야근, 특근, 그리고 근무시간까지 확인할 수 있는 양식을 만들어 놓았습니다.
해당 양식을 참고하셔도 되고, 사용하고 계시는 양식이 있으시다면 사용 양식에 작성을 하셔도 됩니다~~
그럼 출,퇴근시간, 휴게시간에 따라 주휴수당을 포함한 세금공제전 총 급여액을 기준으로 소득세를 공제하시면 되겠는데요.
※ 기본양식에 직접 입력해주시는 부분은 출,퇴근 시간과, 휴게시간(ex : 점심, 저녁, 심야간식등) 시간만 입력해 주시면,
나머지 값들은 알아서 함수 수식에 의해 자동으로 계산되어 집니다.
다른 값들도 하나씩 추후에 수식 작성하는 방법을 작성해 보도록 할게요^^!!
▲ 위 그림에서 빨간색 동그라미 부분인 소득세 값이 산출되어 표시될 곳에 수식을 입력해 주시면 자동으로 계산 되겠죠??!!
국세청에서 다운로드한 "근로소득간이세액표"를 살펴보면,
1. 월 급여가 최저 770,000원 ~ 10,000,000원까지는 부양가족수에 따라 소득세가 정해진 값으로 공제 된다는 것을 알 수 있습니다.
2. 10,000,000원 초과 ~ 14,000,000원 이하 까지는
= 10,000,000원에 해당하는 소득세 + (10,000,000원을 초과하는 금액 중 * 98%) * 35%의 금액을 소득세로 공제 하게 됩니다.
3. 14,000,000원 초과 ~ 28,000,000원 이하 까지는
= 10,000,000원에 해당하는 소득세 + 1,372,000원 + (14,000,000원을 초과하는 금액 중 * 98%) * 38%의 금액을 소득세로 공제 하게 됩니다.
4. 28,000,000원 초과 ~ 45,000,000원 이하 까지는
= 10,000,000원에 해당하는 소득세 + 6,585,600원 + (28,000,000원을 초과하는 금액 중 * 98%) * 40%의 금액을 소득세로 공제 하게 됩니다.
5. 45,000,000원을 초과하는 경우에는
= 10,000,000원에 해당하는 소득세 + 13,249,600원 + (45,000,000원을 초과하는 금액 중 98%) * 42%의 금액을 소득세로 공제하게 됩니다.
이렇게 소득세를 공제하는 조건들을 5가지로 구분해 보았습니다.
여기에 조건 하나를 더 생각하자면 근로소득간이세액표에 표기되지 않은 770,000원 이하는 소득세 = 0원이라고 생각할 수 있겠습니다.
수식을 작성할 조건들은 완성이 되었고,
근로소득간이세액표를 수식의 참조 표로 사용할 수 있도록 약간 변형해 주면 수식을 좀 더 이해하기 쉽게 작성할 수 있을 것 같습니다.
▲ 다운로드한 근로소득 간이세액표의 공제 대상 가족수를 위 그림처럼 변경해 주세요~
▲ 그리고 근로소득간이세액표의 하단쪽 10,000천 원 부분을 → "천 원"이라는 텍스트만 지워주세요.
■ 자!! 여기까지 되었다면,
소득세를 자동으로 계산하기 위한 수식을 작성해 보면,
=IF(M37<=10000000,VLOOKUP(M37/1000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(D39="",1,D39),
근로소득간이세액표!$A$5:$M$5,0),1),
ROUND(IF(M37>10000000,VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(D39="",1,D39),
근로소득간이세액표!$A$5:$M$5,0),1)
+IF(AND(M37>1000000,M37<=14000000),((M37-10000000)*0.98)*0.35,
IF(AND(M37>14000000,M37<=28000000),(((M37-14000000)*0.98)*0.38)+1372000,
IF(AND(M37>28000000,M37<=45000000),(((M37-28000000)*0.98)*0.4)+6585600,
IF(M37>45000000,(((M37-45000000)*0.98)*0.42)+13249600))))),-1))
위와 같이 수식을 작성해 주시면 되겠습니다.
수식이 길게 작성되어 있고, 괄호가 많기 때문에 헷갈리거나 어렵다고 느껴질 수 있겠습니다만,
색상이 들어간 셀들을 잘 기억하신다면, 크게 어렵지 않고, 누구나 작성할 수 있는 수식 구문이 되니,
하나씩 확인해 보시기 바랄게요.
M37은 주휴수당을 포함한 세금공제전 총 급여액의 셀 주소.
D39은 부양가족수를 입력한 셀 주소.
근로소득간이세액표!$A$6:$M$652은 근로소득간이세액표 시트의 조견표에서 표 부분인 770 ~ 10,000,000원에 해당하는 부양가족 수 11명까지를 전부 드래그한 셀 주소가 되겠습니다.
근로소득간이세액표!$A$5:$M$5은 부양가족수 제목 행, 이상 ~ 11명까지를 드래그한 셀 주소가 되겠습니다.
함수는 IF, Vlookup, Match, Round, And 함수를 사용하여 수식을 작성해 보았습니다.
막상 완성된 수식은 뭔가 복잡해 보이지만, 수식을 하나씩 뜯어보면 생각보다 간단하게 작성되어 있음을 알 수 있습니다.
1. IF(M37<=10000000,VLOOKUP(M37/1000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(D39="",1,D39),
근로소득간이세액표!$A$5:$M$5,0),1),
→ 만약, 총 급여가 10,000,000원 보다 작거나 같다면,
Vlookup 함수로 총 급여에 해당하는 소득세를 A6 셀 ~ M652셀에서 찾아서 값을 가져오는데,
부양가족수를 참조할 셀이 공란이면, 부양가족수가 1인 값을 표에서 찾고,
그러지 않으면 부양가족수 셀에 입력된 숫자를 참조해서 표에서 값을 출력하라는 구문입니다.
2. IF(M37>10000000,VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(D39="",1,D39),
근로소득간이세액표!$A$5:$M$5,0),1)
→ 만약, 총 급여가 천만원을 초과한다면,
부양가족수를 참조한 천만원에 해당하는 소득세의 값과 다음에 해당하는 값을 더해준 값을 출력.
2-1. +IF(AND(M37>1000000,M37<=14000000),((M37-10000000)*0.98)*0.35,
→ 만약, 총 급여가 천만원을 초과하고, 천 4백만원 이하라면, ((총급여 - 천만원) * 98%) * 35% + 2번의 값.
2-2. IF(AND(M37>14000000,M37<=28000000),(((M37-14000000)*0.98)*0.38)+1372000,
→ 만약 2-1번에 해당하지 않고, 총급여가 천 4백만원을 초과하고, 2천 8백만원 이하라면,
((총급여 - 천 4백만원) * 98%) * 38% +1,372,000원 + 2번의 값.
2-3. IF(AND(M37>28000000,M37<=45000000),(((M37-28000000)*0.98)*0.4)+6585600,
→ 만약 2-1번과 2-2번에도 해당하지 않고,
총급여가 2천 8백만원을 초과하고, 4천 5백만원 이하의 구간에 속한다면,
(((총급여 - 2천 8백만원) * 98%) *40%) +6,585,600원) + 2번의 값.
2-4. IF(M37>45000000,(((M37-45000000)*0.98)*0.42)+13249600)
→ 만약 2-1번과, 2-2번, 2-3번에도 해당하지 않고,
총급여가 4천 5백만원을 초과 한다면,
(((총급여 - 4천 5백만원) * 98%) * 42%) + 13,249,600 + 2번의 값을 결과값으로 계산하라는 의미가 되겠습니다.
이렇게 총 6개의 단계를 거쳐 해당하는 값을 소득세로 출력할 수 있게 만드는 수식을 작성하는 방법이었습니다.
많은 분들께 도움이 되시길 바라며,
오늘 하루도 좋은 하루 보내시길 바라겠습니다.^^!!
급여관리 소득세 자동계산 샘플 양식.xlsx'엑셀' 카테고리의 다른 글
| [엑셀] 날짜를 자동으로 입력하는 수식 작성 방법!! (0) | 2020.03.08 |
|---|---|
| [엑셀] 특정한 날짜 요일과 몇주차에 해당하는지 알 수 있는 엑셀 함수 수식 작성하기!! (0) | 2020.03.07 |
| [엑셀] Tip - 잘 사용하던 엑셀이 프로세서 계산 ~%와 같은 버퍼링으로 엑셀 느려짐이 느껴질때 시도해 볼 수 있는 간단한 해결 방법. (0) | 2019.12.11 |
| [엑셀] Tip - 실제 업무에 자주 쓰이는 엑셀 단축키 모음. (0) | 2019.12.10 |
| [엑셀] 순환참조경고 메시지 해결 방법 (0) | 2019.12.05 |
댓글