엑셀 손익분석 완전 정복 | NPV·IRR·손익분기점 실무 완벽 가이드!

NPV/IRR/할인율이란? 빠르게 살펴보기

사업과 투자 의사결정에서 가장 중요한 항목은 바로 '숫자로 된 확실한 근거'입니다. 따라서, 이번 강의에서는 사업과 투자 분석의 핵심 지표인 NPV(순현재가치), IRR(내부수익률), 할인율 개념을 빠르게 정리해보겠습니다.

⭐ NPV(Net Present Value, 순 현재가치)란? 

돈의 가치는 시간이 지날수록 변하며, 일반적으로 점점 낮아집니다. 따라서 현재의 1만원과 20년 뒤의 1만원은 같은 금액이라도 실질 가치가 다릅니다.

투자는 일반적으로 오랜 기간에 걸쳐 수익이 발생하므로, 미래에 얻게 될 수익을 현재 시점의 가치로 환산해야 합니다. 이렇게 계산한 값이 바로 NPV 입니다.

⭐ IRR(Internal Rate of Return, 내부수익률 = 투자수익률)

IRR은 흔히 '투자 수익률' 지표로 사용됩니다. 은행에 100만원을 예치하면 1년 뒤 정해진 이자율만큼 이자를 받듯이, 투자로 얻을 수 있는 연간 수익률을 의미합니다.

예를 들어 A사업의 IRR이 15%, B사업의 IRR이 8%라면 같은 금액을 투자할 때 A사업이 더 높은 수익을 기대할 수 있다고 해석합니다. 단, IRR이 은행 이자율(예: 5%)보다 낮게 계산된다면 차라리 은행에 예치하는 것이 더 유리한 선택이 됩니다.

⭐ 할인율 (Discount Rate)

할인율은 미래의 돈을 현재 가치로 환산할 때 사용하는 비율입니다. 예를 들어 매년 5%의 수익률로 자산이 불어난다면 약 15년 뒤에는 가치가 2배로 증가합니다. (예: 1만원을 5% 이율의 예금에 15년 동안 예치하면 만기 시 약 2만원이 됩니다.)

따라서 15년 뒤 1만원의 수익이 예상되는 투자라면, 이를 현재 가치로 환산할 때 사용하는 비율이 할인율입니다. 할인율은 일반적으로 7% ~ 15% 사이로 적용하며, 사업의 위험도에 따라 조정합니다. (예: 할인율 8%를 적용하면 약 10년 뒤 2배 수준으로 증가)

목표값 찾기로 손익분기점 계산하기

사업을 운영하다 보면 "월 몇 개를 팔아야 손익분기점을 넘길 수 있을까?" 라는 고민을 자주 마주하게 됩니다. 이때 엑셀의 '목표값 찾기' 기능을 활용하면 복잡한 수식 없이도 단 몇 번의 클릭만으로 정확한 손익분기점을 계산할 수 있습니다.

1. 손익계산서 기본 양식 만들기 : 먼저 예제파일을 실행하고 [기초] 시트로 이동한 다음, 아래 수식을 참고하여 손익계산서의 비어 있는 항목을 하나씩 입력합니다. (기초 예제에서는 계산을 단순화하기 위해 원가가 매출에 이미 반영되어 있다고 가정합니다.)
   · 일 매출(C6셀) : =C4*C5
   · 매출 합계(D7셀) : =C6*22+C6*8*40%
   · 직원수(F11셀) : =ROUNDDOWN(C4/300,0)+2
   · 로얄티(C12셀) : =D7*4%
   · 감가상각비(D17셀) : =C15/C16/12
   · 영업이익(D19셀) : =D7-D13-D17

   

2. 위와 같이 수식을 모두 입력하면 영업이익이 -3,152,773원으로 계산되어 손실 상태임을 확인할 수 있습니다. 이제 영업이익이 0원이 되는 손익분기점을 찾아보겠습니다. 먼저 손익분기점을 맞추기 위한 적정 초기투자비용을 구합니다.
3. 목표값 찾기로 손익분기점 찾기 : 기초시트의 J-R 열 범위를 선택한 뒤 우클릭 - [숨기기 취소]를 실행하면 목표값 찾기 기능에 대해 간략하게 정리한 안내를 확인할 수 있습니다.
4. [데이터] 탭 - [가상 분석] - [목표값 찾기] 를 실행합니다.
   "수식셀"의 값이 "찾는 값"이 되기 위한 "값을 바꿀 셀"의 값을 찾습니다.

   

5. 목표값 찾기 대화상자가 열리면 아래와 같이 수식 셀, 찾는 값, 값을 바꿀 셀을 하나씩 입력한 뒤 [확인] 버튼을 클릭합니다.
   · 수식 셀 : D19
   · 찾는 값 : 0
   · 값을 바꿀 셀 : C15

   

