#1
|
|||
|
|||
Tiered Fee Calculations
I have to perform a tiered fee calculation as follows:
$0 - $1,000,000,000 = 0.535% (Cell K1) $1,000,000,001 - $2,000,000,000 = 0.485% (Cell K2) $2,000,000,001 - $3,000,000,000 = 0.435% (Cell K3) Over 3,000,000,001 = 0.385% (Cell K4) Net Assets = 2,000,452,222 (Cell C1) The fee will be charged in each designation. How can I write in a single cell how to calculate the fee based on the table above? Is it an IF statement nested? |
#2
|
|||
|
|||
I would use a lookup to get the fee from a table based on one of the tier endpoints (minimum or maximum). It would be much easier to write and debug than the nested if it would require.
|
#3
|
||||
|
||||
It would also be easier to maintain; you have a few rows and columns on a worksheet somewhere designating just what you said in your original post, and the VLOOKUP returns the correct percentage in one step, but if you change the ranges and/or percentages later then you need change only that one table and the rest of your references to it are updated automatically.
|
#4
|
|||
|
|||
Right. That is actually the biggest advantage and I'm so glad you pointed it out since it slipped my mind.
Also, I suggested to use one of the tier endpoints, either minimum or maximum. Minimum would be best since the minima are all specific. See attached. |
#5
|
|||
|
|||
This does not work as you need to charge the fees at the levels of assets. IE: first billion .535 the next billion .485 and so on. I think I got it, see what I did attached.
|
#6
|
|||
|
|||
OK...I don't really understand I guess. :-)
But if you got it figured out, excellent. Cheers! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Running Calculations in MS Word | ha2014 | Word | 1 | 02-05-2014 05:03 PM |
Table Calculations, VBA | SuzeG | Word VBA | 7 | 12-15-2013 07:12 PM |
How to do Calculations In a Text Box? | SteveF | Word | 4 | 09-27-2013 08:34 AM |
Can I do calculations with times? | cubsfan_1 | Excel | 1 | 01-14-2010 06:55 PM |
Having problems with Duration calculations. | bgane | Project | 1 | 03-20-2006 06:36 AM |