[엑셀] 원리금 균등 분할 상환 대출금 이자 계산기 - 나만의 엑셀 가계부 만들기 (feat. PPMT, IPMT 함수)

썸네일

2022년이 코앞으로 다가왔다. 다들 새로운 다이어리 · 가계부 등을 준비하고 있을 텐데, 2022년부터 계획적인 지출을 위해 엑셀로 가계부를 만들려는 분들이 분명 계실 것이다.

 

사실 필자도 계획적인 지출을 하겠다는 여러분과 동일한 목표를 가지고 정확한 지출금액을 파악하고자 엑셀 가계부를 만들고 있다. 필자와 같은 목표를 가진 여러분을 위해 정확한 대출금 이자 계산기를 만드는 방법을 알려드리겠다.

 

목차(선택 시 해당 내용으로 이동)

• 1. 원리금 균등 분할 상환, 정확한 의미가 뭘까?
• 2. 매 월 상환액을 구하는 공식
• 3. 납입 원금과 이자까지 구하는 방법은 없는가?
• 4. 꿀 팁 - 문제 해결
• 5. 마치며..

---

 

원리금 균등 분할 상환, 정확한 의미가 뭘까?

혹시나 대출을 아직 받지 않았거나 받을 예정인 사람 중에, 원리금 균등 분할 상환이 정확히 무엇을 의미하는지 모르는 분들이 계실 수도 있을 것 같아 조금 설명드리겠다.

 

우선, 원리금 균등 분할 상환 방식의 경우 대부분의 부동산 관련 대출 상품에서 적용되는 이율 방식이다. 저리(이율이 낮음)로 큰 금액을 빌려주지만, 실상 원금균등상환과 비교했을 때 총 이자 부담액이 더 높아 은행에서 더 선호하는 방식이라 보면 된다.

 

'부동산용어사전'이라는 책(장희순, 김성진 저)에서는 '원리금 균등 상환 방식'에 대해 다음과 같이 설명하고 있다.

 

대출금 원금과 이자를 융자 기간 동안
매달 같은 금액으로 나눠 갚는 방식.

 

깔끔한 설명이지만 조금 첨언을 하자면, 원금 + 이자 매달 같은 금액이라는 얘기다.

예를 들어 원금이 A, 이자가 B, 상환금이 C라고 했을 때 매월 A와 B는 달라지지만 상환금액 C는 항상 같은 금액이다.

 

아래 사진을 보면 좀 더 이해가 빠를 것이다.

아래 표는 2천만 원을 48개월(4년) 동안 16.9% 이율에 '원리금 균등 분할 상환' 방식으로 대출을 받은 경우의 실상환금, 납입 원금, 납입 이자를 표로 나타낸 것이다.

 

이때 매월 상환금액은 576,067원이다. 이 상환금액은 중도상환을 하지 않는 이상 대출금을 모두 갚는 종료 시점까지 변하지 않는다. 다만 납입 원금은 매월 조금씩 늘고, 납입 이자는 매월 조금씩 줄어들게 된다.

 

원리금 균등 분할 상환의 경우 시간이 지날 수록 이자 부담은 줄고, 원금 비율이 증가한다.

 

---

 

매 월 상환액을 구하는 공식

원리금 균등 분할 상환 방식에서 매 월 상환액을 구하는 공식이 있다.

 

원리금 균등 분할 상환액을 구하는 공식은 생각보다 간단했다.

A는 대출받은 총금액(원금)

B는 대출 이자율 ÷ 12

지수 n은 대출 개월 수이다.

 

만약 대출금이 2천만 원, 대출 기간이 4년, 연 이자가 16.9% 라면

• A = 20,000,000
• B = (16.9%/12)
• n = 48

위와 같은 값으로 대치가 되겠다.

 

 

이 식을 그대로 엑셀에 입력하면 월 상환액을 구할 수 있다. 다들 알다시피 분수의 경우 분자는 왼쪽에, 분모는 오른쪽에 두고 분수 막대를 나누기로 바꾸면 된다. 그리고 지수의 경우 컴퓨터에서 입력할 때 '^'를 사용하면 된다.

• 엑셀에 입력할 때의 형식 = A*B*(1+B)^n / (1+B)^n-1

 

 

그럼 위에 예시대로 대출금 2천만 원, 대출기간 4년, 연 이자 16.9% 일 경우 엑셀 식으로 전환하면 아래와 같다.

• 20000000*(16.9%/12)*(1+(16.9%/12))^48 / (1+(16.9%/12))^48-1

공식에 맞춰 수식을 입력했지만 어딘가 허전하다.

 

---

 

하지만 위와 같이 계산하면 결괏값이 나오긴 나오지만 틀린 값이 나온다. 왜 그럴까?

