자신의 대출 상환 계산기를 만드는 방법

대부분의 사람들은 대출을 만드는 일종의 부두교 마술이 있다고 생각합니다. 할부 상환 일정 . 그러나 초급자조차도 Microsoft Excel을 사용하여 쉽게 만들 수 있습니다.

대출 상환 표는 많은 정보를 제공합니다. 대출자에게 지불 할 총 금액,이자 지불 부분, 원금 지불 부분 및 대출 잔액입니다. 상환 할 때, 단순히이자 지급과 원금 상환이 하나의 정기 지급으로 결합되고 지급 기간이 각 지급 기간 동안 동일하다는 것을 의미합니다. 그러나 상환 된 대출의 가장 흥미로운 특징 중 하나는 지불 할 때마다이자와 원금이 변경된다는 점입니다.

Excel은 매우 상환 테이블을 계산하는 데 매우 유용한 도구입니다. 서로 다른 이자율, 상환 기간 및 원금 대출 금액이 대출 결제에 미치는 영향을 확인하기 위해 여러 입력을 실험 할 수도 있습니다.

프로세스를 설명하기위한 예를 살펴 보겠습니다.

4 년 내에 완전히 상환해야하는 2 만 달러짜리 차를 구입했다고 가정 해보십시오. 대출에 대한 연간 이자율은 3 %입니다. 대출금이 상환되기 때문에 월별 지불액은 모두 동일한 금액이됩니다.

첫 번째 단계는 월별 지불액을 계산하는 것입니다. 이것은 Excel에서 PMT ("지불"의 약자) 기능을 사용하여 수행 할 수 있습니다.

새로운 스프레드 시트를 만들고 지불 금액 계산에 필요한 다음 데이터를 입력하여 시작하십시오.

A 열에는 데이터에 대해 다음 레이블이 포함됩니다.

A1 = 원래 대출 원금 (대출 금액)

A2 = 대출 기간 (년) A3 = 연간 이자율

A4 = 연간 지불 횟수

A5 = 지불 금액 (우리가 계산하는 것)

B 열

B1 = $ 20,000

B2 = 4

B3 = 3 %

B4 = 12

B5 = 우리가 찾고있는 값

Excel의 PMT 기능은 재무 기능 메뉴에서 찾을 수 있습니다. 먼저 B5 셀을 선택한 다음 수식 탭 아래의 재무 함수 메뉴를 클릭하고 목록에서 PMT 함수를 선택합니다.

스프레드 시트의 데이터를이 이미지와 동일하게 입력해야하는 상자가 표시됩니다.

여기서 유의해야 할 몇 가지 중요한 사항이 있습니다. 첫째, 월간

지불액을 계산하기 때문에 두 가지 중요한 조정이 필요합니다. 월별 이자율 (Rate)을 얻으려면 연이율을 12로 나누어야하며, 상환 기간 (Nper)의 총 개월 수를 계산하려면 4를 곱해야합니다.

두 번째로 현재 가치 (PV)는 그 금액이 대금업자에게 빚지고 있기 때문에 음수로 입력됩니다.

상자가 채워지면 "OK"를 누르십시오.

이제 스프레드 시트가 다음과 같이 보일 것입니다:

이제 상각 테이블 구축을 시작합니다.

파트 1에서 만든 스프레드 시트 아래 행을 건너 뛰고 행 7에서 시작하여 다음을 수행하십시오 A7 = 월

B7 = 지불

C7 =이자

D7 = 교장

E7 = 균형

상각 표에 5 개의 제목이 있습니다.

이제 표를 채워 보겠습니다. 첫 번째 단계는 대출 기간에 개월 수를 입력하는 것입니다. "월"제목 아래에 셀 A8 (대출이 발행 된 시간)에 "0"을 입력하십시오. 번호 48을 통해 순차적 인 순서로 후속 셀을 채 웁니다 (그러면 A56 셀에 놓이게됩니다). 한 가지 요령: 0을 입력했으면 셀이 선택되었는지 확인한 다음 (A8) 셀의 오른쪽 하단 모서리를 56로 드래그하십시오. 그런 다음 오른쪽에 나타나는 작은 상자에서 "시리즈 채우기"를 선택하십시오. 마지막 셀. 48 개월 동안 숫자 시리즈를 자동으로 채 웁니다.

