대출 상환 스케줄표 엑셀로 만들기 — PMT·IPMT·PPMT 5분 완성

 

엑셀에서 대출 상환 스케줄표는 PMT, IPMT, PPMT 세 함수면 끝납니다. 각각 월 상환액, 그중 이자, 그중 원금을 구합니다. 1행만 만들어 아래로 끌면 360개월 전체가 채워집니다. 아래 수식을 그대로 복사해 쓰세요.

이 글에서 답하는 질문

• PMT, IPMT, PPMT는 각각 뭘 구하나?
• 왜 결과가 음수로 나오나?
• 5년 뒤 남은 원금은 어떻게 구하나?
• 변동금리는 어떻게 반영하나?
• 중간에 추가 상환하면?

---

세 함수의 역할

함수	구하는 것	비유
PMT	매달 내는 총액	통장에서 빠져나가는 금액
IPMT	그중 이자	은행이 가져가는 몫
PPMT	그중 원금	내 빚이 줄어든 만큼

PMT = IPMT + PPMT 가 항상 성립합니다. 검산에 쓰세요.

---

기본 사용법

PMT — 월 상환액

=PMT(이자율, 기간, 현재가치, [미래가치], [납입시점])

실무에서는 앞 세 개만 쓰면 됩니다.

=PMT(4%/12, 360, -300000000)

• 이자율은 반드시 월 단위로 나눕니다. 연 4%면 4%/12
• 기간도 개월 수입니다. 30년이면 360
• 원금 앞에 마이너스를 붙이면 결과가 양수로 나옵니다

마이너스를 안 붙이면 결과가 -1,432,000처럼 음수로 나옵니다. 엑셀이 돈의 방향(유입/유출)을 구분하기 때문입니다. 오류가 아니니 당황하지 마세요.

IPMT — n회차 이자

=IPMT(4%/12, 1, 360, -300000000)

두 번째 인수가 회차입니다. 1회차면 1, 60회차면 60.

PPMT — n회차 원금

=PPMT(4%/12, 1, 360, -300000000)

인수 구조는 IPMT와 같습니다.

---

스케줄표 만들기 — 복사해서 쓰세요

입력부 (B1~B4)

셀	항목	값
B1	대출원금	300000000
B2	연이율	4%
B3	상환년수	30
B4	상환개월수	=B3*12

스케줄부 (6행부터)

셀	항목	수식
A6	회차	1
B6	월상환액	=PMT($B$2/12, $B$4, -$B$1)
C6	이자	=IPMT($B$2/12, A6, $B$4, -$B$1)
D6	원금	=PPMT($B$2/12, A6, $B$4, -$B$1)
E6	남은 원금	=$B$1-SUM($D$6:D6)

핵심은 $ 기호입니다. 입력부 셀은 $B$2처럼 고정하고, 회차(A6)와 누계 범위($D$6:D6)는 상대참조로 둡니다. 이렇게 해야 아래로 끌었을 때 제대로 늘어납니다.

A6에 1을 넣고 A7에 =A6+1을 넣은 뒤, A7:E7을 선택해 365행까지 드래그하면 완성입니다.

드래그할 때 값이 복사만 되고 늘어나지 않는다면 → [엑셀 자동 채우기 안 될 때 해결법] 참고

---

바로 확인되는 것들

3억 / 4% / 30년 스케줄표를 만들면 이런 게 보입니다.

회차	이자	원금	남은 원금
1	1,000,000	432,000	299,568,000
60 (5년)	920,000	512,000	275,000,000 수준
120 (10년)	816,000	616,000	244,000,000 수준
360	4,800	1,427,000	0

5년을 갚아도 원금은 2,500만 원밖에 안 줄었습니다. 1년에 500만 원꼴입니다.

이 숫자를 처음 보면 대부분 놀랍니다. 그리고 이게 상환 방식 선택이 왜 중요한지를 가장 잘 보여줍니다 → [원리금균등 vs 원금균등 비교]

---

자주 막히는 지점

① n년 뒤 남은 원금만 빠르게 알고 싶다

CUMPRINC 함수를 쓰면 한 줄로 끝납니다.

=B1 + CUMPRINC(B2/12, B4, B1, 1, 60, 0)

1회차부터 60회차까지 상환한 원금 누계를 구해 더합니다(결과가 음수라 더하면 차감됩니다).

② 변동금리는 어떻게 하나

IPMT·PPMT는 고정금리 전제입니다. 변동금리는 함수로 한 번에 처리할 수 없습니다.

실무적으로는 이렇게 합니다.

• 구간별로 나눠 계산 (1~24회차 3.5%, 25회차 이후 4.2% 등)
• 또는 금리를 0.5%p씩 올려 가며 시나리오 3개를 만들어 최악의 경우를 확인

두 번째 방식이 더 유용합니다. 정확한 예측보다 금리가 1%p 오르면 월 얼마가 늘어나는지를 아는 게 중요합니다. 3억/30년 기준으로 1%p면 월 약 17만 원입니다.

③ 중간에 목돈으로 추가 상환하면

함수로는 처리가 안 됩니다. 스케줄표에 열을 하나 더 만드세요.

셀	항목	수식
F열	추가상환	직접 입력 (해당 월에만)
E열 수정	남은 원금	=$B$1-SUM($D$6:D6)-SUM($F$6:F6)

엄밀한 재계산은 아니지만, 원금이 얼마나 빨리 줄어드는지 감을 잡기에는 충분합니다. 정확한 재계산이 필요하면 추가 상환 시점에서 스케줄표를 새로 시작하는 편이 깔끔합니다.

---

마무리

PMT·IPMT·PPMT 세 개와 $ 고정만 익히면 어떤 대출이든 15분 안에 분석할 수 있습니다.

은행이 주는 상환 스케줄표를 받아만 보지 말고, 조건을 바꿔 가며 직접 돌려 보세요. 금리 0.3%p, 기간 5년이 실제로 얼마를 바꾸는지 눈으로 보면 협상할 때 기준이 생깁니다.

• 상환 방식 자체를 고민 중이라면 → [원리금균등 vs 원금균등 vs 만기일시]
• 갈아타기를 검토한다면 → [중도상환수수료 계산법 2026]

엑셀 대출 상환 스케줄표 만들기