엑셀 퇴직금 계산 방법과 퇴직 소득세 자동 계산 수식 작성/서식(2021년 종합소득세율 적용)

안녕하세요. 행아아빠 입니다.

오늘 엑셀 내용으로는 퇴직금 계산을 자동으로 계산 할 수 있도록 하는 자동 계산 서식을 만들어 보려고 합니다.

퇴직금 계산 방법과 지급 기준, 그리고 퇴직 소득세 세율과 종합소득세 세율을 확인해보고 퇴직금 계산 방법에 따라 엑셀로 퇴직금을 자동으로 계산 할 수 있는 수식을 작성하여 퇴직금 - 퇴직소득세 = 실제 수령 퇴직금이 계산 될 수 있는 자동 계산 서식을 만들어 보도록 하겠습니다. 

오늘 작성할 수식에 사용할 엑셀 함수는 Datedif함수, Date함수, Year함수, Month함수, Edate함수, If함수, Index Match함수, Rounddown함수, Iferror함수 입니다.

위 함수 중에서 Datedif, Date, If, Index Maxch 함수와 함께 제가 이제껏 설명 드린 몇몇 함수만 추가해서 자유롭게 구사하실 줄 아신다면 왠만한 업무에 적용할 수 있는 수식은 작성이 가능할 것 같습니다.

따라서 중요한 몇몇 함수는 그 수식 구문 작성 방법을 꼭 익혀 두시길 바라겠습니다.

그럼 오늘 내용도 많은 분들께 도움이 될 수 있기를 바랍니다.

해당 서식을 다운 받을실 땐 흔적(댓글)과 함께 YouTuBe 구독, 좋아요, Url공유 함께 해 주시면 감사하겠습니다.

YouTuBe : www.youtube.com/channel/UCQlocK7yFlXLZFt-VTxVaAg

네이버Tv : tv.naver.com/hangadaddy

---

■ 퇴직금 지급 기한과 지급기준

근로자가 퇴직한 경우 지급 사유가 발생한 날로부터 14일안에 퇴직금을 지급하는 것을 원칙으로 합니다. 

하지만 보통 근로계약서에 퇴직금 지급일자를 명시해 두거나 회사와 근로자간의 협의를 통하여 급여와 퇴직금을 같이 지급하는 경우가 통상적이라고 할 수 있을 것 같습니다. 

 

퇴직금을 지급하기 위한 조건으로는 1년 이상 계속적으로 근로가 이루어져야 하고, 소정근로시간이 1주일간 15시간 이상이어야 합니다. 두가지 조건을 갖춘다면 퇴직금의 지급기준에 부합한다고 할 수 있겠습니다.

하지만 직장을 다니다 보면 근로기간중 휴직기간이 발생할 수 있는데, 휴직기간이 발생했다면 1년이라는 조건을 계산함에 있어서 휴직기간이 직무와 관련되어 있다면 1년의 기간에 포함되지만 질병 또는 학업과 같은 직무와 관련이 없는 휴직이라면 1년의 기간안에 포함되지 않는것으로 보실 수 있겠습니다.

 

■ 퇴직금 계산 방법.

퇴직금은 30일분 이상의 평균임금을 퇴직금으로 지급해야 합니다.

평균임금이란 퇴직금을 산정해야 할 사유가 발생한 날로부터 이전 3개월 동안 근로자에게 지급된 급여의 총액을 총 일수로 나눈 금액을 말하는 것으로 볼 수 있겠습니다.

퇴직금 계산을 공식으로 표현해 보면 다음과 같이 계산 할 수 있겠습니다.

((평균임금 * 30일) * 총 재직일수) / 365

 

■ 퇴직금 소득세(세금) 계산 방법 - 2021년 종합소득세 세율 적용.

퇴직금도 소득으로 볼 수 있기 때문에 소득세를 공제해야 합니다.

따라서 퇴직금 소득세를 계산하기 위한 세율과 공제 되는 금액은 무엇이 있는지 확인해 볼 필요가 있습니다.

우선은 퇴직금 총액에서 근속연수에 따라 공제 되는 금액을 공제 하게 됩니다.

이렇게 근속연수에 따라 공제 되는 금액을 공제 후 계산된 금액을 환산급여라고 하고 환산급여는 또다시 환산급여 구간별로 공제액이 달라지게 됩니다.

마지막으로 환산급여에서 공제 되는 금액까지 공제 후 계산한 금액을 종합소득세 세율표에 따라 소득세를 계산 할 수 있겠습니다.

 

우선 근속연수에 따라 공제 되는 금액을 확인해 보면 다음과 같습니다.

