엑셀 손익분석 완전 정복 | 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%)보다 낮게 계산된다면 은행에 넣는 것이 더 나을 것이라고 볼 수 있습니다.
IRR은 투자 수익률 지표입니다. ⭐ 할인율 (Discount Rate)
할인율은 미래의 돈을 현재 가치로 환산할 때 쓰는 비율입니다. 예를 들어, 물가가 매년 5%씩 상승한다면 15년 뒤에 돈의 가치는 2배로 떨어집니다. (예: 1만원을 5% 이율의 예금으로 15년을 넣으면, 만기시 2만원을 받습니다)
따라서 투자에 대한 수익으로 15년 뒤 1만원의 수익이 예상된다면, 이를 현재 가치로 환산할 때 쓰는 빈율이 할인율입니다. 할인율은 일반적으로 7% ~ 15% 사이로 계산합니다. (예: 할인율 8%일 시, 10년 뒤 2배로 증가)
할인율은 미래의 돈을 현재 가치로 환산할 때 쓰는 비율입니다.
목표값 찾기로 손익분기점 계산하기
사업을 운영하다보면 "월 몇 개를 팔아야 손익분기점을 넘길 수 있을까?" 라는 고민을 하게 됩니다. 이 때, 엑셀의 '목표값 찾기' 기능을 활용하면 복잡한 수식 없이도 단 몇 번의 클릭만으로 정확한 손익분기점을 계산할 수 있습니다.
- 손익계산서 기본 양식 만들기 : 먼저 예제파일을 실행하고 [기초] 시트로 이동한 후, 아래와 같이 손익계산서의 비어있는 항목을 하나씩 입력합니다. (기초 예제에서는 쉬운 계산을 위해 원가는 매출에 반영했다고 가정 후 진행합니다.)
· 일 매출(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
손익계산서에 비어있는 항목을 하나씩 채웁니다. - 위와 같이 수식을 모두 입력하면 영업이익으로 -3,152,773 원이 계산됩니다. 손실로 계산되므로, 영업이익이 0원이 되는 손익분기점을 찾아보겠습니다. 먼저 손익분기점을 맞추기 위한 초기투자비용을 계산합니다.
영업이익이 0원이 되는 초기투자비용을 찾습니다. - 목표값 찾기로 손익분기점 찾기 : 기초시트의 J-R 열 범위를 선택한 후, 우클릭 - [숨기기 취소]를 하면 목표값 찾기 기능에 대해 간략하게 정리한 내용을 확인할 수 있습니다.
J:R 범위의 숨기기를 취소하면 목표값찾기 설명이 정리되어있습니다. - [데이터] 탭 - [가상 분석] - [목표값 찾기] 를 실행합니다.
"수식셀"의 값이 "찾는 값"이 되기 위한 "값을 바꿀 셀"의 값을 찾습니다.
데이터 - 가상분석 - 목표값 찾기를 실행합니다. - 목표값 찾기가 실행되면, 다음과 같이 수식 셀, 찾는 값, 값을 바꿀셀을 하나씩 입력하고 [확인] 버튼을 클릭합니다.
· 수식 셀 : D19
· 찾는 값 : 0
· 값을 바꿀 셀 : C15
목표값 찾기에 항목을 하나씩 입력합니다. - 확인 버튼을 클릭하면 아래와 같이 초기투자비용이 3억 1천만원 정도가 되었을 때 손익분기점으로 계산되는 것을 확인할 수 있습니다.
손익분기점 달성을 위한 초기투자비용이 계산됩니다. - 영업이익을 위한 판매랑 찾기 : 이번에는 초기투자비용을 3억 1천만원으로 낮췄을 때, 영업이익이 200만원이 되는 판매량을 찾아보겠습니다. 이전과 동일하게 [데이터] 탭 - [가상 분석] - [목표값 찾기]를 실행한 후 아래와 같이 값을 하나씩 입력합니다.
· 수식 셀 : D19
· 찾는 값 : 2000000
· 값을 바꿀 셀 : C4
영업이익이 200만원이 되는 판매량을 계산합니다. - 확인 버튼을 클릭하면 약 449잔을 팔았을 때 영업이익이 200만원으로 맞춰지는 것을 확인할 수 있습니다.
영업이익이 200만원이 되는 판매량이 계산됩니다.
데이터표+조건부서식으로 영업이익 분석 표 만들기
"판매가격을 5% 올리고 제품을 200개 만큼 더 판다면 영업이익이 얼마나 증가할까?" 이런 가정(What-if) 분석은 사업 의사결정에서 매우(x100) 중요합니다. 이러한 다양한 상황별 분석이 필요할 때, 엑셀의 '데이터 표' 기능을 사용하면 각 시나리오별 예상되는 결과를 편리하게 비교분석할 수 있습니다.
- 시나리오 분석 표 만들기 : 이번에는 평균 가격과 판매량이 특정 범위 안에서 바뀔 때 계산되는 영업이익을 시나리오별로 분석하는 데이터 표를 만들어 보겠습니다. 예제파일에서 판매량(U5:Z5)과 평균가격(T6:T14)의 범위를 자동채우기해서 값을 입력합니다.
판매량과 평균가격을 자동채우기로 입력합니다. - 판매량과 평균가격이 바뀌었을 때 계산할 값은 '영업이익'입니다. 따라서, 판매량과 평균가격 머리글이 만나는 T5셀에 영업이익 셀을 참조해서 입력합니다.
두 머리글이 만나는 지점에 결과를 계산할 셀을 참조해서 입력합니다. - 이후 데이터표를 작성할 T5:Z14 범위를 선택한 후, [데이터] 탭 - [가상 분석] - [데이터 표]를 선택해서 데이터 표 기능을 실행합니다.
데이터 표 기능을 실행합니다. - 데이터 표에서 행(상단) 머리글은 판매량이므로 C4셀을 참조하고, 열(좌측) 머리글은 평균가격인 C5셀을 각각 참조합니다.
행 입력, 열 입력 셀을 입력합니다. - [확인] 버튼을 클릭하면 아래와 같이 각 판매량과 평균가격별 영업 이익의 시나리오 분석 표가 완성됩니다.
각 판매량과 평균가격별 시나리오 분석표가 완성됩니다. - 조건부 서식으로 시각화하기 : 이제 조건부서식을 적용해서 데이터 표를 시각화하겠습니다. 데이터가 입력된 U6:Z14 범위를 선택한 후, [홈] 탭 - [조건부서식] - [색조] - [기타 규칙]으로 이동합니다.
시각화 할 범위를 선택한 후, 조건부서식 - 색조 - 기타규칙으로 이동합니다. - 새 서식 규칙 대화상자가 실행되면 서식 스타일로 [3가지 색조]를 선택하고 중간값의 종류는 [숫자], 값은 0으로 변경합니다. 이후 색을 흰색으로 선택하고 [확인] 버튼을 클릭합니다.
색조의 서식 스타일을 변경합니다. - 색조가 적용되면서 시나리오 분석 데이터 표가 완성됩니다. 이제 판매량과 평균 가격을 바꿔가면서 실시간으로 판매랑/평균가격별 영업이익 시나리오를 분석할 수 있습니다.
시나리오 분석 표가 완성되었습니다.
투자 손익분석을 위한 실전 손익분석표 만들기
이번에는 실제 창업이나 신규 사업투자를 가정하고, 프랜차이즈 창업을 예시로 초기 투자비부터 연간 고객수, 영업 이익까지 체계적으로 예측하는 손익분석표를 만드는 방법을 단계별로 알아보겠습니다.
- 연간 손익분석표 만들기 : 이번에는 본격적으로 '펫 케어' 관련 창업을 하는 가정하에 손익분석표를 만들고 투자 수익률까지 단계별로 계산해보겠습니다. 예제파일에서 [갓빠두 펫케어] 시트로 이동한 후, 투자수익률 예측 관련 항목을 다음과 같이 입력합니다.
· 초기투자비용 : =E13+H40*3
· 연간 고객수 : =H5*12
· 할인율 : 8%
· 회원유지율 : 45%
· 세율 : 19%
갓빠두펫케어 시트에서 수익률 예측 관련 항목을 입력합니다. - 이제 연간 손익분석표를 작성합니다. 먼저 0년차에는 초기투자금액만 발생하므로, 초기투자비에 =초기투자비용*-1 을 입력하고 나머지 항목은 0으로 작성합니다.
0년차 손익 분석 항목을 입력합니다. - 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-세율), 세율은 절대참조로 입력
1년차 손익 분석 항목을 입력합니다. - 작성한 수식을 선택한 후, 오른쪽으로 자동채우기하면 각 년차별 손익분석표가 완성됩니다.
1년차에 작성한 수식을 오른쪽으로 자동채우기해서 손익분석표를 완성합니다. - 연간 고객수별 변동비 계산하기 : 이전 단계에서 작성한 비용은 월 고객수에 따라 바뀌는 변동비로 수정해서 다시 작성해야 합니다. 이때 데이터표를 사용하면 고객수에 따라 바뀌는 변동비용을 편리하게 계산할 수 있습니다.
데이터 표를 사용하면 매년 다른 고객수별 비용을 편리하게 계산할 수 있습니다. - 먼저 데이터표의 머리글로 사용할 월 고객수 범위를 선택해서 복사한 후, 우클릭 - 선택하여 붙여넣기에서 값형식으로 붙여넣습니다.
고객수 범위를 복사한 후 값 형식으로 붙여넣기합니다. 오빠두Tip : 데이터 표 기능은 머리글이 '값'으로 작성된 경우에만 올바르게 동작하며, 머리글이 수식일 경우 잘못된 값이 계산되는 점을 주의하세요!😊 - 이렇게 표를 만들면 상단 머리글에는 "고객수"를 참조하고, 값으로는 월 비용(H40)을 참조하여 계산하는 데이터표를 만들 수 있습니다.
각 고객수별 비용 계산을 위한 범위가 완성되었습니다. - 데이터 표를 생성할 W19:AD20 범위를 선택하고 [데이터] 탭 - [가상분석] - [데이터 표]를 실행한 후, 행 입력 셀로 H5셀을 선택합니다.
데이터표를 실행한 후, 행 머리글로 고객수 셀을 선택합니다. - 확인 버튼을 클릭하면 매월 변동되는 고객수에 대한 월 비용이 계산되며 년간 손익분석표가 완성됩니다.
연간 손익분석표가 완성됩니다.
NPV/IRR 함수로 투자 수익률 분석하기
이전 단계에서 손익분석표를 완성했다면, 이제 "이 사업에 투자했을 때 몇 년 안에 원금을 회수할 수 있을까?", "얼마 만큼의 수익이 발생할까?" 또는 "위험할까" 등 사업을 운영하는 사람이라면 누구나 궁금해하는 질문에 대해 NPV 함수, IRR 함수와 MATCH 함수를 조합해 명쾌하게 답을 구할 수 있습니다.
- 잉여현금흐름 구하기 : 년간 손익분석표를 만들면 엑셀의 기본 함수를 활용해 순현재가치, 투자수익률, 투자회수기간 등을 편리하게 계산할 수 있습니다. 먼저 각 지표를 구하기 위해 누적 당기순이익과 '잉여현금흐름(OCF)'를 계산하겠습니다. 누적당기순이익 밑에 '잉여현금흐름' 항목을 추가합니다.
당기 순이익 밑에 잉여현금흐름 항목을 추가합니다. - 0년차 누적 당기순이익은 윗 셀을 그대로 참조하고, 1년차 누적 당기순이익은 아래와 같이 수식을 작성해서 오른쪽으로 자동채우기하면 누적 당기순이익이 계산됩니다.
누적 당기순이익을 계산합니다. - 잉여현금흐름은 당기순이익에서 감가상각비용을 더해서 계산합니다. (실제 현금 지출인 세금을 비용으로 포함하고, 그 외 현금 지출은 제외)
=당기순이익+감가상각비
당기순이익에 감가상각을 더해 잉여현금흐름을 계산합니다. - NPV, IRR, 투자 회수기간 구하기 : 이제 NPV(순 현재가치)와 IRR(투자 수익률)을 계산해보겠습니다. Z6셀에는 아래와 같이 NPV 함수를 작성합니다. NPV 함수를 입력하면 현재 투자하는 9,100만원의 비용이 2억 4천만원 정도의 현재 가치를 갖는 것을 알 수 있습니다.
=NPV(V8,W26:AD26)-V6
'=NPV(할인율,금액범위)-초기투자비용
'금액범위에는 0년차를 제외하고, 결과값에서 초기투자비용을 빼주는 것을 주의하세요!
NPV 함수로 투자비용의 순현재가치를 계산합니다. - 이제 IRR(투자 수익률)을 구합니다. Z7셀에 아래와 같이 IRR 함수를 작성합니다. 함수를 입력하면 투자 수익률로 약 53%가 계산됩니다. (이대로 운영된다면 굉장히 좋은 사업이지만, 이는 예측치이므로 꼼꼼한 시장조사와 신중한 결정이 필요합니다..!!^^;;)
=IRR(V26:AD26)
IRR 함수로 투자 수익률을 계산합니다. - 마지막으로 투자 회수기간은 MATCH 함수로 구할 수 있습니다. 다음 Z8 셀에 다음과 같이 함수를 입력하면 투자 회수기간이 약 3년으로 계산됩니다. 또는 AB8셀에 남겨드린 공식을 사용하면 보다 정확한 투자 회수 기간을 구할 수 있습니다.
=MATCH(0,V25:AD25)
MATCH 함수 또는 미리 작성해드린 공식으로 투자금 회수 기간을 계산합니다.
