대출 상환액을 계산할 때 Excel을 사용할 수 있다는 것을 알고 있습니까? 이 기사는 그렇게하는 데 필요한 모든 단계를 안내합니다. ( 모기지 계산기 : 작동 원리 .)

Excel을 사용하면 세 가지 간단한 단계를 통해 주택 담보 대출을 더 잘 이해할 수 있습니다. 첫 번째 단계는 월별 지불액을 결정하는 것입니다. 두 번째는 금리를 발견하는 것이며, 세 번째는 대출 일정을 찾는 것입니다. 이렇게하려면 Excel에서 다음을 알려주는 표를 작성할 수 있습니다. 이자율; 기간 동안 대출 계산; 대출 해지, 월별 임대료에 대한 할부 상환 및 계산.

월간 임대료에 대한 대출 계산

먼저, 모기지에 대한 월별 지불 계산을 구현하는 방법을 살펴 보겠습니다. 즉, 연간 이자율, 원금 및 기간을 사용하여 매월 상환해야 할 금액을 결정할 수 있습니다.

위 스크린 샷에서 볼 수있는 공식은

- 수식이 음수를 반환하기 때문에 PMT 앞에있는 빼기 기호가 필요합니다. - PMT (비율; 길이; 현재 값; [미래 값]; [유형]) 처음 세 가지 인수는 대출 금리, 대출 기간 (기간 수) 및 차용 된 원금입니다. 마지막 두 인수는 선택 사항이며 잔여 값의 기본값은 0, 미리 지불 (1) 또는 끝에 (0), 선택 사항입니다.

- - PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) - 매월 지급액을 계산하는 데 사용 된 Excel 공식은 다음과 같습니다. = PMT ((1 + 3, 10 %) ^ (1/12) -1, 10 * 12, 120000)

설명 : 우리는 월간 요율 인 요율 기간을 사용하는 요율에 대해 기간 수 (여기에 12 개월을 곱한 10 년 동안 120 개월)를 입력하고 마지막으로 차용 한 원금을 나타냅니다. 우리의 월급은 $ 1, 161. 88이 될 것입니다.

이자율에 대한 모기지 계산

모기지에 대한 월별 지불 계산 방법을 설정했습니다. 그러나 우리는 우리가 감당할 수있는 최대 월간 지불액을 설정하기를 원할 수도 있습니다. 그 이유로 우리는 해당 연간 이자율을 알고 싶습니다.

대출에 대한 이자율 계산

위 스크린 샷에서 볼 수 있듯이 먼저 기간 비율 (우리의 경우 매달)과 연이율을 계산합니다. 위의 스크린 샷에서 볼 수 있듯이 RATE는 다음과 같이 작성됩니다.

= RATE (Nper; pmt; present_value; [future_value]; [type])

처음 세 인수는 대출 기간 (기간 수) 및 차용 한 원금을 상환하기위한 월별 지불금. 마지막 세 인수는 선택적이며 잔여 값의 기본값은 0이며 미리 성숙도를 관리하는 용어 인수 (1) 또는 끝에 (0) 또한 선택 사항이며 마지막으로 추정 인수는 선택 사항이지만 비율의 초기 추정치를 제공하십시오.

대출 금리를 계산하는 데 사용 된 Excel 공식은 다음과 같습니다.

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000) 월별 지불은 음수 부호를 부여해야합니다. 이것이 수식 앞에 빼기 부호가있는 이유입니다. 우리의 비율 기간은 0. 294 %입니다.

우리 대출금의 연간 이자율을 3으로하기 위해

= (1 + B5) = 12-1 ^ = (1 + 0.294 %) ^ 12-1

%. 즉, 월간 960 $을 지불하기 위해 13 년 동안 120,000 달러를 빌리려면 연간 3. 58 %의 최대 이자율로 대출을 협상해야합니다.

대출 기간에 대한 모기지 계산

우리는 연이율, 빌린 교장 및 상환해야 할 월간 지불액을 알면 대출 기간을 알아볼 것입니다. 다시 말해서, 우리는 얼마나 오랫동안 $ 3.00의 이자율과 $ 100의 매월 지불금으로 $ 120, 000 모기지를 상환해야할까요?

대출금 상환 횟수 위 스크린 샷과 같이 NPER를 사용하는 공식은 = NPER (rate; pmt; present_value; [future_value]; [유형])

