엑셀 함수 수식을 활용하여 다양한 업무 형태에 효율적으로 적용할 수 있는 자동 서식을 만들 수 있습니다.
이번 글에서는 퇴직금 계산을 자동으로 추출할 수 있는 엑셀 자동 서식을 만들면서 사용되는 함수와 수식을 활용하여 사용자의 환경에 맞도록 활용하여 업무 효율을 증대 시킬 수 있도록 하면 좋을것 같습니다.
※ 단, 필자는 노무사가 아니기 때문에 일반적 퇴직금 계산 방법으로 설명 및 계산과 관련한것으로 급여 및 퇴직금 계산시 근로자와 사용주간의 불미스러운 마찰이 생길 요지가 있을 수 있으므로 해석이 애매한 부분은 반듯이 노무사와 상담을 진행하는것이 좋겠습니다.
퇴직금 지급 기한과 계산 방법및 적용 세율.
퇴직금 자동 계산 서식과 수식을 작성해 보기 전에 퇴직금 계산방법, 지급 기준과 기한, 적용 세율을 먼저 확인해 보도록 합니다.
퇴직금 지급 기준과 기한.
퇴직금 지급은 근로자가 퇴직한 경우 지급 사유가 발생한날로부터 14일안에 퇴직금을 지급하는것을 원칙으로 하고 있습니다.
그러나 일반적으로 근로 계약서에 퇴직금 지급일자를 명시해 두거나 회사와 근로자간의 합의가 이루어진경우 급여지급일자와 퇴직금을 함께 지급하는 경우가 많다고 하겠습니다.
퇴직금을 지급하기 위한 조건으로는 ① 1년 이상 계속적으로 근로가 이루어져야 하고 ② 소정근로시간이 1주일간 15시간 이상이어야 합니다.
위 두가지 조건을 충족했을 경우 퇴직금 지급 기준에 부합한다고 볼 수 있겠습니다.
| 1년 이상 계속적으로 근로가 이루어져야 하는 기준에 있어서 보통은 입사일로부터 퇴직일자까지 기간을 기준으로 하고 있지만 세부적으로 그 기간을 계산한다면 근로 기간중 발생하는 휴직기간으로 직무와 관련된 휴직인경우 근로기간으로 인정하고 있으며 질병, 학업등의 직무와 관련이 없는 휴직기간은 근로기간으로 산정하지 않는다고 볼 수 있습니다.
하지만 질병, 학업등의 직무와 관련이 없는 휴직기간이라 할지라도 유급휴직인경우 근로기간에 포함시켜 산정할 수 있기 때문에 일반적으로 입사일로부터 퇴직일자까지의 기간을 1년 이상 계속적 근로 기간으로 산정하는 경우가 많다고 할 수 있습니다. |
퇴직금 총액 계산 방법.
퇴직금의 지급액은 30일 이상의 평균임금을 퇴직금으로 지급해야 합니다.
평균임금이란 퇴직금을 산정해야할 사유가 발생한 날로부터 이전 3개월동안 근로자에게 지급된 급여 총액을 총 일수로 나눈 금액으로 1일 평균 급여로 계산한것을 평균임금이라고 할 수 있습니다.
따라서 퇴직급여 총액은 다음과 같이 계산할 수 있습니다.
| (평균임금 x 30일) x (총 재직일수/365) |
다만, 퇴직예정자가 의도적으로 평균임금을 상승시킬 요소가 있기 때문에 고정적으로 지급되는 이외의 급여로 상여, 연장수당의 경우에는 상여 또는 연장수당급여등에 해당하는 (총급액*(3/12))/퇴직 직전 3개월간 재직일수로 계산하여 평균임금에 합산하여 계산할 수 있습니다.
실제로 이러한 평균임금을 산출하는 과정에서 퇴직자와 사용주간에 마찰이 종종 일어나게 되므로 다시 한번 회사와 관계한 전문 노무사분과 상의를 거친 후 퇴직급여를 산출하는것이 도움이 될것입니다.
퇴직금 소득세 계산 방법.
급여 및 소득에 대해서 세금을 공제해야 하는데 퇴직금도 소득으로 볼 수 있기 때문에 산출한 퇴직금에서 퇴직 소득세를 원천징수 한 이후 지급해야할것이므로 공제 세액을 계산해야 합니다.
퇴직금 소득세를 산출하기 위해서는 퇴직급여 총액에서 근속연수 공제액을 공제한 금액으로 환산급여를 산출한 후에 환산급여 공제 구간에 해당하는 공제액을 환산급여에서 추가로 공제하고 산출된 금액을 과세표준으로 하여 종합소득세 세율표에 따라 퇴직 소득세를 계산할 수 있습니다.
| ① 퇴직급여총액 – 근속연수 공제액 = 퇴직소득
(퇴직급여총액 = (평균임금 x 30) x (총재직일수/365) ② 퇴직소득 x (12 / 근속연수) = 환산급여 (퇴직소득 = 퇴직급여총액 – 근속연수 공제액) ③ 환산급여 – 환산급여 공제액 = 과세표준 ④ (과세표준 x 기본세율) – 누진공제액 = 환산산출세액 ⑤ (환산산출세액/12개월) x 근속연수 |
근속연수 공제액, 환산급여등 “국세청 → 국세신고안내 → 원천세 → 퇴직소득“에서 확인할 수 있습니다.
1. 근속연수 공제액.
근속연수 공제액이 2023년 이후부터 확대 되면서 퇴직소득세 절감 혜택을 받을 수 있습니다.
5년 이하 근무시 적용되던 근속연수 공제액은 30만원 x 근속연수 → 100만원 x 근속연수로 산출함으로써 동일한 기간 근무시 23년 이전 퇴직자보다 23년 이후 퇴직자가 70만원 더 많은 세제 혜택을 받게될 수 있습니다.
2. 환산급여 공제.
위에서도 설명했듯이 환산급여는 (퇴직급여-근속연수공제액) x (12개월/근속연수)로 계산하여 환산급여 구간에 해당하는 공제액을 차감하여 과세표준을 산출 할 수 있습니다.
3. 과세표준.
(과세표준으로 산출된 금액에 해당하는 금액) x 세율 – 누진공제액으로 환산세액을 산출할 수 있습니다.
4. 퇴직 소득세 최종 세액 산출.
(환산세액산출액 / 12개월) x 근속연수로 퇴직급여에 대한 최종 소득세를 계산할 수 있습니다.
위와같이 4단계에 걸쳐 퇴직소득세액을 산출할 수 있고 최종적으로 지급되어야할 퇴직금을 계산할 수 있습니다.
퇴직금 계산 엑셀 예제 서식.
위에서 퇴직금을 계산하기 위한 기준, 계산 방법들을 확인해 본것을 토대로 퇴직금 계산 예제 서식을 “퇴직급여 지급 내역서” 제목으로 서식을 작성해 보았습니다.
음영처리된 셀 연한 파란색은 제목, 연한 핑크색은 수식이 작성된셀로 직접 입력해줘야 하는 값을 입력하면 자동으로 결과값을 계산할 셀입니다.
직접 입력해줘야 하는 데이터로는 이름, 입사일, 퇴사일, 급여와 기타 급여를 입력하게 되면 퇴직금 계산에 필요한 나머지값들은 수식으로 계산된 결과값으로 자동으로 퇴직금 지급 내역에 대한 데이터값을 산출할 수 있게 됩니다.
단, 급여항목 C12:E12셀에 입력할 급여는 비급여를 제외한 금액으로 입력하는것을 원칙으로 합니다.
그러나 현실적으로 세금을 줄이기 위해 총 급여에서 비급여 항목으로 식대, 주유비와 같은 항목을 분류 하여 지급하는것이 일반적이기 때문에 이러한 항목 역시 전체 급여로 책정해서 입력하는경우도 다수 있다고 볼 수 있습니다.
비급여 항목을 설명하기에는 내용이 길어질 수 있고 필자는 노무사가 아니기 때문에 절대 이렇다라고 단정지어 말하기는 어려운 부분으로 원칙을 고수하고자 하시는분들께서는 회사와 관계된 노무사분과 상의 해 보시면 좋을것 같습니다.
예제 서식은 2개의 시트로 “퇴직금지급내역서”, “퇴직소득공제”로 분리 되어 있고 퇴직금지급내역서 시트에서는 이미지에서 보는것과 같이 퇴직급여와 관련한 산출금액들을 확인할 수 있고 퇴직소득공제시트에서는 작성된 수식에서 근속연수공제, 환산급여공제, 과세표준을 참조하여 결과값을 계산할 수 있도록 각각의 구간에 대한 표를 작성해 두었습니다.
만약 공제 구간이 새롭게 변경된다면 수식을 수정하지 않고 퇴직소득공제 시트의 표에 작성된 구간값들의 수치만 수정해 주는것만으로도 계속 사용이 가능할것입니다.
엑셀 퇴직금 지급내역서 샘플 서식.xlsx 다운로드첨부된 파일에서 수식이 작성되어 있는 셀에 데이터를 입력하여 수식이 지워지는것을 방지 하기 위해서 시트보호 설정이 되어 있습니다. 비밀번호 설정은 없으며 잠금되어 있는 셀보호를 해제 하기 위해서는 “검토탭 → 시트보호해제“로 입력이 제한된 셀을 해제 할 수 있습니다. |
계산수식.
사용자 환경에 맞는 서식으로 수정했을때 수식을 함께 수정해야 하는 경우가 생길 수 있기 때문에 예제 서식에서 작성되어 있는 수식들을 하나씩 확인해도록 하겠습니다.
1. 총재직일수, 근속연수.
날짜와 날짜 사이의 기간을 계산하는 함수는 DATEDIF함수로 수식을 작성할 수 있습니다.
=DATEDIF($C$3,$C$4,”d”)
=DATEDIF($C$3,$C$4,”y”) |
동일하게 DATEDIF함수 수식으로 작성되었지만 C5셀은 날짜 기간을 일수로 계산하기 위해서 표시형식 “D”로 인수를 작성하고, C6셀은 날짜 기간을 연수로 계산하기 위해서 표시형식 “Y”로 인수를 작성합니다.
2. 퇴직일 직전 3개월 연, 월, 재직일수.
퇴직일 직전 3개월 연, 월, 재직일수를 수식으로 작성하여 데이터값이 자동으로 입력되도록 합니다.
=YEAR(EDATE($C$4,-1))
=MONTH(EDATE($C$4,-1))
=DATEDIF(DATE(E9,E10,1),EDATE(DATE(E9,E10,1),1),”D”) |
3. 1일 평균 임금 및 퇴직 급여 계산 수식.
1일 평균 임금 계산 방법은 “퇴직일 이전 3개월 (급여총액/총재직일수)”로 계산할 수 있습니다.
다만 이렇게 산출한 퇴직급여는 상황에 따라 퇴직자가 퇴직일 이전 3개월동안 연장 근로등으로 평균급여를 상승시킬 요소가 발생하기 때문에 통상급여 이외에 발생하는 상여등의 급여항목은 3/12로 계산하여 산출하도록 합니다.
=ROUNDDOWN(SUM($C$12:$E$12)/SUM($C$11:$E$11),0) ▶ ① ROUNDDOWN(SUM($C$12:$E$12)/SUM($C$11:$E$11),0) → 퇴직일자 이전 3개월 (급여총액 / 재직일수), 원단위절사. ▶ ② ROUNDDOWN((SUM($C$13:$E$13)*(3/12))/SUM($C$11:$E$11),0) → 퇴직일자 이전 3개월 ((상여총액)*(3/12)) / 3개월간 재직일수, 원단위 절사. ▶ ③ ROUNDDOWN((SUM($C$14:$E$14)*(3/12))/SUM($C$11:$E$11),0) → 퇴일일자 이전 3개월 ((연차수당 미지급 총액)*(3/12)) / 3개월간 재직일수, 원단위 절사. ※ 15, 16, 17행에는 기타급여로 해당 항목이 있을 경우 항목을 수정하고 급여액을 입력합니다. 이때 매월 정기적으로 지급되는 급여 항목이라면 ①번과 같은 수식으로 참조범위만 달리하고, 상여등과 같이 불특정월에 지급되는 항목의 경우에는 ②번과 같은 수식으로 참조범위만 달리하여 수식을 작성하도록 합니다.
=ROUNDDOWN($C$19*30*($C$5/365),-1) ▶ 퇴직급여 계산은 “1일평균임금*30일(총재직일수/365)”로 계산할 수 있고, Rounddown함수로 원단위는 절사하여 계산할 수 있습니다. |
4. 근속연수공제, 환산급여, 환산급여공제, 과세표준, 환산산출세액, 산출세액 계산 수식.
예제서식 두번째 시트 “퇴직소득공제”시트에는 근속연수공제, 환산급여구간별공제, 과세표준구간별 종합소득 기본세율표를 작성한 수식에서 참조하여 결과값을 계산 할 수 있도록 작성해 두었습니다.
따라서 각각의 공제 구간이 추가되지 않고 각각의 구간에서 공제되는 금액등이 변경된다면 “퇴직소득공제”시트 표에서 변경된 내용의 수치만 입력해 주면 별도로 수식을 수정하지 않아도 될것입니다.
=IF($C$6<=5,INDEX(퇴직소득공제!$G$3:$G$6,MATCH(퇴직금지급내역서!$C$6,퇴직소득공제!$E$3:$E$6,1))*$C$6, +((퇴직금지급내역서!$C$6+INDEX(퇴직소득공제!$I$3:$I$6,MATCH($C$6,퇴직소득공제!$E$3:$E$6,1))) ▶ 근속연수가 5년 이하인 경우 “퇴직소득공제”시트에서 근속연수에 해당하는 공제액x근속연수 계산값을 반환.
=ROUNDDOWN(IF((($E$19-$C$22)/C6)*12<=0,0,(($E$19-$C$22)/C6)*12),0) ▶ ((퇴직급여 – 근속연수공제액)/근속연수)*12개월
=ROUNDDOWN( ▶ 환산급여공제표에서 환산급가 해당하는 추가공제금액을 찾아 차감하고
=ROUNDDOWN(SUM($C$23-$C$24),0) ▶ 환산급여 – 환산급여 공제액 반환.
=ROUNDDOWN( ▶ 과세표준 금액이 해당하는 구간을 찾아 “(과세표준 x 기본세율) – 누진공제액” 계산식을 적용한 값을 반환.
=ROUNDDOWN(SUM($E$23/12*$C$6),0) ▶ (환산산출세액/12개월)*근속연수
=$E$19-$C$25 ▶ 퇴직급여 – 퇴직소득세 |
글을 마치며…
퇴직금을 지급하기 위해서 퇴직급여와 퇴직급여에서 공제되어야 하는 퇴직소득세를 공제하여 계산해 주는 자동서식을 작성해 보았습니다.
하지만 현실적으로 퇴직금과 관련하여 사용주와 근로자간의 불미스러운 마찰이 발생하곤 하는데 주로 평균임금을 계산하는데 있어 ① 통상임금의 범주를 어디까지로 볼것인가 혹은 ② 평균 임금을 계산하는것 자체에서 의문을 품는 경우도 생길 수 있습니다.
또한 근로 형태가 복잡한 경우 퇴직급여를 산출하는 방식이 달라질수도 있는만큼 이러한 마찰이 예상되는 경우에는 회사와 관계된 전문 노무사분과 함께 상의해서 퇴직급여를 산출하는것이 좋을것입니다.
다만 근속연수공제, 환산급여, 환산급여공제, 과세표준, 환산산출세액, 산출세액과 관련하여서는 국세청에서 그 구간별 공제액과 계산 방법이 정확히 명시되어 있는만큼 이에 대한 부분으로는 큰 문제가 없을것입니다.
또한 필자가 전문 노무사 자격을 갖춘 사람은 아니고 엑셀과 관련된 글을 작성하는 목적은 우리가 업무 효율을 증대 시킬 수 있도록 엑셀 함수와 수식을 작성하여 활용할 수 있도록 정보를 전달하는데 있다고 하겠습니다.
마지막으로 모두 본문에서 언급한 내용이지만 간단하게 퇴직급여 계산 방식을 정리하면 다음과 같습니다.
- 퇴직급여, 퇴직소득세 원단위 절사.
- 환산급여 = ((퇴직급여 – 근속연수공제액) / 근속연수) x 12개월
- 과세표준 = 환산급여 – 환산급여공제액
- 환산산출세액 = (과세표준 x 기본세율) – 누진공제액
- 산출세액 = (환산산출세액 / 12개월) x 근속연수
- 실지급 퇴직급여 = 퇴직급여 – 산출세액
