엑셀 DSR·DTI 계산기: 소득·부채만 넣으면 한도가 나오는 시트 설계법

대출 미리보기는 엑셀 DSR·DTI 계산기가 가장 빠릅니다. 월 소득과 기존 월 납입을 넣고, 금리와 기간을 바꾸면 가능한 한도가 즉시 갱신되죠. 이 글은 누구나 따라 만들 수 있는 엑셀 DSR·DTI 계산기 설계 순서와 핵심 수식을 공유합니다. 완성 후에는 금리 0.x%p, 기간 30/40년, 방식 변경에 따른 차이를 클릭 몇 번으로 확인할 수 있습니다.

시트 구조(한 장으로 끝)

• 입력 영역

  • 월 소득

  • 목표 비율(DSR 또는 DTI %)

  • 기존 대출 월 납입 합계

  • 신규 대출 금리(연)

  • 상환기간(년)

  • 상환방식(원리금균등/원금균등)

• 계산 영역

  • 월 상환가능액

  • 월 금리(r)·개월수(n)

  • 연금현가계수(계수)

  • 추정 한도(PV)

  • 같은 조건의 예상 월 납입액

• 민감도 영역

  • 금리 ±0.5%p / 기간 30↔40년 스위치

  • 기존 월 납입 ±10만 변화

핵심 공식 — 엑셀 DSR·DTI 계산기의 심장

• 월 상환가능액

  • = 월소득 * 목표비율 - 기존월납입합계

• 월 금리, 개월수

  • r = 연이자율/12

  • n = 상환기간(년)*12

• 원리금균등 월 납입(PMT 함수)

  • =PMT(r, n, -한도) (한도를 알고 월 납입을 보고 싶을 때)

• 원리금균등 한도(PV 함수)

  • =PV(r, n, -월상환가능액) (월 상환가능액으로 역산)

• 연금현가계수(계수)만 써서 한도 구하기(선호 방식)

  • 계수 = (1 - (1+r)^(-n)) / r

  • 한도 = 월상환가능액 * 계수

• 원금균등 월 납입(참고용 수식)

  • 초기월납입 = 한도/n + 한도*r

  • 월이 지날수록 이자부담이 줄어 총이자는 적지만, **초기 월 납입↑**로 DSR에 불리.

팁

• 엑셀 DSR·DTI 계산기는 PV/PMT를 중심으로 만들고, 상환방식 토글에 따라 공식을 분기하세요.

만들면서 막히는 포인트(해결법)

• 부호 오류: 엑셀의 PV/PMT는 현금유출입 부호가 반대면 #NUM!이 납니다. 보통 -월상환가능액, -한도로 처리.

• 소수 자릿수: 금리는 퍼센트 서식으로 관리(0.055 vs 5.5% 혼동 방지).

• DSR/DTI 전환: 같은 구조에서 목표비율만 바꾸면 동작.

• 변동/혼합 금리: 보수적으로 스트레스 금리(+0.5~1.0%p) 셀을 만들어 함께 계산.

예시 숫자 — 손으로 감각 잡기(근사)

가정: 월 소득 500만, 목표 비율 40%(= 200만), 기존 월 납입 30만 → 월 상환가능액 170만.

• 금리 6%, 30년

  • 계수 ≈ 166.8 → 한도 ≈ 2.84억

  • 같은 한도로 월 납입 확인: =PMT(0.06/12,360,-284000000) → 약 170만

• 금리 5%, 30년

  • 계수 ≈ 186.3 → 한도 ≈ 3.16억

• 금리 6%, 40년

  • 계수 ≈ 181.7 → 한도 ≈ 3.09억

읽는 법

• 금리 1%p 상승 시 한도는 월 상환가능액 × (계수 차이) 만큼 감소.

• 기간 30→40년은 한도를 늘리지만 총이자↑.

엑셀 DSR·DTI 계산기에 추가하면 좋은 것

• 시나리오 3종 비교 카드: 금리/기간 다른 3세트를 나란히 계산.

• 민감도 슬라이더: 금리·기간·기존 월 납입에 데이터 유효성 + 양식 컨트롤(스핀 단추) 사용.

• 조건부 서식: DSR 초과 시 빨간색, 여유 구간은 초록색.

• 요약 박스: “추천 조합(최대 한도/최저 월 납입/균형안)”을 CHOOSE로 표시.

• 생활여유선: 생활고정비를 추가로 빼는 자율 상한 라인.

체크리스트 — 배포 전 10분 점검

• 단위 통일: 만원/원 혼용 금지, 시트 상단에 단위 명시.

• 경계 테스트: r=0, n=12, 기존 월 납입 0 등 특수값에서 에러 없는지.

• 잠금/보호: 입력 셀만 해제, 나머지는 보호로 수식 변조 방지.

• 라벨링: “DSR용/DTI용” 구분, 변수 정의 주석.

• 스트레스 금리: 기본 금리 외 +0.5~1.0%p 계산 칸 필수.

FAQ — 엑셀 DSR·DTI 계산기

• Q. DSR과 DTI를 어떻게 구분하나요?
  A. 구조는 같고 포함하는 부채 범위가 다릅니다. 시트에 체크박스로 포함 항목을 선택하게 하세요.

• Q. 원금균등도 지원해야 하나요?
  A. 총이자 비교용으로 유용하지만, 한도 산정은 보통 원리금균등이 실무 친화적입니다.

• Q. 변동금리는 어떻게 보수적으로?
  A. 금리 셀을 2개(현재/스트레스) 두고, 더 큰 값으로도 통과하는지 확인하세요.

결론 — 계산기는 단순하고, 결정은 명확해진다

엑셀 DSR·DTI 계산기의 본질은 두 줄입니다.

• 월 상환가능액 = 월 소득 × 목표비율 − 기존 월 납입

• 한도 = 월 상환가능액 × (1 − (1+r)^(-n)) / r
  이 두 줄만 정확히 넣으면, 금리·기간·방식이 바뀌어도 당신의 한도와 월 납입은 즉시 보입니다.