Forum Discussion
Tiered Calculation Formula Help
Yes! That is correct.
So.. For an account that is $85000 the fee would be $1164.50
and an account that is $147000 the fee woudl be $1887.00
Thank you for your help!
I woke up this morning realizing that the solution to your request is to emulate the way the tax table works. So I've extended your table (and given it the range name "FeeTable" in the formula that results. Here's the new table:
And here's an example of the formula and an explanation of the components. Cell A11 contains the Account Balance for which you're going to calculate the appropriate fee and it is used in each of the VLOOKUP formulas, in each case just retrieving one of the data elements in that row of FeeTable.
=VLOOKUP(A11,FeeTable,3,1)
+
(VLOOKUP(A11,FeeTable,5,1)
*
(A11-VLOOKUP(A11,FeeTable,6,1)))
- The first VLOOKUP instance retrieves the info from column C, the "base fee" based on reaching that tier
- the second VLOOKUP retrieves the percentage to be applied
- and the third VLOOKUP retrieves the number from column F, which is then subtracted from the balance itself
- The formula then concludes by ;multiplying the excess $ by the applicable percentage and then adding that to what I've called the "base fee"
I've attached the resulting spreadsheet. Try it out. Have fun. Let me know if you have any questions.
Thank you! mathetes
These tiered fee schedules are common for financial companies. I thought a VLOOKUP could be a solution.
Appreciate the help!
These tiered fee schedules are common for financial companies.
And are they commonly computed in this fashion as well?
I have dealt with financial companies myself. (I am a retiree, living on my IRA.) I just always assumed that the lower fees were it (the way I first approached this). I don't recall anybody ever explaining the proposed fee as "Well, John, we charge x.xx% on the first $100,000, a shade less on the next $400,000, another shade less up to a million, and so on. Any questions?" And in articles on the subject, there's just the reference to "At that level, people are usually paying x.xx%" rather than (again) some convoluted explanation of how they get there.
On the other hand, I can see how it makes sense. The net effect of doing it as we've now done it (as you always wanted it) is that the fee very gradually declines, on a dollar by dollar basis, as the total asset base increases.
Or increases as the asset base declines. Or does it? It certainly would if you followed it mechanically. But may I ask you a customer relations question? Say I'm one of your clients, living (as I've already said) on my IRA's Required Minimum Distribution, and that as I approach my 80s, my asset base has started to decline. But I've been a client of yours for decades. And at its peak, my IRA deserved a nice low fee rate. I'm wondering if there's a clause in your contracts that entitles long-time customers to that lowest fee rate attained, even as their IRA (plus whatever other total assets you might be managing) starts to decline.
Just curious. It's kind of the converse of the "Cap" that @Riny_van_Eekelen incorporated in his ingenious solution.
From my tenure in the industry.. I have seen a lot of different iterations of how advisors and asset managers charge fees. Some are a flat percentage, some are commission based, etc. The company I work had charged this tier schedule in the past and it is a common way to do so in the industry. We are moving away from it now.
However, you are correct in that in that as your asset base goes down (it does not matter the fee rate you previously benefited from) your fee will go up. Most companies do apply this schedule to your overall Household of accounts so it potentially easier to hit those break points. But yes, as you take those RMDs your asset base goes down and fees could go up.