Compounding with varying principal?

The question pertains to determining average rate of return per year over $n$ years when the final amount and principal invested each year is known and it is assumed that the principal is invested at the beginning of each year (as otherwise, the problem gets even more complicated I guess).

So, my principal would be : $P_1(invested~in~2012) + P_2(2011) + P_3 ...... = P$

My final amount = $A$

Using the formula for compound interest and assuming average rate of return over $n$ years is $x%$, we can propose the following formula:

$A = P_1(1+x\%)^1 + P_2(1+x\%)^2 + P_3(1+x\%)^3 +.......+ P_n(1+x\%)^n$

Assuming we know $A$, $P_1$, $P_2$, ....$P_n$ and $n$, how do we solve for x?

-

Even if all the $P_i$ are equal, there is no nice formula for the interest rate $r$ in terms of the $P_i$ and $A$.

The usual way to solve this kind of problem is by using a numerical root-finding procedure. There are a few specialized quick approximations that have been developed for the case when all the $P_i$ are equal. They are no longer much used, since a spreadsheet will quickly calculate $r$ to high accuracy. And in your case of variable deposits, the old approximation formulas were never useful.

-
+1 Great! Thank you for your response. I'm curious. You say since a spreadsheet will quickly calculate r to high accuracy. Would you mind giving me some pointers on how to achieve this? Specifically, I would still need to represent $r$ in terms of $P_i$ and $A$ to get this isn't it? –  Legend Dec 29 '12 at 18:42
Yes, you would have to insert that formula into the spreadsheet. I am pretty inexperienced at this, have only used spreadsheets for computing first approximations of grades! For a quick calculation, you could also use Wolfram Alpha (free). –  André Nicolas Dec 29 '12 at 18:46

You can't solve for it algebraically, you will have to solve for it numerically. (said so here as well)

As for how to do that, you can either try plugging in different interest values for yourself on a spreadsheet to see what gets close, or you can use Mathematica or Matlab or any other program that has a numerical solver. The one in Mathematica is called NSolve, you can read about how to use it here.

If your payments/principals are constant then you can calculate the interest rate using spreadsheets with the formula RATE(NPER, PMT, PV, FV).

-