무료 임대료 명부·보증금 원장 엑셀 템플릿: 임대차 만료, 연체료, 에스크로 잔액 (수식 설명 포함)

구성

통합 문서에는 시트가 6개 있어요. 입력하는 시트는 몇 개뿐이고 나머지는 계산돼요.

시트	용도	입력하는 것	자동 계산되는 것
Start Here	설정과 월간 요약	보고 월, 유예 기간, 연체료, 만료 경고 일수	세대 수, 입주율, 예정 임대료, 수납액, 수납률, 미납액, 연체료, 만료 예정·만료 임대차, 다음 만료, 보유·대조 보증금, 공제와 반환
Units	세대당 한 행(40)	부동산, 세대, 임차인, 임대차 날짜, 임대료, 보증금, 상태	청구·수납 임대료, 미납액, 임대차 상태, 만료까지 일수, 이번 달 상태
Payments	모든 수납(300)	날짜, 부동산, 세대, 유형, 금액, 방법, 메모	월, 연체 표시
Deposits	보증금 원장(100)과 대조	날짜, 부동산, 세대, 임차인, 유형, 금액, 메모	세대별 원장 잔액 vs Units 탭, 일치 표시
Lists	드롭다운	부동산, 상태, 납부 유형, 방법, 보증금 유형	없음
Checks	데이터 검사 6개	없음	OK 또는 Review

시작하기 전에

모든 세대를 현재 임대차와 함께 적으세요. 시작, 종료, 임대료, 보관 중인 보증금.
원장 시작 월을 정하세요. 예시는 2026년 6월부터 청구를 세요. Units 탭의 DATE(2026,6,1)을 자기 시작으로 바꾸세요.
첫날부터 원장을 대조할 수 있게 보증금 에스크로 명세서를 준비하세요.

단계별 사용법

보고 월 설정
Start Here B6, 그 달 1일. 수납 임대료, 수납률, 세대별 "이번 달" 상태가 그 달 기준이에요. B7–B9가 유예 기간, 연체료, 만료 경고 창이에요.
세대 등록
Units 탭에 부동산(드롭다운), 세대, 임차인, 임대차 시작·종료, 월 임대료, 보유 보증금, 상태. 공실은 임차인과 보증금이 없어요.
납부 기록
Payments 탭에 날짜, 부동산, 세대, 유형(Rent, Late fee, …), 금액, 방법. 월과 연체 표시가 계산돼요. 1일 + 유예 기간 이후 날짜의 임대료 납부는 연체예요.
보증금 원장 유지
Deposits 탭에 수령(양수), 공제·반환(음수)을 모두 기록하세요. 오른쪽 대조가 세대별 원장 잔액을 Units 탭 보증금과 비교해요.
Units 탭 검토
미납액은 원장 시작 이후 청구 임대료에서 수납 임대료를 뺀 값이에요. 임대차 상태가 경고 창 안의 임대차와 이미 끝난 임대차를 표시해요.
Start Here와 Checks 읽기
입주율, 수납률, 미납액, 연체료, 만료, 보유 보증금과 대조 여부. Checks 탭은 원장 불일치, 미등록 세대 납부, 불완전한 세대 행, 초과 납부, Occupied로 남은 만료 임대차, 1일이 아닌 보고 월을 잡아내요.

수식 지도

통합 문서의 모든 수식을 시트와 열별로 정리했어요. 복사된 수식 열은 한 번만 세고, 적용 범위를 함께 표시해요.

