Excel 에서 대출 상각 스케줄 만들기 – 단계별 튜토리얼

Excel 에서 대출 상각 스케줄을 만드는 것은 매우 유용한 기술로, 대출 상환 내역을 시각화하고 효과적으로 관리할 수 있게 해줍니다. 상각 스케줄은 상각 대출(주로 주택담보대출이나 자동차 대출)의 각 기간별 지급 내역을 상세히 보여주는 테이블입니다. 이 테이블은 각 지급액을 이자와 원금 구성 요소로 분할하여 매 지급 후 남은 잔액을 표시합니다. 이제 Excel 에서 이러한 스케줄을 만드는 단계별 가이드를 살펴보겠습니다。

상각 일정이란 무엇인가요？

Excel 에서 상각 일정 만들기

가변 기간 수의 상각 일정 만들기

추가 상환 포함 상각 일정 만들기

Excel 템플릿을 사용하여 추가 상환 포함 상각 일정 만들기

상각 일정이란 무엇인가요？

상각 스케줄은 대출 상환 과정을 시간 순서대로 보여주는 상세한 테이블로， 대출 계산에 사용됩니다。 상각 스케줄은 주택담보대출， 자동차 대출， 개인 대출 등과 같이 대출 기간 동안 지급액이 일정하지만 이자와 원금 비중이 시간이 지남에 따라 변하는 고정 금리 대출에 자주 사용하는입니다。

Excel 에서 대출 상각 스케줄을 만들기 위해서는 내장 함수인 PMT, PPMT, IPMT 가 필수적입니다。 이제 각 함수의 역할을 살펴보겠습니다：

• PMT 함수： 이 함수는 일정한 지급액과 일정한 이자율을 기준으로 대출의 각 기간별 총 지급액을 계산하는 데 사용됩니다。
• IPMT 함수： 이 함수는 특정 기간의 지급액 중 이자 부분을 계산합니다。
• PPMT 함수： 이 함수는 특정 기간의 지급액 중 원금 부분을 계산하는 데 사용됩니다。

이러한 함수를 Excel 에서 사용하면 각 지급액의 이자 및 원금 구성 요소와 매 지급 후 남은 대출 잔액을 표시하는 상세한 상각 스케줄을 만들 수 있습니다。

Excel 에서 상각 일정 만들기

이 섹션에서는 Excel 에서 상각 스케줄을 만드는 두 가지 서로 다른 방법을 소개합니다。 이 방법들은 사용자의 선호도와 숙련도 수준에 따라 다양하게 적용 가능하며， Excel 활용 능력에 관계없이 누구나 정확하고 상세한 대출 상각 스케줄을 성공적으로 작성할 수 있도록 지원합니다。

수식을 사용하면 기본 계산 방식을 더 깊이 이해할 수 있으며, 특정 요구 사항에 따라 스케줄을 자유롭게 맞춤 설정할 수 있습니다. 이 접근법은 직접 실습을 통해 각 지급액이 어떻게 이자와 원금으로 분할되는지를 명확히 파악하고자 하는 사용자에게 이상적입니다. 이제 Excel 에서 상각 스케줄을 만드는 과정을 단계별로 살펴보겠습니다：

⭐️ 단계 1: 대출 정보 및 상각 테이블 설정

1. 다음 스크린샷과 같이 연이율， 대출 기간(년)， 연간 지급 횟수 및 대출 금액과 같은 관련 대출 정보를 셀에 입력합니다：
   
2. 그런 다음 Excel 에서 기간, 지급액, 이자, 원금, 잔액 등의 레이블을 포함한 상각 테이블을 셀 A7:E7 에 만듭니다。
3. 기간 열에 기간 번호를 입력합니다. 이 예에서는 총 지급 횟수가 24 개월(2 년)이므로 기간 열에 1 부터 24 까지의 숫자를 입력합니다。 스크린샷 참조：
   
