목차
- 모기지 이해하기
- 월별 지불 계산
- 연간 이자율 계산
- 대출 기간 결정
- 대출 분해
- Excel에서 대출 계산
- 대출 상각
- 대출 일정 만들기
대출 상환은 대출 기관에서 이전에 빌린 돈을 일반적으로 원금과이자를 포함한 일련의 정기적 지불을 통해 상환하는 행위입니다. 대출 상환을 계산하기 위해 소프트웨어 프로그램 Excel을 사용할 수 있다는 것을 알고 있습니까?
이 기사는 대출 계산 설정에 대한 단계별 안내서입니다.
주요 테이크 아웃
- Excel을 사용하여 월별 지불, 이자율 및 대출 일정을 결정하여 모기지를 처리하십시오. Excel을 사용하여 대출 내역을 자세히 살펴보고 자신에게 적합한 상환 일정을 만들 수 있습니다. 각 단계마다 특정 요구 사항을 충족하기 위해 조정할 수있는 계산이 있습니다. 대출을 단계별로 검토하고 검토하면 상환 프로세스가 덜 압도적이며 관리하기 쉽다고 느낄 수 있습니다.
모기지 이해하기
Excel을 사용하면 간단한 3 단계만으로 모기지를보다 잘 이해할 수 있습니다. 첫 번째 단계는 월별 지불을 결정합니다. 두 번째 단계는 이자율을 계산하고 세 번째 단계는 대출 일정을 결정합니다.
Excel에서 이율, 대출 기간 동안의 대출 계산, 대출의 분해, 할부 상환 및 월별 지불을 알려주는 테이블을 작성할 수 있습니다.
월별 지불 계산
먼저, 모기지에 대한 월별 지불을 계산하는 방법이 있습니다. 연간 이자율, 원금 및 기간을 사용하여 매월 상환 할 금액을 결정할 수 있습니다.
위의 스크린 샷과 같이 수식은 다음과 같이 작성됩니다.
= -PMT (rate; length; present_value;;)
수식이 음수를 반환하므로 PMT 앞에 빼기 기호가 필요합니다. 처음 세 가지 인수는 대출 비율, 대출 기간 (기간 수) 및 빌린 원금입니다. 마지막 두 인수는 선택 사항이며 잔차 값은 기본적으로 0입니다. 선불 (1 인) 또는 끝 (0 인)도 선택 사항입니다.
대출의 월별 지불을 계산하는 데 사용되는 Excel 수식은 다음과 같습니다.
= -PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10 %) ^ (1/12) -1; 10 * 12; 120000)
설명: 요율에는 월간 요율 (요율 기간)을 사용한 다음 기간 (10 년 동안 120에 12 개월을 곱한 값)을 계산하고 마지막으로 빌린 원금을 표시합니다. 월별 지불액은 10 년 동안 $ 1, 161.88입니다.
연간 이자율 계산
우리는 모기지에 대한 월별 지불 계산을 설정하는 방법을 보았습니다. 그러나 우리는 대출을 상환해야 할 기간 (년)도 표시 할 수있는 최대 월 지불액을 설정하고자 할 수 있습니다. 이러한 이유로 해당 연간 이자율을 알고 싶습니다.
위의 스크린 샷에서 볼 수 있듯이 먼저 기간 비율 (이 경우에는 월간)을 계산 한 다음 연간 비율을 계산합니다. 사용 된 공식은 위의 스크린 샷과 같이 RATE입니다. 다음과 같이 작성되었습니다.
= RATE (Nper; pmt; 현재 _ 값;;)
처음 세 가지 인수는 대출 기간 (기간 수), 대출 상환을위한 월별 지불 및 빌린 원금입니다. 마지막 3 개의 인수는 선택 사항이며 잔차 값은 기본적으로 0입니다. 성숙도를 미리 (1 개) 또는 끝 (0)으로 관리하는 용어도 선택 사항입니다. 마지막으로 추정 인수는 선택 사항이지만 초기 추정 속도를 제공 할 수 있습니다.
대출 비율을 계산하는 데 사용되는 Excel 수식은 다음과 같습니다.
= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)
참고: 월별 결제의 해당 데이터에는 음수 부호가 지정되어야합니다. 이것이 수식 앞에 빼기 부호가있는 이유입니다. 평가 기간은 0.294 %입니다.
공식 = (1 + B5)는 12-1 ^ = (1 + 0.294 %) ^ 12-1 을 사용하여 연간 대출 금리 인 3.58 %를 얻습니다. 다시 말해, 월별 960 달러를 지불하기 위해 13 년 동안 120, 000 달러를 빌리려면 연간 3.58 %의 최대 요율로 대출을 협상해야합니다.
Excel을 사용하면 지불해야 할 일을 추적하고 지불해야 할 비용을 최소화하는 상환 일정을 마련 할 수 있습니다.
대출 기간 결정
이제 연간 요율, 차용 한 원금 및 상환되는 월별 지불액을 알 때 대출 기간을 결정하는 방법을 살펴 보겠습니다. 다시 말해, 3.10 %의 비율과 1, 100 달러의 월별 지불금으로 120, 000 달러의 모기지를 상환해야하는 기간은 얼마나됩니까?
우리가 사용할 공식은 위의 스크린 샷과 같이 NPER이며 다음과 같이 작성됩니다.
= NPER (rate; pmt; present_value;;)
처음 세 가지 인수는 대출의 연간 비율, 대출 상환에 필요한 월별 지불 및 빌린 교장입니다. 마지막 두 인수는 선택 사항이며 잔차 값은 기본적으로 0입니다. 사전에 (1) 또는 끝 (0)에 지불 할 수있는 인수라는 용어도 선택 사항입니다.
= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10 %) ^ (1/12) -1; -1100; 120000)
참고: 월별 결제의 해당 데이터에는 음수 부호가 지정되어야합니다. 이것이 수식 앞에 빼기 부호가있는 이유입니다. 상환 기간은 127.97 기간 (우리의 경우 몇 개월)입니다.
우리는 대출 상환을 완료하기 위해 수년간 수식 = B5 / 12 = 127.97 / 12를 사용합니다. 다시 말해, 연간 3.10 %의 비율로 $ 120, 000를 빌리고 월간 $ 1, 100를 지불하려면 128 개월 또는 10 년 8 개월 동안 만기 상환해야합니다.
대출 분해
대출 지불은 원금과이자로 구성됩니다. 이자는 각 기간에 대해 계산됩니다. 예를 들어 10 년 동안의 월별 상환은 120 기간을 제공합니다.
위의 표는 PPMT 및 IPMT 수식을 사용한 대출 (총 기간 120)을 보여줍니다. 두 공식의 인수는 동일하며 다음과 같이 분류됩니다.
= -PPMT (rate; num_period; length; principal;;)
"num_period"를 제외하고는 이미 본 PMT 공식의 인수와 동일합니다.이 숫자는 원금과이자를 고려하여 대출을 분류하는 기간을 표시하기 위해 추가됩니다. 예를 들면 다음과 같습니다.
= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10 %) ^ (1/12) -1; 1; 10 * 12; 120000)
결과는 분석 된 기간 동안의 "대출 분해"위의 스크린 샷에 표시됩니다 ("하나"). 즉, 첫 번째 기간 또는 첫 번째 달입니다. 우리는 $ 1, 161.88을 $ 856.20의 원금과 $ 305.68의이자로 지불합니다.
Excel에서 대출 계산
처음 12 개월 또는 처음 15 개월과 같은 여러 기간 동안 원금 및이자 상환액을 계산할 수도 있습니다.
= -CUMPRINC (속도; 길이; 주체; 시작 날짜; 끝 날짜; 유형)
우리는 PMT 공식으로 첫 번째 부분에서 이미 본 논쟁, 비율, 길이, 교장 및 용어 (필수)를 찾습니다. 그러나 여기에는 "start_date"및 "end_date"인수도 필요합니다. "start_date"는 분석 할 기간의 시작을 나타내고 "end_date"는 분석 할 기간의 끝을 나타냅니다.
예를 들면 다음과 같습니다.
= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)
결과는 "Cumul 1st year"스크린 샷에 표시되므로 분석 된 기간은 첫 번째 기간 (1 개월)의 1-12에서 12 번째 (12 개월)까지입니다. 1 년에 걸쳐 원금은 $ 10, 419.55, 이자는 $ 3, 522.99를 지불합니다.
대출 상각
이전 공식을 사용하면 기간별로 일정을 정하고 매월 원금과이자를 지불 할 금액과 지불해야 할 금액을 알 수 있습니다.
대출 일정 만들기
대출 일정을 만들기 위해 위에서 설명한 다양한 수식을 사용하여 여러 기간에 걸쳐 확장합니다.
첫 번째 기간 열에서 첫 번째 기간으로 "1"을 입력 한 다음 셀을 아래로 끕니다. 우리의 경우, 우리는 10 년 대출 지불에 12 개월을 곱한 120에 120을 곱한 이후 120 기간이 필요합니다.
두 번째 열은 매월 지불해야하는 월별 금액으로, 전체 대출 일정에 따라 일정합니다. 금액을 계산하려면 첫 번째 기간의 셀에 다음 수식을 삽입하십시오.
= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10 %) ^ (1/12) -1; 10 * 12; 120000)
세 번째 열은 매월 상환되는 교장입니다. 예를 들어, 40 번째 기간 동안 우리는 월 총액 $ 1, 161.88에 대해 원금 $ 945.51를 상환합니다.
사용 된 원금을 계산하기 위해 다음 공식을 사용합니다.
= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10 %) ^ (1/12); 1; 10 * 12; 120000)
네 번째 열은이자이며, 이 공식을 사용하여 매월 상환 된 원금 상환액을 계산하여이자가 지불되는 금액을 확인합니다.
= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10 %) ^ (1/12); 1; 10 * 12; 120000)
다섯 번째 열에는 남은 금액이 있습니다. 예를 들어, 40 번째 지불 후 $ 120, 000에 $ 83, 994.69를 지불해야합니다.
공식은 다음과 같습니다.
= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)
이 수식은 빌린 원금을 포함하는 셀보다 앞서서 원금의 조합을 사용합니다. 셀을 복사하고 아래로 드래그하면이 기간이 변경됩니다. 아래 표는 120 기간 말에 대출이 상환되었음을 보여줍니다.
