calculate fees from table

I am trying to automate something I've been doing manually. Let me explain.

1.) I am givien information about fees associated with a mutual fund product. A sample is reproduced in F4:F8.
2.) Next, this information is entered into the table. See A4:D12.
3.) Finally, from this table, I must calculate the fees associated with various account sizes.
4.) I did this manually in cells G15:G16 for 2 different account sizes. You can view my formulas.

Can something like this be automated? If so, can anyone help me get started? I don't know what code structures to use.

I like your approach where you are trying to calculate weights. I need to make this thing generic. I changed the example. I could get the weight of the first one correct. But the other ones I am still working on.

Why not make a custom function that does this painlessly? Plus if you ever need to use it in more than one workbook, you can easily put it in any other workbook as an add-in. All you'd need is this:

Please Login or Register  to view this content.

Just copy and paste that into the module portion of the VBA editor screen. To use the formula simply type in
=fee(*insert cell with the monetary value, or type in the monetary value yourself*) and it'll give you the applicable fees.

So for example, if cell A1 had 100,000,000 and you typed in cell B1 =fee(A1) you'd get 631,250. You could also type in =fee(100000000) and get the same result.

Hope this simplifies things for you. I also have the file attached demonstrating the formula.

But there is one small problem. The schedule I showed you was just a sample. The number 1%, 0.75%, 0.625%, 0.5 are not constants. The user inputs these. Also the user inputs the amounts. So I need something that is very flexible. I need generic code. I attached my sheet. It has two more examples.

Thank you for the try.

Hmmm...yeah it seems like a crazy formula would be the best way to do it then. I could tweak what I just gave you to account for the fact that both amounts and percentages will change but once you change them, you'll have to re-enter the formula again. Unless there was someway to rerun the function automatically once a cell changed or if a macro was written that would run through the range of the cells with the calculated fees and recalculate them...the new function would look something like this:

Please Login or Register  to view this content.

I mean, it works just fine but a macro or something along those lines would have to be written in the event that the user changes those values. With the way the function is written, it will only recalculate automatically when the account size itself is changed. A formula is probably a better, although messier, alternative.

I have attached my worksheet where I modified your code. Apparently, I didn't do a good job. If I did, I would have produced the correct values [I did those manually with the fee schedule that is inputted.].

Can you give this another look? I feel like I am closed and missing something small. It is critically important to be able to do calculations on the balance.

As far as the function recalculating automatically that isn't a problem. I can work around that.

Sure, I'll take a look at it and give something back to you later today (eastern time zone here).

The reason it wasn't working before was because of the formula you had in cell B12. Since the If formula returned a false value, the cell still began with "=If" which isn't a number thus returning the "#Value" error...I'd of never found that probably if you didn't specify that the cell had a formula in it lol. I also found out, through some quick research, that you can make every formula recalculate. You use the syntax

Please Login or Register  to view this content.

This makes Excel recalculate all formulas as soon as a change in any cell is made. This makes the function extremely flexible, as you requested.

The if statement you have in that cell brings up another question. Lets say the account value is $100M with the fees set up in three tiers of $15M...cell B12 would then equal $55M...which would mean that from values $45,000,001 to $55,000,000 would not be charged a fee...or at least Excel wouldn't know what the percentage fee actually is. Wouldn't a better way of doing it is just sum up however many tiers of data you have? So in case you have three tiers, each equal to $15M with each tier having its own respected percentage fee, you could say "Anything over $45M". To me that makes more sense. A few words of caution about the formula:

Cell B12 (cell D12 can be blank since I'd assume its possible that if an account is over a certain value, no fee would be charged on the remaining balance) must always have a value. The rest can be blank since it won't effect the calculation. I've set it up so that Cell B12 simply sums up the values of cells B5:B11. Any changes you make to cells B5:B11 or D5:D12 as well as the amount itself will instantly be recalculated. Heres the code:

Please Login or Register  to view this content.

I hope we're on the same page here, if I've misinterpreted the meaning of "on anything after" portion, please let me know. I've also attached the revised file.

This looks like it works. I've tested it a bit. Great job! I will have to study this code and really learn from it. The code strctures here are very useuful and applicable to other situations.

When I finish this thing, I will show you the final product.

Thank you so much.

No problem, glad I could help.

It is here.

I got your PM, I fixed the file as you requested. The new code is as follows:

Please Login or Register  to view this content.

Works just great.