4. 레이블과 기간 번호로 테이블을 설정한 후에는 대출 조건에 따라 지급액， 이자， 원금 및 잔액 열에 수식과 값을 입력할 수 있습니다。

⭐️ 단계 2: PMT 함수를 사용하여 총 지급액 계산

PMT 의 구문은 다음과 같습니다：

셀 B7 에 다음 수식을 입력한 후 채우기 핸들을 아래로 드래그하여 다른 셀에도 이 수식을 복사합니다。 그러면 모든 기간에 대해 일정한 지급액이 표시됩니다。 스크린샷 참조：

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ 단계 3: IPMT 함수를 사용하여 이자 계산

이 단계에서는 Excel 의 IPMT 함수를 사용하여 각 지급 기간의 이자를 계산합니다。

셀 C7 에 다음 수식을 입력한 후 열 아래로 채우기 핸들을 드래그하여 각 기간의 이자를 계산합니다。

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ 단계 4: PPMT 함수를 사용하여 원금 계산

각 기간의 이자를 계산한 후， 상각 스케줄 작성을 위한 다음 단계는 각 지급액의 원금 부분을 계산하는 것입니다。 이를 위해 PPMT 함수를 사용하며， 이 함수는 일정한 지급액과 일정한 이자율을 기준으로 특정 기간의 지급액 중 원금 부분을 결정하도록 설계되었습니다。

IPMT 의 구문은 다음과 같습니다：

PPMT 수식의 구문과 매개변수는 앞서 설명한 IPMT 수식과 동일합니다。

셀 D7 에 다음 수식을 입력한 후 열 아래로 채우기 핸들을 드래그하여 각 기간의 원금을 입력합니다。 스크린샷 참조：

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ 단계 5: 잔액 계산

각 지급액의 이자와 원금을 모두 계산한 후， 상각 스케줄에서 다음 단계는 매 지급 후 남은 대출 잔액을 계산하는 것입니다。 이는 대출 잔액이 시간이 지남에 따라 어떻게 감소하는지를 보여주는 핵심 요소입니다。

1. 잔액 열의 첫 번째 셀(E7)에 다음 수식을 입력합니다。 이는 잔액이 원래 대출 금액에서 첫 번째 지급액의 원금 부분을 뺀 금액임을 의미합니다：

   =B4-D7

   
2. 두 번째 및 이후 모든 기간에 대해서는 이전 기간의 잔액에서 현재 기간의 원금 지급액을 빼서 잔액을 계산합니다. 셀 E8 에 다음 수식을 적용하세요：

   =E7-D8

   참고： 잔액 셀에 대한 참조는 상대 참조여야 하며， 수식을 아래로 드래그할 때 자동으로 업데이트되어야 합니다。
   
3. 그런 다음 채우기 핸들을 열 아래로 드래그합니다。 그러면 각 셀이 자동으로 조정되어 갱신된 원금 지급액을 기반으로 잔액을 계산합니다。
   

⭐️ 단계 6: 대출 요약 작성

상세한 상각 스케줄을 설정한 후에는 대출 요약을 만들어 대출의 핵심 내용을 빠르게 확인할 수 있습니다。 이 요약에는 일반적으로 대출 총비용과 총 이자 지급액이 포함됩니다。

● 총 지급액 계산：

=SUM(B7:B30)

● 총 이자 계산：

=SUM(C7:C30)

⭐️ 결과：

이제 간단하면서도 포괄적인 대출 상각 스케줄이 성공적으로 생성되었습니다。 스크린샷 참조：

KUTOOLS AI 와 함께 Excel 의 마법을 경험하세요

• 스마트 실행： 간단한 명령어로 셀 작업을 수행하고， 데이터를 분석하며， 차트를 생성하세요。
• 사용자 지정 수식： 워크플로우를 간소화하는 맞춤형 수식을 생성하세요。
• VBA 코딩： VBA 코드를 손쉽게 작성하고 구현하세요。
• 수식 해석： 복잡한 수식을 쉽게 이해하세요。
• 텍스트 번역： 스프레드시트 내에서 언어 장벽을 극복하세요。