근속연수에 따라 공제 되는 금액표

1. 근속연수 5년 이하 공제액 = 30만원 * 근속연수

2. 근속연수 5년 초과 ~ 10년 이하 공제액 = 150만원 + (50만원 * (근속연수 -5년))

3. 근속연수 10년 초과 ~ 20년 이하 공제액 = 400만원 + (80만원 * (근속연수 - 10년))

4. 20년 초과 공제액 = 1,200만원 + (120만원 * (근속연수 - 20년))

 

다음으로 환산급여 구간별로 공제 되는 금액을 확인해 보면 다음과 같습니다.

환산급여 구간별로 공제 되는 금액표

1. 800만원 이하 공제액 = 환산급여의 100%

2. 800만원 초과 ~ 7,000만원 이하 공제액 = 800만원 + (800만원을 초과 하는 금액의 60%)

3. 7,000만원 초과 ~ 1억원 이하 공제액 = 4,520만원 + (7,000만원을 초과 하는 금액의 55%)

4. 1억원 초과 ~ 3억원 이하 공제액 = 6,170만원 + (1억원을 초과 하는 금액의 45%)

5. 3월원 초과 공제액 = 1억 5,170만원 + (3억원을 초과 하는 금액의 35%)

 

마지막으로 2021년 기준 종합소득 기본세율 과세표준은 다음과 같습니다.

2021년 종합소득세율 표

1.  1,200만원 이하 소득세 = 합산급여 공제액의 6%

2. 1,200만원 초과 ~ 4,600만원 이하 = (합산급여 산출액 - 누진세액공제 108만원) * 15%

3. 4,600만원 초과 ~ 8,800만원 이하 = (합산급여 산출액 - 누진세액공제 522만원) * 24%

4. 8,800만원 초과 ~ 1억5천만원 이하 = (합산급여 산출액 - 누진세액공제 1,490만원) * 35%

5. 1억5천만원 초과 ~ 3억원 이하 = (합산급여 산출액 - 누진세액공제 1,940만원) * 38%

6. 3억원 초과 ~ 5억원 이하 = (합산급여 산출액 - 누진세액공제 2,540만원) * 40%

7. 5억원 초과 ~ 10억원 이하 = (합산급여 산출액 - 누진세액공제 3,540만원) * 42%

8. 10억원 초과 = (합산급여 산출액 - 누진세액공제 6,540만원) * 45%

 

예를들어 입사일이 2019-05-01일이고, 퇴사일이 2021-03-01일 일때 퇴직금을 계산해 보면 총 재직일수는 670일이고, 근속년수는 2년이 됩니다.

퇴직일 이전 3개월 총 급여액은 900만원으로 가정하고, 퇴직일 이전 3개월 총 상여금은 120만원이라고 가정할 경우

1일평균임금은

퇴직 3개월 이전 총급여액 900만원/90일(퇴직일 이전 3개월 = 2021.02월 28일 + 2021.01월 31일 + 2020.12월 31)

+ 퇴직 3개월 이전 총 상여금 (120만원/(3/12))/90일 = 103,333원으로 계산 할 수 있습니다.

따라서 퇴직금은 1일 평균임금 (103,333원 * 30일) * (총 재직일수 670일 / 365일) = 5,690,392원으로 계산 할 수 있겠습니다. 여기에서 원단위 절사를 하게 되면 5,690,390원이 최종 퇴직금이라고 할 수 있겠습니다.

 

그럼 계산한 퇴직금에서 공제액을 차감 한 후 종합소득세율 구간에 해당하는 소득세액을 계산해서 차감해 보면

근속연수는 2년이기 때문에 근속연수 5년 이하에 해당하는 30만원 * 2년 = 60만원을 공제 할 수 있습니다. 60만원을 공제한 환삽 급여는 5,690,390 - 600,000 = 5,090,390원으로 계산되고 해당 환산급여 공제액은 800만원 이하에 해당 하기 때문에 환산급여의 100%인 5,090,390원 전액을 공제 하게 됩니다. 따라서 차감해야할 소득세는 0원이 되겠습니다.

퇴직금이 소액일 경우에는 차감할 소득세가 없겠지만 퇴직금이 고액이라면 그만큼 내야할 세금이 많아지게 되겠습니다.

 

만약, 퇴직금 소득세를 절약 하고 싶다면

퇴직금IRP 연금저축형으로 퇴직금을 받을 경우 세금이 더욱 절세 된다고 하니 관심 있으신 분들은 퇴직금IRP에 대해서  확인 보시면 도움이 되실 것 같습니다.

 

