안녕하세요. 행아아빠 입니다.
오늘 엑셀 내용은 엑셀 급여관리 중 4대보험(국민연금, 건강보험, 장기요양보험, 고용보험)과 근로 소득세 계산 방법과 엑셀 수식을 작성하여 자동 계산 할 수 있는 서식을 만들어 보고자 합니다.
오늘 작성한 서식을 다운로드 받으실 때에는 댓글로 흔적을 남겨주시길 바라겠습니다.
오늘 작성할 수식에 사용할 엑셀 함수는 IF함수, And함수, RoundDown함수, Vlookup함수, IFerror함수를 이용해서 작성 할 수 있으니 각각의 함수 수식 작성 방법을 익히셔서 많은 분들이 업무에 적용 하실 수 있기를 바라겠습니다.
행아아빠 YouTuBe : www.youtube.com/channel/UCQlocK7yFlXLZFt-VTxVaAg
행아아빠 네이버Tv : tv.naver.com/hangadaddy
■ 4대 보험 (국민연금, 건강보험, 장기요양보험, 고용보험) 요율과 계산 방법.
● 국민연금
국민연금은 정부가 직접 운영하는 공적 연금제도로, 국민 개개인이 소득 활동을 할때 납부한 보험료를 기반으로 하여 나이가 들거나, 갑자스런 사고나 질병으로 사망 또는 장애를 입어 소득활동이 중단된 경우 본인이나 유족에게 연금을 지급함으로써 기본 생활을 유지할 수 있도록 하는 연금제도이며, 공적 연금으로서 가입이 법적으로 의무화되어 있기 때문에 사 보험에 비해 관리운영비가 적게 소요되며, 관리운영비의 상당 부분이 국고에서 지원되므로 사 보험처럼 영업 이익을 추구하지 않고, 현행 국민연금 제도는 부담과 급여의 수준이 일정 기간 불완전 균형을 이루는 수정 적립 방식을 채택해 운용하고 있습니다.
국민연금 보험료 요율 : 기준 월 소득액 기준으로 근로자 4.5%, 사업주 4.5%
기준소득월액은 최저 32만원에서 최고 503만원까지의 범위로 결정하게 됩니다. 따라서 신고한 소득월액이 32만원보다 적으면 32만원을 기준소득월액으로 하고, 503만원보다 많은면 503만원을 기준소득월액으로 합니다. (2027.07.01 기준)
국민연금 납부 대상 : 18세 이상 60세 미만 국내 거주국민으로 공무원, 군인, 사립학교 교직원 제외.
따라서 국민연금 계산은 총급여액이 32만원보다 작다면 32만원 * 4.5%이고, 총 급여액이 503만원보다 많다면 503만원 * 4.5%로 계산하고, 총 급여액이 32만원 ~ 503만원 사이라면 총 급여액 * 4.5%로 계산 할 수 있겠습니다.
● 건강보험
건강보험료 요율은 총급여액의 6.86%로 근로자와 사업주 각각 해당 요율의 50%씩 부담으로 근로자 3.43%, 사업주 3.43%로 총급여액 * 3.43%로 계산 할 수 있겠습니다.
● 장기요양보험
장기요양보험료는 건강보험료 기준으로 보험료율은 11.52%로, 건강보험료 * 11.52%로 계산 할 수 있겠습니다.
● 고용보험
고용보험은 1인 이상의 근로자를 고용하는 모든 사업 또는 사업장에 적용되고, 계산 방법은 일반적으로 근로자 0.8%, 사업주 0.8%로 총 급여액 * 0.8%로 계산 할 수 있겠습니다.
■ 근로 소득세 계산 방법.
근로소득세는 근로소득간이세액표에 따라 총 급여액의 구간과 부양가족 수에 따라서 세액을 계산 할 수 있습니다.
근로소득간이세액표는 국세청 홈페이지에서 다운로드 할 수 있으며, 다운로드 경로는 인터넷 검색창에 "국세청 홈텍스"검색 → 조회/발급 → 기타조회 → 간이세액표 → 근로소득간이세액표 (원하는 파일 형식으로 다운로드)를 다운로드 할 수 있겠습니다.
■ 4대보험 / 근로 소득세 계산 엑셀 서식 만들기와 자동 계산 수식 작성 방법.
4대보험과 소득세를 자동으로 계산하기 위해 작성한 샘플 양식으로 노란색 음영부분은 직접 입력해 주셔야 할 기본 데이터들이고, 음영처리 되지 않은 곳은 수식을 작성해서 4대보험(국민연금, 건강보험, 장기요양보험, 고용보험)과 소득세와 주민세를 계산하기 위한 셀 입니다.
4대보험 요율표를 별도로 작성한 이유는 4대보험의 요율이 변경되었을때 수식을 변경하는 것이 번거로울 수 있기 때문에 4대보험요율 표에서 변경된 보험료 요율을 변경해 주면 수식의 변경 없이 계산을 적용하기 위해 작성 한 것 입니다.
근로소득세를 계산하기 위해서는 홈텍스에서 다운받은 근로소득간이세액표 엑셀 파일을 두번째 시트에 붙여 넣어놓은 것으로 월 급여액 770,000만원~10,000,000만원까지는 부양가족수에 따라 정해진 금액을 근로소득세로 공제하게 되고, 월 급여액이 10,000,000만원을 초과 하면서 87,000,000만원을 초과 하는 급여까지 구간별로 정해진 계산식에 의해서 소득세를 계산 할 수 있겠습니다.
즉 1,000만원 이하의 급여의 근로소득세는 표에서 참조하여 값을 가져오면 되고 고액 급여는 구간별로 계산식에 의해 근로소득세를 계산하면 되는 것 입니다.
고액 급여의 구간별로 계산식으로 작성해보면 다음과 같이 작성 할 수 있겠습니다.
1,000만원 초과 ~ 1,400만원 이하 : (1,000만원에 해당하는 세액) + (((총급여액-10,000,000만원)*98%)*35%)
1,400만원 초과 ~ 2,800만원 이하 : (1,000만원에 해당하는 세액) + (1,372,000원) + ((총급여액 -1,400만원)*98%)*38%
2,800만원 초과 ~ 3,000만원 이하 : (1,000만원에 해당하는 세액) + (6,585,600원) + ((총급여액 - 2,800만원)*98%)*40%
3,000만원 초과 ~ 4,500만원 이하 : (1,000만원에 해당하는 세액) + (7,369,600원) + (총급여액 - 3,000만원)*40%
4,500만원 초과 ~ 8,700만원 이하 : (1,000만원에 해당하는 세액) + (13,369,600원) + (총급여액 - 4,500만원)*42%
8,700만원 초과 : (1,000만원에 해당하는 세액) + (31,009,600원) + (총급여액 - 8,700만원)*45%
다시 첫번째 시트로 돌아와서 국민연금부터 주민세까지 각각의 값을 자동으로 계산 할 수 있게 수식을 작성해 보면 다음과 같이 작성 할 수 있겠습니다.
● 국민연금 계산 수식
=IF(B1<=320000,320000*E3,
IF(B1>=5030000,5030000*E3,
IF(AND(B1>320000,B1<5030000),B1*E3)))
국민연금은 18세 ~ 60세 미만이라는 대상 조건이 있지만, 위 수식은 나이에 대한 조건은 제외하고 최저급여액과 최고급여액의 범위에 대한 조건을 만족 시킬 수 있도록 작성 한 것 입니다. 나이에 대한 조건이 필요하다면 If함수와 And함수로 최저액과 최고액의 조건에 대한 수식처럼 If조건을 하나 추가해서 넣어주시면 되겠습니다.
● 건강보험 계산 수식
=ROUNDDOWN(B1*E4,-1)
건강보험은 단순하게 총급액 * 보험요율로 Rounddown함수를 이용해서 원단위 절사하여 계산 할 수 있겠습니다.
● 장기요양보험 계산 수식
=ROUNDDOWN(B6*E5,-1)
장기요양보험역시 단순하게 건강보험료 * 보험요율로 Rounddown함수를 이용해서 원단위 절사하여 계산 할 수 있습니다.
● 고용보험 계산 수식
=ROUNDDOWN(B1*E6,-1)
고용보험도 단순하게 총급여액 * 보험요율로 Rounddown함수를 이용해서 원단위 절사하여 계산 할 수 있겠습니다.
4대보험은 국민연금을 제외한 나머지 보험료는 단순하게 계산 할 수 있습니다. 국민연금은 나이에 대한 대상과 급여액의 범위에 대한 조건이 주어져 있기 때문에 If함수로 조건식을 작성해서 계산해야 하기 때문입니다. 하지만 어렵고 복잡한 수식은 아니기 때문에 충분히 누구나 작성 할 수 있는 수식입니다.
● 근로 소득세 계산 수식
=IFERROR(
IF(B1<=10000000,VLOOKUP(B1/1000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(B2="",1,B2),근로소득간이세액표!$A$5:$M$5,0),1),
ROUND(
IF(AND(B1>1000000,B1<=14000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(B2="",1,B2),근로소득간이세액표!$A$5:$M$5,0),1)+
((B1-10000000)*0.98)*0.35,
IF(AND(B1>14000000,B1<=28000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(B2="",1,B2),근로소득간이세액표!$A$5:$M$5,0),1)+
(((B1-14000000)*0.98)*0.38)+1372000,
IF(AND(B1>28000000,B1<=30000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(B2="",1,B2),근로소득간이세액표!$A$5:$M$5,0),1)+
(((B1-28000000)*0.98)*0.4)+6585600,
IF(AND(B1>30000000,B1<=45000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(B2="",1,B2),근로소득간이세액표!$A$5:$M$5,0),1)+
((B1-30000000)*0.4)+7369600,
IF(AND(B1>45000000,B1<=87000000),VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(B2="",1,B2),근로소득간이세액표!$A$5:$M$5,0),1)+
((B1-45000000)*0.42)+13369600,
IF(B1>87000000,VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(B2="",1,B2),근로소득간이세액표!$A$5:$M$5,0),1)+((B1-87000000)*0.45)+31009600)))))),-1)),0)
근로 소득세는 고액 급여 구간에 속할 경우에 해당하는 계산식과 1,000만원 이하의 급여에 해당 할 경우 근로소득간이세액표 내의 부양가족 수 에 따라 정해진 금액을 가져오는 수식을 작성하면 되겠습니다.
위 수식은 급여 총액이 1,000원 이하에 해당할 경우 부양가족 수에 따라 근로소득간이세액표의 범위내에서 해당하는 구간의 값을 가져오고, 1,000만원 ~ 1,400만원 구간, 1,400 ~ 2,800만원 구간, 2,800만원 ~ 3,000만원 구간, 3,000만원 ~ 4,500만원 구간, 4,500만원 ~ 8,700만원 구간, 8,700만원을 초과하는구간으로 나누어서 조건에 맞는 수식을 작성해 주시면 되겠습니다.
만약, 해당 샘플 양식에서 부양가족 수를 입력하지 않을 경우에는 부양가족 수를 1로 계산하여 소득세를 계산 하도록 수식을 작성했으니 참고해 주시길 바라겠습니다.
● 주민세 계산 수식
=IFERROR(ROUNDDOWN((B9*0.1),-1),0)
주민세는 단순하게 소득세의 10%로 소득세 * 10%로 Rounddown함수를 이용해서 원단위 절사하여 계산 수식을 작성 할 수 있겠습니다.
이상으로 4대보험과 근로 소득세 그리고 주민세까지 계산하는 방법과 계산식 그리고 자동으로 계산 할 수 있는 수식을 작성해 보았습니다.
오늘 내용이 많은 분들께 전달되어 도움이 되셨으면 좋겠습니다.
그럼 오늘 하루도 좋은 하루 보내시길 바라겠습니다.
▼ 4대보험 및 소득세 계산 샘플 서식
'엑셀' 카테고리의 다른 글
| 2021년 급여 대장 엑셀 자동 서식(양식) 만들기와 엑셀 자동 계산 수식 작성 방법. (0) | 2021.05.16 |
|---|---|
| 엑셀 경력 기간(재직 기간) 계산 서식 만들기와 수식 작성 방법! (2) | 2021.05.08 |
| 엑셀 퇴직금 계산 방법과 퇴직 소득세 자동 계산 수식 작성/서식(2021년 종합소득세율 적용) (0) | 2021.04.29 |
| 엑셀 날짜 시간 자동 입력 방법과 단축키! (날짜 자동 입력 수식 작성 방법) (0) | 2021.04.25 |
| 엑셀 함수 수식 쉽게 작성하기 위한 조건과 방법!! (0) | 2021.04.22 |
댓글