Fee Report by Fund and Account

[WeekendRider]

Is there a report that will calculate and show fees by fund for each sub-portfolio?

I have a sub-portfolio called Managed Accounts that has a number of sub-portfolios that represent each client (client sub-portfolios.) Within those client sub-portfolios I have sub portfolios that represent various accounts for the client (e.g. IRA, Roth, Individual, etc. - account sub-portfolios.) Fees are tiered and calculated based on the client's total assets. I'm not having a problem with the actual fee setup and I get the correct fees by client-account sub-portfolio.

I need a report to submit to the custodian showing fees by account sub-portfolio that is broken down by each mutual fund held in each account. I can't seem to find such a report. I have created a custom report that calculates fees outside of the built in method (I created a user-defined field with an equation) that seems to work but the totals by client at the end of the report are not correct. Additionally, since the fee is calculated outside of the built-in fee functionality, I need to have a way to exclude certain accounts that I do not charge fees against.

[Mark]

Hi WeekendRider,

Sorry, the management fee is not available for display at the investment level. It is calculated at the sub-portfolio level where you have assigned a management fee method.

Additionally, since the fee is calculated outside of the built-in fee functionality, I need to have a way to exclude certain accounts that I do not charge fees against.

Are you asking about how to do this when calculating a management fee, or for your custom field? For the management fee case you can assign this sub-portfolio to be "No fee", for it to be excluded from the higher level calculation. For a custom field, you'd have to build a sub-portfolio hierarchy without this sub-portfolio, as you can't exclude a sub-portfolio from a report calculation. You could make use of link sub-portfolios, but it is some effort to build this...

[WeekendRider]

Well can you consider adding the ability in a future release?

I understand how to use the built in feature and how to exclude accounts, so that is not what I'm asking.

How can I get what I need from Fund Manager? That is my bottom line question. I've built a custom report as I've said but the totals by client sub-account at the end of the report are not correct, even though the detail is correct.

[Mark]

Hi WeekendRider,

Yes, we can consider adding this.

What type of management fee method are you using? Some can get quite complicated, and some are more simple. For example: "% of Ending Value", etc... If you're using one of the more simple ones you might be able to reproduce this with a user-defined custom field.

[WeekendRider]

It's a simple two tiered formula base on the end of quarter value of all accounts of a given client. I've created a custom account that seems to calculate the fees by fund correctly but as I've said, the totals by client at the end of the report are not correct. So, bottom line, how do I create a custom report to get the data I need?

[Mark]

Hi WeekendRider,

You're using multiple tiers, where each tier has a different percentage? If so, this isn't so simple to calculate on a per-investment basis. When FM does this calculation, it just adds up all the investment values, and then calculates the fee off the total portfolio value. One way to figure out the per-investment portion would be to start with the total fee, and then allocate it on a percentage basis among all the investments in the portfolio. You can't determine the fee just from the investment's perspective, as it depends on what the total portfolio value is worth.

[WeekendRider]

Ok, so how do I do this. How do I calculate the fee and then allocate it to the underlying funds?

[Mark]

I don't think there is a great way to do it. You could accomplish this with multiple steps:

1) You can use the Management Fee report to get the fee for each sub-portfolio.
2) You would then need the value percentage of each investment and multiply this percentage by the management fee. You could do this in a user-defined custom report field with an equation like this:

5000*EndP_parent/100

where you replace 5000 with whatever the management fee is for the investments in this sub-portfolio. List by investment and turn off sub-totals. Run the report for only the sub-portfolio that had the (5000) management fee.

This new defined field will give you the percentage by investment of the management fee.

[WeekendRider]

Are you suggesting doing this account-by-account, manually changing the fee for each account and then running the report?

[Mark]

Hi WeekendRider,

I'm just answering your question on how you could possibly do it. As I mentioned previously, this is not a supported feature. Management fees are calculated/reported at the sub-portfolio level. If you _really_ want to have FM calculate these numbers for you at an investment level, this is the only way I could think of to accomplish this currently.

[WeekendRider]

It's just frustrating as this is a basic need of an investment advisor. I shouldn't have to export data to a csv and do the calc in a spreadsheet.

I have made a custom report with the following fee formula that seems to work:

Fee Schedule:
a% of the first $x assets (aka m), plus
b% of assets between $x and $y (aka n), plus
c% of assets in excess of $y

(IF( REPORT_SUBTOTAL(End_value)<x,REPORT_SUBTOTAL(End_value)*a,IF( REPORT_SUBTOTAL(End_value)<y,((REPORT_SUBTOTAL(End_value)-x)*b)+m,((REPORT_SUBTOTAL(End_value)-y)*c)+m+n))/4)*(End_value/REPORT_SUBTOTAL(End_value))

The problem is that the totals by customer at the end of the report are wrong. Is there a way I can get you the report and you can look at it and help me fix it?

[WeekendRider]

Also, is there a way in a user-defined formula to condition on if "Management Fee Method" = XXX then do YYY otherwise do ZZZ?

[Mark]

Hi WeekendRider,

this is a basic need of an investment advisor

This is surprising to me, as I've never received this feedback or feature request before. In my experience only the management fee for an account is needed. That is the only way the Management Fee method in Fund Manager has ever worked. I'm curious why you're looking for the breakdown by investment?

The problem is that the totals by customer at the end of the report are wrong. Is there a way I can get you the report and you can look at it and help me fix it?

Sure. I'll send you an email, where you can reply with whatever you'd like me to look at. I suspect I can guess the problem however. The function REPORT_SUBTOTAL() evaluates for the parent of each line item. So, if you have several clients at some sub-portfolio hierarchy level, the REPORT_SUBTOTAL() function will be calculating based off the level of hierarchy above this, which is for all your clients put together. Your function would work at the investment level, but won't work at the client level in a report containing multiple clients, as REPORT_SUBTOTAL() at the client level would be analyzed for sub-portfolio of all your clients.

Also, is there a way in a user-defined formula to condition on if "Management Fee Method" = XXX then do YYY otherwise do ZZZ?

Sorry, no. There is no "Management Fee" field available to select in a Custom report. This information is available only in the Management Fee or Invoice report, and only at a sub-portfolio level.

I have another suggestion however on how you might be able to get the data you want more easily. There is an option in the Management Fee Method dialog box called "Report Fees at Account Level" that is available when using "Tiered %". If you turn on this option and you also put each of your investments in their own sub-portfolio, with an account number (it could be any account number, just non-empty), you will get the management fee reported out on each one of these sub-portfolios, and if they each only contain your 1 investment (or however you want to group them), you'd get the data you're looking for.