AI 기반 도구로 Excel 기능을 강화하세요。지금 다운로드하고 전에 없던 효율성을 경험하세요！

가변 기간 수의 상각 일정 만들기

이전 예제에서는 고정된 지급 횟수에 대한 대출 상환 스케줄을 만들었습니다。 이 방법은 조건이 변경되지 않는 특정 대출이나 주택담보대출을 처리할 때 적합합니다。

하지만 다양한 기간의 대출에 반복적으로 사용할 수 있고， 서로 다른 대출 시나리오에 따라 필요에 따라 상환 횟수를 수정할 수 있는 유연한 상각 스케줄을 만들고자 한다면 보다 자세한 방법을 따라야 합니다。

⭐️ 1 단계： 대출 정보 및 상각 테이블 설정

1. 다음 스크린샷과 같이 연이율， 대출 기간(년)， 연간 지급 횟수 및 대출 금액과 같은 관련 대출 정보를 셀에 입력합니다：
   
2. 그런 다음 Excel 에서 기간, 지급액, 이자, 원금, 잔액 등의 레이블을 포함한 상각 테이블을 셀 A7:E7 에 만듭니다。
3. 기간 열에 고려할 수 있는 최대 지급 횟수를 입력합니다. 예를 들어, 1 부터 360 까지의 숫자를 입력합니다. 이렇게 하면 월별 지급을 가정할 때 표준 30 년 대출도 모두 커버할 수 있습니다。
   

⭐️ 2 단계： IF 함수로 상환액， 이자 및 원금 공식 수정

해당 셀에 다음 공식을 입력한 후 채우기 핸들을 끌어 설정한 최대 상환 기간까지 공식을 확장합니다。

● 상환 공식：

일반적으로 상환액 계산에는 PMT 함수를 사용합니다。 IF 문을 포함하려면 구문은 다음과 같습니다：

따라서 공식은 다음과 같습니다：

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● 이자 공식：

구문은 다음과 같습니다：

따라서 공식은 다음과 같습니다：

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● 원금 공식：

구문은 다음과 같습니다：

따라서 공식은 다음과 같습니다：

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ 3 단계： 잔액 공식 조정

잔액의 경우 일반적으로 이전 잔액에서 원금을 차감합니다。 IF 문을 사용하여 다음과 같이 수정합니다：

● 첫 번째 잔액 셀：(E7)

=B4-D7

