Mgt Fee / Incentive fee calc based on Hurdle rate and High Water Mark

[sillynanny]

Hello All,

I have a very complicated task on figuring out an excel formula to calculate the following

1% mgt fee, 15% Incentive fee with a Hurdle Rate of index+2% (hard hurdle rate is calculated on all profits above the hurdle rate), High Water mark.

The incentive fee is accrued monthly, so if we charge one month based on the hurdle rate and high water mark and next month we have a negative cumulative return for the year then the incentive fee needs to be reimbursed.

below is an example of 3 months that was calculated by a system, but i need to calculate historic numbers.

Gross Fee Rounded	index	System Calculated
-0.57	-3.46	-0.65
2.75	4.57	2.52
0.02	0.84	0.08

<colgroup><col><col><col></colgroup><tbody>
</tbody>

First month, we only charged mgt fee since the return was negative.
second month we charged mgt fee and incentive fee since the cumulative was higher than the hurdle rate
third month we charged mgt fee not incentive plus we had to give back the incentive fee since the cumulative was lower for the ytd

i need to do this for the past three years which also needs to take into consideration the high water mark

below i provided a sample returns for gross fee and index

Gross fee Index

0.65%	2.37%
1.72%	3.43%
0.31%	0.04%
1.40%	2.96%
0.30%	-1.13%
-1.12%	-1.67%
-0.34%	-2.03%
-3.81%	-5.43%
-4.18%	-7.03%
5.14%	10.93%
-0.10%	-0.22%
0.08%	1.02%
2.82%	4.48%
2.85%	4.32%
1.18%	3.29%
0.16%	-0.63%
-2.05%	-6.01%
0.35%	4.12%
0.72%	1.39%
1.45%	2.25%
0.83%	2.58%
0.20%	-1.85%
0.88%	0.58%
1.35%	0.91%
2.93%	5.18%
0.49%	1.36%
1.41%	3.75%
-0.61%	1.93%
0.81%	2.34%
-0.78%	-1.34%
1.27%	5.09%
-0.75%	-2.90%
1.54%	3.14%
1.08%	4.60%
1.25%	3.05%
1.29%	2.53%

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Thank you very much in advance

 

[StephenCrump]

Welcome to MrExcel!

This should be relatively straightforward to put into Excel, but first we'll need to understand how your numbers are calculated ...

I am guessing that what you call the Gross Fee Rounded of -0.57 is in fact the monthly investment return pre fees? So with a 1% p.a. management fee this reduces to -0.57 - 0.08 = -0.65% return after management fee?

We'll also need to understand what history is being carried forward, how the hurdle is calculated (I'd expect +2% p.a. but given the index volatility of whatever this investment is, perhaps it's +2% per month?), and how the high water mark is applied/reset?

 

[sillynanny]

Hi Stephen, thanks for taking the time to reply.

Gross fee is the monthly investment return pre fees. Net fee would come to -0.65%. You are correct. The hurdle is +2% annual and the high water mark resets at year end.

 

[StephenCrump]

OK, I got lucky guessing this one.

There are plenty of smart people on this Forum who can convert complicated formulae into Excel. But we need to understand your formulae first, i.e. it's easier if we know the answers you're expecting to see!

- Can we assume the table you have provided commences with the first month of a financial year, with no carry forwards?

Can you please:

- Show us how the incentive fee is calculated/reimbursed for these months (including how cumulative actual and hurdle rates are calculated).

- Clarify what you mean by high water mark? You said this resets at year end, so I assume it means a ratcheting up of the target based on the best year-to-date performance (i.e. rather than a carry forward of underperformance "losses" from previous years before an incentive fee can be paid this year)?

 

[sillynanny]

Let me start with an easier one maybe this one will make more sense. I am having a hard time explaining.

New fund, we charge 0.75% annual mgt fee and 15% on any excess of 5% annual.

lets say fund started on Jan 1 with 2%, so we charge .75% mgt fee and whatever is in excess of 2% and 5% hurdle annualized 15%.

So if feb goes -3% then we have to give back the incentive fee back in feb.

High water mark is every end of year. So we started at 0, if at the end of year its lets say 6% then that's the new high water mark, next year if the fund does better than 6% and 5% annualized then we charge incentive. so if year 2 is 15% then that's the high water mark, if year three (end of year) is less than 15% then we don't charge until we pass the 15%.

