Excel에서 대출 상환 일정을 작성하는 것은 대출 상환을 효과적으로 시각화하고 관리할 수 있는 귀중한 기술이 될 수 있습니다. 분할 상환 일정은 분할 상환 대출(일반적으로 모기지 대출 또는 자동차 대출)에 대한 각 정기 지불을 자세히 설명하는 표입니다. 각 지불액을 이자 및 원금 구성요소로 분류하여 각 지불 후 남은 잔액을 표시합니다. Excel에서 이러한 일정을 만드는 단계별 가이드를 살펴보겠습니다.

상각 일정이란 무엇입니까?

Excel에서 상환 일정 만들기

다양한 기간에 대한 상환 일정 생성

추가 지불로 분할 상환 일정 만들기

Excel 템플릿을 사용하여 분할 상환 일정(추가 지불 포함) 생성

상각 일정이란 무엇입니까?

상각 일정은 시간에 따른 대출 상환 과정을 표시하는 대출 계산에 사용되는 세부 테이블입니다. 상각 일정은 주택담보대출, 자동차 대출, 개인 대출과 같은 고정 금리 대출에 일반적으로 사용되며, 이 대출의 경우 지불 금액은 대출 기간 내내 일정하게 유지되지만 원금 대비 이자에 대한 지불 비율은 시간이 지남에 따라 변합니다.

Excel에서 대출 상환 일정을 작성하려면 실제로 내장된 PMT, PPMT 및 IPMT 기능이 중요합니다. 각 기능이 수행하는 작업을 이해해 보겠습니다.

• PMT 기능: 고정금리, 고정이자율을 적용한 대출에 대한 기간별 총 상환액을 계산하는 함수입니다.
• IPMT 기능: 이 함수는 특정 기간 동안 지불금의 이자 부분을 계산합니다.
• PPMT 기능: 특정 기간 동안 지급액의 원금을 계산하는 데 사용되는 함수입니다.

Excel에서 이러한 기능을 사용하면 각 지불 후 대출 잔액과 함께 각 지불의 이자와 원금 구성 요소를 표시하는 상세한 상각 일정을 만들 수 있습니다.

Excel에서 상환 일정 만들기

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

수식은 기본 계산에 대한 더 깊은 이해를 제공하고 특정 요구 사항에 따라 일정을 사용자 정의할 수 있는 유연성을 제공합니다. 이 접근 방식은 실제 경험을 갖고 각 지불금이 원금과 이자 구성 요소로 어떻게 분류되는지에 대한 명확한 통찰력을 원하는 사람들에게 이상적입니다. 이제 Excel에서 상각 일정을 만드는 과정을 단계별로 살펴보겠습니다.

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

1. 표시된 다음 스크린샷과 같이 연간 이자율, 대출 기간(년), 연간 지불 횟수 및 대출 금액과 같은 관련 대출 정보를 셀에 입력합니다.
   
2. 그런 다음 셀 A7:E7에 기간, 지불, 이자, 원금, 남은 잔액 등 지정된 레이블을 사용하여 Excel에서 상각 테이블을 만듭니다.
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)

⭐️ 결과:

이제 간단하면서도 포괄적인 대출 상환 일정이 성공적으로 생성되었습니다. 스크린샷을 참조하세요:

다양한 기간에 대한 상환 일정 생성

이전 예에서는 고정된 지불 횟수에 대한 대출 상환 일정을 생성했습니다. 이 접근 방식은 조건이 변경되지 않는 특정 대출이나 모기지를 처리하는 데 적합합니다.

그러나 다양한 기간의 대출에 반복적으로 사용할 수 있는 유연한 상환 일정을 만들어 다양한 대출 시나리오에 필요한 대로 지불 횟수를 수정할 수 있도록 하려면 보다 자세한 방법을 따라야 합니다.

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

1. 표시된 다음 스크린샷과 같이 연간 이자율, 대출 기간(년), 연간 지불 횟수 및 대출 금액과 같은 관련 대출 정보를 셀에 입력합니다.
   
2. 그런 다음 셀 A7:E7에 기간, 지불, 이자, 원금, 남은 잔액 등 지정된 레이블을 사용하여 Excel에서 상각 테이블을 만듭니다.
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. 입양 부모로서의 귀하의 적합성을 결정하기 위해 미국 이민국에 > 신제품, 검색창에 다음을 입력하세요. 상각 일정, 그리고 언론 엔터 버튼 열쇠. 그런 다음, 귀하의 필요에 가장 적합한 템플릿을 클릭하여 선택하세요. 예를 들어 여기에서는 Simple Loan Calculator 템플릿을 선택하겠습니다. 스크린샷 보기:
   
2. 템플릿을 선택한 후 만들기 버튼을 눌러 새 통합 문서로 엽니다.
3. 그런 다음 자신의 대출 세부 정보를 입력하면 템플릿이 입력 내용을 기반으로 일정을 자동으로 계산하고 채웁니다.
4. 마지막으로 새 상각 일정 통합 문서를 저장하십시오.