시트	셀	함수	계산 내용	읽는 값	쓰이는 곳
Units	`I5` 44행까지 아래로 복사됨	IF, OR, MAX, YEAR, DATE, MONTH	원장 시작 이후 청구 임대료	D5, F5	미납액이 수납 임대료를 빼요.
Units	`J5` 44행까지 아래로 복사됨	IF, OR, SUMIFS	수납 임대료	A5, B5	미납액 = 청구 − 수납.
Units	`L5` 44행까지 아래로 복사됨	IF, OR	임대차 상태	E5	Start Here B22와 B23에서 세고 조건부 서식으로 색을 입혀요.
Units	`N5` 44행까지 아래로 복사됨	IF, OR, SUMIFS, DAY, TODAY	이번 달	A5, B5, F5	이번 달의 실시간 보기예요.
Payments	`I5` 304행까지 아래로 복사됨	IF, OR, DAY	연체 표시	A5, D5	주황색으로 표시돼요. 어떤 연체료를 부과할지 정할 때 쓰세요.
Deposits	`K5` 44행까지 아래로 복사됨	IF, SUMIFS	세대별 원장 잔액	I5, J5	M열에서 Units 탭 보증금과 비교해요.
Deposits	`M5` 44행까지 아래로 복사됨	IF, ABS	일치 여부	K5, L5	No가 하나라도 있으면 Checks 탭에 표시돼요.
Start Here	`B17`	SUMIFS	이번 달 수납 임대료	B6	수납률이 예정 임대료로 나눠요.
Start Here	`B24`	IFERROR, INDEX, MATCH, MINIFS, TEXT	다음 만료 임대차	B6	갱신을 위한 한 줄 안내예요.
Checks	`B6`	IF, SUMPRODUCT, IFERROR, COUNTIFS	모든 납부가 등록된 세대의 것인지	—	세대 번호 오타는 임차인을 연체자로 보이게 만들어요.

모든 수식 설명

아래 수식은 파일에서 그대로 가져온 것이라 여기서 읽는 내용이 셀에 들어 있는 내용과 정확히 같아요. 엑셀에서 셀을 클릭하면 수식 입력줄에서 볼 수 있어요.

원장 시작 이후 청구 임대료Units!I5 · 44행까지 아래로 복사됨

=IF(OR(B5="", C5=""), "",
    F5*MAX(0, (YEAR('Start Here'!$B$6)-YEAR(MAX(D5, DATE(2026, 6, 1))))*12+MONTH('Start Here'!$B$6)-MONTH(MAX(D5, DATE(2026, 6, 1)))+1))

월 임대료 × 임대차 시작과 원장 시작(2026년 6월) 중 늦은 날부터 보고 월까지의 개월 수.

부분별 설명

MAX(D5, DATE(2026,6,1)): 임대차 시작과 원장 시작 중 늦은 날.
(YEAR('Start Here'!$B$6)-YEAR(…))*12+MONTH('Start Here'!$B$6)-MONTH(…)+1: 양끝 포함 개월 수.
MAX(0, …): 보고 월 이후 시작 임대차는 음수가 되지 않게.
F5*…: 임대료 × 개월.

예시 데이터로 보면: D5 = 2025년 6월 1일, F5 = 1,650 → I5 결과 9,900

어떻게 쓰이나: 미납액이 수납 임대료를 빼요.

바꾸는 방법: DATE(2026,6,1)을 자기 원장 시작 월로 바꾸세요.

배우기: 엑셀 텍스트 함수와 날짜 함수 사용법 →

수납 임대료Units!J5 · 44행까지 아래로 복사됨

=IF(OR(B5="", C5=""), "",
    SUMIFS(Payments!$E$5:$E$304,
    Payments!$B$5:$B$304, A5,
    Payments!$C$5:$C$304, B5,
    Payments!$D$5:$D$304, "Rent"))

이 부동산·세대의 임대료 납부(유형 Rent)를 더해요.

부분별 설명

Payments!$E$5:$E$304: 금액.
Payments!$B$5:$B$304, A5: 부동산 일치.
Payments!$C$5:$C$304, B5: 세대 일치.
Payments!$D$5:$D$304, "Rent": 임대료만. 연체료는 따로.

예시 데이터로 보면: A5 = Maple Court, B5 = 101 → J5 결과 9,900

어떻게 쓰이나: 미납액 = 청구 − 수납.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →

임대차 상태Units!L5 · 44행까지 아래로 복사됨

=IF(OR(B5="", C5=""), "",
    IF(E5="", "No end date",
    IF(E5<'Start Here'!$B$6, "Expired", IF(E5-'Start Here'!$B$6<='Start Here'!$B$9, "Expiring soon", "Current"))))