6. 확인 버튼을 클릭하면 아래와 같이 초기투자비용이 약 3억 1천만원일 때 손익분기점이 형성되는 것을 확인할 수 있습니다.
7. 영업이익을 위한 판매랑 찾기 : 이번에는 초기투자비용을 3억 1천만원으로 낮췄을 때, 영업이익이 200만원이 되도록 만드는 판매량을 구해보겠습니다. 앞서 살펴본 것과 동일하게 [데이터] 탭 - [가상 분석] - [목표값 찾기]를 실행한 후 아래와 같이 값을 입력합니다.
   · 수식 셀 : D19
   · 찾는 값 : 2000000
   · 값을 바꿀 셀 : C4

   

8.  확인 버튼을 클릭하면 약 449잔을 판매했을 때 영업이익이 200만원에 도달하는 것을 확인할 수 있습니다.

데이터표+조건부서식으로 영업이익 분석 표 만들기

"판매가격을 5% 올리고 제품을 200개 더 판매하면 영업이익은 얼마나 증가할까?" 이러한 가정(What-if) 분석은 사업 의사결정에서 매우 중요한 과정입니다. 다양한 상황별 분석이 필요할 때 엑셀의 '데이터 표' 기능을 사용하면 각 시나리오별 예상 결과를 한눈에 비교 분석할 수 있습니다.

1. 시나리오 분석 표 만들기 : 평균 가격과 판매량이 특정 범위 안에서 변할 때의 영업이익을 시나리오별로 분석하는 데이터 표를 만들어 보겠습니다. 예제파일에서 판매량(U5:Z5)과 평균가격(T6:T14)의 범위를 자동채우기로 채워 넣습니다.
2. 판매량과 평균가격이 변할 때 계산할 결과값은 '영업이익'입니다. 따라서 판매량과 평균가격 머리글이 만나는 T5셀에 영업이익 셀을 참조하여 입력합니다.
3. 이후 데이터표를 작성할 T5:Z14 범위를 선택한 뒤, [데이터] 탭 - [가상 분석] - [데이터 표]를 선택하여 데이터 표 기능을 실행합니다.
4. 데이터 표 대화상자에서 행(상단) 머리글은 판매량을 의미하므로 C4셀을, 열(좌측) 머리글은 평균가격이므로 C5셀을 각각 참조합니다.
5. [확인] 버튼을 클릭하면 아래와 같이 판매량과 평균가격별 영업이익을 한눈에 비교할 수 있는 시나리오 분석 표가 완성됩니다.
6. 조건부 서식으로 시각화하기 : 이제 조건부서식을 적용하여 데이터 표를 시각화해보겠습니다. 데이터가 입력된 U6:Z14 범위를 선택한 후, [홈] 탭 - [조건부서식] - [색조] - [기타 규칙]으로 이동합니다.
7. 새 서식 규칙 대화상자가 실행되면 서식 스타일을 [3가지 색조]로 선택하고, 중간값의 종류는 [숫자], 값은 0으로 변경합니다. 이후 중간값의 색상을 흰색으로 지정한 뒤 [확인] 버튼을 클릭합니다.
8. 색조가 적용되면서 시나리오 분석 데이터 표가 완성됩니다. 이제 판매량과 평균가격을 변경할 때마다 실시간으로 판매량/평균가격별 영업이익 시나리오를 비교할 수 있습니다.

투자 손익분석을 위한 실전 손익분석표 만들기

이번에는 실제 창업이나 신규 사업 투자를 가정하여, 프랜차이즈 창업을 예시로 초기 투자비부터 연간 고객수, 영업이익까지 체계적으로 예측하는 손익분석표를 단계별로 만들어 보겠습니다.

1. 연간 손익분석표 만들기 : '펫 케어' 분야 창업을 가정하여 손익분석표를 만들고 투자 수익률까지 단계별로 계산해보겠습니다. 예제파일에서 [갓빠두 펫케어] 시트로 이동한 뒤, 투자수익률 예측에 필요한 항목을 다음과 같이 입력합니다.
   · 초기투자비용 : =E13+H40*3
   · 연간 고객수 : =H5*12
   · 할인율 : 8%
   · 회원유지율 : 45%
   · 세율 : 19%

   

2. 이제 연간 손익분석표를 작성합니다. 0년차에는 초기 투자금액만 발생하므로, 초기투자비에 =초기투자비용*-1 을 입력하고 나머지 항목은 모두 0으로 작성합니다.
3. 1년차부터는 실제로 수익이 발생합니다. 아래 수식을 참고하여 1년차 항목을 하나씩 작성합니다.
   · 초기고객 : =V17 → 전년도 최종고객
   · 신규고객 : =$V$7 → 연간 고객수, 절대참조로 입력
   · 이탈고객 : =W14*(1-$V$9) → 초기고객 * (1-이탈률), 이탈율은 절대참조 입력
   · 최종고객 : =W14+W15-W16 → 초기고객+신규고객-이탈고객
   · 매출 : =W17*$H$6 → 최종고객 * 객단가
   · 월 고객수 : =W17/12 → 최종고객 / 12
   · 월 비용 : =H40
   · 비용 :=W20*12 → 비용 * 12
   · 감가상각 : =$H$13
   · 영업이익 : =W18-W21-W22 → 매출 - 비용 - 감가상각
   · 당기순이익 : =W23*(1-$V$10) → 영업이익*(1-세율), 세율은 절대참조로 입력

   

