본문으로 건너뛰기
ArumSoft Learning
EN
    엑셀 템플릿부동산

    상업용 부동산 캡레이트 및 민감도 엑셀 분석기

    상업용 부동산 검토는 NOI, 매입 캡레이트, 얹는 부채, 매각 캡레이트로 귀결돼요. 이 통합 문서는 간단한 프로포마로 NOI를 만들고, 거래와 대출 가격을 정하고, 대출이 상환되는 10년 현금흐름을 돌리고, 매각 캡레이트로 팔아 자기자본 배수와 IRR을 돌려줘요. 민감도 표 3개가 캡레이트, 금리, 레버리지, 성장률에 따른 가치, 현금수익률, 자기자본 배수를 보여 주는데, 셀마다 읽을 수 있는 보통 수식이에요.

    시트:
    5
    지원 버전:
    Microsoft 365용 Excel, Excel 2016 이후 버전, Mac용 Excel, 웹용 Excel
    업데이트:

    CRE Cap Rate and Sensitivity Analyzer.xlsx

    엑셀 통합 문서(.xlsx) · 25KB · 시트 5개

    템플릿 내려받기

    무료로 내려받을 수 있고 가입할 필요가 없어요.

    이 템플릿으로 할 수 있는 일

    • 1년차 NOI, 매입 캡레이트, DSCR, 부채수익률, 손익분기 입주율
    • 수입·지출 성장과 상환 대출(CUMPRINC)이 있는 10년 현금흐름
    • 매각 캡레이트 매각, 순매각대금, 자기자본 배수, 레버리지·무레버리지 IRR
    • 가치(NOI × 캡), 현금수익률(금리 × LTV), 자기자본 배수(매각 캡 × 성장) 민감도 표, What-If 데이터 표 없음
    상업용 부동산 엑셀 분석기의 Start Here 시트: 매입과 금융 입력, 1년차 NOI, 캡레이트, DSCR, 부채수익률, 손익분기 입주율, 보유 기간 매각, 자기자본 배수, IRR, NOI와 현금흐름 차트
    파일에 들어 있는 예시 12세대 건물로 본 Start Here 시트예요.

    구성

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

    시트용도입력하는 것자동 계산되는 것
    Start Here입력, 1년차 결과, 보유 기간 결과가격, 거래 비용 %, LTV, 금리, 상환 기간, 보유 기간, 성장률, 매각 캡, 매각 비용NOI, 캡레이트, 대출, 자기자본, 원리금, 현금흐름, 현금수익률, DSCR, 부채수익률, 손익분기 입주율, 매각 연도 NOI, 매각가, 대출 잔액, 매각대금, 자기자본 배수, IRR
    Pro Forma1년차 수입과 지출수입 항목, 공실 %, 지출 항목GPI, EGI, 관리 수수료, 총지출, NOI, 지출 비율, 세대당 NOI
    Cash Flow10년없음EGI, 지출, NOI, 원리금, 현금흐름, 현금수익률, 대출 잔액, DSCR, IRR 흐름
    Sensitivity5×5 표 3개없음(머리글 간격 수정 가능)가치, 현금수익률, 자기자본 배수
    Checks검토 검사 6개없음OK 또는 Review

    시작하기 전에

    • 매도인에게 최근 12개월 수입과 지출을 받고, 세금(매각 시 재평가), 보험, 관리비는 직접 산정하세요.
    • 대출기관 조건을 알아 두세요. LTV, 금리, 상환 기간, 요구 DSCR과 부채수익률.
    • 5년 뒤 금리를 알 수 없으니 매각 캡레이트는 현재 이상으로 잡으세요.

    단계별 사용법

    1. 프로포마 만들기

      Pro Forma 탭에 수입 항목, 공실·대손 %, 지출 항목을 입력하세요. 관리비는 수식으로 EGI의 5%예요. NOI와 지출 비율이 계산돼요.

    2. 거래 입력

      Start Here B6–B15: 가격, 거래 비용 %, LTV, 금리, 상환 기간, 보유 기간, 수입·지출 성장률, 매각 캡, 매각 비용.

    3. 1년차 읽기

      매입 캡레이트, 연 원리금, 원리금 후 현금흐름, 현금수익률, DSCR, 부채수익률, 손익분기 입주율(B19–B28).

    4. 보유 기간 읽기

      매각 연도 NOI, 매각가(다음 해 NOI ÷ 매각 캡), 매각 시 대출 잔액, 순매각대금, 자기자본 배수, 레버리지·무레버리지 IRR(B32–B38).

    5. 거래 스트레스 테스트

      Sensitivity 탭: NOI 변화 × 캡레이트별 가치, 금리 × LTV별 현금수익률, 매각 캡 × 수입 성장별 자기자본 배수. 각 표의 가운데 셀이 Start Here 케이스예요.

    6. Checks 확인

      캡레이트 범위, 매입 캡 이상의 매각 캡, DSCR ≥ 1.25, 1–10년 보유, 지출 비율 범위, Start Here와 일치하는 표 중심.

    수식 지도

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

    시트셀함수계산 내용읽는 값쓰이는 곳
    Pro FormaB22사칙연산순영업소득(NOI)B11, B21Start Here의 모든 것이 여기서 시작해요.
    Start HereB20IF매입 캡레이트B19, B6가치 표의 머리글이 이 값을 중심으로 만들어져요.
    Start HereB23IF, PMT연 원리금B9, B10, B21현금흐름, DSCR, 손익분기 입주율이 써요.
    Start HereB28IF손익분기 입주율B23돈을 더 넣기 전까지 거래가 흡수할 수 있는 공실이에요.
    Cash FlowF13IF, CUMPRINC5년차 말 대출 잔액—Start Here B34가 INDEX로 매각 연도 잔액을 골라요.
    Cash FlowF15IFIRR용 레버리지 현금흐름F11A열이 0년차 자기자본 유출이고, Start Here B37이 보유 기간 + 1 셀에 IRR을 돌려요.
    Start HereB33IF, INDEX매각가B11, B12, B14순매각대금이 매각 비용과 대출 잔액을 빼요.
    Start HereB37IFERROR, IRR, OFFSET레버리지 IRRB11레버리지를 포함한 자기자본 수익률이에요.
    SensitivityC7 10행까지 아래로 복사됨IFNOI 변화와 캡레이트별 가치—매입 캡을 쓰면 가운데 셀이 매입가와 같아요.
    SensitivityC16 19행까지 아래로 복사됨PMT금리와 LTV별 현금수익률—금리가 움직일 때 레버리지가 양쪽으로 작용하는 걸 보여 줘요.

    모든 수식 설명

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

    순영업소득(NOI)Pro Forma!B22
    =B11-B21

    유효총수입 − 총운영지출. 부채, 감가상각, 적립금 외 자본지출은 없어요.

    부분별 설명

    B11
    EGI = 총잠재수입 × (1 − 공실).
    -B21
    총운영지출.

    예시 데이터로 보면: B11 = 236,316, B21 = 99,115.8 → B22 결과 137,200.2

    어떻게 쓰이나: Start Here의 모든 것이 여기서 시작해요.

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

    배우기: 엑셀 기본 수식 작성법: 더하기, 빼기, 곱하기, 나누기 →
    매입 캡레이트Start Here!B20
    =IF(B6=0, 0, B19/B6)

    1년차 NOI ÷ 매입가.

    부분별 설명

    B6=0
    가격 없음: 0.
    B19/B6
    NOI ÷ 가격.

    예시 데이터로 보면: B19 = 137,200.2, B6 = 2,100,000 → B20 결과 6.5%

    어떻게 쓰이나: 가치 표의 머리글이 이 값을 중심으로 만들어져요.

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

    배우기: 엑셀 기본 수식 작성법: 더하기, 빼기, 곱하기, 나누기 →
    연 원리금Start Here!B23
    =IF(B21=0, 0, -PMT(B9/12, B10*12, B21)*12)

    대출의 월 상환액 12회. PMT는 음수를 주므로 부호를 바꿔요.

    부분별 설명

    B9/12
    월 금리.
    B10*12
    상환 횟수.
    B21
    대출액.
    -PMT(…)*12
    연 합계, 양수.

    예시 데이터로 보면: B9 = 6.8%, B10 = 30, B21 = 1,365,000 → B23 결과 106,240.37

    어떻게 쓰이나: 현금흐름, DSCR, 손익분기 입주율이 써요.

    바꾸는 방법: 이자만 내면 B21*B9를 쓰세요.

    배우기: 엑셀 필수 함수 SUM, AVERAGE, COUNT, MIN, MAX 사용법 →
    손익분기 입주율Start Here!B28
    =IF('Pro Forma'!$B$9=0, 0,
    ('Pro Forma'!$B$21+B23)/'Pro Forma'!$B$9)

    NOI가 원리금을 정확히 감당하는 입주율: (지출 + 원리금) ÷ 총잠재수입.

    부분별 설명

    'Pro Forma'!$B$21
    운영지출.
    +B23
    원리금.
    /'Pro Forma'!$B$9
    총잠재수입으로 나눔.

    예시 데이터로 보면: B23 = 106,240.37 → B28 결과 81.7%

    어떻게 쓰이나: 돈을 더 넣기 전까지 거래가 흡수할 수 있는 공실이에요.

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

    배우기: 엑셀 기본 수식 작성법: 더하기, 빼기, 곱하기, 나누기 →
    5년차 말 대출 잔액Cash Flow!F13
    =IF('Start Here'!$B$21=0,
    0, 'Start Here'!$B$21+CUMPRINC('Start Here'!$B$9/12,
    'Start Here'!$B$10*12, 'Start Here'!$B$21,
    1, 5*12, 0))

    원 대출액 + 60회차까지 누적 원금 상환(CUMPRINC는 음수를 줌).

    부분별 설명

    'Start Here'!$B$9/12
    월 금리.
    'Start Here'!$B$10*12
    총 기간.
    'Start Here'!$B$21
    대출액.
    1, 60
    1회차부터 60회차까지.
    0
    기말 상환.

    예시 데이터로 보면: F13 결과 1,281,403.44

    어떻게 쓰이나: Start Here B34가 INDEX로 매각 연도 잔액을 골라요.

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

    배우기: 엑셀 필수 함수 SUM, AVERAGE, COUNT, MIN, MAX 사용법 →
    IRR용 레버리지 현금흐름Cash Flow!F15
    =F11+IF(5='Start Here'!$B$11, 'Start Here'!$B$35, 0)

    원리금 후 현금흐름에 매각 연도면 순매각대금을 더해요.

    부분별 설명

    F11
    원리금 후 현금흐름.
    IF(5='Start Here'!$B$11, 'Start Here'!$B$35, 0)
    매각 연도에만 매각대금 추가.

    예시 데이터로 보면: F11 = 50,330.07 → F15 결과 1,086,401.14

    어떻게 쓰이나: A열이 0년차 자기자본 유출이고, Start Here B37이 보유 기간 + 1 셀에 IRR을 돌려요.

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

    배우기: 엑셀 IF 함수 사용법 →
    매각가Start Here!B33
    =IF(B14=0, 0, INDEX('Cash Flow'!$B$9:$K$9,
    1, B11)*(1+B12)/B14)

    매수자는 받을 NOI로 가격을 매기므로 매각 연도 NOI를 1년 더 성장시켜 매각 캡으로 나눠요.

    부분별 설명

    INDEX('Cash Flow'!$B$9:$K$9, 1, B11)
    매각 연도 NOI.
    *(1+B12)
    1년 성장.
    /B14
    매각 캡으로 나눔.

    예시 데이터로 보면: B11 = 5, B12 = 3.0%, B14 = 6.8% → B33 결과 2,389,148.97

    어떻게 쓰이나: 순매각대금이 매각 비용과 대출 잔액을 빼요.

    바꾸는 방법: 매각 연도 NOI 자체로 가격을 매기려면 *(1+B12)를 지우세요.

    배우기: 엑셀 INDEX와 MATCH 함수 사용법 →
    레버리지 IRRStart Here!B37
    =IFERROR(IRR(OFFSET('Cash Flow'!$A$15, 0, 0, 1, B11+1)), "")

    자기자본 현금흐름의 IRR: 0년차 자기자본 유출, 연간 원리금 후 현금흐름, 매각 연도 매각대금. OFFSET이 Cash Flow 탭에서 정확히 보유 기간 + 1 셀을 가져와요.

    부분별 설명

    OFFSET('Cash Flow'!$A$15, 0, 0, 1, B11+1)
    0년차부터 매각 연도까지.
    IRR(…)
    현재가치를 0으로 만드는 수익률.
    IFERROR(…, "")
    IRR이 수렴하지 않으면 빈칸.

    예시 데이터로 보면: B11 = 5 → B37 결과 10.5%

    어떻게 쓰이나: 레버리지를 포함한 자기자본 수익률이에요.

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

    배우기: 엑셀 필수 함수 SUM, AVERAGE, COUNT, MIN, MAX 사용법 →
    NOI 변화와 캡레이트별 가치Sensitivity!C7 · 10행까지 아래로 복사됨
    =IF(C$5=0, 0, 'Start Here'!$B$19*(1+$A7)/C$5)

    행의 비율로 조정한 1년차 NOI를 열의 캡레이트로 나눠요. $A7이 열을, C$5가 행을 고정해 수식 하나로 표를 채워요.

    부분별 설명

    'Start Here'!$B$19*(1+$A7)
    행 머리글로 조정한 NOI.
    /C$5
    열 머리글 캡레이트로 나눔.

    예시 데이터로 보면: C7 결과 2,160,331.23

    어떻게 쓰이나: 매입 캡을 쓰면 가운데 셀이 매입가와 같아요.

    바꾸는 방법: 머리글 간격을 수정해 다른 범위를 시험하세요.

    배우기: 엑셀 상대 참조, 절대 참조, 혼합 참조 이해하기 →
    금리와 LTV별 현금수익률Sensitivity!C16 · 19행까지 아래로 복사됨
    =('Start Here'!$B$19+PMT($A16/12,
  'Start Here'!$B$10*12, 'Start Here'!$B$6*C$14)*12)/('Start Here'!$B$6*(1-C$14)+'Start Here'!$B$6*'Start Here'!$B$7)

    열의 LTV와 행의 금리로 잡은 대출의 연 상환액을 1년차 NOI에서 빼고, 그 LTV가 뜻하는 자기자본으로 나눠요.

    부분별 설명

    PMT($A16/12, 'Start Here'!$B$10*12, 'Start Here'!$B$6*C$14)*12
    이 금리와 대출액의 연 원리금(음수).
    'Start Here'!$B$19+…
    NOI − 원리금.
    /('Start Here'!$B$6*(1-C$14)+'Start Here'!$B$6*'Start Here'!$B$7)
    자기자본: 계약금 + 거래 비용.

    예시 데이터로 보면: C16 결과 5.0%

    어떻게 쓰이나: 금리가 움직일 때 레버리지가 양쪽으로 작용하는 걸 보여 줘요.

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

    배우기: 엑셀 상대 참조, 절대 참조, 혼합 참조 이해하기 →

    팁과 한계

    • 매각 캡이 가장 큰 단일 가정이에요. 자기자본 배수 표에서 예시는 매각 캡 6.75%에서 1.6x, 7.25%와 성장 1%에서 1.1x로 떨어져요. 중심이 아니라 비관적인 모서리로 검토하세요.
    • 세금은 보통 매각 시 재평가돼요. 매도인의 세금이 아니라 매각 후 세금을 프로포마에 넣으세요.
    • 부채수익률(NOI ÷ 대출)은 캡레이트가 움직일 때 대출기관이 의지하는 숫자예요. 8–10%가 흔한 하한이에요.
    • 표는 혼합 참조($A7, C$5)를 써서 엑셀, 구글 시트, 리브레오피스, Numbers에서 모두 동작해요. What-If 데이터 표는 그렇지 않아요.
    • 밸류애드 거래는 Cash Flow 성장 행을 직접 수정해 2–3년차 안정화 NOI를 모델링하세요. IRR 행이 따라와요.

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

    튜토리얼엑셀 필수 함수 SUM, AVERAGE, COUNT, MIN, MAX 사용법튜토리얼엑셀 상대 참조, 절대 참조, 혼합 참조 이해하기튜토리얼엑셀 INDEX와 MATCH 함수 사용법튜토리얼엑셀 IF 함수 사용법튜토리얼엑셀 가상 분석: 목표값 찾기, 시나리오, 데이터 표 사용법튜토리얼엑셀 조건부 서식 사용법

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