다음으로, 매월 지불 금액이 이미 결정되었으므로 모든 지불액을 기입 할 수 있습니다. 1 개월부터 시작하여 매월 말 지불이 이루어지기 때문에 0 개월에 지불하지 않습니다. 매월 1-48 일에 $ 442.69를 기입하십시오. 그 후에이자 및 원금은 각 Excel 기능을 사용하여 계산할 수 있습니다.

Excel 기능을 사용하여이자 및 원금 구성 요소를 계산하는 것은 간단하며 전체 상환 표를 채울 수 있습니다. 이자 지불을 계산하려면 C9 셀을 선택한 다음 수식 탭 아래의 재무 함수 메뉴를 클릭하십시오. IPMT ("이자 지급") 기능을 목록에서 선택하십시오.

참고: 셀 문자와 숫자 사이의 "$"기호는 복사 할 때 필요합니다. 나중에 나머지 셀 수식. "$"는 참조 된 셀을 잠급니다. 따라서 수식은 나머지 셀로 복사 할 때 그대로 유지됩니다. F4를 눌러서 셀을 잠글 수 있습니다 (그리고 "$"를보십시오).

다음으로 우리는 지불에서 원금의 액수를 계산할 수 있습니다. IPMT 계산과 마찬가지로 셀 D9를 선택한 다음 수식 탭 아래의 재무 함수 메뉴를 클릭합니다. 목록에서 PPMT (Principal Payment) 기능을 선택하십시오:이 이미지와 같이 스프레드 시트의 데이터를 입력해야하는 곳에 상자가 표시됩니다:

참고: 교장은 이 함수를 사용하거나 총 지불 금액에서이자 금액을 단순히 뺀 값으로 계산할 수 있습니다. 마찬가지로 PPMT 함수를 사용하여 주체를 먼저 계산하고 총 지불액에서이 값을 빼면이자가 계산 될 수 있습니다. 두 수식을 모두 사용하는 방법을 알기 때문에 아래에서 볼 수 있듯이 나머지 테이블을보다 효율적으로 채울 수 있습니다.

마지막 열 (열 E)은 나머지 잔액 열입니다. 매월이자와 원금을 결정했습니다.0 개월 동안 아직 지급되지 않았으므로 잔액은 2 만 달러입니다. 그러나 Excel에 각 기간의 잔액을 자동으로 계산하도록 명령하고 싶습니다. 마누 "$ 20,000"을 입력하고 "= B1"을 입력하고 지불 계산을위한 위 표를 참조하십시오. 1 개월의 경우, 잔액은 당월의 원금으로 전월의 잔액을 빼서 계산하므로 E9 셀에 "= E8-D9"를 입력하십시오. 저울이 자동으로 계산됩니다. 할부 상환 테이블은 이제 1 개월 동안 완료되었으며 다음과 똑같을 것입니다:

셀 B9-E9를 선택하고 셀렉션을 복사 한 다음 셀에 붙여 넣기 만하면 남은 달의 모든 데이터를 채울 수 있습니다 B10-E10. 각 셀에 대한 수식을 입력하고 모든 계산에 사용될 셀을 고정 했으므로 관심 값, 원금 및 잔액이 자동으로 계산됩니다. 2 개월 째 작업을 보면서, B-E부터 48 번째 열까지를 선택하고 이미 복사 한 정보를 붙여 넣어 전체 테이블을 완성 할 수 있습니다. Excel은 이제 입력 한 수식을 사용하여 할부 상환 테이블의 모든 값을 계산했습니다. 완성 된 테이블은 다음과 같습니다:

대출 첫 해: