So, let's say you have a deposit, an yearly interest rate and a fixed fee for every time you compound. I need to find out what would be the best compounding period so that the the win will overcome the tax. To do that, I can make an excel that would calculate for different compounding rates and chose the best one but I can't figure out the correct formula.
- I have tried transforming the fee into negative % but it is not right because as the principal grows, the fee remains constant.
- I have tried to use the compound with contribution formula and making contributions negative but still doesn't feel right. For P = 10000, r = 50%, fee = 1.6, t = 1 year, I get the best period n = 16.59 which is about 22 days. I used such a big rate to make errors more obvious.
Formula used: $Final = P*(1+\frac rn)^n+\frac{fee*(1+\frac rn)^n-1}{\frac rn}$
I believe that the problem is the fixed fee that counts less and less as the principal grows, so probably I should just calculate for smaller periods but I wanted to ask if anyone knows a better way.
Let's let $(1+\frac{r}{n})=x$, and fee = $f$.
We start out with $P$, and then we have $Px-f$ after first compound.
Next, we have $x(Px-f)-f = Px^2-fx-f$
Then, $x(x(Px-f)-f)-f=Px^3-fx^2-fx-f$.
After $t$ compounds, we have $Px^t-\sum_{i=0}^{t-1}fx^i$, and after $n$ compounds we have $\displaystyle Px^n-\sum_{i=0}^{n-1}fx^i$.
We can rewrite $\displaystyle\sum_{i=0}^{n-1}fx^i=f\cdot\dfrac{x^n-1}{x-1}$.
Now, wrapping everything together, the final amount of money is $$\displaystyle P\left(1+\frac{r}{n}\right)^n-f\dfrac{\left(1+\frac{r}{n}\right)^n-1}{\left(1+\frac{r}{n}\right)-1}$$.
This is similar to the formula you're using but the $f$ is multiplied over the entire fraction, not just the compounding rate. And this only works with a fixed fee, not a percentage fee. If it's a percentage fee, then it's entirely different.
If you have a fixed percentage fee of $f%$, that means that each time you compound by $1+\dfrac{r}{n}$, you must also multiply by $(1-f)$. So as long as $(1+\dfrac{r}{n})(1-f) > 1$, the growth will overcome the fee. From the example numbers you gave, I think you're talking about a percentage fee, not a fixed fee.
Now to answer what I think you need: you must maximize $f(x) = (1+\frac{r}{x})^x(1-f)^x$: this you can easily do in Excel.