Calculate the average rate of an investment with periodic deposits

Question

Let's say I make an investment with monthly deposits of 100€ (starts from 0 and add 100 every month).
After 10 years my account have 16.000€ (120 deposits of 100€).
How could I calculate the yearly average rate of this investment over the 10 years?
The interests are payed at the end of the year, and I would like to take into account the compound interests.
I have the following formula but I don't manage to isolate the rate:
$Ac = De * ( \frac{(1 + Ra)^{n+1} - 1}{Ra} - 1 )$
Ac: amount of money at the end (after the 10 years = 16.000)
De: amount of a deposit (100)
Ra: monthly rate
n: number of deposits (120)

Answer 1

For your specific case, as yW0K5o wrote in comment, you need to find the zero of function $$f(x)=161\,x+1-(1+x)^{121}\qquad \text{where} \qquad x=Ra$$ and for that, as already said, you need some numerical method.

You can have reasonable estimates considering that $x \ll1$. For example, using the binomial expansion or Taylor series, you would get $$f(x)=40 x-7260 x^2-287980 x^3+O\left(x^4\right)$$ which reduces to a quadratic equation; its positive solution is $$x=\frac{\sqrt{2041}-33}{2618}\approx 0.00465142$$ corresponding to $5.582$% which is not too bad compared to the exact solution.

Sooner or later, you will learn than, better than with Taylor series, functions can be approximated using Padé approximants. A rather simple one could be $$f(x)=\frac{40 x-\frac{11642740 }{1327} x^2} {1-\frac{50218 }{1327}x+\frac{878339 }{2654}x^2 }$$ leading to $$x=\frac{2654}{582137}\approx 0.00455906$$ corresponding to $5.471$% which is much better.

For an exact solution, Newton method would be easy. Considering the first two terms of the Taylor expansion, let us start using $x_0=\frac{40}{7260}=\frac{2}{363}$.

The iterates would be $$\left( \begin{array}{cc} n & x_n \\ 0 & 0.0055096419 \\ 1 & 0.0047266909 \\ 2 & 0.0045673223 \\ 3 & 0.0045606399 \\ 4 & 0.0045606282 \end{array} \right)$$

Answer 2

You can't isolate the rate algebraically. You need a numerical approximation. Spreadsheets have an IRR (internal rate of return) function, or you can make a table that assumes an interest rate and computes the final balance. Then use Goal Seek to adjust the interest rate to make the final balance correct. If you have all positive inflows and one withdrawal at the end there will be only one interest rate that works. If you have a mix of inflows and outflows along the way there can be more than one.

Assuming deposits at the beginning of the month and withdrawal at the end, so the last deposit earns one month interest, I find an annual interest rate of about $5.473\%$ works.