구글 스프레드시트로 대출 상환액 계산하기 (PMT, PPMT, IPMT)

* 본문 중간에 스프레드시트 링크가 있습니다~ 자유롭게 가져가서 사용해주세요~!

대출..? 받을 일이 없으면 좋겠지만 알고 있으면 1000만 원 아낄 수 있네?

건강한 상환 계획과 이자 계산이 철저해야 하는 이유.

살면서 대출을 받을 일이 몇 번이나 있을까 생각했지만, 정신을 차리고 보니 이미 국가에 1억을 빚지고 있었던...

바로 그 정체는 청년 전세자금 대출.

물론 청년 전세자금 대출 같은 성격의 대출이야 총 빌린 돈 * 이자율을 하면 내가 연간 혹은 월간 갚아야 할 돈이

쉽게 나오기 때문에 크게 걱정은 되지 않습니다.

하지만 제가 오늘 준비한 것은 바로

원리금 균등 분할 상환

 

은행마다 부르는 명칭은 조금씩 다른데요. 원리금 균등 상환 / 원금 균등 분할 / 원금 분할 상환 등등 대부분 원금을 쪼개서 갚는다는 의미의 한자어들이 들어가 있는 경우는 매 월 대출금을 갚는 대신 시간이 지날수록 갚아야 할 이자비용이 낮아지는 구조를 갖고 있습니다.

물론 여기서 은행의 수수료나, 중도 상환 수수료 등은 계산하지 않을 예정이에요.

왜냐! 사실 이건 스프레드 시트 공부를 위한 페이지 이기 때문

 

일단 은행 이자율을 알아야 월마다 얼마를 어떻게 갚는 게 이득인지 알 수 있다.

구글 스프레드시트의 함수에는 이 같은 이자율에 대한 원리금 상환을 쉽게 계산할 수 있는 수식이 존재합니다.

바로 PMT (payment의 약자라고 합니다)라는 함수 연금 등 고정 수익률을 위한 정기 납입액 계산에도 사용되는데요.

PMT (연간 이자율/12 , 납입 기간 [개월] , 대출금액 [PV] )

연이율 3.4%

상환 기간 2년

대출 금액 3400만 원

이라면?

3400만 원에 대한 실제 내는 이자는 121만 원!

월마다 내야 하는 상환금액은 146만 원

음...? 뭔가 싶으시죠?

'원금 균등 상환'은 이름 그대로 원금을 매월 일정한 금액(대출 기간)으로 나눠 상환하고, 이자는 원금 중 남은 금액에 따라 내는 방식입니다. 때문에 납입일마다 대출원금이 줄어들어 총이자도 낮아집니다.

 

이를 도식해서 개월 수로 나눠보면 아래와 같은 조합이 나오게 됩니다.

월마다 내야 하는 이자는 점차 줄어드는 걸 알 수 있겠죠?

개월 수	원리금 (월)	이자 (월)	상환액 (월)	원금
1	₩1,371,051	₩96,333	₩1,467,384	₩32,628,949
2	₩1,374,936	₩92,449	₩1,467,384	₩31,254,013
3	₩1,378,831	₩88,553	₩1,467,384	₩29,875,182
4	₩1,382,738	₩84,646	₩1,467,384	₩28,492,444
5	₩1,386,656	₩80,729	₩1,467,384	₩27,105,788
6	₩1,390,585	₩76,800	₩1,467,384	₩25,715,203
7	₩1,394,525	₩72,860	₩1,467,384	₩24,320,679
8	₩1,398,476	₩68,909	₩1,467,384	₩22,922,203
9	₩1,402,438	₩64,946	₩1,467,384	₩21,519,765
10	₩1,406,412	₩60,973	₩1,467,384	₩20,113,353
11	₩1,410,397	₩56,988	₩1,467,384	₩18,702,956
12	₩1,414,393	₩52,992	₩1,467,384	₩17,288,564
13	₩1,418,400	₩48,984	₩1,467,384	₩15,870,163
14	₩1,422,419	₩44,965	₩1,467,384	₩14,447,745
15	₩1,426,449	₩40,935	₩1,467,384	₩13,021,295
16	₩1,430,491	₩36,894	₩1,467,384	₩11,590,805
17	₩1,434,544	₩32,841	₩1,467,384	₩10,156,261
18	₩1,438,608	₩28,776	₩1,467,384	₩8,717,653
19	₩1,442,684	₩24,700	₩1,467,384	₩7,274,968
20	₩1,446,772	₩20,612	₩1,467,384	₩5,828,196
21	₩1,450,871	₩16,513	₩1,467,384	₩4,377,325
22	₩1,454,982	₩12,402	₩1,467,384	₩2,922,343
23	₩1,459,104	₩8,280	₩1,467,384	₩1,463,239
24	₩1,463,239	₩4,146	₩1,467,384	₩0

 

간단한 함수 식으로 대출을 일으켰을 때 얼마나 현명하게 갚아나갈 수 있는지 총이자 비용은 얼마나 되는지 쉽게 알아볼 수 있겠죠?

 

계산기 시트 만들어보기

 

[아래 링크를 통해 복사본을 받으실 수 있습니다.]

https://docs.google.com/spreadsheets/d/1X6C7BTq883HORvOAEzb45U6vWlkh2kBQeT3MCu-84J8/copy 

매월 원금과 이자가 달라지는 부분은 어떻게 구했나요?

PPMT와 IPMT 함수

PPMT는 일정한 이자율과 지불 일정에 따라 원금을 계산해주는 함수 (principal payment)

IPMT는 이자를 계산해주는 함수! (interest payment)

ppmt + ipmt는 곧 pmt! 

 

결국 저리로 오래 빌릴수록 이득 나의 조건에 맞는 최적의 답을 찾아보자.

대출하면 심리적 거부감부터 드는 게 사실이지만 의외로 목적에 맞는 합리적인 대출은 나쁘지 않은 선택이 됩니다.

예를 들어, 자동차 같은 고가의 물건을 살 때 일시불로 살 수 없는 경우

특히 할부나 리스가 아니라 제1 금융권에서 오토론 등을 알아볼 때 유효한 계획을 세워보실 수 있습니다.

일시불로 구매하는 게 할인폭이 크지 않다면 자동차 등 회사에서 먼저 대출을 진행하고 주거래 은행의 대출로 갈아탄다거나 하는 방식인데요

결국 발품을 잘 팔아야 하는 것은 대출도 똑같은 것 같습니다.

 

대출 이외에도 정기 결제, 연금 등도 계산할 수 있는 다재다능한 PMT 함수

PMT 함수의 출력 값은 기본적으로 -입니다. 왜냐하면 해당 금액은 은행에서 인출(차감)되는 것을 기본 골자로 하고 있는 함수 식이기 때문에 PMT에서 PV 값 앞에 - 를 붙여줄 필요가 있습니다.

PMT 함수 안에는 미래가치도 계산할 수 있습니다. (FV 선택사항)

지급 유형도 선택사항으로 0은 정기 (매년 말) 1은 연초 시작으로 옵션 선택을 할 수 있네요.

ex) PMT(rate, month, PV, FV, 0)

이런 형태가 되겠죠.

계산기 시트 확인해보기

https://docs.google.com/spreadsheets/d/1X6C7BTq883HORvOAEzb45U6vWlkh2kBQeT3MCu-84J8/edit?usp=sharing 

계산기 만들기