종료일이 보고 월 전이면 Expired, 경고 창 안에 끝나면 Expiring soon, 그 외 Current, 비어 있으면 No end date.

부분별 설명

E5="": 종료일 없음.
E5<'Start Here'!$B$6: 보고 월 전에 종료.
E5-'Start Here'!$B$6<='Start Here'!$B$9: 경고 창 안에 종료.

예시 데이터로 보면: E5 = 2026년 5월 31일 → L5 결과 Expired

어떻게 쓰이나: Start Here B22와 B23에서 세고 조건부 서식으로 색을 입혀요.

바꾸는 방법: Start Here B9에서 경고 창을 바꾸세요.

배우기: 엑셀 IF 함수 사용법 →

이번 달Units!N5 · 44행까지 아래로 복사됨

=IF(OR(B5="", C5=""), "",
    IF(SUMIFS(Payments!$E$5:$E$304, Payments!$B$5:$B$304, A5, Payments!$C$5:$C$304, B5, Payments!$D$5:$D$304, "Rent", Payments!$H$5:$H$304, 'Start Here'!$B$6)>=F5,
    "Paid", IF(DAY(TODAY())>'Start Here'!$B$7+1, "Late", "Due")))

보고 월의 수납 임대료가 임대료를 채우면 Paid, 아니면 오늘이 유예 기간을 지났으면 Late, 아니면 Due.

부분별 설명

SUMIFS(Payments!$E…, …, Payments!$H$5:$H$304, 'Start Here'!$B$6): 보고 월 날짜의 수납 임대료.
>=F5: 월 임대료를 채움.
DAY(TODAY())>'Start Here'!$B$7+1: 오늘이 유예 기간을 지남.

예시 데이터로 보면: A5 = Maple Court, B5 = 101, F5 = 1,650 → N5 결과 Paid

어떻게 쓰이나: 이번 달의 실시간 보기예요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 TODAY, NOW 함수 사용법: 오늘 날짜와 현재 시간 →

연체 표시Payments!I5 · 304행까지 아래로 복사됨

=IF(OR(A5="", D5<>"Rent"),
    "", IF(DAY(A5)>'Start Here'!$B$7+1,
    "Late", ""))

1일 + 유예 기간 이후 날짜의 임대료 납부.

부분별 설명

D5<>"Rent": 임대료 납부만 검사.
DAY(A5)>'Start Here'!$B$7+1: 유예 기간을 넘긴 날짜.

예시 데이터로 보면: A5 = 2026년 6월 2일, D5 = Rent → I5 결과 —

어떻게 쓰이나: 주황색으로 표시돼요. 어떤 연체료를 부과할지 정할 때 쓰세요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 IF 함수 사용법 →

세대별 원장 잔액Deposits!K5 · 44행까지 아래로 복사됨

=IF(J5="", "", SUMIFS($F$5:$F$104,
    $B$5:$B$104, I5, $C$5:$C$104,
    J5))

부동산·세대의 모든 보증금 변동을 더해요. 수령은 양수, 공제·반환은 음수.

부분별 설명

$F$5:$F$104: 금액.
$B$5:$B$104, I5: 부동산 일치.
$C$5:$C$104, J5: 세대 일치.

예시 데이터로 보면: I5 = Maple Court, J5 = 101 → K5 결과 1,650

어떻게 쓰이나: M열에서 Units 탭 보증금과 비교해요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →

일치 여부Deposits!M5 · 44행까지 아래로 복사됨

=IF(J5="", "", IF(ABS(K5-L5)<0.005, "Yes", "No"))

원장 잔액이 Units 탭 보증금과 센트 단위로 같으면 Yes.

부분별 설명

ABS(K5-L5)<0.005: 차이가 0.5센트 미만.

예시 데이터로 보면: K5 = 1,650, L5 = 1,650 → M5 결과 Yes

어떻게 쓰이나: No가 하나라도 있으면 Checks 탭에 표시돼요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 IF 함수 사용법 →

이번 달 수납 임대료Start Here!B17