● 두 번째 잔액 셀：(E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ 4 단계： 대출 요약 작성

수정된 공식으로 상각 스케줄을 설정한 후 다음 단계는 대출 요약을 만드는 것입니다。

● 총 지급액 계산：

=SUM(B7:B366)

● 총 이자 계산：

=SUM(C7:C366)

⭐️ 결과：

이제 Excel 에서 포괄적이고 동적인 상각 스케줄과 상세한 대출 요약이 완성되었습니다。 상환 기간을 조정할 때마다 전체 상각 스케줄이 자동으로 업데이트되어 변경 사항을 반영합니다。 아래 데모를 참조하세요：

추가 상환 포함 상각 일정 만들기

예정된 상환 외 추가 상환을 하면 대출을 더 빠르게 상환할 수 있습니다. Excel 에서 추가 상환이 포함된 상각 스케줄을 만들면 이러한 추가 상환이 대출 상환을 얼마나 앞당기고 총 이자 지급액을 얼마나 줄이는지 확인할 수 있습니다。 설정 방법은 다음과 같습니다：

⭐️ 1 단계： 대출 정보 및 상각 테이블 설정

1. 다음 스크린샷과 같이 연이율， 대출 기간(년)， 연간 지급 횟수， 대출 금액 및 추가 지급액과 같은 관련 대출 정보를 셀에 입력합니다：
   
2. 그런 다음 예정 지급액을 계산합니다。
   입력 셀 외에도 후속 계산을 위해 또 다른 사전 정의된 셀이 필요합니다. 즉, 추가 지급 없이 대출에 대해 정기적으로 지급해야 하는 금액입니다. 셀 B6 에 다음 수식을 적용하세요：

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. 그런 다음 Excel 에서 상각 테이블을 만듭니다：
   • A8:G8 셀에 Period, Schedule Payment, Extra Payment, Total Payment, Interest, Principal, Remaining Balance 와 같은 지정된 레이블을 설정하세요；
   • Period 열에 고려할 수 있는 최대 상환 횟수를 입력하세요. 예를 들어, 0 부터 360 까지 숫자를 채우세요. 이는 월별 상환을 가정할 때 일반적인 30 년 대출을 포함할 수 있습니다；
   • Period 0(이 경우 행 9)의 경우, 초기 대출 금액에 해당하는 B4 와 동일한 잔액 값을 다음 수식으로 가져오세요=B4。 이 행의 다른 모든 셀은 공백으로 남겨두세요。
   • 

⭐️ 2 단계： 추가 상환이 포함된 상각 스케줄용 공식 작성

해당 셀에 다음 공식을 하나씩 입력합니다。 오류 처리를 강화하기 위해 이 공식과 이후 모든 공식을 IFERROR 함수로 감쌉니다。 이를 통해 입력 셀이 비어 있거나 잘못된 값을 포함할 경우 발생할 수 있는 여러 오류를 방지할 수 있습니다。

● 예정 상환액 계산：

B10 셀에 다음 공식을 입력합니다：

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● 추가 상환액 계산：

C10 셀에 다음 공식을 입력합니다：

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● 총 상환액 계산：

D10 셀에 다음 공식을 입력합니다：

=IFERROR(B10+C10, "")

● 원금 계산：

E10 셀에 다음 공식을 입력합니다：

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● 이자 계산：

F10 셀에 다음 공식을 입력합니다：

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● 잔액 계산

G10 셀에 다음 공식을 입력합니다：

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

모든 공식을 완료한 후 B10:G10 셀 범위를 선택하고 채우기 핸들을 끌어 전체 상환 기간에 걸쳐 공식을 확장합니다. 사용하지 않은 기간의 셀에는 0 이 표시됩니다。 스크린샷 참조：

⭐️ 3 단계： 대출 요약 작성

● 예정 상환 횟수 확인：

=B2:B3

● 실제 상환 횟수 확인：

=COUNTIF(D10:D369,">"&0)

● 총 추가 상환액 확인：

=SUM(C10:C369)

● 총 이자 확인：

=SUM(F10:F369)

⭐️ 결과：

이러한 단계를 따르면 Excel 에서 추가 상환을 고려한 동적인 상각 스케줄을 만들 수 있습니다。

Excel 템플릿을 사용하여 상각 스케줄 만들기

Excel 템플릿을 사용하여 상각 스케줄을 만드는 것은 간단하고 시간 효율적인 방법입니다. Excel 은 각 상환의 이자， 원금 및 잔액을 자동으로 계산하는 기본 제공 템플릿을 제공합니다。 Excel 템플릿을 사용하여 상각 스케줄을 만드는 방법은 다음과 같습니다：

1. 클릭파일>새로 만들기， 검색 상자에상각 스케줄을 입력하고Enter키를 누릅니다。 그런 다음 요구 사항에 가장 적합한 템플릿을 클릭하여 선택합니다。 예를 들어， 여기에서는 간단한 대출 계산기 템플릿을 선택하겠습니다。 스크린샷 참조：
   
2. 템플릿을 선택한 후만들기버튼을 클릭하여 이를 새 워크북으로 엽니다。
3. 그런 다음 자신의 대출 세부 정보를 입력하면 템플릿이 자동으로 해당 입력값을 기반으로 스케줄을 계산하고 채워줍니다。
4. 마지막으로 새 상각 스케줄 통합 문서를 저장합니다。