Retroactively calculating fees in one formula.

**MCCCLXXXV** · 07-29-2009, 10:09 AM

Hello all,

I have a somewhat strange project here. My input is a list of daily account balances. These balances are gross of any fees. We want to re-calculate these balances as if fees had been charged and removed from the account at the end of each month. The fee schedule is based on the following procedure:

At the end of each month, find the average daily balance of the account. Multiply this by a constant [specifically the constant is (1.02^(1/12)-1)]. That is the fee that would have been charged at the end of the month. The month-end balance would be reduced by this amount and this new amount would be the invested amount that grows based on the same returns that were used to obtain the gross balances. This process is repeated for every month.

I've been able to do this manually, but I want to know if there is some formula or set of formulas that would be able to do this for me automatically. I've attached a spreadsheet that shows my desired results. The starting gross balances are in column E. Column D shows the returns associated with these balances. My end of month fee is calculated in F. Here is where I am looking for a new formula. What I did here was manually paste my averaging formula at the end of each month and manually adjust each range to average over the appropriate days. Column G re-invests the balances after fees and H shows what the new end of day balances would have been.

I've tried using a average(if(.... formula in F, but I keep getting circular reference warnings.

Hopefully this is clear. Thanks for any help.

**windknife** · 07-29-2009, 11:23 AM

Try this,

F2

Please Login or Register  to view this content.

copied down.

You can also see attached.
Hope this helps,
WindKnife

**MCCCLXXXV** · 07-29-2009, 11:55 AM

hi, thanks for the response. Can you explain this formula? It actually is not working exactly how I want (although it does come close to the same fee values).

Also, there should be no fee for Jun 30 06, so I just deleted the formula from there. But I still don't get the same numbers as when I did it manually.

Specifically, I don't understand the LOOKUP part of the formula. You are looking up the value "2" in the vector that results from 1/($F$1:F21<>0) and returning the corresponding value in the row associated with column "2:Gx", where x is the current row I'm in? I dont get that at all.

Thanks for your help though, this is helpful in getting me on the right track. I just want to be able to get the exact same value I calculated manually.

**windknife** · 07-29-2009, 12:32 PM

I made a mistake, try this:

F2

Please Login or Register  to view this content.

copied down.

Lookup function is used to find the last nonzero position in this case.
You can also see attachment.
Hope this helps,
WindKnife

**MCCCLXXXV** · 07-29-2009, 02:08 PM

That formula works - thank you very much. But I still don't understand how. I mostly don't understand the 1/($F$1:F44<>0) part. what does that return?

Also, this formula seems to be very dependent on the specific rows that are being used. Anyone know if there is a solution that is independent of what row the data is in?

Thanks

**windknife** · 07-29-2009, 10:57 PM

I try to explain lookup(2, 1/($F$1:F44<>0), row(2:45)).
First, about $F$1:F44<>0, only row 1 and row 22 are true, others are false in your data.
Second, 1/($F$1:F44<>0), only row 1 and row 22 return 0, others return #DIV/0!.
Third, lookup(2, 1/($F$1:F44<>0, row(2:45)), If LOOKUP cannot find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.
Therefore, 2 can't find in 1/($F$1:F44<>0), so row 22 must be return. Then, I shift one row in row(2:45), so 23 will be return.

Hope this clear.
WindKnife