4. 작성한 수식을 선택한 뒤 오른쪽으로 자동채우기하면 각 년차별 손익분석표가 완성됩니다.
5. 연간 고객수별 변동비 계산하기 : 이전 단계에서 작성한 비용은 월 고객수에 따라 변동되는 변동비로 다시 작성해야 합니다. 이때 데이터표를 활용하면 고객수에 따라 변동되는 비용을 편리하게 계산할 수 있습니다.
6. 먼저 데이터표의 머리글로 사용할 월 고객수 범위를 선택하여 복사한 뒤, 우클릭 - 선택하여 붙여넣기 - 값 형식으로 붙여넣습니다.
   오빠두Tip : 데이터 표 기능은 머리글이 '값'으로 작성된 경우에만 올바르게 동작합니다. 머리글이 수식이면 잘못된 결과가 계산되므로 주의해야 합니다.
7. 이렇게 표를 구성하면 상단 머리글에는 "고객수"를 참조하고, 값에는 월 비용(H40)을 참조하는 데이터표를 만들 수 있습니다.
8. 데이터 표를 생성할 W19:AD20 범위를 선택한 뒤 [데이터] 탭 - [가상 분석] - [데이터 표]를 실행하고, 행 입력 셀로 H5셀을 지정합니다.
9. 확인 버튼을 클릭하면 매월 변동되는 고객수에 따른 월 비용이 계산되어 연간 손익분석표가 완성됩니다.

NPV/IRR 함수로 투자 수익률 분석하기

이전 단계에서 손익분석표를 완성했다면, 이제 "이 사업에 투자했을 때 몇 년 안에 원금을 회수할 수 있을까?", "수익은 얼마나 발생할까?", "투자 위험은 어느 정도일까?" 등 사업을 운영하는 누구나 궁금해할 질문에 대해 NPV 함수, IRR 함수, MATCH 함수를 조합하여 명확한 답을 구할 수 있습니다.

1. 잉여현금흐름 구하기 : 연간 손익분석표가 준비되었다면 엑셀의 기본 함수만으로 순현재가치, 투자수익률, 투자회수기간 등을 편리하게 계산할 수 있습니다. 먼저 각 지표를 구하기 위해 누적 당기순이익과 '잉여현금흐름(OCF)'을 계산합니다. 누적 당기순이익 아래에 '잉여현금흐름' 항목을 추가합니다.
2. 0년차 누적 당기순이익은 위 셀을 그대로 참조하고, 1년차부터는 아래와 같이 수식을 작성한 뒤 오른쪽으로 자동채우기하면 연도별 누적 당기순이익이 계산됩니다.
3. 잉여현금흐름은 당기순이익에 감가상각비를 더해 계산합니다. (실제 현금 지출인 세금은 비용으로 포함하고, 현금 유출이 아닌 감가상각비는 다시 더해주는 방식입니다.)

   =당기순이익+감가상각비

   

4. NPV, IRR, 투자 회수기간 구하기 : 이제 NPV(순현재가치)와 IRR(투자수익률)을 계산해보겠습니다. Z6셀에 아래와 같이 NPV 함수를 입력합니다. NPV 함수를 적용하면 현재 투입되는 9,100만원의 투자 비용이 약 2억 4천만원의 현재 가치를 갖는 것으로 계산됩니다.
   =NPV(V8,W26:AD26)-V6
   '=NPV(할인율,금액범위)-초기투자비용
   '금액범위에는 0년차를 제외하고, 결과값에서 초기투자비용을 빼주는 것을 주의하세요!

   

5. 이제 IRR(투자수익률)을 구합니다. Z7셀에 아래와 같이 IRR 함수를 입력합니다. 함수를 입력하면 투자 수익률이 약 53%로 계산됩니다. (이는 어디까지나 예측 시나리오이므로, 실제 투자 결정 시에는 꼼꼼한 시장조사와 신중한 검토가 반드시 필요합니다.)

   =IRR(V26:AD26)

   

6. 마지막으로 투자 회수기간은 MATCH 함수를 활용해 구할 수 있습니다. Z8 셀에 아래와 같이 함수를 입력하면 투자 회수기간이 약 3년으로 계산됩니다. 보다 정확한 회수기간이 필요하다면 AB8셀에 함께 제공한 공식을 사용해 소수점 단위까지 구할 수 있습니다.

   =MATCH(0,V25:AD25)