■ 엑셀 퇴직금 자동 계산기 만드는 수식 작성 방법.

퇴직금 계산을 위해 작성한 임의의 양식 첫번째 시트

퇴직 소득세 계산을 위한 공제액과 2021년 종합소득 기본세율표를 수식 작성을 편리하게 구간표로 변경해 놓은 두번째 시트

우선 퇴직 소득세를 계산하기 위한 두번째 시트는 근속연수 구간별 공제액과 환산급여 구간별 공제액, 종합소득 기본세율표를 수식을 참조할 수 있도록 텍스트는 빼고 숫자와 세율만을 표로 작성한 것이니 자세한 설명은 제외 하기로 하겠습니다.

첫번째 시트인 퇴직금과 퇴직 소득세를 계산하기 위한 양식에 작성된 수식을 살펴 보면 입사일, 퇴사일, 퇴직일 이전 3개월 급여와 퇴직일 이전 3개월 상여금, 퇴직일 이전 미지급 연차수당, 퇴직금 중간정산 금액을 기본적으로 입력해 주시면 되겠습니다.

총 근로기간을 일수로 표기한 B2:C2셀의 수식은 다음과 같이 간단하게 작성 할 수 있겠습니다.

=DATEDIF(B1,D1,"d")

 

총 근로기간의 년수와 개월수를 계산한 수식은 다음과 같이 작성 할 수 있겠습니다.

B3셀은 년수를 계산한 셀로 =DATEDIF(B1,D1,"y") 작성하고, C3셀은 개월수를 계산한 셀로 =(DATEDIF(B1,D1,"m")-(B3*12)) 작성하면 총 근로기간의 년수와 개월수를 표기 할 수 있겠습니다.

 

다음으로 퇴직일 이전 3개월의 연도는 B7, C7, D7에 각각 다음과 같이 각각의 셀에 수식을 작성 하여 표기 할 수 있겠습니다.

=YEAR(EDATE($D$1,-3))

=YEAR(EDATE($D$1,-2))

=YEAR(EDATE($D$1,-1))

 

퇴직일 이전 3개월의 월을 표기한 B8, C8, D8셀에는 다음과 같이 각가의 셀에 수식을 작성해 주시면 되겠습니다.

=MONTH(EDATE($D$1,-3))

=MONTH(EDATE($D$1,-2))

=MONTH(EDATE($D$1,-1))

 

다음으로 퇴직일 이전 3개월의 해당 월의 일수를 표기하는 수식으로 다음과 같이 수식을 작성할 수 있겠습니다.

=DATEDIF(DATE(B7,B8,1),DATE(C7,C8,1),"D")

=DATEDIF(DATE(C7,C8,1),DATE(D7,D8,1),"D")

=DATEDIF(DATE(D7,D8,1),EDATE(DATE(D7,D8,1),1),"D")

 

이제 기본 데이터를 입력하고 수식으로 계산된 데이터를 기준으로 1일 평균 임금을 작성하는 수식은 다음과 같이 작성 할 수 있겠습니다.

=ROUNDDOWN(SUM($B$10:$D$10)/SUM(B9:D9),0)+ROUNDDOWN(((SUM($B$11:$D$11)*(3/12))/SUM($B$9:$D$9)),0)+ROUNDDOWN(((SUM($B$12:$D$12)*(3/12))/SUM($B$9:$D$9)),0)

수식이 길게 작성되어 있기 때문에 복잡해 보일 수 있지만, RoundDown함수와 Sum함수를 이용해서 작성된 기본 데이터들을 사칙연산 해 준것 이므로 간단하게 작성된 수식입니다.

 

다음은 1일 평균임금을 계산한것을 기준으로 퇴직금을 계산할 수 있습니다. 퇴직금 계산 공식은 ((평균임금 * 30일) * 총 재직일수) / 365 으로 공식에 수식을 단순히 입혀보면 다음과 같이 작성 할 수 있겠습니다.

=ROUNDDOWN($B$14*30*($B$2/365),0)

 

위 퇴직금을 계산했다면 퇴직금 중간정산을 받은 경우를 고려해서 중간정산 받은 퇴직금 금액을 차감해 주시면 되겠습니다만, 만약, 퇴직금 지급 조건인 총 재직일수가 1년 미만일 경우 값을 텍스트로 표기 하기 위해서 IF함수를 이용해서 다음과 같이 수식을 작성 할 수 있겠습니다.

