그나마 다행입니다. 국회의원 여러분 수고하셨습니다.


여러 조건에 맞는 값을 찾아서... EXCEL/VBA

조건에 맞는 값을 찾는 엑셀의 대표적인 워크시트함수는 VLOOKUP()이다. 그러나 VLOOKUP은 자주 사용하기는 하지만 기능면에서 약간 아쉬운 함수이다.

우선 가장 먼저 체감하는 단점은 찾으려는 값이 테이블의 첫 번째 컬럼에 있어야 한다는 점이다. 그래서 INDEX와 MATCH의조합을 동원하기도 한다. 두 번째 단점은 여러 개의 조건을 만족하는 값을 찾지 못하는 것이다. 그러나 이것 역시 조건을 &(앰퍼샌드)로 묶고 조건을 검사할 컬럼들도 묶어 INDEX와 MATCH함수의 조합으로 해결할 수 있다(다중조건을 만족하는 텍스트 값 찾기)

그러면 VBA에서라면 어떨 까? VBA는 엑셀 워크시트함수에 비해 자유롭게 활용가능하지만 딱 그 목적에 맞는 함수를 제공하진 않는다. 다음은 반복문을 사용하여 VBA로 값을 찾는 경우이다. 펀드코드와 종목코드 두 조건을 만족하는 평가손익 값을 찾는 것이다.
Sub Multiple_Criteria()
Const COL_FCODE As Long = 1
Const COL_JCODE As Long = 5
Const COL_PL As Long = 20

Dim s As Worksheet
Dim fcode As String
Dim jcode As String
Dim pl As Double
Dim rng As Range

Set s = Sheet1
fcode = "10010"
jcode = "KR7207940008"

For Each rng In s.Range(s.Range("A2"), s.Range("A2").End(xlDown))
If rng.Offset(0, COL_FCODE) = fcode And rng.Offset(0, COL_JCODE) = jcode Then
pl = rng.Offset(0, COL_PL)
Debug.Print Format(pl, "#,###")
Exit For
End If
Next
End Sub
위의 코드는 For 반복문을 사용하는데, 가장 무난한 방법이다. 만일 파이썬이나 R처럼 세련되게 반복하지 않고 할 수 있는 방법이 있지 않을 까? 그래서 궁리해본 것이 엑셀의 워크시트함수중 SUMIFS 함수이다. SUMIFS 함수는 여러 개의 조건에 맞는 값을 합산하는 것이다. 여러 개의 조건을 만족하는 값이 하나라면, 즉 데이터베이스로서 검색할 조건이 Unique한 키이고 가져 오려는 값이 데이터 타입이 숫자이면 가능하다(사실 이것이 한계점이기도 한다. 숫자가 아닌 데이터를 가져올 필요도 있을 경우도 있다) 다음은 SUMIFS 함수를 사용하여 반복하지 않고 값을 검색하는 예이다.
Sub Multiple_Criteria()
Const COL_FCODE As Long = 2
Const COL_JCODE As Long = 6
Const COL_PL As Long = 21

Dim s As Worksheet
Dim fcode As String
Dim jcode As String
Dim pl As Double
Dim rng As Range

Set s = Sheet1
fcode = "10010"
jcode = "KR7207940008"
Set rng = s.Range(s.Range("A2"), s.Range("A2").End(xlDown).End(xlToRight))
pl = WorksheetFunction.SumIfs(rng.Columns(COL_PL), rng.Columns(COL_FCODE), fcode, rng.Columns(COL_JCODE), jcode)
Debug.Print Format(pl, "#,###")
End Sub

산출평균과 기하평균에 따른 성과보수 차이 금융공학


성과보수를 계산하려고 엑셀에서 직접 수식 만져보는 중이다(이제 시작하는 단계라서 가능한 간단한 시나리오를 가정해본다) 애초에 주어진 자료에는 결산과 같이 성과보수가 계산되는 시점에 한번에 성과보수를 계산하였는데(앞서 포스팅에 나온 수식을 사용한다), 각 투자금액마다 계산하는 방법이 필요하다고 생각되어(이연성과보수도 계산하려면) 시작했는데, 재미삼아 수익률의 산술평균 대신 각 투자금액을 독립적으로 분리하여 각 투자금액별 수익율에 대한 기하평균을 사용하여 계산해보았는데, 평균방법에 따라 결과가 다르듯이 성과보수 금액도 달라진다(당연한 얘기이지만)

세 차례에 거쳐 1억을 투자하여 결산때에는 15%(산출평균기준)의 누적수익률이 나왔다. 이때 허들레이트가 5%, 성과보수율을 15%로 잡고 성과보수를 계산하면 투자금액 3억 * MAX(누적수익률 15%- 허들레이트 5%, 0)*성과보수율 15% = 4백5십만원 정도 나온다.
이걸 매기간 별로 하는 경우 SUM( 각 투자금액* MAX(누적수익률 15%- 허들레이트 5%, 0)*성과보수율 15% )이 된다.

그러나 산출평균대신 매기간 수익률을 기하평균으로 하면 누적수익율은 약 14%정도 나오게 되고 따라서 성과보수도 약 60만원 정도 줄어든다. 펀드운용하는 입장에선 기하평균을 사용하면 60만원 정도 성과보수를 못받아서 손해보는 셈인데, 아무래도 이런 방식으로는 계산하지 않을 것이다.

우리가 해봐서 아는 데...


성과보수를 계산하려니... 금융공학

헤지펀드에서 성과보수 계산하는 것이 의외로 어렵다는 것을 최근 실감하고 있는데, 다양한 경우의 수(결산시 기준가가 1,000원이상인가? 아닌가? 중간에 추가설정하는 경우, 일부해지/전액해지 등등)를 생각하는 게 사실 머리 아픈 일이다.

일단 성과보수 금액은 수익자의 복수의 설정에 대하여 각각 독립적으로 계산하고 합산하여야 한다. 수식으로 따지자면

성과보수 = SUM( 기준금액 × Max( 수익자 계좌별 수익률 - 목표수익률, 0 ) × 성과보수율 )

1) 수익자계좌별 수익률 = (보수계산기간종료일의 1좌당 평가금액 ÷ 보수계산기간초일의 1좌당 평가금액) - 1
2) 기준금액
  • 회계기간 종료시 : 계좌별 보유수익증권 원본금액(재투자된 이익금 포함)
  • 일부해지 또는 전부해지시 : 계좌별 해지원본금액(재투자된 이익금 포함)
3)목표수익률(Hurdle Rate)

다들 내가 이거 해결해주길 바라는데,...사실 나도 누가 이것 좀 해주었으면 합니다(아~ 머리 쓰기 싫어). ㅎㅎ

1 2 3 4 5 6 7 8 9 10 다음