첫 번째 세 가지 인수는 대출의 연간 이자율, 대출금 상환에 필요한 월간 지불액 및 차용 된 원금입니다. 마지막 두 인수는 선택 사항이며, 나머지 값의 기본값은 0이고, 미리 지불 할 수있는 인수 (1) 또는 끝 (0)은 선택 사항입니다. NPER ((1 + 3, 10 %) ^ (1/12) -1; -1100; 120000) = NPER (NPER (1 + B2) ^ (1/12) -1; -B4;

참고 : 월별 지불에서 해당 데이터는 음수 부호를 부여해야합니다. 이것이 수식 앞에 빼기 기호가있는 이유입니다. 상환 기간은 127. 97 기간 (우리의 경우 개월)입니다.

우리는 대출 상환을 완료하기 위해 수 = B5 / 12 = 127. 97/12를 사용할 것입니다. 다시 말해, 연 120 만원을 빌리면 연간 10 %의 이자율을 내고 매월 100 만원을 지불하기 위해 만기를 128 개월 또는 10 년 8 개월 동안 상환해야합니다.

대출 해지

대출 지불은 원금과이자의 두 가지로 구성됩니다. 이자는 각 기간에 대해 계산됩니다. 예를 들어 10 년 동안의 월별 상환액은 120 기간을 제공합니다.

위의 스크린 샷은 PPMT 및 IPMT 공식을 사용하여 대출 기간 (전체 기간은 120)을 보여줍니다. 두 수식의 인수는 동일하며 다음과 같이 세분화됩니다.

= - PPMT (요율, num_period, 길이, 원금, [잔여], [terme])

= - INTPER (요율; 인수는 첫 번째 부분에서 볼 수있는 PMT 수식과 동일하지만 num_period는 대출을 무너 뜨릴 기간을 표시하기 위해 추가되는 것을 제외하고는 다음과 같습니다. 그것의 원금과이자. 예를 들어 보자 : PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10 %) ^ (1/12 (1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = INTPER ((1 + 3, 10) 결과는 "Loan Decomposition"스크린 샷에 표시된 분석 기간 동안 "1"로 표시되므로 첫 번째 기간 (1 분의 12 초) , 또는 첫 달.이 중 하나는 1161 달러입니다. 88 달러 856 달러, 교장 20 달러 305 달러. 68 관심사.

Excel Loan 계산

처음 12 개월 또는 처음 15 개월과 같은 여러 기간에 대해 원금 및이자 상환액을 계산할 수도 있습니다. (999) = - CUMIPMT (rate; length; principal; 시작 날짜; end_date; 유형)

인수, 요율, 길이, 교장 및 학기 (필수)는 공식 PMT를 사용하여 처음 부분에서 이미 보았습니다. 하지만 여기서도 start_date 및 end_date 인수가 필요합니다. 첫 번째는 분석 할 기간의 시작을 나타내고 두 번째는 끝을 나타냅니다. 예를 들어 봅시다 : CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0) (1 + B2) ^ (1/12) -1; B4 * 12; B3 * (1 / 12) -1; 10 * 12; 120000; 1; 12; 0) (1 + 3, 10 %) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

결과는 스크린 샷 "Cumul 1st year"에서 분석 된 기간은 첫 번째 기간 (첫 달)부터 12 번째 (12 번째)까지 1에서 12까지입니다. 1 년에 걸쳐 우리는 $ 419, 교장 55, $ 3 522 99를 지불 할 것입니다.

대출 상환액

이전 공식을 사용하여 기간별 일정 기간, 원금 및이자로 매월 지불 할 금액 및 지불 할 금액을 작성할 수 있습니다.

Excel에서 대출 일정 만들기

대출 일정을 만들려면 위에 논의 된 다른 수식을 사용하여 기간 수에 따라 확장합니다.

첫 번째 기간 열에는 첫 번째 기간으로 "1"을 입력 한 다음 셀을 아래로 끕니다. 우리의 경우, 10 년 대금 지불에 12 개월 = 120을 곱한 이후로 120 기간이 필요합니다.

두 번째 열은 매월 지불해야하는 월별 금액으로 전체 대출 일정에 대해 일정합니다. 이를 계산하려면 첫 번째 기간의 셀에 다음 수식을 삽입하십시오.

= - PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10 %) ^ (1/12 ) -1; 10 * 12; 120000)

세 번째 열은 월별로 상환되는 교장입니다. 예를 들어, 40 번째 기간 동안 우리는 945 달러를 갚을 것입니다. 51 원금을 계산하려면 다음 공식을 사용하십시오 :

= - PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = - PMMT (1 + 3, 10 %) ^ (1/12); 1; 10 * 12; 120000)

4 번째 열은 관심 분야이며, 우리는 월간 금액에 대한 원금 상환액을 계산하여

= - 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) 9000 = 120000 + CUMPRINC ((1 + 3, 10 %) ^ (1/12); 10 * 12; 120000; 1; 1; 0) 차용 한 교장을 포함하는 셀 앞에있는 기간 아래 교장. 이 기간은 셀을 복사하여 끌 때 변경되기 시작합니다.아래의 스크린 샷은 120 개 기간이 끝나면 대출금이 상환되는 것을 보여줍니다.