IRR 계산 (월 단위)

	B	C	D	E	F
2		0	1	2	3
3	7.635%	-10,000.00	2,000.00	2,000.00	8,000.00
4	7.635%	10,000.00	1,858.14	1,726.33	6,415.53
5
6		2007-01-01	2007-03-31	2007-06-30	2007-12-31
7		0	3	6	12
8		0	0.25	0.5	1
9		-10,000.00	100.00	100.00	10,100.00
10	3.038%	10,000.00	99.25	98.51	9,802.23

 

D8 = D7/12 ; 개월 수를 년 단위로 전환. D8 잡아 F8까지 복사.

C10 =SUM(D10:F10)

D4 =D3/(1+$B4)^D2  ; D4 잡아 오른쪽으로 F4까지 복사. C4:F4 잡아 C10:F10에 복사.

 

처음에 B10은 1%라고 입력합니다 (수치는 아무래도 상관 없음). 도구-목표값 찾기 선택, 수식셀 : C10, 찾는 값 : 10000, 값을 바꿀 셀 : B10 입력, 확인하면 구하는 IRR이 B10에 뜹니다.

 

수식 설명 : IRR은 이후에 벌어들이는 수익의 현재가치(NPV) 합이 투자액과 같아지는 할인율입니다. 표를 C3:F3와 같이 입력한 후 B3 =IRR(C3:F3,4) 이라고 입력하면 통상 구하는 IRR이 B3에 찍힙니다. 그런데 이 IRR함수는 데이타가 1년, 2년...발생한 것으로 자동 인식합니다. 예시에서 0년차는 마이너스(투자 금액), 1년차~3년차의 수익으로 인식하는 것이지요. 따라서 1년 이내 월 단위 수익이 제시된 경우 IRR은 엑셀의 IRR함수로 구할 수 없습니다. 할 수 없이 수작업을 해야지요.

 

수작업을 하기 위해 원리를 파악합니다. 1년차, 2년차,...각 해 수익이 지금 돈으로 얼마냐, 즉 NPV로 환산하면 얼마냐를 D4:F4와 같이 계산합니다. 원금이 a, 이자율 r일 때 n년 후 원리합계 b=a(1+r)^n이지요. 그걸 역산하면 b로부터 a를 구할 수 있습니다. 이렇게 각 해 수익의 NPV를 구해 모두 더하면 C4 =SUM(D4:F4)가 됩니다. 이제 C4가 투자액과 같아지는 이자율 B4를 목표값 찾기로 구하면 B4, 아까 구한 B3의 IRR과 같이 나옵니다. 이 원리로 월을 연단위로 바꾸고 (3개월 지났으면 0.25년) 마찬가지 작업을 한 것이 위 수식입니다.

 

위 표를 마우스로 긁어 엑셀에 붙이고 해당되는 셀 수식 창에 위의 순서로 수식을 (마우스로 긁고 CTRL+C 로) 복사하여 붙인 후 살펴보면 보다 쉽게 이해하실 수 있을 겁니다.