[기타 함수] 엑셀 XIRR , 비정기 현금 흐름의 내부수익률 계산

엑셀 XIRR 함수는 일정한 주기가 없는 현금 흐름에 대한 내부수익률을 구하는 재무함수이다. 이전 두 개의 내부수익률을 구하는 함수들( IRR, MIRR )은 정기적인 현금 흐름을 전재로 한 함수들이라는 것이 가장 큰 차이이다.

IRR, MIRR, XIRR 함수의 공통점은 모두가 NPV 함수의 결과가 0 이 될 때의 비율을 구하는 엑셀 재무함수라는 것이다. 이를 내부수익률이라고 부른다.

컴활 1급 실기 순 현재 가치 계산, 엑셀 NPV 함수 :
http://secstart.tistory.com/623

엑셀 XIRR 함수 기본 설명

설명 : XIRR 함수는 values 인수의 현금 흐름으로 내부 수익률을 반환한다. ( 현금 흐름은 반드시 정기적일 필요가 없다 )
구문 : XIRR( values, dates, [guess] )
인수 :
    values : 현금(투자액과 순이익) 흐름을 포함하는 셀 범위 참조. 최소 하나의 음수와 양수를 포함.
    dates : 현금 흐름과 일치하는 일련의 날짜. 셀 참조나 함수식의 반환값 사용.
    [guess] : 추정 내부 수익율. 옵션 인수, 생략시는 기본값 10% 사용.

    노트1 : dates 인수의 첫 번째 날짜는 투자( 대출 )의 시작이고, 이후의 날짜는 소득일자이다.
    노트2 : [guess] 인수는 함수의 수렴을 위한 반복 작업을 효율적으로 한다.

    참고1 : dates 를 텍스트 형태로 직접 전달한다면 잘못된 결과를 얻을 수 있다.

엑셀 XIRR 함수 사용 예제

초기 투자액 5백만원은 B4 셀에, 향후 4 차례에 걸친 순수익금은 B5:B8 범위에 표시된다.
투자액을 대출받고 순수익금들이 회수된 현금 흐름의 날짜는 C4:C8 범위에 표시된다.
이제 예제에서 요구하는 회차별로 내부수익률을 구해보자.

[그림1] 엑셀 XIRR 함수로 비정기 내부수익율 구하기

사용 예제1 : =XIRR( B4:B6, C4:C6, -20% )
사용 결과1 : -97%

투자액( 대출금 )과 순이익이 포함된 현금의 흐름을 범위 B4:B6 참조로 전달하였다. 투자액은 음의 값으로 표현되고 수익금은 양의 값으로 표현되어 values 인수에 포함되어 있다.

순수익의 회수가 비정기적이라 XIRR 함수를 사용하였고 따라서, 회수되는 날짜가 인수로 필요하다. dates 인수로 투자일과 1차, 2차 수익금이 회수된 날짜 범위를 전달한다. 추측되는 내부수익률을 마지막 guess 인수로 전달한다. 이는 옵션 인수라 생략하면 10% 의 기본값이 지정된다.

2차 수익금 회수까지는 순이익이 투자액에 많이 미치지 못한다는 것을 반환값 -97% 가 음수로 알려준다.

사용 예제3 : =XIRR( B4:B8, C4:C8, 100% )
사용 결과2 : 110%

values 인수로 B4:B8 범위가 전달되었다. 이는 투자액과 이후 4차 수익금까지를 포함한다. C4:C8 의 수익금 회수 날짜를 dates 인수로 전달한다. 내부수익률 추정값으로는 100% 가 전달되었다. ( 노트2 )

XIRR 함수식으로 얻은 비정기적인 4차 수익금까지의 내부수익률은 110% 이다.

엑셀 XIRR 함수 오류

XIRR 함수에서 발생하는 주요 오류이다.

#NUM! 오류 :

    - values 인수와 dates 인수 범위( 배열 )의 길이가 다르다면 오류 발생
    - values 범위( 배열 )에 적어도 1개씩의 음수값과 양수값이 없다면 오류 발생
    - 나머지 모든 dates 의 날짜가 첫 번째의 날짜보다 앞선다면 오류 발생
    - 100 회 반복 후에도 수렴된 결과를 얻지 못한다면 오류 발생

#VALUE! 오류 :

    - dates 인수로 전달된 날짜 중 하나라도 인식할 수 없는 경우 오류 발생

엑셀 XIRR 함수에서 핵심은 '비정기적' 이란 단어이다. 비정기적으로 발생하는 현금 흐름에 대한 내부수익률을 계산하는 것이 XIRR 함수인 것이다.
 
내부 수익률은 투자의 수익성을 나타낸다. 따라서, 투자를 선택하는 방법으로 비지니스에 주로 이용된다.
엑셀에서는 이 내부수익률을 계산하는 함수로 IRR 함수, MIRR 함수, XIRR 함수가 있다. 세 함수의 차이를 알고 필요에 따라 활용할 수 있기를 바란다.

머리 아프지만 열공하세요 ~~.

내부 수익률 계산 함수 :
[기타 함수] 엑셀 IRR 로 내부수익률 구하기 - 투자수익성 :
http://secstart.tistory.com/786
[기타 함수] 엑셀 MIRR 로 수정 내부수익률 구하기 :
http://secstart.tistory.com/801

마늘빵.