들쭉날쭉 입금일, 입금액 - 연평균 성장률(연이율, GAGR) 계산하기

참고) 연평균 성장률이라는 건 결국은 작년 성장한 결과에 올해 성장률을 적용하는 것.
즉 연복리 이율과 같은 의미임을 알 수 있다. 

이런 데이터가 있다면 연평균 성장률(GAGR, 연복리이율)은 어떻게 계산할 수 있을까?

입금일, 입금액에 규칙이 없기 때문에
각 입금액 별로 경과일수와 예상이율을 반영하여
복리 적용된 원금 + 이자(원리합계)를 계산하고
이를 합쳐 전체 원리합계를 계산한 뒤,
총잔고와 전체 원리합계를 비교하면서
예상이율을 조절하여 맞출 수밖에 없다. 

알고리듬을 배운 사람들은
그나마 이분탐색 같은 것을 이용해서 ....

참고)
경과일수는 "기준일 - 입금일"이다. 
연복리는 원금 * (1 + 예상_이율) ^ (경과일 / 365)이다. 

실제로 해보면 상당히 귀찮은 일이다. 

그런데~ 이 귀찮은 것을 해주는 엑셀함수가 있다. 

XIRR~!

https://support.microsoft.com/ko-kr/office/xirr-%ED%95%A8%EC%88%98-de1242ec-6477-445b-b11b-a303ad9adc9d

Excel에서는 반복 기법을 사용하여 XIRR 함수를 계산합니다. 계속 새로운 이자율을 사용하면서(guess에서 시작하여) 결과의 오차가 0.000001% 이내로 정확해질 때까지 반복합니다. 100번 이상 반복한 후에도 결괏값을 찾지 못하는 경우 #NUM! 오류 값이 반환됩니다. 다음 식을 만족할 때까지 이자율이 변하게 됩니다.

엑셀도 나랑 똑같이 반복해서 계산을 하는 군화 ㅠ,.ㅠ 

사용법은 다음과 같다. 

이분탐색을 안다면
추정 수익률을 넣으면
좀 더 효율적으로 탐색이 가능함을 알 수 있을 것이다. 

주의해야 할 것은
입금을 + 출금을 -로 표기했다면,
최종 잔고에 -를 붙여서 출금으로 처리해줘야 한다는 것. 

액셀 개발자 형님들이
별도로 최종 잔금을 입력하는 것보다
출금으로 처리하는 게
한방에 해결하는 방법이라 이런 방법을 선택했나 보다. 

깔끔하게 계산이 잘 된다. 

구글 스프레드 시트에서는 필터 함수로 특정 조건을 만족하는 셀들만 평가할 수도 있다. 

=XIRR(
  FILTER(B:B, A:A <= A2),
  FILTER(A:A, A:A <= A2)
)