IRR 계산기 (일별, 월별, 불특정 구간 적용 가능)

	B	C	D	E	F	G	H	I	J	K	L
2		0	1	2	3		15.000%	10079.52	15.000%	74.901%	1
3	7.63%	-10,000.00	2,000.00	2,000.00	8,000.00		44.950%	8114.846	15.000%	74.901%	0
4	7.63%	10,000.00	1,858.14	1,726.33	6,415.53		29.975%	8985.317	15.000%	44.950%	0
5							22.488%	9499.352	15.000%	29.975%	0
6		2007-01-01	2007-03-31	2007-06-30	2007-12-31		18.744%	9780.391	15.000%	22.488%	0
7		0	3	6	12		16.872%	9927.586	15.000%	18.744%	0
8		0	0.25	0.5	1		15.936%	10002.95	15.000%	16.872%	1
9		-10,000.00	500	500	10,500.00		16.404%	9965.116	15.936%	16.872%	0
10	15.97%	9,999.98	481.816027	464.293368	9,053.87		16.170%	9983.993	15.936%	16.404%	0
11							16.053%	9993.46	15.936%	16.170%	0
12	IRR	최대	74.901%				15.994%	9998.2	15.936%	16.053%	0
13		최소	15.000%				15.965%	10000.57	15.936%	15.994%	1
14		오차허용	0.1				15.980%	9999.386	15.965%	15.994%	0
15							15.973%	9999.979	15.965%	15.980%	0
16							★		15.965%	15.973%	1
17							★				1

 

 

1. 엑셀 IRR함수의 개념 설명을 위한 표 (B2:F4)

C2:F3에 연 단위 구간 입력, C3:F3에 각 구간의 현금 흐름 입력

B3 =IRR(C3:F3,4)

D3 =D3/(1+$B4)^D2

C3 =SUM(D4:F4)

 

2. IRR 계산기 B6:F16

C6:F6, C7:F7에 과제의 4구간 일단 입력, C8:F8에 연단위 구간이 C2:F2와 같이 표시되도록 수식 설정. (예시의 경우 월 구간이므로 C8 =C7/12, C8잡아 F8까지 복사.) C9:F9에 과제의 처음 4구간에 해당하는 현금 흐름 입력. C4:F4를 C10:F10에 복사해 넣음.

D12 =(SUM(D9:F9)/ABS(C9))^(1/D8)-1

D13 =(SUM(D9:F9)/ABS(C9))^(1/F8)-1

D14 =1*10^(-1)   (원하는대로 바꾸어도 됨. 아래 설명 참조)

 

3. IRR 자동 계산 흐름표 (H2:L17)

H2 =D13

I2 =SUM(D$9:F$9/(1+H2)^D$8:F$8)  ; 수식 입력 후 CTRL+SHIFT+ENTER

J2 =D13

K2 =D12   ; 이상 변수 초기화

L2 =N(I2>ABS(C$9))  ; I2의 NPV 합계가 투자액을 넘을 때 1이 되는 플래그. L2를 L3에 복사.

H3 =IF(H2="★","★",IF(ABS(I2-ABS(C$9))<D$14,"★",(2*L2-1)*(K3-J3)/2+H2))

I3 =IF(H3="★","",SUM(D$9:F$9/(1+H3)^D$8:F$8))  ; 수식 입력 후 CTRL+SHIFT+ENTER

J3 =IF(I2="","",L2*H2+ABS(L2-1)*J2)

K3 =IF(I2="","",ABS(L2-1)*H2+L2*K2)

; H3:L3 잡아 아래로 복사. 복사해야 하는 행 수는 D14의 허용오차에 달려 있음. 대략 한 오더 더 엄밀하게 찾기 위해 5행이 더 소요됨. 즉, 오차허용 범위가 1E(-10)일 때 50행 필요.

B10 =INDIRECT(ADDRESS(MIN(IF(H2:H100="★",ROW(H2:H100),ROW(H101)))-1,COLUMN(H2),4))   ; 수식 입력 후 CTRL+SHIFT+ENTER. 여기에 구하는 답이 찍히게 됨.

 

4. 과제에 맞추어 계산기 표 확장

과제의 현금흐름표 구간이 4개 이상인 n개일 때, F열을 잡아 C9부터 n개의 현금흐름 데이타를 입력할 수 있을 때 까지 열 삽입을 함. 이렇게 함으로써 위 수식들의 셀참조가 자동으로 고쳐짐. 이제 과제의 구간을 C8부터 오른쪽으로 n개, 현금흐름을 C9부터 오른쪽으로 n개 입력하면 B10에 구하는 IRR이 찍힐 것임.

 

5. 이 계산기를 만든 배경