=IF(DATEDIF(B1,D1,"y")>=1,ROUNDDOWN(SUM(B15-B16),-1),"재직기간이
1년 미만 입니다.")

 

근속연수 공제액을 계산하기위한 수식은 IF함수와 And함수, Index Match함수를 이용해서 두번째 시트의 근속연수 구간별 표를 참조해서 다음과 같이 수식을 작성할 수 있겠습니다.

=IF(AND($B$4>=1,$B$4<=5),300000*$B$4,
(INDEX('퇴직소득세 공제율'!$H$3:$H$6,MATCH(Sheet1!$B$4,'퇴직소득세 공제율'!$E$3:$E$6,1))*($B$4+INDEX('퇴직소득세 공제율'!$I$3:$I$6,MATCH($B$4,'퇴직소득세 공제율'!$E$3:$E$6,1))))
+(INDEX('퇴직소득세 공제율'!$G$3:$G$6,MATCH($B$4,'퇴직소득세 공제율'!$E$3:$E$6,1))))

총 재직일수가 5년 이하라면 30만원 * 근속연수 금액으로 계산하고, 재직일수가 5년을 초과 한다면 근속연수 구간별 참조표를 참조하여 계산하라는 수식이 되겠습니다.

 

환산급여 공제액을 계산하기 위한 수식은 두번째 시트의 구간 표를 참조하기 위한 수식을 작성 해야 하기 때문에 기본적으로 구간 값을 구할 수 있는 Index Match 함수를 생각할 수 있겠습니다. 해당 수식은 다음과 같이 작성할 수 있겠습니다.

=ROUNDDOWN((($B$17-$D$15-INDEX('퇴직소득세 공제율'!$H$10:$H$14,MATCH($D$15,'퇴직소득세 공제율'!$E$10:$E$14,1)))*(INDEX('퇴직소득세 공제율'!$I$10:$I$14,MATCH($D$15,'퇴직소득세 공제율'!$E$10:$E$14,1))/100))+(IF($D$15<=0,0,INDEX('퇴직소득세 공제율'!$G$10:$G$14,MATCH($D$15,'퇴직소득세 공제율'!$E$10:$E$14,1)))),-1)

 

종합소득세율 역시 구간 표를 참조해서 계산하기 때문에 Index Match함수를 이용할 수 있습니다만, 퇴직금 - 근속연수 공제액 - 환산급여 공제액의 결과값이 -(마이너스)일 경우에는 차감할 소득세가 없기 때문에 IF함수와 함께 작성해 주시면 되겠습니다. 해당 수식은 다음과 같이 작성할 수 있겠습니다.

=ROUNDDOWN(
IF(SUM($B$17-$D$15-$D$16)<=0,0,
(SUM($B$17-$D$15-$D$16)-(INDEX('퇴직소득세 공제율'!$G$19:$G$26,MATCH($B$17-$D$15-$D$16,'퇴직소득세 공제율'!$E$19:$E$26,1)))))*
IF(SUM($B$17-$D$15-$D$16)<=0,0,
(INDEX('퇴직소득세 공제율'!$H$19:$H$26,MATCH($B$17-$D$15-$D$16,'퇴직소득세 공제율'!$E$19:$E$26,1))/100)),-1)

 

마지막으로 퇴직금 - 소득세(세금)의 실제 지급해야할 퇴직금을 원단위 절사하여 계산하면 퇴직금과 퇴직소득세 그리고 실제 지급해야할 퇴직금까지 수식으로 모두 작성할 수 있겠습니다. 원단위 절사하여 실 지급해야할 퇴직금 계산 수식은 다음과 같이 작성 할 수 있겠습니다.

=IFERROR(ROUNDDOWN(SUM(B17-D17),-1),0)

---

이상으로 퇴직금 계산 방법과 퇴직금 계산기, 퇴직 소득세 계산등에 대해서 확인해 보았습니다.

수식과 함수를 말씀 드리면서 Sum함수와 같은 기본적인 함수들은 별도로 추가 설명은 드리지 않았습니다만, 기본적인 함수들에 대해서도 설명이 필요하다면 블로그내 엑셀 함수를 설명드린 글을 확인해 주시면 도움이 되실 수 있습니다.

오늘 내용도 많은 직장인분들과 사업주 분들, 그리고 엑셀로 퇴직금을 계산하고자 하시는 분들께 많은 도움이 될 수 있기를 바라겠습니다.

수식 질문, 요청, 수정사항은 댓글로 남겨주세요.

그럼 오늘 하루도 좋은 하루 되시길 바라겠습니다.^^!

---

▼ 퇴직금 계산 엑셀 서식 다운로드

퇴직금 계산기 샘플 양식.xlsx

0.02MB