A*B*(1+B)^n / (1+B)^n-1 이라는 수식 자체는 틀린 것이 없지만, 분자와 분모의 형태를 갖추게 하려면 한 가지가 더 추가돼야 한다. 바로 아래와 같이 말이다.

 

(A*B*(1+B)^n) / ((1+B)^n-1)

 

학창 시절에 계산 우선순위를 배웠던 것이 기억나는가? 컴퓨터 역시 이 계산 우선순위를 철저하게 따르는 녀석이다.

괄호가 없으면 그냥 왼쪽에서 오른쪽으로 순차 계산을 해버리지만, 괄호 처리를 하면 왼쪽 괄호를 먼저 계산하고, 그다음 오른쪽 괄호를 계산한 다음, 왼쪽 괄호 결괏값에서 오른쪽 괄호 결괏값을 나누게 된다.

 

그리고 또 한 가지 반드시 주의해야 할 점은, 이자율을 입력할 때 반드시 퍼센트(%) 기호까지 넣어줘야 한다는 것이다.

그럼 이것들을 모두 다시 적용해서 계산을 하게 되면?

• (20000000*(16.9%/12)*(1+(16.9%/12))^48) / ((1+(16.9%/12))^48-1)

괄호까지 적용해 진짜 분수 계산 형식을 갖추게 되면 정확한 계산 값을 얻을 수 있다.

위와 같이 정확히 계산된 값을 얻을 수 있게 된다.

 

---

 

 

납입 원금과 이자까지 구하는 방법은 없는가?

위 공식은 어디까지나 '월 상환금'을 구하는 것이었다. 그런데 어떤 분들은 '내가 이 대출에서 매 월 부담하는 이자가 정확히 얼마지?' 하며 이자까지 매월 세세하게 계산하길 원하는 분들이 분명 있을 것이다.

 

그럴 때 사용하는 엑셀 함수가 있다. 바로 PPMT와 IPMT라는 함수다.

 

---

 

PPMT는 납입 원금을 구하는 함수다. PPMT는 다음과 같이 사용한다.

 

PPMT(rate, per, nper, pv, [fv], [type])

• rate - (필수) 이자
• per - (필수) 회차. 1 ~ nper(총 대출 기간) 사이에서 지정해야 함
• nper - (필수) 총 대출 기간
• pv - (필수) 대출 원금
• fv - (선택) 미래 가치 혹은 잔액. 생략 시 0(0)으로 가정됨 = 대출의 미래 값이 0이라는 의미
• type - (선택) 납입 시점. 0/생략은 기간 말, 1은 기간 초

 

위 항목들을 조금 더 알기 쉽게, 적용하기 쉽게 바꿔보면 아래와 같은 형식이 된다.

=PPMT(연이자/12,납입회차,대출기간(개월),대출원금,0)

 

---

 

IPMT는 납입 이자를 구하는 함수다. IPMT는 다음과 같이 사용한다.

 

IPMT(rate, per, nper, pv, [fv], [type])

• rate - (필수) 이자
• per - (필수) 회차. 1 ~ nper(총 대출 기간) 사이에서 지정해야 함
• nper - (필수) 총 대출 기간
• pv - (필수) 대출 원금
• fv - (선택) 미래 가치 혹은 잔액. 생략 시 0(0)으로 가정됨 = 대출의 미래 값이 0이라는 의미
• type - (선택) 납입 시점. 0/생략은 기간 말, 1은 기간 초

 

위 항목들을 조금 더 알기 쉽게, 적용하기 쉽게 바꿔보면 아래와 같은 형식이 된다.

=IPMT(연이자/12,납입회차,대출기간(개월),대출원금,0)

 

---

 

위 함수에 우리의 조건에 맞게 수식을 입력해 보겠다.

마찬가지로 대출 원금은 2천만 원, 이자는 16.9%, 총 4년(48개월)이다.

 

그럼 매 월 납입 원금을 구하는 PPMT의 경우는 아래와 같이 되고

=PPMT(16.9%/12,납입회차,48,20000000,0)

 

매 월 납입 이자를 구하는 IPMT의 경우는 아래와 같이 된다.

=IPMT(16.9%/12,납입회차,48,20000000,0)

 

그리고 PPMT와 IPMT를 통해 각각 구해진 두 값을 더하면? 처음 원리금 균등 분할 상환액을 구하는 공식을 통해 얻었던 월 상환액이 된다.

PPMT + IPMT = 월 상환액

 

---

 

PPMT와 IPMT 함수에서 중요한 것은 '회차'를 지정해 주는 부분이다. 이걸 조금 해석해 보면 이렇다. 만약 48개월 만기 대출일 때, 함수의 회차에 5라는 숫자를 넣게 되면 "총 48개월 중 5회차에 해당하는 원금 혹은 이자를 알려줘"가 된다.

 

