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

Excel에서 대출 상환 스케줄을 만드는 것은 대출 상환을 효과적으로 시각화하고 관리할 수 있는 유용한 기술입니다. 상환 스케줄은 일반적으로 주택 담보 대출이나 자동차 대출과 같은 상환 대출에 대한 각 정기 지불 내역을 보여주는 표입니다. 이 표는 각 지불 금액을 이자와 원금으로 나누어 보여주며, 매 지불 후 남은 잔액도 표시합니다. 이제 Excel에서 이러한 스케줄을 만드는 단계별 가이드를 살펴보겠습니다.

상환 스케줄이란 무엇인가요?

Excel에서 상환 스케줄 만들기

가변적인 기간에 따른 상환 스케줄 만들기

추가 상환을 포함한 상환 스케줄 만들기

Excel 템플릿을 사용하여 추가 상환을 포함한 상환 스케줄 만들기

상환 스케줄이란 무엇인가요?

상환 스케줄은 대출 상환 과정을 시간이 지남에 따라 보여주는 세부 표로, 대출 계산에 사용됩니다. 상환 스케줄은 일반적으로 모기지, 자동차 대출 및 개인 대출과 같은 고정 금리 대출에 사용되며, 대출 기간 동안 지불 금액은 일정하게 유지되지만 이자와 원금 간의 비율은 시간이 지남에 따라 변합니다.

Excel에서 대출 상환 스케줄을 생성하기 위해 PMT, PPMT 및 IPMT와 같은 내장 함수들이 필수적입니다. 각 함수의 역할을 살펴보겠습니다:

• PMT 함수: 이 함수는 일정한 지불과 일정한 이자율을 기반으로 대출에 대한 매 기간 총 지불 금액을 계산하는 데 사용됩니다.
• IPMT 함수: 이 함수는 특정 기간에 대한 지불 금액 중 이자 부분을 계산합니다.
• PPMT 함수: 이 함수는 특정 기간에 대한 지불 금액 중 원금 부분을 계산하는 데 사용됩니다.

Excel에서 이러한 함수들을 사용하면 각 지불 금액의 이자와 원금 구성 요소뿐만 아니라 매 지불 후 남은 대출 잔액까지 상세히 보여주는 상환 스케줄을 만들 수 있습니다.

Excel에서 상환 스케줄 만들기

이 섹션에서는 Excel에서 상환 스케줄을 만드는 두 가지 방법을 소개하겠습니다. 이러한 방법들은 다양한 사용자 선호도와 기술 수준에 맞춰져 있어, Excel 숙련도에 관계없이 누구나 자세하고 정확한 대출 상환 스케줄을 성공적으로 작성할 수 있습니다.

수식을 사용하면 기본 계산에 대한 깊은 이해를 얻을 수 있으며, 특정 요구 사항에 맞게 스케줄을 사용자 정의할 수 있는 유연성을 제공합니다. 이 접근 방식은 각 지불 금액이 어떻게 원금과 이자로 분해되는지 명확히 이해하고 직접 경험하길 원하는 사람들에게 이상적입니다. 이제 Excel에서 상환 스케줄을 만드는 과정을 단계별로 살펴보겠습니다.

⭐️ 1단계: 대출 정보와 상환 테이블 설정하기

1. 아래 스크린샷과 같이 연간 이자율, 대출 기간(년), 연간 지불 횟수 및 대출 금액과 같은 관련 대출 정보를 셀에 입력하세요:
   
2. 그런 다음 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로 엑셀의 마법을 풀다

• 스마트 실행: 셀 작업 수행, 데이터 분석 및 차트 생성 - 간단한 명령어로 모든 것을 처리합니다.
• 사용자 정의 수식: 작업을 간소화하기 위한 맞춤형 수식을 생성합니다.
• VBA 코딩: 손쉽게 VBA 코드를 작성하고 실행합니다.
• 수식 해석: 복잡한 수식도 쉽게 이해할 수 있습니다.
• 텍스트 번역: 스프레드시트 내 언어 장벽을 허물어 보세요.

AI 기반 도구로 엑셀 활용 능력을 강화하세요. 지금 다운로드하여 그 어느 때보다 높은 효율성을 경험해 보세요!

가변적인 기간에 따른 상환 스케줄 만들기

이전 예제에서는 고정된 지불 횟수에 대한 대출 상환 스케줄을 만들었습니다. 이 방법은 조건이 변경되지 않는 특정 대출이나 모기지 처리에 완벽합니다.

하지만, 다양한 기간을 가진 대출에 반복적으로 사용할 수 있는 유연한 상환 스케줄을 만들고자 한다면, 다양한 대출 시나리오에 따라 지불 횟수를 수정할 수 있도록 더 자세한 방법을 따라야 합니다.

⭐️ 1단계: 대출 정보와 상환 테이블 설정하기

1. 아래 스크린샷과 같이 연간 이자율, 대출 기간(년), 연간 지불 횟수 및 대출 금액과 같은 관련 대출 정보를 셀에 입력하세요:
   
2. 그런 다음 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 셀에 기간, 예정된 지불 금액, 추가 지불, 총 지불 금액, 이자, 원금, 남은 잔액과 같은 지정된 레이블을 설정합니다;
   • 기간 열에 대출에 대해 고려할 수 있는 최대 지불 횟수를 입력합니다. 예를 들어, 0에서 360까지의 숫자를 채웁니다. 이는 월별 지불을 하는 경우 표준 30년 대출을 커버할 수 있습니다;
   • 기간 0(우리의 경우 9행)에 대해, 초기 대출 금액에 해당하는 잔액 값을 다음 수식으로 가져옵니다 =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 키를 누릅니다. 그런 다음 가장 적합한 템플릿을 선택하여 클릭합니다. 예를 들어, 여기서는 Simple loan calculator 템플릿을 선택하겠습니다. 스크린샷 참조:
   
2. 템플릿을 선택한 후, Create 버튼을 클릭하여 새 통합 문서로 엽니다.
3. 그런 다음 자신의 대출 세부 정보를 입력하면, 템플릿이 자동으로 계산하고 스케줄을 작성합니다.
4. 마지막으로, 새로운 상환 스케줄 통합 문서를 저장합니다.