지식in의 엑셀게시판, 경영-산업 게시판에 심심찮게 IRR 계산을 요청하는 질문이 올라옴. 그런 질문에 때때로 답하다가 계산기가 없나? 하는 의문이 들어 뒤져보니 엑셀로는 없는 듯. (보다 복잡한 계산을 하려면 공학용 또는 재무계산기를 사라는 답이 꽤 제시되어 있음)  그래서 시행착오를 거쳐 완성한 바, 기왕이면 오픈백과에 걸어두는 것이 보다 영양가 있겠다는 생각을 하게 됨.

 

6. 엑셀 IRR함수의 한계

위 예시의 B3에서 보듯 엑셀이 지원하는 IRR함수는 IRR(범위, 초기값)의 문법인 바 엑셀은 주어진 범위(혹은 값들)를 0년차, 1년차, 2년차...의 현금흐름으로 인식함. 이것은 통상 IRR을 계산하는 방식인데, 연단위라는 한계가 있음. 즉 일별, 월별, 10년 구간 따위를 계산하려면 개인이 그때그때 맞추어 프로그래밍 또는 단순 노동을 해야 함. (필자가 쓰고 있는 엑셀2002 기준)

 

7. IRR과 NPV의 개념

IRR은 투자 후에 벌어들이는 수익(정확히는 창출하는 현금)의 현재가치(NPV) 합이 투자액과 같아지는 할인율임. 다른 말로는 0차년도부터 마지막 구간까지 현금흐름의 NPV 총계가 0이 되는 할인율(=이자율). 보다 자세하게 설명하기 위해 NPV(Net Present Value ; 현재 가치(=現價))부터 살펴보면, 위 예시의 D3셀 2000은 1차년도 현금창출액임. 1년후 2000원은 현재 얼마에 해당할까? 하는 것이 NPV임.

 

위 예시에서는 (예를 들어) 은행 이자율이 7.63%일 때 현재 시점 1,858.14(D4 셀)가 1년후 2000에 해당됨을 보여주고 있음. (원금 a, 이자율 r일 때 n년 후 원리합계 b=a(1+r)^n...) 마찬가지로 2년 후, 3년 후 데이타 역시 현재가치로 환산할 수 있음. 그 현재 가치를 모두 모으면 C4의 10,000이 됨. 그럼 이자율 7.63%일 때 0차년도부터 3차년도까지 현금흐름의 현재 가치 합계는 0이 됨. 거꾸로 말해 이 현금흐름표의 IRR은 7.63%인 것임.

 

n차년도 현금흐름을 현재가로 환산하는 것은 b=a(1+r)^n를 가공하면 어렵지 않음. 즉, b, r, n을 알면 a를 구할 수 있음. (a=b/(1+r)^n ; D4의 수식임) 문제는 r을 모를 때인데 엑셀에서는 '목표값 찾기'라는 기능이 있음. 어떤 이자율일 때 각 구간의 현재값을 모두 더하면 투자액과 절대치가 같아질까? 를 찾기 위해 도구-목표값 찾기 선택, 수식셀 : C10, 찾는 값 : 10000, 값을 바꿀 셀 : B10 입력, 확인하면 구하는 이자율이 B4에 뜸. 이 작업을 한방에 하는 것이 엑셀의 IRR함수이며 B3=IRR(C3:F3,4) 식으로 입력하면 B4와 똑같은 값이 B3에 출력됨. (이 수식에서 4는 임의의 값임. 대충 4%에서 시작해서 네가 알아서 찾아라는 뜻.)

 

이 원리로, 투자 이후 창출하는 현금흐름이 클수록, 기간이 짧을수록 이자율인 IRR은 커지게 되며 투자의사결정의 주요 참조 기준이 됨. (은행이자율보다는 높아야 되잖아? 그거보다 낮으면 그 돈 투자 않고 저금하지 왜 사업하나? 게다가 폭삭 망할 수 있다는 위험부담을 감안하면 적어도 은행이자율 보다 5%는 높아야지...따위.)

 

8. IRR계산기, 계산 흐름 수식 개요

IRR이 어떤 값이고 어떻게 계산되는지를 이해한 후엔 일별, 월별, 불특정 구간(1년, 3년, 5년, 15년...따위)이 주어지더라도 맞춤 계산이 가능할 것임. 그 중 하나의 예시를 보인 것이 B6:F10의 표로서, 월별 현금흐름 데이타가 주어진 경우임. 그 구간을 투자 n년 후로 가공하고 같은 수식을 적용, 목표값 찾기를 하면 됨. 엑셀은 IRR을 % 소수 15째 자리까지 찾는데 근사값 범위가 주요의사결정 기준으로 작용될 때도 있을 터이고 그 이상 엄밀히 찾을 길을 제시하기 위해 목표값 찾기가 아닌 자동계산을 하도록 한 것이 K:L열임. (사실 1E(-12) 이상이 되면 엑셀이 0으로 인식하므로 수치가 달라지지 않음.) 프로그래밍 로직 설명은 여기서는 생략하는데, 관심이 있는 이는 위 표를 마우스로 긁어 엑셀에 붙여 넣고 수식 역시 해당 셀에 복사해 넣은 후 살펴보십사 함.