📑 목차
대출을 비교할 때 금리만 보면 실제 부담을 알기 어렵습니다. 같은 원금이라도 기간이 길어지면 월 상환액은 줄지만 총이자는 늘어나므로 두 금액을 함께 계산해야 합니다.
Excel의 PMT 함수를 사용하면 고정금리·원리금균등상환 대출의 월 상환액을 구할 수 있습니다. IPMT와 PPMT를 함께 쓰면 매월 납부액에서 이자와 원금이 각각 얼마인지 상환표로 나눌 수도 있습니다.
PMT 대출 계산표 입력칸
| 셀 | 항목 | 예시 |
|---|---|---|
| B2 | 대출원금 | 300,000,000원 |
| B3 | 연 이자율 | 4.0% |
| B4 | 대출기간 | 20년 |
| B5 | 총 상환개월 | 240개월 |
| B6 | 월 상환액 | PMT 자동 계산 |
| B7·B8 | 총 상환액·총이자 | 수식 자동 계산 |
B3은 4가 아니라 4%로 입력해야 합니다. 숫자 4를 입력하면 Excel은 연 400%로 계산합니다.
PMT 함수의 세 가지 핵심 인수
| 인수 | 뜻 | 월 상환 계산 |
|---|---|---|
| rate | 기간별 이자율 | 연 이자율÷12 |
| nper | 총 납부횟수 | 대출연수×12 |
| pv | 현재가치 | 대출원금 |
계산 전제: PMT는 이자율과 납부액이 일정한 대출을 계산합니다. 변동금리, 거치기간, 체증식 상환, 일수 계산, 보증료·인지세·중도상환수수료는 기본 수식에 포함되지 않습니다.
1단계｜총 상환개월 계산
B4에 대출기간을 연 단위로 입력했다면 B5에서 12를 곱합니다.
20년이면 240개월, 30년이면 360개월입니다. 기간을 이미 개월로 입력한다면 12를 다시 곱하지 않습니다.
2단계｜PMT로 월 상환액 계산
B6에 다음 수식을 입력합니다. PMT 결과 앞에 마이너스를 붙이면 납부액을 양수로 표시할 수 있습니다.
대출원금 3억원, 연 4%, 20년 원리금균등상환 예시의 월 상환액은 약 181만8천원 수준입니다. Excel 표시 형식에 따라 원 미만 반올림 값은 달라질 수 있습니다.
원 단위 반올림
은행은 실제 상환일수와 원 단위 처리 규정에 따라 매월 청구액 또는 마지막 회차 금액을 조정할 수 있으므로 계산표는 예상치로 사용하세요.
3단계｜총 상환액과 총이자 구하기
B7의 총 상환액은 월 상환액과 총 개월 수를 곱합니다.
B8 총이자는 총 상환액에서 대출원금을 뺍니다.
월 납부액을 원 단위로 먼저 반올림하면 장기간 곱했을 때 오차가 커질 수 있습니다. 정확한 비교용 총액은 반올림하지 않은 PMT 결과로 계산하고 화면 표시만 원 단위로 설정하는 방법이 좋습니다.
4단계｜월별 원금과 이자 상환표
12행부터 상환표를 만든다고 가정합니다. A12에 회차 1을 입력하고 A13에 다음 수식을 넣어 아래로 복사합니다.
C12의 해당 회차 이자는 IPMT로 계산합니다.
D12의 원금 상환분은 PPMT로 계산합니다.
E12의 월 상환액은 두 값을 더합니다.
F12의 상환 후 잔액은 원금에서 누적 원금상환액을 뺍니다.
원리금균등 상환표 구조
| 회차 | 이자 | 원금 | 월 상환액 | 잔액 |
|---|---|---|---|---|
| 초기 | 비중 큼 | 비중 작음 | 거의 일정 | 천천히 감소 |
| 중기 | 감소 | 증가 | 거의 일정 | 지속 감소 |
| 말기 | 비중 작음 | 비중 큼 | 마지막 회차 조정 가능 | 0에 근접 |
원금균등상환 계산 방법
원금균등은 PMT처럼 매달 같은 금액을 내는 방식이 아닙니다. 매월 원금은 일정하고 잔액에 붙는 이자가 줄어 월 상환액도 점차 감소합니다.
월 원금상환액은 다음과 같습니다.
상환 전 잔액이 B12라면 해당 월 이자는 다음과 같습니다.
월 상환액은 월 원금과 월 이자를 더합니다. 원금균등은 초기 부담이 크지만 같은 금리·기간이면 일반적으로 원리금균등보다 총이자가 적습니다.
거치기간이 있는 대출 계산
거치기간에 이자만 내는 단순 예시라면 거치 중 월 이자는 다음과 같습니다.
거치가 끝난 뒤 남은 기간의 원리금균등 월 상환액은 전체 기간이 아니라 실제 원금상환 개월 수를 PMT의 nper에 넣습니다. 거치 중 이자를 원금에 더하는 상품이라면 계산이 달라지므로 약정서를 확인해야 합니다.
중도상환 효과 간단 비교
특정 시점 잔액에서 중도상환금 B10을 뺀 새 원금을 B11에 계산합니다.
남은 개월 수와 같은 금리를 유지하면서 월 상환액을 다시 계산합니다.
실제 은행은 월 상환액을 낮추는 방식과 기간을 줄이는 방식 중 처리방식이 다를 수 있고 중도상환수수료가 발생할 수 있습니다. 앱의 상환 예상조회 결과와 비교하세요.
계산 결과가 다를 때 점검
| 증상 | 원인 | 해결 |
|---|---|---|
| 상환액이 음수 | 현금흐름 부호 규칙 | PMT 앞에 - 입력 |
| 금액이 지나치게 큼 | 연 금리를 월 금리로 미변환 | 연 이자율÷12 |
| 기간이 12배 차이 | 연·개월 단위 혼동 | 상환횟수를 개월로 통일 |
| 은행 금액과 다름 | 일수·수수료·변동금리 | 약정 상환표 확인 |
| 마지막 잔액이 조금 남음 | 매월 원 단위 반올림 | 원본 수식 유지·표시만 반올림 |
자주 묻는 질문
Q1. PMT 결과가 왜 마이너스로 나오나요?
Excel은 대출로 받은 돈과 갚는 돈을 반대 방향의 현금흐름으로 처리합니다. 납부액을 양수로 보려면 PMT 앞에 마이너스를 붙이세요.
Q2. 연 4% 금리는 수식에 4를 넣나요?
아닙니다. 4% 또는 0.04를 입력하고 월 상환이면 12로 나눕니다.
Q3. PMT에 중도상환수수료도 포함되나요?
포함되지 않습니다. PMT 기본 결과는 원금과 이자이며 세금, 보험료, 보증료와 각종 수수료는 별도로 더해야 합니다.
Q4. 변동금리 대출도 계산할 수 있나요?
PMT 한 번으로는 향후 금리 변경을 반영할 수 없습니다. 금리 적용구간별로 남은 원금과 남은 기간을 기준으로 상환액을 다시 계산해야 합니다.
Q5. 원리금균등과 원금균등 중 무엇이 유리한가요?
원리금균등은 월 부담이 일정해 계획하기 쉽고, 원금균등은 초기 부담이 크지만 같은 조건에서는 총이자가 적은 편입니다. 초기 상환여력과 총비용을 함께 비교하세요.