let me know if that makes sense

 

[StephenCrump]

let me know if that makes sense

Yes, as a concept, performance fees make sense. However, they can be implemented in a variety of ways, so we can't guess what answers are correct for your circumstances.

Can you perhaps start by populating the performance fees for this hypothetical new fund scenario (and explain how these are calculated):

Excel 2010

	A	B	C	D	E
1		Actual return	Index	Monthly	Performance
2		pre fees	return	Management	Fee/Reimbursement
3		(Monthly, not p.a.)	(Monthly, not p.a.)	Fee
4	January	3.00%	1.50%	-0.0625%	?
5	February	0.95%	1.00%	-0.0625%	?
6	March	-1.00%	-3.00%	-0.0625%	?
7	April	0.30%	0.00%	-0.0625%	?
8	May
9	June
10	July
11	August
12	September
13	October
14	November
15	December

<tbody>
</tbody>

Sheet1
​

 

[sillynanny]

See below how I calculated to get to where I want. I need the highlighted field to be my main formula, note that Dec went negative and I used a different formula to get to where I want. I am trying to consolidate all of the below into one formula

A	B	C	D	E	F	G	H	I
date	gross	net of mgt	YTD	YTD Hurdle	YTD-Hurdle	15% on YTD-Hurdle	YTD Net of Incentive fees	MTD Net of incentive Fee
		0.75%		5%		15%
10/31/2014	1.1000%	1.0375%	1.0375%	0.4167%	0.6208%	0.0931%	0.9444%	0.9444%
11/30/2014	0.2000%	0.1375%	1.1764%	0.8333%	0.3431%	0.0515%	1.1250%	0.1789%
12/31/2014	-0.8000%	-0.8625%	0.3038%	1.2500%	-0.9462%	-0.0505%	0.3038%	-0.8120%
10/31/2014	1.1000%	*=+B12-($C$11/12)	*=FVSCHEDULE(1,C$12:$C12)-1	*=$E$11/12	*=+D12-E12	*=IF(F12<0,I12-C12,F12*$G$11)	*=+D12-G12	*=+C12-G12
11/30/2014	0.2000%	*=+B13-($C$11/12)	*=FVSCHEDULE(1,C$12:$C13)-1	*=$E$11/12*2	*=+D13-E13	*=IF(F13<0,I13-C13,F13*$G$11)	*=+D13-G13	*=(1+H13)/(1+H12)-1
12/31/2014	-0.8000%	*=+B14-($C$11/12)	*=FVSCHEDULE(1,C$12:$C14)-1	*=$E$11/12*3	*=+D14-E14	*=IF(F14<0,-(I14-C14),F14*$G$11)	*=+D14	*=(1+H14)/(1+H13)-1

<tbody>
</tbody>

 

[sillynanny]

Sorry the 12/31/2014 -0.8120% cell should have been highlighted as well.

 

[StephenCrump]

I am trying to consolidate all of the below into one formula

I agree it would be good to have the same formula copied down each column, but I think you're probably better off continuing to use helper columns .. it makes it much easier to check what's going on month by month, what's being carried forward etc.

Some more questions/comments:

- These latest calculations effectively benchmark against a nominal return of 5% p.a. I'd expect to see performance fees calculated relative to a benchmark of the relevant Index + x% p.a., as you said in your original post?

- I don't agree with the way you've applied the 15% performance fee in month 2. After one month, the investor is 0.62% ahead of benchmark (after management fee), so you take out a 0.9% (i.e. 15% of 0.62%) performance fee. After two months, the investor has slipped back to being only 0.34% ahead of benchmark. Instead of reimbursing part of the first month's performance fee, you take a further 0.05% (= 15% of the entire excess)? So in simple terms, you've taken 0.14%/0.34% = 41% as a performance fee.

- In practice, won't you be calculating performance fees on money-weighted, rather than time-weighted monthly returns? Otherwise your performance fees/reimbursements could be hugely distorted by cash flows. For example, suppose you charge a performance fee of 0.9% one month, and fully reimburse it the following month. If I have $1m invested in the first month, and $5 million in the second month, are you going to credit 0.9% to my entire $5m balance?