대출 상환 일정을 사용하면 부채를 시각화하고 더 빨리 상환하는 데 큰 도움이 될 수 있습니다. 그리고 대출 노예에서 보다 빨리 벗어 날수 있습니다. Excel에서 쉽게 만드는 방법은 다음과 같습니다.
대출 즉 부채는 새 차, 대학 교육, 새 집 등의 일이 이루어지도록 도와줍니다. 그러나 반드시 갚아야 합니다. 대출 명세서를 요청할 수 있지만 항상 이해하기 쉬운 것은 아닙니다. 그러나 돈이 어디로 가는지 알 수 있다면 대출이 끝날 때 빛을 보는 것이 더 쉽습니다.
여기에서 상환 일정이 시작됩니다. 부채가 어떻게 작동하고 지불금이 부채에 어떤 영향을 미치는지 이해하면 대출을 더 잘 시각화할 수 있습니다. 약간의 추가 지불이 얼마나 큰 영향을 미칠 수 있는지 확인할 수도 있습니다. Microsoft Excel에서 만드는 방법은 다음과 같습니다.
상환 일정이란 무엇입니까?
Investopedia 에 따르면 , “대출 상환 일정은 대출 기간이 끝날 때까지 대출이 상환될 때까지 각 지불을 구성하는 원금과 이자 금액을 보여주는 정기적인 대출 지불에 대한 완전한 표입니다.”
따라서 일정을 만들면 매월 지불하는 금액과 이자와 원금이 어떻게 분할되는지 확인할 수 있습니다. 월별 지불액을 기준으로 대출금을 상환하는 데 걸리는 시간도 확인할 수 있습니다. 그리고 추가 비용을 지불하는 경우 전체 지불 일정에 어떤 영향을 미치는지도 확인할 수 있습니다.
상환 일정은 부채를 상환하는 데 걸리는 시간과 부채 상환을 위해 투자한 모든 금액이 부채에 어떤 영향을 미치는지 확인하는 데 도움이 됩니다. 주요 구매 계획을 세울 때도 사용할 수 있으므로 매달 얼마를 지불하고 있는지, 일찍 지불하면 얼마를 절약할 수 있는지 확인할 수 있습니다.
Excel에서 대출 상환 세부 정보를 계산하는 방법
이 예에서는 자동차 중고를 할부금리 3%로 구매할 계획이라고 가정해 보겠습니다. 예상 순 가격은 10,000,000원 이고 월별 지불액은 290,812입니다. 이 계산은 36개월 선수금 5,000,00과 연 3% 이율을 기준으로 합니다.
Excel에 데이터를 입력하여 올바른지 확인합니다. 각각 PMT, RATE, NPER 및 NPV 공식을 사용하여 월별 지불금, 연간 이자율, 대출 상환 기간 또는 총 대출 금액을 결정할 수 있습니다. 은행이나 딜러가 하나의 데이터 포인트(예: 이자율)를 포함하지 않은 경우에도 이 공식을 사용할 수 있습니다.
이러한 공식에는 FV, TYPE 및 GUESS도 포함되지만 애플리케이션과 관련이 없으므로 무시해도 됩니다.
note
그래서 위의 주어진 데이터를 보면 PMT=1,164, RATE=5%, NPER=60, PV=61,676(계약금을 뺀 순가격 추정)이다. 하지만 불완전한 데이터가 있는 경우 이를 계산할 수 있는 방법은 다음과 같습니다.
월별 결제
월별 지불액을 계산하려면 PMT 함수를 사용하십시오.
=-PMT(RATE,NPER,PV,[FV],[TYPE])월별 지불액을 계산하므로 RATE를 12로 나누는 것을 잊지 마세요.
이자율
이자율을 결정해야 하는 경우 RATE 함수를 사용하십시오.
=RATE(NPER,-PMT,PV,[FV],[TYPE],[GUESS])이 공식은 월별 요금을 제공하므로 최종 결과에 12를 곱하는 것을 잊지 마십시오.
지불 기간
대출금을 몇 개월간 갚아야 할지 확실하지 않지만 대출 금액과 매월 상환할 수 있는 금액을 알고 있는 경우 NPER 함수를 사용할 수 있습니다.
=NPER(RATE,-PMT,PV,[FV],[TYPE])다시 한 번, 대출금을 상환할 개월 수를 계산하므로 RATE를 12로 나눕니다.
총 대출금액
마지막으로, 특정 이자율, 월별 감당할 수 있는 금액, 대출금을 지불할 의향이 있는 개월 수를 고려하여 빌릴 수 있는 금액을 알고 싶다면 PV 함수를 사용하세요.
=PV(RATE,NPER,-PMT,[FV],[TYPE]NPER가 월을 기준으로 하는 경우 RATE는 항상 12로 나누어야 합니다. 그렇지 않으면 심각하게 위축된 가치를 얻게 될 것입니다.
이제 누락된 값을 찾는 방법을 알았으므로 상환 테이블을 생성할 차례입니다.
고정 이자율 대출 상환 테이블 구축
먼저 대출 세부정보를 추가해야 합니다. 여기에는 총액, 계약금, 대출 금액, 이자율, 대출 기간 및 월별 지불액이 포함됩니다. 올바른 데이터를 확보하려면 특히 대출 기간이나 대출 금액을 실험하려는 경우 위의 PMT 공식을 사용할 수 있습니다.
또는 월별 상환액, 이자율 또는 총 대출 금액 수치를 가지고 놀고 싶다면 언급된 다른 공식을 사용할 수 있습니다.
세부 정보를 추가한 후 대출 상환 테이블을 만듭니다. 먼저 초기 데이터 아래에 기간 , 기초 잔액 , 월별 지불 , 원금 지불 , 이자 지불 , 누적 원금 , 누적 이자 , 기말 잔액 , 이자율 및 일시 지불 열을 추가하십시오 .
상환 일정표의 첫 번째 행
열을 생성한 후 횟수 아래의 첫 번째 행(행 9) 을 작성 합니다 . 그런 다음 Excel의 자동 채우기 기능을 사용하여 기간 열을 빠르게 채웁니다. 셀을 선택하고 선택한 셀의 오른쪽 아래 모서리에 있는 작은 녹색 상자에 커서를 놓습니다. 그러면 커서가 작은 검은색 십자가로 변합니다.
커서가 바뀌면 작은 녹색 상자를 클릭한 다음 마우스를 아래쪽으로 드래그하세요. Excel이 열에 적용되는 개월 수를 나타내는 작은 표시기가 마우스 옆에 나타납니다. 횟수 36에 도달하면 마우스 버튼을 중지했다가 놓습니다 .
기간 열을 작성한 후 첫 번째 행의 나머지 부분을 채울 차례입니다. 기초 잔액 아래에 =$B$3을 입력하여 대출 금액을 입력한 셀을 참조하세요 . 테이블의 나머지 부분을 자동으로 채울 때 변경되지 않도록 열 문자와 행 번호 앞에 $ 기호를 추가하는 것을 잊지 마세요.
- 월별 결제 아래에 다음을 입력합니다.
=$B$6
이는 초기 데이터와 함께 입력한 이자율을 복사합니다. 테이블을 자동으로 채울 때 변경되지 않도록 $ 기호를 추가하는 것을 잊지 마세요.
- 원금 아래에서 다음 수식을 사용합니다.
=C9-E9이렇게 하면 월별 지불금에서 이자를 뺍니다.
- 이자 아래에서 다음 공식을 사용하여 금액을 계산합니다.
=B9*(I9/12)이는 월별 지불금에 월 이자율을 곱하는 것입니다.
- 누적 원금 의 경우 다음을 입력합니다.
=D9- 누적 이자 에 다음을 입력합니다.
=E9- 이자율 아래에 다음을 입력합니다.
=B4이전에 계산한 이자율이 복사됩니다. 그러나 은행에서 변동 이자율을 사용하는 경우 이를 변경할 수 있습니다.
- 기말 잔액 아래에서 다음 수식을 사용합니다.
=B9-D9이렇게 하면 기초 잔액에서 지불한 원금이 제거됩니다.
- 이번 기간에 추가 금액을 지불할 계획이 아니라면 일시불 지불을 공백으로 유지하십시오 .
대출 상환 표 작성
이제 첫 번째 행을 채웠으므로 두 번째 행(10행)부터 시작하여 나머지 테이블을 추가할 차례입니다.
- 기초 잔액 아래에 다음 수식을 입력합니다.
=H9-I9이전에 지불한 기말 잔액과 선불 금액이 복사됩니다.
- 월별 지불액 , 원금 , 이자 및 기말 잔액 열의 경우 위 행의 수식을 복사합니다.
- 누적 주체 아래에 다음을 입력합니다.
=F9+D10이는 지난 기간에 지불한 총 원금 금액을 이번 기간에 지불한 원금에 추가합니다.
- 누적 이자 아래에 다음을 입력합니다.
=G9+E10이는 지난 기간에 지불한 총 이자 금액을 이번 기간에 지불한 이자 금액에 추가합니다.
- 이번 기간에 추가 금액을 지불할 계획이 아니라면 일시불 지불을 공백으로 유지하십시오 .
위 수식을 입력한 후 기초 잔액부터 일시불 지급 열까지 10행의 데이터를 선택합니다. 셀을 선택한 후 Ctrl + C를 눌러 복사하세요.
3개월의 기초 잔액(11행)에서 셀을 선택합니다 . 그런 다음 60개월 행(68행)까지 아래로 스크롤하고 키보드에서 Shift를 누른 다음 일시불 지급 열 아래의 셀 (셀 I68)을 선택합니다.
선택이 완료되면 Ctrl + V를 눌러 생성한 수식을 삽입하세요. 그러면 필요한 정보로 채워진 전체 상환 테이블을 볼 수 있습니다.
그런 다음 결정을 내리는 데 도움이 되도록 대규모 구매를 하는 경우 이를 파트너에게 보낼 수 있습니다. Google 스프레드시트를 사용하는 경우에도 Excel 파일을 스프레드시트로 가져와 계산을 확인할 수 있습니다.
엑셀 데이터를 테스트 해보세요.
데이터를 변경하여 결제 일정에 어떤 영향을 미치는지 확인할 수 있습니다. 예를 들어, 산발적으로 추가 지급(연간 회사 보너스 지급 등)을 할 수 있다고 생각하는 경우, 일시불 지급의 올바른 기간에 예상 추가 금액을 추가하세요.
결제할 개월 수를 변경할 수도 있습니다. 예를 들어, 대출금을 36개월이 아닌 60개월 안에 상환하려는 경우 초기 데이터의 개월 수만 변경하면 테이블이 조정되어 매월 지불해야 하는 금액을 보여줍니다.
시작 잔액이 0 아래로 떨어지면 대출금이 전액 지불되었음을 의미합니다! 이 표는 고정 금리 대출에만 적용됩니다. 대출에 변동 이자율이 있는 경우 다른 접근 방식을 사용해야 합니다.