전세자금대출 상환 계획표 만들기

은행 대출 상담을 받으면 "월 상환액은 대략 이 정도입니다"라는 안내를 받지만, 정확히 원금이 얼마씩 줄어드는지, 이자는 언제부터 확 줄어드는지는 은행 앱에서 매번 조회해야 합니다. 엑셀로 상환 계획표를 한 번 만들어두면 금리가 바뀌거나 대출금액이 달라질 때마다 숫자만 바꿔서 바로 확인할 수 있습니다.

이번 글은 원리금균등상환 방식을 기준으로, PMT·IPMT·PPMT 세 함수로 월별 상환 계획표를 만드는 법을 정리합니다. 아래 계산에 쓰인 금리와 금액은 계산 방법을 보여주기 위한 예시이며, 실제 대출의 금리·한도·상환 조건은 반드시 해당 은행에 확인해야 합니다.

세 함수의 역할부터 구분하기

원리금균등상환은 매달 갚는 총액(원금+이자)이 항상 똑같습니다. 다만 그 안에서 원금과 이자가 차지하는 비중이 매달 조금씩 바뀝니다. 이 구조를 계산하는 함수가 세 가지입니다.

PMT: 매달 갚아야 할 총 상환액 (원금+이자, 항상 동일)
IPMT: 그중 이자에 해당하는 부분 (초반에 많고 점점 줄어듦)
PPMT: 그중 원금에 해당하는 부분 (초반에 적고 점점 늘어남)

1단계: 기본 정보 셀 만들기

계산에 필요한 세 가지 값을 먼저 셀에 입력합니다.

셀	내용	예시
B1	연 금리	3.0%
B2	상환 기간(년)	30
B3	대출 원금	150,000,000

2단계: PMT로 월 상환액 구하기

=PMT($B$1/12,$B$2*12,-$B$3)

연 금리는 12로 나눠서 월 금리로, 기간은 12를 곱해서 개월수로 바꿔야 합니다. 대출 원금 앞에 마이너스를 붙이는 이유는 "내가 받는 돈"과 "내가 갚는 돈"의 방향을 엑셀이 구분하게 하기 위해서입니다. 마이너스를 빼먹으면 결과값이 음수로 나와서 헷갈립니다.

3단계: IPMT·PPMT로 회차별 이자·원금 나누기

회차(1회차, 2회차...)마다 이자와 원금이 얼마인지 알고 싶다면 회차 번호를 인수로 추가합니다.

이자: =IPMT($B$1/12,회차,$B$2*12,-$B$3)
원금: =PPMT($B$1/12,회차,$B$2*12,-$B$3)

잔액은 이전 회차 잔액에서 이번 회차 원금을 뺀 값으로 이어서 계산하면 됩니다. =이전잔액-이번회차원금

회차가 뒤로 갈수록 이자 비중은 줄고 원금 비중은 늘어납니다. 초반에 조금씩이라도 원금을 더 갚고 싶다면, 이 구조를 알아야 중도상환이 왜 초반에 효과가 큰지도 이해가 됩니다.

자주 막히는 지점 세 가지

1. 연 금리를 그대로 넣어서 계산했다
증상: 월 상환액이 실제보다 훨씬 크게 나옵니다.
원인: PMT 함수의 이율 인수는 "월" 단위 이율이어야 하는데 연 이율을 그대로 넣었습니다.
해결: 연 금리를 12로 나눈 값을 이율 인수에 넣습니다.

2. 원금에 마이너스를 안 붙였다
증상: 상환액이 음수로 나옵니다.
원인: PMT 계열 함수는 현재가치(원금)와 상환액의 부호가 반대여야 정상적으로 계산됩니다.
해결: 원금 셀 앞에 마이너스를 붙이거나, 결과값 앞에 마이너스를 붙여 양수로 바꿉니다.

3. 회차 번호를 절대참조하지 않았다
증상: 아래로 수식을 복사했을 때 이율이나 원금 참조가 같이 밀려서 오류가 납니다.
원인: 이율($B$1), 기간($B$2), 원금($B$3)은 모든 회차에서 똑같은 값을 참조해야 하는데 상대참조로 두면 행이 내려갈 때마다 참조 위치도 같이 이동합니다.
해결: 이율·기간·원금 셀은 F4를 눌러 절대참조($ 기호)로 고정합니다.

자주 묻는 질문

Q. 원리금균등과 원금균등 중 무엇을 계산해야 하나요?
A. 이 글은 매달 상환액이 동일한 원리금균등상환 기준입니다. 원금을 매달 균등하게 갚고 이자만 줄어드는 원금균등상환은 계산 방식이 다릅니다.

Q. 중도상환하면 이 표를 어떻게 고쳐야 하나요?
A. 중도상환한 시점의 잔액에서 상환한 금액을 뺀 뒤, 그 잔액을 새로운 원금으로 삼아 남은 기간에 대해 PMT를 다시 계산하면 됩니다.

Q. 실제 은행 상환금액과 계산 결과가 살짝 다른데 왜 그런가요?
A. 은행마다 이자 계산 방식(일할 계산 여부), 첫 달 이자 적용 기준이 조금씩 달라서 원단위 차이가 날 수 있습니다. 정확한 금액은 대출 실행 은행의 상환 스케줄표를 기준으로 삼으세요.

정리

PMT는 매달 총 상환액, IPMT는 이자, PPMT는 원금을 계산한다
연 금리는 12로 나누고, 기간은 12를 곱해 월 단위로 맞춘다
원금 앞에 마이너스를 붙여야 상환액이 양수로 나온다
이율·기간·원금 셀은 절대참조로 고정해야 아래로 복사해도 안전하다
회차가 지날수록 이자 비중은 줄고 원금 비중은 늘어난다