거치 + 원리금균등 대출, 엑셀로 상환계획표 직접 만드는 방법 (추가상환까지 자동 반영)

• 거치 X개월 + 이후 원리금균등 상환, 그리고 “추가상환”까지 반영되는 엑셀 상환계획표 파일 첨부
• 아래 링크에서 파일을 다운받아 직접 숫자만 바꾸면, 월별 이자/원금/잔액이 자동으로 계산

---

 

1. 왜 굳이 내가 직접 상환계획표를 만들어야 할까?

은행에서 주는 대출 상환 스케줄표를 보면, 숫자는 잔뜩 있는데 **“내가 중간에 더 갚으면 어떻게 바뀌는지”**는 잘 안 보입니다.

특히 이런 케이스는 더 애매합니다.

• 처음 2개월은 이자만 내는 거치형
• 3개월차부터는 원리금균등 상환
• 중간에 여유자금 생기면 추가로 원금을 더 갚고 싶다

그래서 아예 엑셀로,

1. 거치 2개월 반영,
2. 그 후 원리금균등 계산,
3. 추가 상환 시 잔액 자동 반영
   까지 되는 맞춤 상환계획표를 한 번에 쓸 수 있게 만든 겁니다.

---

2. 기본 입력 영역 설명

파일을 열어보면 상단에 다음 항목들이 있습니다.

• B3: 대출원금
  → 실제 대출받은 금액 입력 (예: 100,000,000)
• B4: 연이자율
  → 5%면 0.05 로 입력
• B5: 총 상환개월수
  → 전체 상환 기간 (예: 36개월)
• B6: 거치개월수
  → 이자만 내는 기간 (예: 2개월)
• B7: 첫 상환일
  → 첫 납부일 날짜 (예: 2026-01-01)
• B8: 거치 후 월 상환액(자동계산)
  → =IF(B4>0,-PMT(B4/12,B5-B6,B3),0)
  연이자율·기간·원금을 기준으로, 거치가 끝난 이후부터 고정으로 내야 할 월 상환액을 PMT 함수로 자동 계산하게 해둔 셀입니다.
  • PMT(이자율, 기간, 현재가치) 구조이고
  • 연이자율을 12로 나눠 월 이자율로 변환했습니다.

여기까지는 사용자가 숫자만 바꾸면 되고, 수식을 건드릴 필요는 없습니다.

---

3. 월별 상환 계획표 구조

10행부터는 실제 상환 스케줄입니다.

열	내용
A열	회차(1,2,3,…)
B열	상환일
C열	기초원금(해당 월 시작 시점 잔액)
D열	정상 상환액 (거치기간: 이자만, 이후: 원리금균등)
E열	이자
F열	원금(정상 상환액 중 이자 제외분)
G열	추가상환(원금) – 사용자가 직접 입력
H열	기말원금(해당 월 상환 후 잔액)

핵심 수식의 개념은 다음과 같습니다.

1. 상환일(B열)
   • =EDATE($B$7, A11-1)
   • 첫 상환일(B7)에서 회차만큼 월을 더해가며 자동으로 날짜가 늘어납니다.
2. 기초원금(C열)
   • 첫 달: = $B$3 (대출원금)
   • 다음 달부터: =H이전행 (이전달 기말원금 = 이번달 기초원금)
3. 정상 상환액(D열)
   • 거치기간(회차 ≤ 거치개월수)에는 이자만 내는 구조
   • 이후에는 B8에 계산된 고정 원리금균등 상환액을 사용
4. =IF(A행번호<=$B$6, C행번호*$B$4/12, $B$8)
5. 이자(E열)
   • =C행번호*$B$4/12
   • 해당 월 기초원금에 월이자율을 곱해 계산
6. 원금(F열)
   • =D행번호-E행번호
   • 정상 상환액에서 이자를 뺀 나머지가 원금 상환액
   • 거치기간에는 정상 상환액 = 이자 ⇒ 원금 0
7. 추가상환(G열)
   • 사용자가 직접 입력하는 칸
   • 여유 있을 때 500,000 등을 입력하면, 그만큼 원금이 추가로 줄어듭니다.
8. 기말원금(H열)
   • 기초원금에서 정상 원금 상환 + 추가 상환을 뺀 잔액
9. =C행번호 - F행번호 - G행번호

즉, G열(추가상환)에 얼마를 넣느냐에 따라, 다음 달 C열(기초원금)이 자동으로 줄어들고, 이후 이자/원금도 모두 다시 계산되는 구조입니다.

---

4. 실제 사용 예시

1. 기본 세팅
   • B3에 실제 대출원금 입력
   • B4에 연이자율(0.05 등)
   • B5에 전체 상환 개월수
   • B6에 거치 개월수(예: 2)
   • B7에 첫 상환일
2. 추가상환 없이 보기
   • G열을 모두 0으로 두고 스크롤을 내려보면,
     • 1~2회차는 이자만 납부
     • 3회차부터는 같은 금액을 월마다 내면서, 이자비중은 줄고 원금비중이 늘어나는 모습이 쭉 보일 겁니다.
3. 추가상환 시뮬레이션
   • 예를 들어 5회차 G열(추가상환)에 500000 입력
   • 바로 그 달 H열(기말원금)이 줄어들고,
   • 6회차부터의 C열(기초원금)이 낮아진 금액으로 이어집니다.
   • 그 결과, 이후 이자도 줄어들고 상환속도가 빨라지는 걸 한눈에 확인할 수 있습니다.

---

5. 응용 팁

• 거치개월수(B6)를 바꾸면
  → 1개월 거치, 3개월 거치 등 구조를 쉽게 바꿔볼 수 있습니다.
• 총 상환개월수(B5)를 조정하면
  → “10년짜리를 5년으로 줄이면 월 상환액이 얼마나 튀는지”를 바로 비교할 수 있습니다.
• 여러 시나리오를 시트 복사로 만들어두면
  → “기본 상환”, “연 1회 100만 추가상환”, “매달 10만 추가상환” 같은 케이스를 나란히 비교 가능.

---

6. 마무리

은행에서 준 스케줄표는 “보기”에는 좋지만, 내가 전략적으로 추가 상환을 설계하기에는 부족합니다.
이번 엑셀 파일은,

• 거치기간이 포함된 실제 계약 구조를 반영하고,
• 원리금균등 상환을 기본으로 깔고,
• 추가상환 입력만으로 잔액·이자·원금이 자동 재계산되는 형태라,

앞으로 대출 상환 전략을 세울 때 꽤 유용하게 써먹을 수 있을 겁니다.

거치식대출상환.xlsx

0.02MB