이제 여기서 엑셀을 조금 배우신 분들이라면, 한쪽 열 혹은 행에 회차를 쭉-적어놓고, 함수들이 매 행 혹은 열에서 지금 회차를 읽어내 자동으로 값을 쭉쭉 뽑아내게 할 수 있을 것이다.

 

PPMT 함수와 IPMT 함수에서 가장 중요한 부분은 '회차'를 지정하는 것이다.

 

---

 

꿀 팁 - 문제 해결

이 글을 정독하며 위의 PPMT와 IPMT 함수를 이용하는 것 까지 따라 하신 분들에게 발생할 수 있는 문제점에 대해 발생 원인과 해결방법을 알려드리겠다.

 

---

 

첫 째, 값이 음수(마이너스)로 표시된다

일단 이유부터 설명하자면, 엑셀이라는 프로그램에서 정해진 예산이나 금액에서 차감이 되는 함수를 이용할 경우 '돈이 나갔다'를 표현하기 위해 음수로 값을 나타낸다.

 

그러니까 실제로 PPMT와 IPMT 함수의 결괏값의 의미는 "대출원금 A에서 이번 B회차에 나간 돈이 X 입니다. 그러니까 A에서 -X 됐어요~" 라는 뜻이다.

 

그럼 원인을 알았으니 해결만 하면 되겠다.

해결 방법은 두 가지가 있다.

• ABS 함수(절댓값으로 변환) 이용
• -(마이너스)를 곱해주기

 

둘 다 적용 방법은 너무나 간단하다. ABS의 경우 함수이기 때문에 ABS 함수 안에 PPMT, IPMT 함수가 들어가면 된다.

=ABS(PPMT(...)) 혹은 =ABS(IPMT(...))

 

-(마이너스)를 곱해주는 것은 ABS 함수를 적용하는 것 보다도 더 쉽다. PPMT나 IPMT 함수 앞에 그냥 '-'를 써주면 된다.

=-PPMT(...) 혹은 =-IPMT(...)

 

필자는 혹시 모를 에러 발생과 귀차니즘을 피하기 위해 그냥 - 하나 적는 것을 추천한다.

 

---

 

둘째, 총 상환액과 총 이자액이 차이가 나는 경우

PPMT와 IPMT를 이용해 납입 원금과 납입 이자를 구한 경우에는 이 문제가 발생하지 않는다.

 

그런데 그냥 원리금 균등 분할 상환 공식을 통해 상환액을 구하고 그것을 통해 납입 원금과 이자를 구한 경우 총 상환액과 총 이자액 간에 몇 백 원 ~ 몇 천 원의 금액 차이가 발생한다.

 

이 것은 바로 '소수 값' 때문에 발생하는 문제다.

우리가 만약 현실에서 친구에게 돈을 빌린다고 할 때, "야 아무개야, 나 500.20원만 빌려줘"라고 하면 분명 친구는 "뭐래 바보가"라고 할 것이다. 이것은 특정한 값을 매번 다른 비율로 나눴기 때문에 발생하는 수학적인 문제인 것이다.

 

해결하는 방법은 지정한 특정 자릿수 위로 반올림하는 ROUND 함수를 이용하는 것이다.

ROUND 함수의 구성 요소와 개념에 대해서는 세부적으로 다루지 않겠다. PPMT 함수와 IPMT 함수에 ROUND 함수를 아래와 같이 적용하면 된다.

 

=ROUND(PPMT(...),4)

=ROUND(IPMT(...),4)

 

간단하게만 설명하면, PPMT나 IPMT를 통해 구해진 값을 소수점 이하 4번째 자리에서 반올림하고, 소수점 이하 5번째 자리부터는 버린다는 의미다.

 

필자가 굳이 4번째 자리를 이렇게 집어서 쓰는 이유는, 인터넷 상의 다른 대출금 계산기들과 결괏값이 가장 흡사한 게 4번째 자리까지 썼을 때였기 때문이다.

 

소수점 이하 4번째 자리까지 썼을 때 다른 계산기들과 결괏값이 가장 흡사했다.

 

---

 

마치며..

오늘 이렇게 엑셀을 이용해 원리금 균등 분할 상환 방식의 대출금 납입 원금과 이자, 실 상환금까지 모두 구하는 방법을 알아보았다.

 

갈수록 부의 격차는 커져만 가고, 내 집 마련, 계층 이동은 꿈만 같아지는 요즘과 같은 시기에 우리가 적극적으로 할 수 있는 것은 비용 절감을 통한 투자금(시드 머니) 확보다.

오늘 글이 여러분의 가계부 작성에 꼭 도움이 됐으면 좋겠고, 2022년 새해에는 여러분도 적극적인 가계 관리를 통해 꼭 계층 이동, 경제적 자요, 내 집 마련의 꿈을 이루시길 바란다.

 

다음 글에서는 오늘 분량상 다루지 못한 중도상환금 적용 방법 등을 다루도록 해보겠다.