=SUMIFS(Payments!$E$5:$E$304,
        Payments!$D$5:$D$304, "Rent",
        Payments!$H$5:$H$304, B6)

월이 보고 월과 같은 임대료 납부.

부분별 설명

Payments!$D$5:$D$304, "Rent": 임대료만.
Payments!$H$5:$H$304, B6: 납부 월 = 보고 월.

예시 데이터로 보면: B6 = 2026년 11월 1일 → B17 결과 21,950

어떻게 쓰이나: 수납률이 예정 임대료로 나눠요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →

다음 만료 임대차Start Here!B24

=IFERROR(INDEX(Units!$C$5:$C$44,
         MATCH(MINIFS(Units!$E$5:$E$44, Units!$E$5:$E$44, ">="&B6), Units!$E$5:$E$44, 0))&" ("&TEXT(MINIFS(Units!$E$5:$E$44, Units!$E$5:$E$44, ">="&B6),
         "mmm d")&")", "")

보고 월 이후 가장 이른 종료일을 찾아 임차인 이름과 날짜를 한 라벨로 합쳐요.

부분별 설명

MINIFS(Units!$E$5:$E$44, Units!$E$5:$E$44, ">="&B6): 아직 지나지 않은 가장 이른 종료일.
MATCH(…, Units!$E$5:$E$44, 0) → INDEX(Units!$C…): 그 행의 임차인.
TEXT(…, "mmm d"): 날짜를 텍스트로.
&" ("&…&")": 연결.

예시 데이터로 보면: B6 = 2026년 11월 1일 → B24 결과 Avery Chen (Nov 30)

어떻게 쓰이나: 갱신을 위한 한 줄 안내예요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 MIN, MAX 함수 사용법 →

모든 납부가 등록된 세대의 것인지Checks!B6

=IF(SUMPRODUCT((Payments!$A$5:$A$304<>"")*IFERROR(COUNTIFS(Units!$A$5:$A$44, Payments!$B$5:$B$304, Units!$B$5:$B$44, Payments!$C$5:$C$304)=0, 1))=0,
    "OK", "Review")

납부마다 같은 부동산·세대의 Units 행을 세요. 0이면 납부를 맞출 수 없어요.

부분별 설명

COUNTIFS(Units!$A…, Payments!$B…, Units!$B…, Payments!$C…): 납부별 일치 세대 수.
=0: 없음.
SUMPRODUCT((Payments!$A…<>"")*IFERROR(…, 1))=0: 불일치 납부 없음.

예시 데이터로 보면: B6 결과 OK

어떻게 쓰이나: 세대 번호 오타는 임차인을 연체자로 보이게 만들어요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 COUNTIF, COUNTIFS 함수 사용법 →

팁과 한계

용량: 세대 40, 납부 300, 보증금 항목 100. Ctrl+H로 범위($44, $304, $104)를 늘리세요.
기록이 깨끗한 달에 파일을 시작하세요. 원장 시작 전 이월 잔액은 기존 미납액을 음수 "Rent" 납부로 한 번 입력하거나 시작일을 조정해 반영하세요.
보증금 원장을 매달 에스크로 은행 명세서와 대조하세요. Start Here의 차이 줄은 0이어야 해요.
임차인이 퇴거하면 Deposits 탭에 공제와 반환을 기록한 뒤 Units 탭에서 세대를 Vacant로 바꾸고 보증금을 지워 대조가 계속 맞게 하세요.
"이번 달" 열은 TODAY()를 써요. 같은 미납 세대도 3일에 열면 Due, 9일에 열면 Late로 보여요.

도움이 되었나요? 잘못된 내용이 있나요? 의견 보내기 · admin@arumsoft.com

임차인 임대료 명부 및 보증금 원장 엑셀 템플릿

이 템플릿으로 할 수 있는 일

구성

시작하기 전에

단계별 사용법

보고 월 설정

세대 등록

납부 기록

보증금 원장 유지

Units 탭 검토

Start Here와 Checks 읽기

수식 지도

모든 수식 설명

팁과 한계

이 템플릿에 쓰인 기능 배우기